DB2- Programming With Cursors – Part(1 of 2)

SriniForumWhat is cursor?

CURSOR is a DB2 object. It deals with a set of rows. Why we need to go for CURSOR- since we have already SELECT statement . The answer is – if  SELECT statement retrieves more than one row, COBOL program may not handle it. So we need at a time one row.

At this time, we need to introduce CURSOR. The advantage of it is, though we get multiple rows for a given SELECT statement, CURSOR will process at a time one row to the COBOL host variable.

Life Cycle of a CURSOR?

DECLARE – It is only a definition. It does not execute SQL statement.

OPEN – It EXECUTES SQL statement. It may or may not build result table. And, keeps cursor position in the result table just before first row of result table

FETCH- It fetches data from result table into COBOL host variable

CLOSE- It closes the cursor, and releases all resources

Usage of CURSOR?

If simple SQL statement return only one row, it is up to you using CURSOR. We will get -811 error, when singleton SQL statement retrieves more than a row.

The Solution to resolve -811?

  1.  Use FETCH FIRST ROW ONLY
  2. Use CURSORS

How to handle multiple rows in CURSOR?

We need to put FETCH in the loop. So that , we can access one by one all the rows till end of result table.

Keep reading my Part-2 post.

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.