Working with WITH HOLD argument is little tricky in DB2 Cursors. Positioning the Cursor is the real use when you give WITH HOLD in DB2, CICS, IMS DB and IMS DC.
Rules to Use WITH HOLD
- The Cursor WITH HOLD does not impact on IMS DC programs. In CICS, the WITH HOLD does maintains Cursor position till end of the unit of work
- In CICS or IMS, non message driven programs, you declared a cursor with WITH HOLD option, and you opened a cursor, now in current unit of work, you issued ROLLBACK and you made no change to database, in this scenario, Cursor will not be closed. Since IMS or CICS will not communicate null unit of work to DB2.
- DB2 REXX applications does not support WITH ROWSET POSITIONING
- A Cursor which is declared in Calling program, should not be used in Called module(Subroutine)
- A row can be fetched multiple times, if it is declared as SCROLLABLE and with Not Deterministic
- In IMS DC, you should not declare cursors with WITH HOLD
- In CICS, you can give Cursor WITH HOLD, but see limitations here. In CICS, message driven programs, even you have given WITH HOLD, the Cursors will be closed after Sync point or ROLLBACK operation. So usually, you should not give WITH HOLD clause in CICS programs.
Related Posts
Srinimf 