CursorHold CLI/ODBC and IBM data server driver configuration keyword

Controls the effect of a transaction completion on open cursors.

db2cli.ini keyword syntax:
CursorHold = 1 | 0
IBM® data server driver configuration file (db2dsdriver.cfg) syntax:
<parameter name="CursorHold" value="1 | 0"/>
Attention: The IBM data server driver configuration file (db2dsdriver.cfg) syntax is available in Db2 11.5.4 and later.
Default setting:
Selected--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.
  • 1 = SQL_CURSOR_HOLD_ON, the cursors are not destroyed when the transaction is committed (default).
  • 0 = 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 CursorHold 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 (0) 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 operates more efficiently with the CursorHold keyword disabled. By disabling the keyword, resources do not need to be maintained after the end of a transaction.

Note: The CursorHold 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.