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.
Setting cursor holdability
Use 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 you set with this method becomes the
default. If you specify cursor holdability on the Statement object,
that value overrides the value that you specified on the connection.
You
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 you want 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 your database documentation to see if the product supports
cursor holdability as a data source property. DB2®, for
example, responds to the holdability trigger if you 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
Setting 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 you have set cursor holdability.
- When a transaction is rolled back, all result sets are closed
even if you 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 you
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.
Note: For a global transaction with an unshareable connection,
the backend database has responsibility for supporting cursor holdability.