Point 1 – The best example for cursor WITH HOLD is, just imagine, you are processing Master table and Detail table in DB2, you need to retrieve a row from Master table and process the row data, and you need to update it in Detail table. To perform UPDATE in detail table we need to give commit. During the process we are HOLDING the cursor.
Point 2 – When you do COMMIT, the WITH HOLD cursor will not be closed. But , during ROLL BACK, all the cursors(including WITH HOLD) will be closed, and all the locks will be released.
Syntax of cursor
>>-DECLARE--+-cursor_id------------+---------------------------->
| (1) |
'--------cursor_id_var-'
(1) (2)
>--+-CURSOR--+------------------+--+-FOR-------| Subset of INSERT Statement |-----------------+----+-><
| | (1) | +-| Select Options |---------------------------------------+ |
| '--------WITH HOLD-' | (3) | |
| '-FOR--+-| SELECT Statement |----------------------------+-' |
| +-statement_id------------------------------------+ |
| | (1) | |
| +--------+-statement_id_var---------------------+-+ |
| | | (4) | | |
| | '-| EXECUTE PROCEDURE Statement |------' | |
| | (5) (6) | |
| '--------| EXECUTE FUNCTION Statement |-----------' |
| (1) (3) |
+--------SCROLL CURSOR--+-----------+--FOR--+-| SELECT Statement |----------------------------+-+
| '-WITH HOLD-' +-statement_id------------------------------------+ |
| | (1) | |
| +--------+-statement_id_var---------------------+-+ |
| | | (4) | | |
| | '-| EXECUTE PROCEDURE Statement |------' | |
| | (5) (6) | |
| '--------| EXECUTE FUNCTION Statement |-----------' |
| (5) (7) |
'--------CURSOR FOR--+-| SELECT with Collection-Derived Table |------+--------------------------'
| (8) |
'-| INSERT with Collection-Derived Table |------'
Select Options:
.-FOR READ ONLY-------------------------.
(9) | (1) |
|--| Subset of SELECT Statement |-------+--------FOR UPDATE--+----------------+-+--|
| .-,------. |
| V | |
'-OF----column-+-'Point 3 – When you do issue CLOSE cursor, all the WITH HOLD cursors will be closed.
Point 4 – Cursors with WITH HOLD creates some problems in CICS, before end-of-transaction we need to close all HOLD cursors, else, DB2 force SIGNON to restore the thread to initial state. But, We can use WITH HOLD in CICS, only thing we need to close properly.
Related
Srinimf 