Cursor DECLARE, OPEN, FETCH, and CLOSE in DB2

Cursor lifecycle

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
       EXEC SQL
            DECLARE C_EMP CURSOR FOR
             SELECT EMPNO, LASTNAME, SALARY
               FROM DSN8C10.EMP
              WHERE WORKDEPT = :WS-DEPT
              ORDER BY EMPNO
       END-EXEC.
 
       EXEC SQL OPEN C_EMP END-EXEC.
 
       PERFORM UNTIL SQLCODE NOT = 0
           EXEC SQL
                FETCH C_EMP
                 INTO :WS-EMPNO, :WS-LASTNAME, :WS-SALARY
           END-EXEC
           IF SQLCODE = 0
               PERFORM PROCESS-ROW
           END-IF
       END-PERFORM.
 
       IF SQLCODE = 100
           CONTINUE
       END-IF.
 
       EXEC SQL CLOSE C_EMP END-EXEC.

Check SQLCODE after OPEN and each FETCH. +100 is end of data—not a crash. 0 is a row. Negative is an error; do not keep FETCHing. Re-OPEN after CLOSE if you need the query again with new host-variable values.

DECLARE CURSOR

DECLARE associates a name with a SELECT (or WITH ... SELECT). Optional clauses you will see:

• WITH HOLD — stay open across COMMIT (next page)
• WITH RETURN — result set to a caller (stored procedures)
• ASENSITIVE / INSENSITIVE / SENSITIVE STATIC / SENSITIVE DYNAMIC
• SCROLL — scrollable; omit for forward-only
• WITH ROWSET POSITIONING / WITHOUT ROWSET POSITIONING (default)
• FOR UPDATE OF col,... or FOR READ ONLY / FOR FETCH ONLY

DECLARE is processed at precompile/bind for static SQL. It does not use the host variable values until OPEN. Putting DECLARE inside a COBOL paragraph that “runs” at runtime does not delay the declaration—the cursor still exists for the compilation unit.

OPEN

OPEN executes the SELECT, applying host variables as they are at OPEN. Changing :WS-DEPT after OPEN does not change the result. OPEN positions the cursor before the first row. Failure to OPEN before FETCH is an error (cursor not open).

Isolation, access path, and whether a work file / result table is materialized depend on the SELECT, bind options, and sensitivity. A large ORDER BY without a supporting index may sort into a work file at OPEN—OPEN can be the expensive statement, not FETCH.

FETCH

FETCH copies the current row into host variables (or a descriptor / arrays). The number of INTO targets must match the SELECT list. Use indicator variables for nullable columns.

FETCH orientations

1
2
3
4
5
6
FETCH NEXT FROM C_EMP INTO :A, :B, :C;
FETCH FIRST FROM C_EMP INTO :A, :B, :C;
FETCH LAST FROM C_EMP INTO :A, :B, :C;
FETCH PRIOR FROM C_EMP INTO :A, :B, :C;
FETCH ABSOLUTE 10 FROM C_EMP INTO :A, :B, :C;
FETCH RELATIVE -1 FROM C_EMP INTO :A, :B, :C;

FETCH NEXT is implied if you write FETCH cursor INTO .... BEFORE and AFTER do not use INTO. INSENSITIVE FETCH vs SENSITIVE FETCH on a SENSITIVE STATIC cursor controls whether you see the latest copy of the current row or the original result-table values—advanced option on the next page.

CLOSE

CLOSE releases the cursor’s result. After CLOSE, FETCH is invalid until OPEN again. Implicit close happens at COMMIT for non-held cursors, at ROLLBACK, at thread/program end, and in CICS at end of task even for WITH HOLD across EOT. Get in the habit of explicit CLOSE so resources do not linger in error paths.

Static vs dynamic cursors

Static: SELECT text is in the source, bound into the package. Fast path, stable plan, host variables as parameter markers.

Dynamic: you build SQL at run time, PREPARE it, then open a cursor on the prepared statement (DECLARE CURSOR FOR stmt-name, or ALLOCATE CURSOR in SQL DA / CLI). Use parameter markers (?) instead of concatenating user input. Dynamic is for truly variable SQL; do not make every report dynamic “just in case.”

Forward-only vs scrollable

Default cursors are forward-only: FETCH NEXT only, like a sequential tape. SCROLL enables FIRST, LAST, PRIOR, ABSOLUTE, RELATIVE. Scrolling often requires a declared temporary result (especially INSENSITIVE / SENSITIVE STATIC) and costs memory and OPEN time. Use scroll only when the UI truly needs to go backward.

Sensitive vs INSENSITIVE

• INSENSITIVE — result is a snapshot; later committed changes by others are not visible through this cursor
• SENSITIVE STATIC — result keys are fixed at OPEN; updates/deletes to those rows can be visible (holes for deletes)
• SENSITIVE DYNAMIC — cursor sees the live table within isolation rules (inserts can appear)
• ASENSITIVE — Db2 chooses (often INSENSITIVE for read-only)

Read-only report cursors: INSENSITIVE or ASENSITIVE plus FOR READ ONLY. Updatable cursors cannot be INSENSITIVE.

Cursor stability (isolation)

Cursor stability (CS) is isolation, not a DECLARE keyword. Under CS, Db2 can release the share lock on a fetched row when you move to the next row (unless you updated it). That is why CS is the usual bind default for OLTP: you do not hold every fetched row until COMMIT. RR/RS hold more; UR holds essentially none for read-only. Declare the cursor; pick isolation on BIND or WITH CS on the SELECT.

Cursor blocking

Blocking fetches a group of rows from Db2 into an application buffer so each FETCH does not make a separate trip. It requires a read-only cursor. Ambiguous cursors (Db2 cannot prove you will not UPDATE/DELETE WHERE CURRENT OF) disable blocking. Write FOR FETCH ONLY or FOR READ ONLY on report cursors. Distributed applications feel blocking the most (DDF). CURRENTDATA NO can allow more blocking with a trade-off on seeing the absolute latest row.

Rowset cursors and multi-row FETCH

WITH ROWSET POSITIONING lets one FETCH return many rows into host-variable arrays.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
       EXEC SQL
            DECLARE C1 CURSOR WITH ROWSET POSITIONING FOR
             SELECT EMPNO, LASTNAME, SALARY
               FROM DSN8C10.EMP
              FOR READ ONLY
       END-EXEC.
 
       EXEC SQL OPEN C1 END-EXEC.
 
       EXEC SQL
            FETCH NEXT ROWSET FROM C1
             FOR 20 ROWS
             INTO :HVA-EMPNO, :HVA-LASTNAME, :HVA-SALARY:IND-SALARY
       END-EXEC.

Array dimensions must be at least the FOR n ROWS count. On +100, SQLERRD(3) (in many languages the third SQLERRD element) holds how many rows were placed in the arrays—process them before treating the cursor as empty. FETCH FIRST ROWSET, NEXT ROWSET, and other rowset orientations exist for scrollable rowset cursors. FOR n ROWS on FETCH is independent of FETCH FIRST n ROWS ONLY on the SELECT (the latter limits the whole result).

Rule of thumb used in shops: if you expect five or more rows, rowset FETCH is worth it; diminishing returns often appear above about 100 rows per FETCH—measure.

Explain It Like I'm Five

A cursor is a bookmark in a stack of library cards. DECLARE writes the title of the stack on the bookmark. OPEN builds the stack (using today’s search words). FETCH turns to the next card and copies the name onto your paper. A scrollable bookmark can jump to the first or last card. An insensitive stack is a photocopy; new cards filed in the real catalog do not appear. A rowset FETCH grabs a handful of cards at once. CLOSE puts the stack away. Blocking is the librarian bringing you a small pile so you do not walk to the catalog for every card.

Exercises

1. Write DECLARE / OPEN / FETCH INTO three host variables / CLOSE for employees in :WS-DEPT ordered by EMPNO.
2. When are :WS-DEPT values read—DECLARE or OPEN?
3. List FETCH orientations that require a scrollable cursor.
4. Why add FOR READ ONLY on a report cursor?
5. After FETCH NEXT ROWSET FOR 20 ROWS returns SQLCODE +100, what should you inspect before assuming zero rows?