DisableCursorHold IBM data server driver configuration keyword

Controls the effect of a transaction completion on open cursors.

Equivalent CLI keyword
CursorHold
Equivalent IBM® Data Server Provider for .NET connection string keyword
DisableCursorHold
db2dsdriver.cfg configuration syntax
<parameter name="DisableCursorHold" value="0 | 1"/>
Default setting:
Cursors are not destroyed.
Equivalent statement attribute:
SQL_ATTR_CURSOR_HOLD
Usage notes:
This option controls the effect of a transaction completion on open cursors.
  • 0 = SQL_CURSOR_HOLD_ON, the cursors are not destroyed when the transaction is committed (default).
  • 1 = SQL_CURSOR_HOLD_OFF, the cursors are destroyed when the transaction is committed.
Note: Cursors are always closed when transactions are rolled back.

This option affects the result returned by SQLGetInfo() when called with SQL_CURSOR_COMMIT_BEHAVIOR or SQL_CURSOR_ROLLBACK_BEHAVIOR. The value of DisableCursorHold is ignored if connecting to Db2® Server for VSE & VM where cursor with hold is not supported.

You can use this option to tune performance. It can be set to SQL_CURSOR_HOLD_OFF (1) if you are sure that your application:
  1. Does not have behavior that is dependent on the SQL_CURSOR_COMMIT_BEHAVIOR or the SQL_CURSOR_ROLLBACK_BEHAVIOR information returned via SQLGetInfo(), and
  2. Does not require cursors to be preserved from one transaction to the next.

The DBMS will operate more efficiently with DisableCursorHold enabled, as resources no longer need to be maintained after the end of a transaction.

Note: The DisableCursorHold keyword does not impact a result set that is returned by a stored procedure. The Resultset value that is returned by a stored procedure is preserved after a commit or rollback if the cursor in the stored procedure is declared as WITHHOLD. The Resultset value that is returned by a stored procedure is not preserved after a commit or rollback if the cursor in the stored procedure is not declared as WITHHOLD.