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

You can declare all cursor definitions in working storage section.
  Open the cursor ( Main 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

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.

Related posts

Author: Srini

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