"Invalid operation: result set is closed" with IBM BPM 8.5.5

This is in the context of an IBM Business Process Manager Advanced 8.5.5.0 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][10120][10898][4.19.26] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null; nested exception is com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][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 ): -

How do I resolve a CWLLG2015E on BPM 8.5.5 due to DB2 ERRORCODE=-4470?

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: -

Performing a query that is outside of a transaction in an extended persistence context causes a "result set closed" exception

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][10434][12579][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: -

JR50863: RUNNING A MIGRATED PROCESS INSTANCE CAUSES A CWLLG0181E TO BE LOGGED WITH A TeamWorksRuntimeException

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: -

DB2 JDBC Driver Versions and Downloads

 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: -

Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

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)
Close cursors after a commit operation.

downgradeHoldCursorsUnderXa Specifies whether cursors that are defined WITH HOLD can be opened under XA connections.

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.

Nice :-)