DB2 Mainframe: queryTimeout does not work on SqlTransactionRollbackException: SQLCODE=-911, SQLSTATE=40001

Question

We have been seeing this following exception from DB2 Mainframe. We do have CallableStatement.querytimeout set, but it never really works in this case. Any suggestions? Thanks.

DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=00C9008E;00000801;NULLID.SYSSTAT.5359534C564C3031, DRIVER=3.61.109; nested exception is com.ibm.db2.jcc.am.SqlTransactionRollbackException: DB2 SQL Error: SQLCODE=-911, SQLSTATE=40001, SQLERRMC=00C9008E;00000801;NULLID.SYSSTAT.5359534C564C3031, DRIVER=3.61.109

Same is the case for following exception as well. Query timeout does not work.

nested exception is com.ibm.db2.jcc.am.SqlException: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00E30083, TYPE OF RESOURCE 00000802, AND RESOURCE NAME BINDLOCK

Answer 1

It's a timeout waiting for a lock (BINDLOCK01 to 20) to unlock.

Check the table you are trying to load isn't locked by another long running job doing an update/insert/delete query.

The IBM Knowledge Center suggests adding frequent COMMIT operations where possible.

Toad World provides some further strategies for optimising lock times:

Recommendation: ACQUIRE(USE) and RELEASE(DEALLOCATE) provide good concurrency when using page or row locking and to maximize performance.

To hold exclusive locks as short a time as possible, it is advisable to group INSERT, UPDATE, and DELETE statements together in a host language program followed by a COMMIT. If these SQL statements are interspersed with host language code, the locks are held while executing the host language code.

As a last resort, if you don't want to optimise your query, and assuming it's not deadlock, you could disable lock timemouts:

On DB2 for *NIX/Windows, change the 'LOCKTIMEOUT' parameter to '-1'. (Not sure what it is for z/OS, or potential repercussions.)

Welcome to Stack Overflow.