DB2 : Cursor Repositioning

Cursor Repositioning

Cursor repositioning is required when a batch application abends and restarts from the last commit point. If you do not use cursors with the WITH HOLD option, cursor repositioning is required every time you reopen the cursor after the commit.

WITH HOLD Option for Cursors

If your program completes a unit of work (that is, it either commits or rolls back the changes made until then), and you do not want DB2 to close all open cursors, declare the cursor with the WITH HOLD option. An open cursor defined with the WITH HOLD option remains open after a commit or rollback operation. The cursor is positioned after the last row is retrieved and before the next row of the result table is to be returned. A lock on the last page or row acquired before a commit remains after the commit. If you hold a X-lock on the page or row the cursor is currently positioned on, the X-lock is downgraded to an S-lock. In DB2 V5, if the ZPARM RELCURHL is set to YES (the default value is NO), then DB2 releases the data page or row lock after a commit for cursors defined with the WITH HOLD option. For example, the cursor declaration shown in Figure 127 on page 177 causes the cursor to maintain its position in the DSN510.EMP table after a commit point.

DECLARE EMPLUPDT CURSOR WITH HOLD FOR

SELECT EMPNO, LASTNAME, PHONENO, JOB, SALARY, WORKDEPT

FROM DSN510.EMP

WHERE WORKDEPT < 'D11'

ORDER BY EMPNO