Normally a cursor is closed when its transaction commits. A transaction can involve one or more SQL commands that are committed as a unit---either the entire transaction or none of it is committed to the database. If the held cursors option is enabled, the cursor remains open after the commit. This enables an application to fetch rows from a result set, commit the transaction, and then continue fetching additional rows on the same result set.
Using held cursors, an application can commit immediately after opening the result set to allow locks normally needed to maintain a prepared statement to be freed early. If the application is in auto-commit mode, StarSQL automatically issues a commit at the time the result set is opened and at the time the result set is closed.
Held cursors are supported on DB2 for z/OS, on DB2 for i, and on DB2/UDB.
An application that uses held cursors should turn SQL_AUTOCOMMIT_MODE off before executing a prepared statement multiple times. Otherwise, StarSQL prepares the SQL statement after each SQLExecute. This can potentially result in poor performance.