Mainfrme: 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.

Simple Life cycle of Nested cursor

DECLARE Cursor

You can declare all cursor definitions in working storage section.

OPEN CURSOR
  Open the 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.
Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.