Site icon Voina Blog (a tech warrior's blog)

DB2 : Errors when a database nickname or view is no longer available

Advertisements

In an enterprise application based on hibernate running over DB2 we have to refer with several views and nicknames to external tables. This can be the case when SSO is used or there are some common static data (accounts, user entries, financial constants etc. ) that are kept in a centralized database repository and are not part of our application.
When from some reason the nickname or view is no longer visible (communication problem, grants no longer valid etc.) we get a string of cryptic SQL errors like the following:


[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]
[12/10/15 18:06:54:907 IST] 00000073 BusinessExcep E   CNTR0020E: EJB threw an unexpected (non-declared) exception during invocation of method "a" on bean "BeanId(xxx#xxx.jar#BusinessCalendarManagerBean, null)". Exception data: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not perform addBatch
          at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1387)
          at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1310)
          at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1316)
          at org.hibernate.ejb.AbstractEntityManagerImpl.persist(AbstractEntityManagerImpl.java:881)
          at com.ibm.ws.jpa.management.JPAExEmInvocation.persist(JPAExEmInvocation.java:317)
          at com.ibm.ws.jpa.management.JPAEntityManager.persist(JPAEntityManager.java:143)
          ...
          at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 

There are several DB2 error codes there that may make it more clear. Let us go through them :

STEP 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.

STEP 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.

STEP 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.

STEP 4. SQL Error : “-30090”.

Translate: REMOTE OPERATION INVALID FOR APPLICATION EXECUTION ENVIRONMENT

STEP 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

In our case the operation for which we get SQL Error : “-30090” is done on BusinessCalendar table which is a NICKNAME to the BusinessCalendar from another tablespace.

This means that somehow you are not able to access the NICKNAME to the BusinessCalendar from the other tablespace. We do not have granted access rights, there are communication issues or some other issues the nickname is not accessible.

[paypal_donation_button]

Exit mobile version