Hold cursors

When transaction logging is used, HCL Informix® generally closes all cursors and releases all locks when a transaction ends. In a multiuser environment, this behavior is not always desirable.

Informix® JDBC Driver had already implemented holdable cursor support with Informix® extensions. Informix® database servers (5.x, 7.x, SE, 8.x, 9.x, and 10.x, or later) support adding keywords WITH HOLD in the declaration of the cursor. Such a cursor is referred to as a hold cursor and is not closed at the end of a transaction.

Informix® JDBC Driver, in compliance with the JDBC 3.0 specifications, adds methods to JDBC interfaces to support holdable cursors.

For some of the applications, there might be a need to hold cursors over commit in ANSI databases. JDBC provides an API Connection.setHoldability(), but for more flexibility user can also set this via Connection URL or data source.
  • Add CURSOR_HOLDABILITY = 1 or 2 {In Connection URL}
  • Add setCursorHoldability(1 or 2) {In Informix datasource}
There are 2 constants for doing that:
  • HOLD_CURSORS_OVER_COMMIT: Indicating that open ResultSet objects with this holdability will remain open when the current transaction is committed. Value is 1.
  • CLOSE_CURSORS_OVER_COMMIT:Indicating that open ResultSet objects with this holdability will be closed when the current transaction is committed. Value is 2.

The default is CLOSE_CURSORS_OVER_COMMIT.

For more information about hold cursors, see the Informix® Guide to SQL: Syntax.