DB2 Cursor use in COBOL Programs

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

1. Main Cursor (EMP_CURSOR):
   • Retrieves basic employee data (EMPNO, EMPNAME, DEPTNO, MGRNO).
   • Fetches each employee record using a loop.
2. 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.
3. 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.

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:

1. Accept user input for department number, minimum salary, and maximum salary.
2. Build a dynamic SQL statement using those values as parameters.
3. Prepare the dynamic statement for execution.
4. Declare a cursor for the dynamic statement.
5. Open the cursor to start fetching data.
6. Fetch rows one by one and display the results.
7. 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:

1. Accept user input for the desired action.
2. Build and prepare a dynamic SQL statement tailored to the chosen action.
3. Declare a cursor for the appropriate statement.
4. 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:

1. Outer cursor ORDER_CURSOR fetches each order record.
2. For each order, the inner cursor ITEM_CURSOR is opened, filtering items based on the current order ID.
3. The inner cursor loops through all items for the order, displaying their details.
4. 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:

1. Outer cursor EMP_CURSOR fetches each employee record.
2. For each employee, the BONUS_CURSOR calculates the bonus based on performance.
3. The DEDUCTION_CURSOR retrieves deductions for the employee.
4. 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.

Read DB2 blogs : Click Here IBM DB2 Manual :Click Here