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

Netezza®

Netezza does not close cursors when a transaction ends.

However, cursors declared with a SELECT FOR UPDATE are not supported with Netezza.

Solution

BDL cursors declared WITH HOLD remain open even after terminating a transaction with a COMMIT WORK or 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.