The cursor in DB2 is an important logic to read records one by one from multiple tables. There are scenarios to declare cursors more than one to achieve desired result. I have given here step by step flow to declare nested cursors.
What is nested cursor in DB2?
A cursor within the cursor is called nested cursor.
Life cycle of cursor is:
Declare cursor==>Open cursor==>Fetch cursor==>Close cursor
Notes: Actual execution of Cursor starts with OPEN cursor statement. You can read my other post on cursors advanced topics.
Life cycle of Nested cursor
DECLARE Cursor You can declare all cursor definitions in working storage section. OPEN CURSOR Open the cursor ( Main cursor) FETCH CURSOR When Source=10 OPEN CURSOR-1 FETCH CURSOR-1 Close CURSOR-1 When Source=20 OPEN CURSOR-2 FETCH CURSOR-2 Close CURSOR-2 CLOSE CURSOR
The way coding cursor logic in above fashion is called Nested Cursor.
Steps you need to take care in nested cursors
- Need to close all the cursors properly.
- Always look for Query performance. Since multiple cursors definitely will take more CPU time.
- Only in special cases/unavoidable cases, nested cursors are recommended.