JPA, DB2, and closed ResultSets
I’ve been working on a web application that utilizes JPA for the database persistence layer. During my initial build-out of the database and the data model I used Derby for its easy of use and availability in RAD and WebSphere, but later, once I was ready to move the database onto a “real” RDBMS, I installed DB2-c and reconfigured WebSphere accordingly.
After restarting everything and testing out the app I immediately ran into problems with some interaction between JPA and DB2. The error looked a lot like this :
[ibm][db2][jcc] Invalid operation: result set is closed can be a WebSphere® Application Server configuration problem.
After some searching on the internet and a pointer from an IBM colleague of mine that deals with the JPA runtime, I was able to find this page that describes how to deal with this condition.
By default, the application server configures the resultSetHoldability custom property with a value of 2 (CLOSE_CURSORS_AT_COMMIT). This property causes DB2 to close its resultSet/cursor at transaction boundaries. Despite DB2’s default resultSetHoldability value of 1 (HOLD_CURSORS_OVER_COMMIT), the application server retains the default value of 2 to avoid breaking compatibilities with previous releases of the application server. You can change the default if the need arises
The infocenter tells us that this condition can be fixed by adjusting the cursor behavior on the datasource. I took the following steps to correct the problem:
1. Opened the admin console and navigated to Resources > JDBC > Data Sources > MyDataSource
2. Navigate to Custom Properties located on the right side of the panel.
3. Finally, I changed the resultSetHoldability property from its default of 2 to the new value of 1 (shown below):
4. After the change, I saved my changes and restarted the JVM.
After changing the above value, the problem did not recur. These instructions should work for WAS 6.x, 7.x, and 8.x equally.