Cursors WITH HOLD

Informix®

Informix closes opened cursors automatically when a transaction ends, unless the WITH HOLD option is used in the DECLARE instruction:

DECLARE c1 CURSOR WITH HOLD FOR SELECT ...
OPEN c1
BEGIN WORK
FETCH c1 ...
COMMIT WORK
FETCH c1 ...
CLOSE c1

IBM® DB2®

IBM DB2 can keep cursors open across transactions, when the transaction is terminated with COMMIT.

When a transaction is canceled with a ROLLBACK, DB2 automatically closes all cursors.

Solution

Normally, BDL cursors declared WITH HOLD remain open even after terminating a transaction with a COMMIT WORK or ROLLBACK WORK.

Since IBM DB2 closes cursors when a transaction is rolled back, you must check that your source code does not use WITH HOLD cursors after transactions canceled with ROLLBACK WORK.

For consistency with other database brands, database cursors that are not declared WITH HOLD are automatically closed, when a COMMIT WORK or ROLLBACK WORK is performed.

Important:

Opening a WITH HOLD cursor declared with a SELECT FOR UPDATE results in an SQL error; in the same conditions, this does not normally appear with Informix. Review the program logic in order to find another way to set locks.