Unit of work and open cursors

When your program completes a unit of work, it should commit or roll back the changes you 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, you must 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.

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