Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
In general, a cursor closes when a COMMIT is performed. There are two exceptions to this behavior:
The close_on_endtrans database option is set to Off.
A cursor is opened WITH HOLD, which is the default with Open Client and JDBC.
If either of these two cases is true, the cursor remains open on a COMMIT.
If a transaction rolls back, then cursors close except for those cursors opened WITH HOLD. However, don't rely on the contents of any cursor after a rollback.
The draft ISO SQL3 standard states that on a rollback, all cursors (even those cursors opened WITH HOLD) should close. You can obtain this behavior by setting the ansi_close_cursors_on_rollback option to On.
If a transaction rolls back to a savepoint, and if the ansi_close_cursors_on_rollback option is On, then all cursors (even those cursors opened WITH HOLD) opened after the SAVEPOINT close.
You can change the isolation level of a connection during a transaction using the SET OPTION statement to alter the isolation_level option. However, this change does not affect open cursors.