Last updated: July 5, 2026
Db2 Scrollable Cursor in COBOL: FETCH FIRST, PRIOR, ABSOLUTE, and RELATIVE
A normal Db2 cursor moves forward through a result set. A scrollable cursor is different: after the cursor is opened, the program can move to the first row, last row, previous row, next row, an absolute row number, or a row relative to the current position.
That sounds small until you see a real batch support case. A production support program reads account exceptions from ACCOUNT_EXCP. The operator checks row 200, moves back to row 195, then jumps to the last row to confirm the final exception. A forward-only cursor forces extra host-language logic or a new query. A scrollable cursor lets Db2 manage the row movement.
What Is a Db2 Scrollable Cursor?
A Db2 scrollable cursor is a cursor declared with the SCROLL option. It lets an embedded SQL program fetch rows in more than one direction. In Db2 for z/OS, NO SCROLL is the default, so a cursor is not scrollable unless the program asks for it.
The main difference is cursor positioning. With a forward-only cursor, the common pattern is OPEN, repeated FETCH NEXT, then CLOSE. With a scrollable cursor, the FETCH statement can reposition the cursor before returning data.
When a Scrollable Cursor Helps
Use a scrollable cursor when the program genuinely needs to move around inside the same result set.
- A browse screen where PF7 and PF8 move backward and forward.
- A review program that jumps to the first, last, or nth row in a result set.
- A support utility that compares the current row with the previous row.
- A controlled reporting program where the same row might be inspected more than once.
Do not make every cursor scrollable. If the program only reads rows once from top to bottom, a normal cursor is usually simpler and cheaper.
Basic Syntax
The cursor declaration must include SCROLL. A simple COBOL + Db2 example looks like this:
EXEC SQL
DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
SELECT EMPNO,
LASTNAME,
WORKDEPT
FROM DSN8C10.EMP
WHERE WORKDEPT = :WS-DEPT
ORDER BY EMPNO
END-EXEC.INSENSITIVE means the result table does not reflect inserts, updates, or deletes made to the underlying rows after the cursor is opened. It is also read-only. That is often acceptable for browse and report programs.
Common FETCH Options
After opening the cursor, the program can use different fetch orientations.
| FETCH option | What it does | Typical use |
|---|---|---|
FETCH FIRST |
Moves to the first row | Start browse from the top |
FETCH LAST |
Moves to the last row | Show the most recent or final row after sorting |
FETCH NEXT |
Moves one row forward | PF8 or normal forward browsing |
FETCH PRIOR |
Moves one row backward | PF7 or previous-record logic |
FETCH ABSOLUTE n |
Moves to row number n |
Jump to row 50, 100, or another fixed position |
FETCH RELATIVE n |
Moves n rows from the current position |
Move forward or backward by a page size |
COBOL Example
This example opens a scrollable cursor and fetches the first, next, prior, and absolute rows. The host variables are simplified so the cursor movement is easy to see.
01 WS-DEPT PIC X(03).
01 HV-EMPNO PIC X(06).
01 HV-LASTNAME PIC X(15).
01 HV-WORKDEPT PIC X(03).
01 WS-ABS-ROW PIC S9(9) COMP VALUE 10.
MOVE 'A00' TO WS-DEPT.
EXEC SQL
OPEN C1
END-EXEC.
EXEC SQL
FETCH FIRST FROM C1
INTO :HV-EMPNO,
:HV-LASTNAME,
:HV-WORKDEPT
END-EXEC.
IF SQLCODE = 0
PERFORM DISPLAY-EMPLOYEE
END-IF.
EXEC SQL
FETCH NEXT FROM C1
INTO :HV-EMPNO,
:HV-LASTNAME,
:HV-WORKDEPT
END-EXEC.
EXEC SQL
FETCH PRIOR FROM C1
INTO :HV-EMPNO,
:HV-LASTNAME,
:HV-WORKDEPT
END-EXEC.
EXEC SQL
FETCH ABSOLUTE :WS-ABS-ROW FROM C1
INTO :HV-EMPNO,
:HV-LASTNAME,
:HV-WORKDEPT
END-EXEC.
EXEC SQL
CLOSE C1
END-EXEC.Always check SQLCODE or SQLSTATE after each FETCH. When the requested row is outside the result table, Db2 positions the cursor before the first row or after the last row and no host variables are assigned.
Scrollable Cursor Sensitivity
The sensitivity option controls whether changes to the underlying table can be visible through the cursor.
INSENSITIVE SCROLL
INSENSITIVE gives the program a stable result table after OPEN. Inserts, updates, and deletes made to the base rows are not reflected in the cursor result. The cursor is read-only, so it cannot be used for positioned update or delete.
SENSITIVE STATIC SCROLL
SENSITIVE STATIC keeps the size and order of the result table stable after open. It can see some changes through positioned operations or sensitive fetch behavior. This can introduce update holes or delete holes when the base row no longer matches the result table.
SENSITIVE DYNAMIC SCROLL
SENSITIVE DYNAMIC is useful when the application needs to see committed inserts, updates, or deletes while the cursor is open. It can be useful in interactive applications, but it needs careful isolation-level and concurrency testing.
Performance Rules
- Use a forward-only cursor when the program only reads the result set once.
- Use
ORDER BYwhen the screen or report needs predictable row movement. - Keep the result set small. A browse cursor over millions of rows is usually a design problem.
- For read-only browse logic, say so clearly with
FOR READ ONLYwhere appropriate. - Close the cursor when the program is done with it.
- Test behavior at boundaries: before first row, after last row, empty result set, and single-row result set.
Common Mistakes
Declaring a cursor without SCROLL
If the cursor is declared with the default NO SCROLL, FETCH PRIOR, FETCH FIRST, FETCH LAST, FETCH ABSOLUTE, and FETCH RELATIVE are not valid for that cursor.
Using a scrollable cursor instead of a better WHERE clause
If the program already knows the key value, fetch the target row directly with a predicate. Do not open a large scrollable cursor just to jump to a row that can be found by key.
Ignoring SQLCODE +100
+100 is not just an end-of-file signal. With scrollable cursors, it can also tell you that the requested movement went outside the result table.
Internal Links
- DB2 Cursor Types in COBOL + Db2 Applications
- DB2 Scrollable Cursor Guidelines
- SQLCA, SQLCODE, and SQLSTATE in Db2
External References
FAQ
What is the default cursor type in Db2?
For embedded SQL in Db2 for z/OS, a cursor is not scrollable by default. Use SCROLL when the program needs backward, absolute, or relative movement.
Can a scrollable cursor update rows?
It depends on the cursor declaration and the result table. An INSENSITIVE scrollable cursor is read-only. For positioned update or delete, the cursor and SELECT must be updatable.
Should every COBOL + Db2 cursor be scrollable?
No. Use scrollable cursors only when the program needs random or backward movement through the same result set. A forward-only cursor is better for normal sequential processing.
