1

I am updating a table in dashdb on Bluemix and receiving an error which indicates a possible db issue. Is there a way to look at the db logs for additional information?

The errors I get are:

com.ibm.db2.jcc.am.BatchUpdateException: [jcc][t4][102][10040][4.23.42] 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; [SQLSTATE 40506] 



com.ibm.db2.jcc.am.SqlTransactionRollbackException: Error for batch element #1: **The current transaction was rolled back because of error "-911".. SQLCODE=-1476, SQLSTATE=40506,** DRIVER=4.23.42; 
[SQLSTATE null] com.ibm.db2.jcc.am.SqlException: [jcc][103][10843][4.23.42] Non-recoverable chain-breaking exception occurred during batch processing.
The batch is terminated non-atomically. ERRORCODE=-4225, SQLSTATE=null
William 'Bill' Wentworth
  • 1,791
  • 11
  • 21
behemoth18
  • 11
  • 1
  • Are you using the entry (shared) plan or an enterprise version? – data_henrik May 29 '18 at 14:28
  • If you are doing an `UPDATE` on a large table, you will likely fill all of your available transaction log space. You should either break your `UPDATE` into smaller chunks, with commits in-between, or maybe consider creating a new table and insert your data (including the update you want to do as part of the select) into the new table – Paul Vernon May 29 '18 at 15:11
  • @data_henrik, I am using the entry (shared) plan. – behemoth18 May 29 '18 at 15:19

1 Answers1

3

Your transaction got rolled-back by Db2 because your transaction experienced either a LOCK TIMEOUT or a DEADLOCK.

That is the meaning of the -911 sqlcode.

You need to discover whether it was a lock-timeout or a deadlock, because different avoidance strategies apply.

To determine which (lock timeout or deadlock) your code can either use getNextException() or get cause, or use the PD_GET_LOG_MSGS or PDLOGMSGS_LAST24HOURS functions . The reason-code 2 indicates a deadlock and the reason code 68 indicates a lock-timeout. This information might be available from methods in class (or subclasses of) SQLException or DB2Diagnosable.

To avoid the lock-timeout or the deadlock , you need to examine your isolation-level (for the statement, or transaction, or package, or connection) and the other concurrently running jobs that are using the same table(s) as your transaction, or adjust your lockwait tolerance (via set current lock timeout for your transaction). Typically you need to ensure best access-plans, lowest required isolation level, smallest unit-of-work to achieve the desired outcome (commit more frequently) and ensure there are no hung threads or uncommitted old transactions on the database that are holding locks unneccessarily.

mao
  • 11,321
  • 2
  • 13
  • 29