Cursor
Summary
Why Cursor?
Assume that the SELECT WHERE condition retrieves more than one row while reading the DB2 table in the application program. In that case, the query returns the first row from the result set to the host variables from the result set and returns the SQLCODE as -811.
As per the standard error handling, the program gets abend if SQL query returns SQLCODE other than ZERO or 100.
To avoid the program abending, we need to retrieve the SQLCODE from the executed query and validate it like below -
IF SQLCODE EQUALS ZERO
OR SQLCODE EQUALS -811
Continue-the-program-flow
ELSE
abend-the-program
END-IF.In the above case, we can only retrieve any row that satisfies the condition and can’t retrieve the further rows that satisfy the same condition.
If our requirement is to retrieve the first row even though the WHERE condition coded retrieving more than one row, then handling the SQLCODE is sufficient.
However, if we need to process all the satisfied rows one by one, a simple SELECT statement can't do it on a single iteration.
This requires a unique mechanism to retrieve all rows and process them one by one. The mechanism is known as "CURSOR" in DB2.
What is Cursor?
The cursor is used to retrieve rows from a result set and process them one by one. The process is almost the same as pointing the finger at a specific line on the printed page.
The application program retrieves and processes one or more rows that satisfy the WHERE condition in the SELECT statement using the cursor.
The application program processes the rows one by one sequentially after being retrieved. It’s like the sequential access of the file. The rows can be retrieved based on the search condition (WHERE) provided.
We can use the cursor in two areas to retrieve the data from the table, and those are -
- Application program
- DB2 stored procedure
We will discuss the application program with the cursor concept in this topic.
The result set is also referred to as the result table while using the cursor.
Cursor Life Cycle -
The cursor plays an important role in retrieving the rows one by one from the result table. The cursor life cycle has the following phases -
| Phase | Description |
|---|---|
| Declare Cursor | Declares a cursor in the application program. |
| Open Cursor | Opens the cursor declared in the application program. |
| Fetch Cursor | Retrieves the current positioned row data and assigns it to the corresponding variable(s) / host variable(s). |
| Close Cursor | Closes the cursor specified with it. |
Cursor Types -
The CURSOR can divide into various types based on its definition and usage in the application program. Those are -
- Data modification based cursors
- Data modification statement based cursors
- Positioning based cursors
- Modified data reflection-based cursors
- Processing stability based cursors
- Mixed feature cursors
All cursor types are explained in detail in the Cursor Types topic.
Practical Example -
Scenario - The below example describes how the cursor is declared and used in the COBOL + DB2 program.
Requirement - Display employee id, name, designation from department 1000 in the employee_details table.
Input- employee_details table
CURSOR Example Code -
----+----1----+----2----+----3----+----4----+----5----+----6----+----7--
***************************** Top of Data ******************************
IDENTIFICATION DIVISION.
PROGRAM-ID. CREADONL.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
EXEC SQL
INCLUDE EMPLDET
END-EXEC.
* DECLARING CURSOR
EXEC SQL
DECLARE CSR1 CURSOR FOR
SELECT EMP_ID,
EMP_NAME,
DESIGNATION
FROM EMPLOYEE_DETAILS
WHERE DEPT_ID = 1000
FOR FETCH ONLY
END-EXEC.
PROCEDURE DIVISION.
* OPENING CURSOR
EXEC SQL
OPEN CSR1
END-EXEC.
* FETCHING CURSOR LOOP UNTIL END OF RESULT TABLE
IF SQLCODE EQUAL ZERO
PERFORM FETCH-EMP-DETAILS
THRU FETCH-EMP-DETAILS-EXIT
UNTIL SQLCODE NOT EQUAL 0
END-IF.
* CLOSING CURSOR
EXEC SQL
CLOSE CSR1
END-EXEC.
STOP RUN.
FETCH-EMP-DETAILS.
* FETCH CURSOR
EXEC SQL
FETCH CSR1
INTO :EMP-ID,
:EMP-NAME,
:DESIGNATION
END-EXEC.
EVALUATE SQLCODE
WHEN ZERO
DISPLAY EMP-ID, "|", EMP-NAME, "|",
DESIGNATION
WHEN 100
CONTINUE
WHEN OTHER
DISPLAY "DB2 ERROR: ", SQLCODE
END-EVALUATE.
FETCH-EMP-DETAILS-EXIT.
EXIT.
**************************** Bottom of Data ****************************
Output -
