Cursors in COBOL with DB2

Why a cursor

SELECT INTO is for one row. Zero rows give SQLCODE +100. Two or more rows give -811. As soon as a WHERE clause can match a set, you declare a cursor. Think of the cursor as a bookmark on a result table Db2 built (or is scanning) for your program.

COBOL cursor processing

Put DECLARE CURSOR in WORKING-STORAGE (many shops keep all DECLAREs together before PROCEDURE DIVISION). EXEC SQL and END-EXEC each occupy their own source lines. Host variables in the SELECT list and WHERE clause are prefixed with a colon.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
       WORKING-STORAGE SECTION.
       EXEC SQL
            INCLUDE SQLCA
       END-EXEC.
       01  HV-DEPT            PIC X(3).
       01  HV-EMPNO           PIC X(6).
       01  HV-LASTNAME        PIC X(15).
 
       EXEC SQL
            DECLARE C-EMP CURSOR FOR
             SELECT EMPNO, LASTNAME
               FROM DSN8C10.EMP
              WHERE WORKDEPT = :HV-DEPT
              ORDER BY EMPNO
       END-EXEC.
 
       PROCEDURE DIVISION.
           MOVE 'A00' TO HV-DEPT
           EXEC SQL
                OPEN C-EMP
           END-EXEC
           PERFORM UNTIL SQLCODE NOT = 0
                EXEC SQL
                     FETCH C-EMP
                      INTO :HV-EMPNO, :HV-LASTNAME
                END-EXEC
                IF SQLCODE = 0
                   PERFORM 2000-PROCESS-ROW
                END-IF
           END-PERFORM
           IF SQLCODE NOT = +100
              PERFORM 9000-SQL-ERROR
           END-IF
           EXEC SQL
                CLOSE C-EMP
           END-EXEC.

Rules that bite beginners:

• OPEN uses today's host variables. DECLARE does not snapshot HV-DEPT. Set HV-DEPT before OPEN. Changing it after OPEN does not re-filter the cursor; CLOSE and OPEN again.
• FETCH INTO list must match the SELECT list in number and compatible types. A mismatch can set SQLWARN3 or fail the statement.
• Nullable columns need indicator variables on FETCH or you get -305 when a null arrives.
• Always CLOSE. Program end and COMMIT (without WITH HOLD) also close, but an explicit CLOSE makes the next OPEN legal and releases resources sooner.
• You cannot FETCH a closed or never-opened cursor. Check SQLCODE after OPEN before entering the loop.

FOR UPDATE and positioned UPDATE/DELETE

If you will UPDATE or DELETE the current row, declare the cursor FOR UPDATE OF column-list (or FOR UPDATE). After a successful FETCH:

1
2
3
4
5
       EXEC SQL
            UPDATE DSN8C10.EMP
               SET PHONENO = :HV-PHONE
             WHERE CURRENT OF C-EMP
       END-EXEC.

WHERE CURRENT OF names the cursor, not a key predicate. The cursor must be positioned on a row. Read-only SELECT (DISTINCT, join, UNION, FOR FETCH ONLY) cannot be a positioned-update cursor. Ambiguous cursors (Db2 is not sure you will update) can disable blocking and hurt performance — add FOR FETCH ONLY when you will only read.

WITH HOLD

DECLARE C-EMP CURSOR WITH HOLD FOR ... keeps the cursor open across COMMIT. After commit the position is after the last row you fetched; FETCH next to continue. You cannot positioned-UPDATE until you FETCH again. SQL ROLLBACK closes held cursors. CICS/IMS: do not use SQL COMMIT; WITH HOLD interacts with the transaction manager's syncpoint (see the COMMIT in COBOL page). Held cursors across a CICS pseudo-conversational RETURN do not survive end of task the way beginners hope.

Rowset processing and multi-row FETCH

Each FETCH is a trip into Db2. For thousands of rows that overhead adds up, especially through DDF. Rowset processing returns a block of rows in one FETCH.

Host variables become arrays (or a table with OCCURS). The array dimension must be greater than or equal to the number of rows requested on FETCH.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
       01  WS-FETCH-COUNT     PIC S9(9) COMP-5 VALUE 100.
       01  EMP-ROWSET.
           05  HV-EMPNO-ARR   PIC X(6)  OCCURS 100 TIMES.
           05  HV-LNAME-ARR   PIC X(15) OCCURS 100 TIMES.
           05  IND-LNAME-ARR  PIC S9(4) COMP-5 OCCURS 100 TIMES.
 
       EXEC SQL
            DECLARE C-BULK CURSOR WITH ROWSET POSITIONING FOR
             SELECT EMPNO, LASTNAME
               FROM DSN8C10.EMP
              WHERE WORKDEPT = :HV-DEPT
              ORDER BY EMPNO
       END-EXEC.
 
       EXEC SQL
            OPEN C-BULK
       END-EXEC.
       PERFORM UNTIL SQLCODE = +100
            EXEC SQL
                 FETCH NEXT ROWSET FROM C-BULK
                  FOR :WS-FETCH-COUNT ROWS
                  INTO :HV-EMPNO-ARR,
                       :HV-LNAME-ARR :IND-LNAME-ARR
            END-EXEC
            IF SQLCODE = 0 OR SQLCODE = +100
               PERFORM 2000-PROCESS-ROWSET
            ELSE
               PERFORM 9000-SQL-ERROR
            END-IF
       END-PERFORM.

On a rowset FETCH, SQLCODE +100 can mean you reached the end and the last arrays still hold a partial set. Read SQLERRD(3) (or GET DIAGNOSTICS ROW_COUNT) and process that many occurrences. Do not assume 100 rows just because you asked for 100. FOR n ROWS accepts a host variable or integer; the documented maximum is 32767.

FETCH FIRST n ROWS ONLY on the SELECT limits the result table. FOR n ROWS on FETCH limits how many rows this call copies. They are not the same clause. Mixing FETCH NEXT (one row) and FETCH NEXT ROWSET in one loop shifts the current position in ways that surprise people: after rows 1–10 as a rowset, a single FETCH NEXT is row 2 of the next positioning, not “skip a block.”

Positioned UPDATE/DELETE on a rowset cursor: WHERE CURRENT OF updates or deletes the entire current rowset. FOR ROW n OF ROWSET targets one row inside that rowset.

Multi-row INSERT

The same arrays can feed INSERT. One SQL statement inserts many rows:

1
2
3
4
5
6
       EXEC SQL
            INSERT INTO HR.DAILY_LOG
                 (EMPNO, LOG_DATE, MSG)
            VALUES (:HV-EMPNO-ARR, :HV-DATE-ARR, :HV-MSG-ARR)
            FOR :WS-INSERT-COUNT ROWS
       END-EXEC.

• FOR n ROWS — n is a constant or host variable; arrays must be at least that long
• ATOMIC (typical static default) — if any row fails, the whole INSERT fails and no row from that statement is kept
• NOT ATOMIC CONTINUE ON SQLEXCEPTION — continue after a bad row; use GET DIAGNOSTICS to see per-row outcomes

Multi-row INSERT is not a cursor, but shops learn it next to rowset FETCH because both need host-variable arrays and both exist to cut SQL call volume. Load utilities still win for huge conversions; use multi-row INSERT for application-sized batches.

Practical COBOL habits

• Name cursors with a prefix (C-EMP) so they do not collide with COBOL data-names
• One cursor, one purpose; do not reuse an open cursor for a different filter without CLOSE/OPEN
• COMMIT frequency: without WITH HOLD, commit closes the cursor — reopen after commit in a restartable batch browse
• Isolation (CS, UR, RS, RR) comes from BIND or a WITH clause on the SELECT, not from the word “cursor”

Explain It Like I'm Five

A cursor is a bookmark in a picture book. OPEN is opening the book to the page that matches your search. FETCH is looking at one picture and copying it onto your paper. When there are no pictures left, the librarian says “that’s all” (+100). CLOSE is putting the book back. A rowset FETCH is like photocopying a whole handful of pages at once so you do not walk to the desk a hundred times. Multi-row INSERT is handing the librarian a stack of new pages in one trip instead of one page per trip.

Exercises

1. Rewrite a singleton SELECT that can return many employees in a department as a cursor FETCH loop with +100 handling.
2. Add indicator variables for a nullable PHONENO column on FETCH.
3. Change the cursor to WITH ROWSET POSITIONING and FETCH 50 rows; print how you would use SQLERRD(3) on the last FETCH that returns +100.
4. Explain why FOR FETCH ONLY can be faster than an ambiguous cursor.
5. Write a multi-row INSERT of 20 log rows and state what ATOMIC vs NOT ATOMIC would mean if row 7 violates a unique index.