DB2: (SQL Error: -30090) caused by XA access to a nickname to a federated database

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.

[12/10/15 18:06:54:895 IST] 00000073 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions SQL Error: -4229, SQLState: null
[12/10/15 18:06:54:903 IST] 00000073 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions [jcc][t4][102][10040][4.19.26] Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch.
Use getNextException() to retrieve the exceptions for specific batched elements. ERRORCODE=-4229, SQLSTATE=null
[12/10/15 18:06:54:903 IST] 00000073 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions SQL Error: -1476, SQLState: 40506
[12/10/15 18:06:54:904 IST] 00000073 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions Error for batch element #1: The current transaction was rolled back because of error "-30090". Associated tokens: "".. SQLCODE=-1476, SQLSTATE=40506, DRIVER=4.19.26
[12/10/15 18:06:54:904 IST] 00000073 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions SQL Error: -4225, SQLState: null
[12/10/15 18:06:54:905 IST] 00000073 SqlExceptionH Z org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions [jcc][103][10843][4.19.26] Non-recoverable chain-breaking exception occurred during batch processing. The batch is terminated non-atomically. ERRORCODE=-4225, SQLSTATE=null
[12/10/15 18:06:54:906 IST] 00000073 BatchingBatch Z org.hibernate.engine.jdbc.batch.internal.BatchingBatch performExecution HHH000315: Exception executing batch [could not perform addBatch]

We get several DB2 error codes there that may make it more clear. Let me take you through them :

1. SQL Error: -4229, SQLState: null
Translated: “Batch failure. The batch was submitted, but at least one exception occurred on an individual member of the batch. ”
Note: This is the top error at batch level so we have to go deeper to find the cause.

2. SQL Error: -1476, SQLState: 40506
Translated: Transaction roll-back due to batch element failure
Note: This informs us that an element of the transaction batch failed. We have to go to the batch element error that follows to see the cause.

3.Error for batch element #1: The current transaction was rolled back because of error “-30090”. Associated tokens: “”.. SQLCODE=-1476, SQLSTATE=40506, DRIVER=4.19.26
Translate: The error occurred when trying to process batch element #1, with the batch element SQL Error : “-30090”. Message also tells us this is a child error message for error at point 2.

4. SQL Error : “-30090”.
Translate: REMOTE OPERATION INVALID FOR APPLICATION EXECUTION ENVIRONMENT

5. ERRORCODE=-4225, SQLSTATE=null
Translate: Non-recoverable chain-breaking exception occurred during batch processing. The batch is terminated non-atomically.On 12/10/2015 02:43 PM,
Note: This is just informing us that the transaction batch failed.

The actual root cause of the issue can be isolated from the error from point 4. The operation for which we get SQL Error : “-30090” is done on a table which is a NICKNAME to another table from the federated database.

Thus the error can be caused by one of the reasons:
1. The application is not able to access the NICKNAME to the table from the federated database.
– due to the application database user not having granted access rights
– due to some communication issues
– due to some other issues that make the nickname not accessible or no longer available
2. The application tries to execute a forbidden operation.
– an operation might be invalid for applications that have special restrictions on statements or APIs – applications such as those that operate in an XA Distributed Transaction Processing environment, such as CICS; those that operate with CONNECT type 2 connection settings; or those that use federated system functionality to update multiple heterogeneous data sources.

In our case the point 2. applies. The application was trying to commit an insert/update to the nickname when the database is accessed through an XA data source. In this case the nickname is read only so an insert operation fails with the discussed error message. The solution was to either insert the needed value from the master system that uses directly the federated database or by defining a new non-XA data source and perform the insert/update through that data source.