Here’s All About Nested Cursor in DB2

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.