Problem:
The error below shows in a threaded application which uses a single database connection object to execute multiple queries from different threads and thus it may happen that more than one ResultSet object is open and accessed by a thread at the same time:
INFO [ thread3Exceptioncom.ibm.pdq.runtime.exception.DataRuntimeException: [pdq][10101][2.7.116] An error prevented the SQL query from completing successfully.; Caused by: com.ibm.db2.jcc.am.SqlException: [jcc][10120][10943][3.57.82] Invalid operation: statement is closed. ERRORCODE=-4470, SQLSTATE=null ]
Resolution:
It is caused by multi-threaded code where each thread executes a SQL statement and all threads use the same database connection object. The code was changed so that each thread gets its own separate database connection. This fixed the problem.
During our initial investigation in the problem, we found this APAR https://www-304.ibm.com/support/docview.wss?uid=swg1JR29617&wv=1 which discussed a scenario similar to what we were doing so we installed the DB2 fixpack. Unfortunately, this didn't fix the problem so we decided to go the right path and use a separate connection for each thread.
Hints:
During our initial investigation in the problem, we found this APAR https://www-304.ibm.com/support/docview.wss?uid=swg1JR29617&wv=1 which discussed a scenario similar to what we were doing so we installed the DB2 fixpack. Unfortunately, this didn't fix the problem so we decided to go the right path and use a separate connection for each thread.
Hints:
- This showed for us on DB 9.5
- The problem didn't show up during unit testing on our development machines and constantly showed up after deployment to our testing server which made it harder to trace & it was finally resolved after a code peer review.
Sorry,
ReplyDeletewe are closing all connection, stmt and resultset
finally {
close(pResultSet, pStmt, pConn);
}