DB2 Cursor builds a results table to hold all the rows retrieved by executing a SELECT statement. DB2 uses a cursor to make the rows, from the results table, available to the application program. A cursor identifies the current row of the results table. When you use a cursor, the program can retrieve each row sequentially from the results table until end-of-data (i.e the not found condition SQLCODE=100). The SELECT statement used must be within a DECLARE CURSOR statement and cannot include an INTO clause. The DECLARE CURSOR statement defines and names the cursor, identifying the set of rows to be retrieved with the SELECT statement of the cursor. The results table is processed very much like a sequential data set. The cursor must be opened (with an OPEN statement) before any rows are retrieved. A FETCH statement is used to retrieve the cursor’s current row. FETCH can be executed repeatedly until all rows have been retrieved. When the end-of-data condition occurs the cursor must be closed with a CLOSE statement. You can perform below mentioned distinct operations on cursors:
DECLARE DB2 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. Declarations for host variables that are referenced in a DECLARE CURSOR statement must precede the DECLARE CURSOR statement. The DECLARE CURSOR statement must precede any statement that references the cursor.
EXEC SQL DECLARE cursor-name CURSOR FOR SELECT column1, column2,… FROM table-name WHERE condition FOR UPDATE OF column,… FOR FETCH ONLY END-EXEC.
The DECLARE CURSOR statement is usually placed in Working-Storage but it can go in the Procedure Division.
FOR UPDATE OF Clause
This clause must be specified if you intend to update any (or all) of the rows in the identified table. In this clause you name each column you intend to update. If you do not specify the names of columns you will later update, you will receive an error code in the SQLCODE field when you try to update them. A column of the identified table can be updated even though it is not part of the results table. That is, it can be specified in the FOR UPDATE OF clause even if it was not in the SELECT clause.
FOR FETCH ONLY Clause
This clause is used to indicate that there is no intention to update the table. When it is coded DB2 may perform a block FETCH to improve efficiency.
Read-Only Results Table
The results table is read-only if the SELECT statement includes the DISTINCT keyword, a UNION operator, a column function, a GROUP BY clause, a HAVING clause, or an ORDER BY clause. It is also read-only if the FROM clause identifies a read-only view or identifies more than one table or view. If the results table is read-only then you cannot code the FOR UPDATE OF clause. This is a particular problem when a file of updates has a particular order to its records and you would like to code an ORDER BY clause so a merge can be performed.
WITH HOLD Option
A cursor may be declared WITH HOLD or WITHOUT HOLD.
Examples:
EXEC SQL DECLARE C1 … CURSOR FOR SELECT … WITH HOLD … EXEC SQL DECLARE C1 … CURSOR FOR SELECT … WITHOUT HOLD …
The default is WITHOUT HOLD. If the cursor is declared WITHOUT HOLD, it is closed at commit (all locks released and the cursor is closed).
If the cursor is declared WITH HOLD, the WITH HOLD designation prevents the cursor from being closed at commit. The cursor will remain open and positioned on the current row in the cursor at the time of commit. All page/row locks are released but the cursor position is maintained. The page/row lock is not required to maintain the current position.
A cursor declared with the WITH HOLD clause does not close after a COMMIT operation. A cursor that is not held closes after a COMMIT operation … the associated page/row locks are always released at COMMIT.
ROWSET Positioning (Multi-Row Processing)
ROWSET Positioning (also called “Multi-Row Processing”) is the ability to process multiple rows of a cursor simultaneously, as opposed to NON-ROWSET Positioning in which case you process one row at a time. It can be used for FETCH, INSERT, UPDATE, and DELETE within a cursor. Examples:
EXEC SQL DECLARE C1 … CURSOR FOR SELECT … WITH ROWSET POSITIONING … EXEC SQL DECLARE C1 … CURSOR FOR SELECT … WITHOUT ROWSET POSITIONING …
OPEN DB2 cursor
Readies the cursor for row retrieval. OPEN is an executable statement. It reads the SQL search fields, executes the SQL statement, and it builds the results table. This table is placed in Virtual Storage. It does not assign values to host variables, though.
EXEC SQL OPEN cursor-name END-EXEC.
If any host variables are specified in the SELECT statement then the values in them at the time of the OPEN are used in creating the results table.
FETCH DB2 cursor
Returns data from the results table one row at a time and assigns the values to specified host variables. If the results table is not built at cursor OPEN time, it is built FETCH by FETCH.
DB2 maintains the position of the current row until the next FETCH statement for the cursor is issued. Neither the UPDATE or DELETE statements alter the position of the current row within the results table, the DELETE statement simply marks the current row for deletion.
EXEC SQL FETCH cursor-name INTO :host-variable1,:host-variable2, … END-EXEC.
CLOSE DB2 cursor
Releases all resources used by the cursor.
EXEC SQL CLOSE cursor-name END-EXEC.
You can let DB2 automatically close the cursor when the application program terminates but this practice is not recommended.
UPDATE DB2 cursor
You can update the data of the current row retrieved by a program by using the UPDATE statement. To do this, issue an UPDATE … WHERE CURRENT OF statement which has the following format:
EXEC SQL UPDATE table-name SET column-A = value, column-B = value, … WHERE CURRENT OF cursor-name END-EXEC.
Only one row is updated the current row. The WHERE clause identifies the cursor that points to the row to be updated. Each column to be updated must have been named previously in the FOR UPDATE OF clause of the DECLARE CURSOR statement.
DELETE DB2 cursor
You can delete the current row retrieved by a program by using the DELETE statement. To do this, issue a DELETE … WHERE CURRENT OF statement which has the following format:
EXEC SQL DELETE FROM table-name WHERE CURRENT OF cursor-name END-EXEC.
Only one row is deleted from the current row. The WHERE clause identifies the cursor that points to the row to be deleted.
Examples
Scenario: Retrieve employee data and their associated department and manager information, navigating a multi-level hierarchy.
EXEC SQL DECLARE EMP_CURSOR CURSOR FOR
SELECT EMPNO, EMPNAME, DEPTNO, MGRNO FROM EMP
END-EXEC
EXEC SQL OPEN EMP_CURSOR
END-EXEC
PERFORM UNTIL SQLCODE NOT = 0
EXEC SQL FETCH EMP_CURSOR INTO :EMPNO, :EMPNAME, :DEPTNO, :MGRNO
END-EXEC
IF SQLCODE = 0
DISPLAY 'Employee:' :EMPNO, :EMPNAME
* Retrieve department information
EXEC SQL DECLARE DEPT_CURSOR CURSOR FOR
SELECT DEPTNAME FROM DEPT WHERE DEPTNO = :DEPTNO
END-EXEC
EXEC SQL OPEN DEPT_CURSOR
EXEC SQL FETCH DEPT_CURSOR INTO :DEPTNAME
EXEC SQL CLOSE DEPT_CURSOR
DISPLAY ' Department:' :DEPTNAME
* Retrieve manager information (recursively, if needed)
PERFORM GET-MANAGER
END-IF
END-PERFORM
EXEC SQL CLOSE EMP_CURSOR
END-EXEC
* Recursive procedure to retrieve manager information
PROCEDURE GET-MANAGER
IF MGRNO NOT = 0
EXEC SQL DECLARE MGR_CURSOR CURSOR FOR
SELECT EMPNAME FROM EMP WHERE EMPNO = :MGRNO
END-EXEC
EXEC SQL OPEN MGR_CURSOR
EXEC SQL FETCH MGR_CURSOR INTO :MGRNAME
EXEC SQL CLOSE MGR_CURSOR
DISPLAY ' Manager:' :MGRNAME
* Recursively call GET-MANAGER for manager's manager
PERFORM GET-MANAGER
END-IF
END-PROCEDURE
- Main Cursor (
EMP_CURSOR):- Retrieves basic employee data (EMPNO, EMPNAME, DEPTNO, MGRNO).
- Fetches each employee record using a loop.
- Department Cursor (
DEPT_CURSOR):- Opens within the loop for each employee.
- Retrieves department information (DEPTNAME) based on the employee’s DEPTNO.
- Closes after fetching the department details.
- Manager Cursor (
MGR_CURSOR):- Implemented as a recursive procedure (
GET-MANAGER) to handle potential multi-level hierarchies. - Opens if the employee has a manager (MGRNO ≠ 0).
- Retrieves manager’s name (EMPNAME) from the EMP table.
- Calls itself recursively to retrieve the manager’s manager, if any.
- Implemented as a recursive procedure (
Scenario: Build SQL statements dynamically based on user input and execute them using cursors. Allow users to filter records by department and salary range. Let users choose between viewing employee details or department summaries.
ACCEPT DEPTNO FROM USER
ACCEPT MIN-SALARY FROM USER
ACCEPT MAX-SALARY FROM USER
EXEC SQL PREPARE DYN-STMT FROM
'SELECT EMPNO, EMPNAME, SALARY FROM EMP WHERE DEPTNO = :DEPTNO
AND SALARY BETWEEN :MIN-SALARY AND :MAX-SALARY'
END-EXEC
EXEC SQL DECLARE DYN-CURSOR CURSOR FOR DYN-STMT
END-EXEC
EXEC SQL OPEN DYN-CURSOR
END-EXEC
PERFORM UNTIL SQLCODE NOT = 0
EXEC SQL FETCH DYN-CURSOR INTO :EMPNO, :EMPNAME, :SALARY
END-EXEC
IF SQLCODE = 0
DISPLAY 'EMPNO:' :EMPNO ' EMPNAME:' :EMPNAME ' SALARY:' :SALARY
END-IF
END-PERFORM
EXEC SQL CLOSE DYN-CURSOR
END-EXEC
Explanation:
- Accept user input for department number, minimum salary, and maximum salary.
- Build a dynamic SQL statement using those values as parameters.
- Prepare the dynamic statement for execution.
- Declare a cursor for the dynamic statement.
- Open the cursor to start fetching data.
- Fetch rows one by one and display the results.
- Close the cursor when finished.
ACCEPT ACTION FROM USER IF ACTION = 'VIEW-DETAILS' EXEC SQL PREPARE DYN-STMT FROM 'SELECT * FROM EMP WHERE EMPNO = :EMPNO' EXEC SQL DECLARE DYN-CURSOR CURSOR FOR DYN-STMT ACCEPT EMPNO FROM USER ELSE EXEC SQL PREPARE DYN-STMT FROM 'SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO' EXEC SQL DECLARE DYN-CURSOR CURSOR FOR DYN-STMT END-IF EXEC SQL OPEN DYN-CURSOR ... (Fetch and process results based on the chosen action)
Explanation:
- Accept user input for the desired action.
- Build and prepare a dynamic SQL statement tailored to the chosen action.
- Declare a cursor for the appropriate statement.
- Proceed with fetching and processing results based on the selected action.
Here are examples of processing data from multiple tables with complex relationships using nested cursors in COBOL programs, with explanations:
Scenario: Process data from multiple tables with complex relationships using nested cursors. Fetch customer orders along with the items associated with each order. Retrieve employee salaries, calculate bonuses based on performance, apply deductions based on benefits, and determine net pay.
EXEC SQL DECLARE ORDER_CURSOR CURSOR FOR
SELECT ORDER_ID, CUSTOMER_ID FROM ORDERS
END-EXEC
EXEC SQL DECLARE ITEM_CURSOR CURSOR FOR
SELECT ITEM_ID, PRODUCT_NAME, QTY FROM ORDER_ITEMS WHERE ORDER_ID = :ORDER_ID
END-EXEC
EXEC SQL OPEN ORDER_CURSOR
END-EXEC
PERFORM UNTIL SQLCODE NOT = 0
EXEC SQL FETCH ORDER_CURSOR INTO :ORDER_ID, :CUSTOMER_ID
END-EXEC
IF SQLCODE = 0
DISPLAY 'Order ID:' :ORDER_ID, 'Customer ID:' :CUSTOMER_ID
EXEC SQL OPEN ITEM_CURSOR
PERFORM UNTIL SQLCODE NOT = 0
EXEC SQL FETCH ITEM_CURSOR INTO :ITEM_ID, :PRODUCT_NAME, :QTY
END-EXEC
IF SQLCODE = 0
DISPLAY ' Item ID:' :ITEM_ID, 'Product:' :PRODUCT_NAME, 'Quantity:' :QTY
END-IF
END-PERFORM
EXEC SQL CLOSE ITEM_CURSOR
END-IF
END-PERFORM
EXEC SQL CLOSE ORDER_CURSOR
END-EXEC
Explanation:
- Outer cursor
ORDER_CURSORfetches each order record. - For each order, the inner cursor
ITEM_CURSORis opened, filtering items based on the current order ID. - The inner cursor loops through all items for the order, displaying their details.
- Inner cursor is closed after processing each order’s items.
EXEC SQL DECLARE EMP_CURSOR CURSOR FOR
SELECT EMPNO, SALARY, PERF_RATING FROM EMP
END-EXEC
EXEC SQL DECLARE BONUS_CURSOR CURSOR FOR
SELECT BONUS_RATE FROM BONUS_SCHEME WHERE PERF_RATING = :PERF_RATING
END-EXEC
EXEC SQL DECLARE DEDUCTION_CURSOR CURSOR FOR
SELECT DEDUCTION_AMT FROM DEDUCTIONS WHERE EMPNO = :EMPNO
END-EXEC
EXEC SQL OPEN EMP_CURSOR
END-EXEC
PERFORM UNTIL SQLCODE NOT = 0
EXEC SQL FETCH EMP_CURSOR INTO :EMPNO, :SALARY, :PERF_RATING
END-EXEC
IF SQLCODE = 0
EXEC SQL OPEN BONUS_CURSOR
EXEC SQL FETCH BONUS_CURSOR INTO :BONUS_RATE
EXEC SQL CLOSE BONUS_CURSOR
COMPUTE BONUS = SALARY * BONUS_RATE
EXEC SQL OPEN DEDUCTION_CURSOR
PERFORM UNTIL SQLCODE NOT = 0
EXEC SQL FETCH DEDUCTION_CURSOR INTO :DEDUCTION_AMT
END-EXEC
IF SQLCODE = 0
COMPUTE NET_PAY = SALARY + BONUS - DEDUCTION_AMT
DISPLAY 'Employee:' :EMPNO, 'Net Pay:' :NET_PAY
END-IF
END-PERFORM
EXEC SQL CLOSE DEDUCTION_CURSOR
END-IF
END-PERFORM
EXEC SQL CLOSE EMP_CURSOR
END-EXEC
Explanation:
- Outer cursor
EMP_CURSORfetches each employee record. - For each employee, the
BONUS_CURSORcalculates the bonus based on performance. - The
DEDUCTION_CURSORretrieves deductions for the employee. - Net pay is calculated using salary, bonus, and deductions.
Conclusion
Understanding DB2 cursors in COBOL is essential for effective database interactions. Cursors provide a mechanism to navigate and process result sets, but their usage should be approached judiciously to ensure optimal performance. By following best practices and leveraging the power of SQL in COBOL programs, developers can seamlessly integrate database operations into their applications.