This is in the context of an IBM Business Process Manager Advanced 184.108.40.206 installation on AIX.
I'd noticed a spurious exception in the First Failure Data Capture (FFDC) logs of both members of the AppCluster: -
19/08/15 09:44:01:200 BST] FFDC Exception:org.springframework.jdbc.UncategorizedSQLException SourceId:com.ibm.ws.uow.UOWManagerImpl.runUnderNewUOW ProbeId:934 Reporter:com.ibm.ws.uow.UOWManagerImpl@f8de0965 org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [select reference_id, dep_path_id, target_type, target_id, target_summary_data from lsw_po_reference where branch_id = ? and start_seq_num <= ? and end_seq_num > ?]; SQL state [null]; error code [-4470]; [jcc][t4][4.19.26] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null; nested exception is com.ibm.db2.jcc.am.SqlException: [jcc][t4][4.19.26] Invalid operation:
result set is closed. ERRORCODE=-4470, SQLSTATE=null
which, whilst not necessarily representing a major issue was somewhat of an annoyance.
I dug around online, and found this thread on IBM developerWorks Answers ( dW Answers ): -
This, in part, suggested that the problem might be mitigated by changing a Custom Property of the JDBC data source used by the AppCluster, specifically jdbc/TeamWorksDB, as described here: -
In essence, it directs one to set resultSetHoldability to 1 rather than the default of not set.
I did this, and ended up with a cluster that refused to start, due: -
[19/08/15 08:34:36:288 BST] FFDC Exception:com.ibm.db2.jcc.am.SqlException SourceId:com.ibm.ws.rsadapter.jdbc.WSJdbcConnection.createStatement ProbeId:865 Reporter:com.ibm.ws.rsadapter.jdbc.WSJccSQLJPDQConnection@e99b8d0c com.ibm.db2.jcc.am.SqlException: [jcc][t4][4.11.69] Invalid operation: ResultSet holdability HOLD_CURSORS_OVER_COMMIT is not allowed on an XA connection. ERRORCODE=-4476, SQLSTATE=null
The dW Answers post also suggested that this iFix: -
would mitigate the problem.
Alas, I already had this applied to my BPM environment, as it was released ~12 months ago in August 2014.
Finally, it suggested that the DB2 JDBC driver might needed to be updated. I checked, and the BPM box was running an older version of the driver. I copied updated drivers ( db2jcc.jar and db2jcc4.jar ) across from the DB2 10.5.0.5 server, but to no avail. The problem persisted ....
For the record, the latest DB2 JDBC drivers can be found here: -
I actually had the latest drivers applied to DB2, as they're consistent between 10.5.0.5 and 10.5.0.6: -
I then found this dW Answers post: -
which says, in part: -
For DB2, these driver properties can impact the behavior, although they don't really change things for the BPM XA transactions.
resultSetHoldability Specifies whether cursors remain open after a commit operation. The data type of this property is int. Valid values are:
• DB2BaseDataSource.HOLD_CURSORS_OVER_COMMIT (1)
Leave cursors open after a commit operation.
This value is not valid for a connection that is part of a distributed (XA) transaction.
• DB2BaseDataSource.CLOSE_CURSORS_AT_COMMIT (2)downgradeHoldCursorsUnderXa Specifies whether cursors that are defined WITH HOLD can be opened under XA connections.
Close cursors after a commit operation.
If downgradeHoldCursorsUnderXa is set to false, then an error is thrown for this scenario.
If downgradeHoldCursorsUnderXa is set to true, a cursor that is defined WITH HOLD can be opened under an XA connection. However, the cursor has the following restrictions:
• When the cursor is opened under an XA connection, the cursor does not have WITH HOLD behavior. The cursor is closed at XA End.
• A cursor that is open before XA Start on a local transaction is closed at XA Start.
Therefore, I set resultSetHoldability back to 1 ( HOLD_CURSORS_OVER_COMMIT ) and also added a new Custom Property, downgradeHoldCursorsUnderXa, which
I set to true.
This last most recent change appears to have fixed / mitigated the issue.
In summary, it was a combination of the JR50863 iFix AND the two JDBC data source Custom Properties. The JDBC driver version appears to make no difference, and I did roll back to the shipped version, at least for now.