1

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

user3692238
  • 13
  • 4

1 Answers1

0

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.

aportr
  • 1,331
  • 1
  • 13
  • 21
  • You're welcome, hope it helps solve your problem. Don't forget to mark this as the correct answer if it does. – aportr Apr 19 '15 at 17:14
  • Thanks for the reply, jabolotai. From a Java application developer perspective, I guess my question would be, does these exceptions occur when query being executed? If yes, why the querytimeout setting that we set from Java application is not considered. The query here is to read the data using stored procedure call. – user3692238 Apr 19 '15 at 17:19
  • Yes, while trying to get a resource. Statement.getQueryTimeout() is the client side (the JDBC driver), locktimeout is server side (which needs enough authority to set). There are many timeout options, but queryTimeout should override the client side limits at least. Check what your server is set to. Look at http://database.ittoolbox.com/groups/technical-functional/db2-l/default-value-of-statement-query-timeout-on-db2-v977-5361890 for more info, and if stuck check it's not something weird like a driver-server version mismatch. – aportr Apr 19 '15 at 18:34
  • Thanks a lot for quick response. I do not have permission to set locktimeout. But I guess I am still confused, if the exception that is being thrown would occur when query is executed, and with querytimeout being set to some 10 seconds, why the query is not timing out and runs for some X ( X > 10) seconds. – user3692238 Apr 19 '15 at 19:10
  • I think the JDBC driver does an asynchronous check every QueryTimeoutInterval seconds to see if query has exceeded allowed QueryTimeout, so run time can, worst case, be QueryTimeoutInterval + QueryTimeout seconds (see http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.apdv.cli.doc/doc/r0008809.html). – aportr Apr 20 '15 at 00:17