Do you get the exception Invalid operation: result set is closed in Db2 when you run a java application that uses DB2 connection. Solution is very simple. This error is generated when closed resultset objects are accessed by the application. All the resultset objects which are created for the transaction are closed, whenever the transaction is committed. After that when we access the result set, the exception occurs. Sometimes it is necessary to keep the resultset open even after the commit() method is called. So we have to solve the above error. We can solve this error at application level or Application Server Level (Websphere Application Server in our example) .
The prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) of connection object accepts 4 arguments . resultSetHoldability is the 4th argument. The default value of DB2's resultSetHoldability value is of 1 (HOLD_CURSORS_OVER_COMMIT). If the existing value of resultSetHoldability is 2 (CLOSE_CURSORS_AT_COMMIT) means, setting the resultSetHoldabilty value to 1 or (HOLD_CURSORS_OVER_COMMIT) will solve the problem. So you can control whether or not to close the ResultSets after commit() method by passing 1 or 2 to createStatement() / prepareStatement method .
Now let us see how to solve the above error at WebSphere Application Server Level. By default, the application server's custom property resultSetHoldability is configured with the value of 2 (CLOSE_CURSORS_AT_COMMIT). This property causes DB2 to close its resultSet / cursor at transaction commit. Although DB2's default resultSetHoldability value is 1, the application server retains the default value of 2. You can change the default value doing the following steps
1. Open the IBM administative console.
2. Expand Resources, then Select JDBC providers > DB2 Universal JDBC Driver Provider ->Data sources .
3. Now select the DataSource Name which you have created (For Example EmployeeDS ), then select Custom properties from right side menu under Additional Properties
3. Now select resultSetHoldability and Change the value 2 to 1, then apply->save->save. The following screen shows custom property resultSetHoldabilty
The prepareStatement(String sql, int resultSetType, int resultSetConcurrency, int resultSetHoldability) of connection object accepts 4 arguments . resultSetHoldability is the 4th argument. The default value of DB2's resultSetHoldability value is of 1 (HOLD_CURSORS_OVER_COMMIT). If the existing value of resultSetHoldability is 2 (CLOSE_CURSORS_AT_COMMIT) means, setting the resultSetHoldabilty value to 1 or (HOLD_CURSORS_OVER_COMMIT) will solve the problem. So you can control whether or not to close the ResultSets after commit() method by passing 1 or 2 to createStatement() / prepareStatement method .
Now let us see how to solve the above error at WebSphere Application Server Level. By default, the application server's custom property resultSetHoldability is configured with the value of 2 (CLOSE_CURSORS_AT_COMMIT). This property causes DB2 to close its resultSet / cursor at transaction commit. Although DB2's default resultSetHoldability value is 1, the application server retains the default value of 2. You can change the default value doing the following steps
1. Open the IBM administative console.
2. Expand Resources, then Select JDBC providers > DB2 Universal JDBC Driver Provider ->Data sources .
3. Now select the DataSource Name which you have created (For Example EmployeeDS ), then select Custom properties from right side menu under Additional Properties
3. Now select resultSetHoldability and Change the value 2 to 1, then apply->save->save. The following screen shows custom property resultSetHoldabilty
0 comments:
Post a Comment