In a setup having an enterprise application depending on a database instance where some tables are defined as nicknames to tables from a federated database, it can happen to get this very cryptic DB2 error. I already had a post related to this case when the setup uses access to a federated database https://blog.voina.in/db2-sql-error-30090-caused-by-read-only-table-nickname-to-a-federated-database/.
You can get even stranger errors in case somehow the setup is not correctly done or there is a change in the way DB2 behaves after an update.
After an update DB2 changed the way access to federated databases works, in a way that “two-phase commit” is no longer supported by DB2 when a federated database is involved. In conclusion with the latest version of DB2 11g I was getting the following exception while accessing some date from a nickname over a table from a federated database.
[4/18/16 9:25:31:511 IST] 0000014a SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions SQL Error: -30090, SQLState: 25000
[4/18/16 9:25:31:512 IST] 0000014a SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions Operation invalid for application execution environment. Reason code = "18".. SQLCODE=-30090, SQLSTATE=25000, DRIVER=4.19.26
Analysing the meaning of the error code we get from IBM knowledge base:
SQL Error: -30090 = REMOTE OPERATION INVALID FOR APPLICATION EXECUTION ENVIRONMENT
SQLState: 25000 = An insert, update, or delete operation or procedure call is invalid in the context where it is specified.
See for SQL ERROR: http://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.messages.sql.doc/doc/rsqlmsg.html
See for SQLSTATE: https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.codes/src/tpc/db2z_sqlstatevalues.dita
Now the tricky part is to find the meaning of the reason code 18. Going at the description of the SQL Error 30090 (http://www.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql30090n.html) we have the following for reason code 18:
An update request (or, a DDL operation that results in the update of a system catalog table) has been issued that would result in multiple data sources being updated when one or more of the data sources in the unit of work only supports one-phase commit. Possible causes are:
An attempt was made to update a data source that only supports one-phase commit, but a different data source has already been updated in the same unit of work.
An attempt was made to update a data source that supports two-phase commit, but a different data source that only supports one-phase commit has already been updated in the same unit of work.
An attempt was made to update a local federated server table, but a data source that only supports one-phase commit has already been updated in the same unit of work.
An attempt was made to update a data source that only supports one-phase commit when the application is operating with a CONNECT type 2 connection setting.
So to translate this error to a meaningful explanation that can be delivered to the client I inferred this:
The remote insert, update, or delete operation or procedure call is invalid in the context where it is specified for the current application execution environment.
An attempt was made to update a local federated server table, but a data source that only supports one-phase commit has already been updated in the same unit of work.
We know that this is because DB2 changed the way access to federated databases works, in a way that “two-phase commit” is no longer supported by DB2 when a federated database is involved.
In order to correct this we have to make sure the federated server from client database to the federated database has the option “db2_two_phase_commit” set to N. If is not like this you need to remove all the nicknames and the federated server and recreate them correctly.