JDBC application cursor holdability support

The cursor holdability feature can reduce the overhead of JDBC interaction with your relational database, thereby helping to increase application performance.

By activating cursor holdability, you keep a result set available across transaction boundaries for use by multiple JDBC calls. The holdability setting triggers a database cursor to keep newly updated rows active beyond the commit of the transaction that generated the new values, or result set. Hence the cursor makes the result set available for use by statements in a subsequent transaction.

Set cursor holdability

Using one of the following techniques to set cursor holdability. For more details, see the JDBC 3.0 specification, available at the Oracle website at http://www.oracle.com/technetwork/java/index.html.

• Specify the ResultSet.HOLD_CURSORS_OVER_COMMIT parameter when creating or preparing a statement using the createStatement, prepareStatement, or prepareCall methods.

• Invoke the setHoldability method on the Connection object. The cursor holdability value that we set with this method becomes the default. If we specify cursor holdability on the Statement object, that value overrides the value specified on the connection.

  We cannot specify cursor holdability on a shareable connection after that connection is referenced by a second handle. Invoking the holdability method at this point generates an exception. If to set cursor holdability on a shareable connection, invoke the method before the connection is enlisted. Otherwise a shareable connection retains the same holdability value that applied in the previous enlistment.

• Check the database documentation to see if the product supports cursor holdability as a data source property. DB2, for example, responds to the holdability trigger if we set it as a data source custom property. See the topic, Custom property settings, for more information.

The impact of connection and transaction behaviors on cursor holdability

Set cursor holdability in WebSphere Application Server results in the following behavior for different transaction events:

• When a connection is closed, all statements and result sets are closed even if we have set cursor holdability.

• When a transaction is rolled back, all result sets are closed even if we have set cursor holdability.

• When a local transaction is committed, both shareable and unshareable connections can have an open result set across a transaction boundary.

• When a global transaction is committed, unshareable connections can have an open result set across a transaction boundary. For shareable connections, the statements and result sets are closed even if we have set cursor holdability; the holdability value does not impact shareable connections participating in global transactions.

• When a local transaction scope ends, either at the method level or the activity session level, all statements and result sets for shareable connections are closed. Statements and result sets for unshareable connections remain open until the close method is called on the connection.

For a global transaction with an unshareable connection, the backend database has responsibility for supporting cursor holdability.

Custom property settings