Hold cursors

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

IBM 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.

IBM 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 IBM Informix Guide to SQL: Syntax.