Exclusive DB2 Cursors Simplified Tutorial

What 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.

Related posts