Cursors and Result Sets

When you write embedded SQL in a COBOL program, a simple SELECT INTO statement works perfectly if you are absolutely certain the query will return exactly one row. However, most real-world database queries return multiple rows—a list of employees, a batch of transactions, or a catalog of products. COBOL cannot process an entire set of rows in a single operation.

To bridge this gap, DB2 uses cursors. A cursor acts as a pointer that navigates through the result set of a query, allowing your COBOL program to fetch and process rows one at a time.

The Cursor Lifecycle

Working with cursors in COBOL involves four distinct steps:

1. DECLARE: Define the cursor and the SQL query it will execute.
2. OPEN: Execute the query and prepare the result set.
3. FETCH: Retrieve the next row from the result set into host variables.
4. CLOSE: Release the resources held by the cursor.

(Note: The DB2 examples in this lesson are illustrative. DB2 requires a precompiler that transforms EXEC SQL statements into native calls, which standard compilers like GnuCOBOL cannot process directly.)

Host Variables

Before working with cursors, your program must have memory locations prepared to receive the data. These are called host variables. They are standard COBOL data items:

       IDENTIFICATION DIVISION.
       PROGRAM-ID. HOSTVARS.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01  DCL-EMPLOYEE.
           05 WS-EMP-ID    PIC X(5) VALUE '101  '.
           05 WS-EMP-NAME  PIC X(15) VALUE 'ALICE          '.
       PROCEDURE DIVISION.
           DISPLAY "FETCHED ID: " WS-EMP-ID " NAME: " WS-EMP-NAME.
           STOP RUN.

FETCHED ID: 101   NAME: ALICE          

The Complete Lifecycle in Code

You must first declare the cursor to tie a name to a specific SELECT statement. Then you OPEN the cursor to execute the query, FETCH rows one by one, and finally CLOSE the cursor to free resources.

       EXEC SQL
           DECLARE EMP-CURSOR CURSOR FOR
           SELECT EMP_ID, EMP_NAME
           FROM EMPLOYEE
       END-EXEC.

       EXEC SQL OPEN EMP-CURSOR END-EXEC.

       EXEC SQL
           FETCH EMP-CURSOR
           INTO :WS-EMP-ID, :WS-EMP-NAME
       END-EXEC.

       EXEC SQL CLOSE EMP-CURSOR END-EXEC.

This cannot run here — EXEC SQL needs a DB2 precompiler and a live database, neither of which exists outside a mainframe, so there is no output block to show. Read the four verbs in order: DECLARE names the query without executing it, OPEN runs it and positions before the first row, FETCH pulls one row into your host variables, CLOSE releases the result set.

By default, cursors move forward only. If you need to move backward or jump to specific rows, you can define a scrollable cursor by using the SCROLL keyword (e.g., DECLARE C1 SCROLL CURSOR FOR...).

Evaluating SQLCODE Natively

How do you know if a FETCH worked? The database updates the SQLCODE field in the SQLCA communication block. When a FETCH successfully retrieves a row, SQLCODE is 0. When the result set is exhausted (no more rows to fetch), SQLCODE becomes 100. If an error occurs, it is negative.

Here is a standard COBOL program that simulates checking the SQLCODE using an EVALUATE statement after an operation:

       IDENTIFICATION DIVISION.
       PROGRAM-ID. EVALSQL.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01 SQLCODE PIC S9(9) COMP VALUE -803.
       PROCEDURE DIVISION.
           EVALUATE SQLCODE
               WHEN 0
                   DISPLAY "SUCCESS"
               WHEN 100
                   DISPLAY "NOT FOUND"
               WHEN OTHER
                   DISPLAY "ERROR: " SQLCODE
           END-EVALUATE.
           STOP RUN.

ERROR: -000000803

The Fetch Loop

The most common pattern for processing cursors is a PERFORM UNTIL loop. How does the loop know when it has reached the end of the result set? It checks the SQLCODE field within the SQLCA.

When a FETCH statement successfully retrieves a row, SQLCODE is 0. When the result set is exhausted (there are no more rows to fetch), SQLCODE becomes 100. If an error occurs, SQLCODE is negative (e.g., -818).

       PERFORM UNTIL SQLCODE = 100 OR SQLCODE < 0
           EXEC SQL
               FETCH EMP-CURSOR
               INTO :WS-EMP-ID, :WS-EMP-NAME
           END-EXEC
           IF SQLCODE = 0
               DISPLAY 'FETCHED: ' WS-EMP-NAME
           END-IF
       END-PERFORM.

Run against an EMPLOYEE table holding Alice, Bob and Charlie, that loop would print one FETCHED: line per row and then stop when SQLCODE turns 100. Those three names are an illustration, not captured output — as above, nothing here was executed.

Common Misconceptions

A frequent point of confusion is thinking that a cursor loads the entire result set into your COBOL program’s memory. A cursor is just a pointer. It manages state on the database server side and only transmits exactly one row to your COBOL memory per FETCH call.

Another misconception is that you can update the row currently pointed to by any cursor. If you intend to update rows while fetching, you must explicitly append FOR UPDATE to the DECLARE CURSOR statement.

To understand the fetch loop mechanics natively in COBOL, predict the output of this simulated cursor loop:

       IDENTIFICATION DIVISION.
       PROGRAM-ID. SIMCURS.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       01 SQLCODE PIC S9(9) COMP VALUE 0.
       01 F-COUNT PIC 9 VALUE 0.
       PROCEDURE DIVISION.
           PERFORM UNTIL SQLCODE = 100
               ADD 1 TO F-COUNT
               IF F-COUNT > 2 MOVE 100 TO SQLCODE
               ELSE DISPLAY 'ROW ' F-COUNT END-IF
           END-PERFORM
           DISPLAY 'DONE'
           STOP RUN.

ROW 1
ROW 2
DONE

Understanding how to control cursor flow using SQLCODE is vital for correctly processing data in DB2 COBOL applications.