Db2 Scrollable Cursor in COBOL: FETCH FIRST, PRIOR, ABSOLUTE



Db2 scrollable cursor movement in a COBOL + Db2 application.

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 BY when 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 ONLY where 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

• IBM Db2 for z/OS: DECLARE CURSOR statement
• IBM Db2 for z/OS: FETCH statement

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.