Cursor considerations for CLI applications

When you are writing an application that uses cursors, you must decide which type of cursor to use, how your cursor affects units of work, and how to troubleshoot applications that existed prior to the cursor application.

Which cursor type to use

The first decision to make is between a forward-only cursor and a scrollable cursor. A forward-only cursor incurs less resource usage than a scrollable cursor, and scrollable cursors have the potential for decreased concurrency.

If your application does not need the additional features of a scrollable cursor, then you should use a non-scrollable cursor.

If a scrollable cursor is required then you must decide between a static cursor, a keyset-driven cursor, or a dynamic cursor. A static cursor involves the least amount of resource usage. If the application does not need the additional features of a keyset-driven or dynamic cursor then a static cursor should be used.
Note: Currently, dynamic cursors are only supported when accessing servers that are Db2® for z/OS® Version 8.1 and later.

If the application needs to detect changes to the underlying data or needs to add, update, or delete data from the cursor, then the application must use either a keyset-driven or dynamic cursor. To perform updates and deletions on rows in a dynamic scrollable cursor's result set, the UPDATE or DELETE statement must include all the columns of at least one unique key in the base table. This can be the primary key or any other unique key. Because dynamic cursors incur more resource usage and might have less concurrency than keyset-driven cursors, only choose dynamic cursors if the application needs to detect both changes made and rows inserted by other cursors.

If an application requests a scrollable cursor that can detect changes without specifying a particular cursor type, then CLI will assume that a dynamic cursor is not needed and provide a keyset-driven cursor. This behavior avoids the increased resource usage and reduced concurrency that is incurred with dynamic cursors.

To determine the attributes of the types of cursors supported by the driver and DBMS, the application should call SQLGetInfo() with an InfoType of:
  • SQL_DYNAMIC_CURSOR_ATTRIBUTES1
  • SQL_DYNAMIC_CURSOR_ATTRIBUTES2
  • SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES1
  • SQL_FORWARD_ONLY_CURSOR_ATTRIBUTES2
  • SQL_KEYSET_CURSOR_ATTRIBUTES1
  • SQL_KEYSET_CURSOR_ATTRIBUTES2
  • SQL_STATIC_CURSOR_ATTRIBUTES1
  • SQL_STATIC_CURSOR_ATTRIBUTES2

Unit of work considerations

A cursor can be closed either explicitly or implicitly. An application can explicitly close a cursor by calling SQLCloseCursor(). Any further attempts to manipulate the cursor will result in error, unless the cursor is opened again. The implicit closure of a cursor depends on a several factors including how the cursor was declared and whether or not a COMMIT or ROLLBACK occurs.

By default, the CLI driver declares all cursors as WITH HOLD. This means that any open cursor will persist across COMMITs, thereby requiring the application to explicitly close each cursor. Be aware, however, that if a cursor is closed in autocommit mode, then any other open cursors that are not defined with the WITH HOLD option will be closed and all remaining open cursors will become unpositioned. (This means that no positioned updates or deletes can be performed without issuing another fetch.) There are two ways to change whether a cursor is declared WITH HOLD:
  • Set the statement attribute SQL_ATTR_CURSOR_HOLD to SQL_CURSOR_HOLD_ON (default) or SQL_CURSOR_HOLD_OFF. This setting only affects cursors opened on the statement handle after this value has been set. It will not affect cursors already open.
  • Set the CLI/ODBC configuration keyword CursorHold to change the default CLI driver behavior. Setting CursorHold=1 preserves the default behavior of cursors declared as WITH HOLD, and CursorHold=0 results in cursors being closed when each transaction is committed. You can override this keyword by setting the SQL_ATTR_CURSOR_HOLD statement attribute.
Note: A ROLLBACK will close all cursors, including those declared WITH HOLD.

Troubleshooting for applications created before scrollable cursor support

Because scrollable cursor support is a newer feature, some CLI/ODBC applications that were working with previous releases of Db2 or Db2 for z/OS for OS/390® might encounter behavioral or performance changes. This occurs because before scrollable cursors were supported, applications that requested a scrollable cursor would receive a forward-only cursor. To restore an application's previous behavior before scrollable cursor support, set the following configuration keywords in the db2cli.ini file:
Table 1. Configuration keyword values restoring application behavior before scrollable cursor support
Configuration keyword setting Description
Patch2=6 Returns a message that scrollable cursors (keyset-driven, dynamic and static) are not supported. CLI automatically downgrades any request for a scrollable cursor to a forward-only cursor.
DisableKeysetCursor=1 Disables keyset-driven scrollable cursors. This can be used to force the CLI driver to give the application a static cursor when a keyset-driven or dynamic cursor is requested.