Learning Objectives
After completing the session, you will be able to:
Identify data with multiple rows Cursor - Introduction
COBOL operates on data a row at a time; SQL operates on data a set at time. You have to use cursors to process a result table that contains more than one row in a COBOL program. If multiple rows are returned by a SELECT statement not coded using a cursor, then DB2 returns a -811 SQLCODE.
A cursor is a pointer that identifies the current row in a result table.
As a programmer:
You declare a cursor and define an SQL statement for that cursor.
After that, you can use the cursor in much the same manner as a sequential file. The cursor is opened, rows are fetched from the cursor one row at a time and then the cursor is closed.
Cursor Processing
SQL statements used for Cursor processing and their COBOL equivalent file processing statements are as follows:
SQL Statement Description COBOL equivalent for File
Processing DECLARE CURSOR Defines a result table and names a cursor for
it.
None
Open <cursor-name> Creates the result table and positions the cursor before the first row in the table
OPEN <file-name>
FETCH <cursor-name> Fetches the next row from the result table READ <file-name>
CLOSE <cursor-name> Close the result table CLOSE <file-name>
Cursor Declaration
The DECLARE cursor syntax is as follows.
EXEC SQL
DECLARE <cursor-name> CURSOR [WITH HOLD] FOR <SQL Select clause>
[FOR UPDATE OF <update-column1, update-column2, …>]
END-EXEC.
The DECLARE cursor defines the cursor, gives it a name unique to the program in which it is embedded, and assigns an SQL statement to the cursor name. The DECLARE statement does not execute the SQL statement; it merely defines the SQL statement. You can code the DECLARE statement in the Working-Storage Section or in the Procedure Division, as long as it appears before any other statements that refer to the cursor. As it is a declarative statement and not an action statement, it is advisable to keep it in Working-Storage Section
WITH HOLD
When there is an explicit COMMIT statement in the COBOL program while processing the cursor, the commit will automatically close the cursor. But if you need your cursor to be opened even after the explicit commit, you need to declare the cursor with “WITH HOLD” which prevents the cursor from being closed as a result of the explicit commit.
Cursor Open
OPEN is an executable statement. It executes the SQL statement, and builds the result table specified by the DECLARE CURSOR statement and positions the cursor before the first row. The syntax is as follows:
EXEC SQL
OPEN <cursor-name>
END-EXEC
Cursor Fetch
The FETCH syntax is as follows EXEC SQL
FETCH <cursor-name>
INTO :<host-variable1>, :< host-variable2>,….
END-EXEC
The INTO clause specifies either the list of host variables or host structure where a row in the result table is stored in the COBOL program. When a FETCH statement is executed DB2 advances the cursor one row in the result table. Then, it moves the column data from the current row into the COBOL host variables. A FETCH statement is typically coded in a loop that reads and processes
Cursor Close
The CLOSE syntax is as follows:
EXEC SQL
CLOSE <cursor-name>
END-EXEC
During CLOSE, DB2 releases all resources used by the cursor. DB2 closes the entire cursor-controlled result table when a program ends and while executing the explicit COMMIT.
Using Cursor for Data Modification
UPDATE and DELETE SQL statements, like the SELECT statement, operate on data a set at a time.
To first read the data before modifying it, you declare the cursor with a special FOR UPDATE OF clause. In the UPDATE or DELETE statement, we need to use WHERE CURRENT OF.
Example:
EXEC SQL
DECLARE CS-C1 CURSOR FOR
SELECT DEPT_NO, DEPT_MGRNO
FROM TB_DEPT
WHERE DEPT_ADMRDEPT = :DEPT-ADMRDEPT FOR UPDATE OF DEPT_MGRNO
END-EXEC.
The corresponding UPDATE statement is as follows.
EXEC SQL
FETCH CS-C1
INTO :DEPT-NO, :DEPT-NAME, :DEPT-MGRNO END-EXEC.
IF SQLCODE = 0 EXEC SQL
UPDATE TB_DEPT
SET DEPT_MGRNO = '000060' WHERE CURRENT OF CS-C1
END-EXEC ELSE
….
“FOR UPDATE OF” clause ensures data integrity. This causes a lock to be taken on the data page when it is fetched, ensuring that no other process can modify the data before our program processes it. If the program simply FETCHs without the FOR UPDATE OF specification and then issues an SQL statement to modify the data, another process can modify the data in between, thereby invalidating our program's modification, overwriting our program's modification or both.
Try It Out
Problem Statement:
Create a program which displays all the rows of TB_RATE table.
Code:
EXEC SQL
EXIT.
WS-DB2-ERR-MESSAGE
Refer File Name: Cursor_Program_CURSORPG_Session#36_Slide#07 to obtain soft copy of the program code
Summary
You need to use cursors while retrieving more than one row:
SQL Statement Description COBOL equivalent for File
Processing DECLARE CURSOR Defines a result table and
names a cursor for it
None
Open <cursor-name> Creates the result table and positions the cursor before the first row in the table
OPEN <file-name>
FETCH <cursor-name> Fetches the next row from the result table
READ <file-name>
CLOSE <cursor-name> Close the result table CLOSE <file-name>
Test Your Understanding
1. Why do you need to use Cursor?
2. Explain the different SQL statements to be used for Cursor Processing.
3. How and why do you need to use cursor for updating the data?
Exercises
Develop and execute a COBOL-DB2 program which displays all the patient information available in the Patient table.