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

1
votes

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

1
db2mainframecallable-statementquery-timeout

1 Answers

0
votes

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.