Unit of work and open cursors

 

When your program completes a unit of work, it should commit or roll back the changes that you have made.

Unless you specified HOLD on the COMMIT or ROLLBACK statement, all open cursors are automatically closed by SQL. Cursors that are declared with the WITH HOLD clause are not automatically closed on COMMIT. They are automatically closed on a ROLLBACK (the WITH HOLD clause specified on the DECLARE CURSOR statement is ignored).

If you want to continue processing from the current cursor position after a COMMIT or ROLLBACK, specify COMMIT HOLD or ROLLBACK HOLD. When HOLD is specified, any open cursors are left open and keep their cursor position so processing can resume. On a COMMIT statement, the cursor position is maintained. On a ROLLBACK statement, the cursor position is restored to just after the last row retrieved from the previous unit of work. All record locks are still released. After issuing a COMMIT or ROLLBACK statement without HOLD, all locks are released and all cursors are closed. You can open the cursor again, but you will begin processing at the first row of the result table.

Specification of the ALWBLK(*ALLREAD) parameter of the Create SQL (CRTSQLxxx) commands can change the restoration of the cursor position for read-only cursors. For information about the use of the ALWBLK parameter and other performance-related options on the CRTSQLxxx commands, see Dynamic SQL applications.

 

Parent topic:

Using a cursor

 

Related concepts

Commitment control