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

Microsoft™ SQL Server

Microsoft SQL Server does not close cursors when a transaction ends.

This behavior is controlled with the SET CURSOR_CLOSE_ON_COMMIT ON/OFF instruction.

Solution

Even if Microsoft SQL Server supports cursors across transactions, for consistency with other ODI drivers, BDL cursors that are not declared WITH HOLD are automatically closed, when a COMMIT WORK or ROLLBACK WORK is performed.