Db2 Scrollable Cursor Guidelines for COBOL Programs

Db2 scrollable cursor movement in COBOL.

A COBOL + Db2 program should not declare SCROLL just because a user may press Page Up on a screen. Scrollable cursors can make a program easier to code, but they can also add work for Db2, make locking behavior harder to reason about, and return rows differently depending on cursor sensitivity.

This checklist is for batch and online developers who already understand the basic DECLARE CURSOR, OPEN, FETCH, and CLOSE flow. For syntax and fetch movement examples, read Db2 Scrollable Cursor in COBOL: FETCH FIRST, PRIOR, ABSOLUTE.

Use a Scrollable Cursor Only When the Program Needs Backward Movement

A normal cursor reads forward. That is enough for most report jobs, extract jobs, validation programs, and one-pass update routines. Use a scrollable cursor when the program has a real need for one of these movements:

• FETCH PRIOR to move back one row.
• FETCH FIRST or FETCH LAST to jump to the edge of the result set.
• FETCH ABSOLUTE n to position on a known row number.
• FETCH RELATIVE n to move forward or backward from the current row.
• Repeated reads of the same result set while keeping cursor position in the program.

If the program reads every qualifying row once and writes a report or output file, a forward-only cursor is usually the cleaner choice. The DB2 cursor life cycle is simpler and the program has fewer cursor states to test.

Pick the Cursor Sensitivity Deliberately

Db2 supports ASENSITIVE, INSENSITIVE, SENSITIVE STATIC, and SENSITIVE DYNAMIC cursor behavior. Do not leave this decision to habit. The right choice depends on whether the program must see changes after the cursor opens.

Need in the program	Cursor choice	Developer note
The result set should not change while the cursor is open.	INSENSITIVE SCROLL	Good for review screens, browse lists, and reports where repeatable movement matters more than seeing later changes.
The program may need to see committed updates or deletes after the cursor opens.	SENSITIVE STATIC SCROLL	Rows inserted after the cursor opens are not added to the result set. Updates and deletes can appear through sensitive fetch behavior.
The result table itself may need to reflect committed inserts, deletes, and order changes.	SENSITIVE DYNAMIC SCROLL	Use only when the application really needs this behavior. Db2 can reject the cursor when the query requires materialization or becomes read-only.
The program has no special sensitivity rule.	ASENSITIVE SCROLL	Db2 chooses whether the cursor is insensitive or sensitive dynamic. For production COBOL, an explicit choice is easier to review.

IBM's Db2 for z/OS documentation for the DECLARE CURSOR statement explains these options and the limits around read-only result tables. The FETCH statement documentation covers the scroll fetch forms.

A Safe Starting Pattern

For a browse-style COBOL program that shows customer rows and lets the operator move forward and backward, start with an insensitive scrollable cursor unless the business rule says changed rows must be visible immediately.

EXEC SQL
    DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
        SELECT CUST_NO,
               CUST_NAME,
               STATUS
          FROM CUSTOMER
         WHERE REGION = :WS-REGION
         ORDER BY CUST_NO
END-EXEC.

EXEC SQL
    OPEN C1
END-EXEC.

EXEC SQL
    FETCH FIRST FROM C1
      INTO :WS-CUST-NO,
           :WS-CUST-NAME,
           :WS-STATUS
END-EXEC.

EXEC SQL
    FETCH PRIOR FROM C1
      INTO :WS-CUST-NO,
           :WS-CUST-NAME,
           :WS-STATUS
END-EXEC.

This pattern keeps the result set stable for the screen. The operator can move through rows without seeing a row disappear because another task committed an update after the cursor opened.

Do Not Use Scrollable Cursors in Every CICS Screen

A pseudo-conversational CICS program ends the task between user interactions. A cursor is not a good place to keep conversational state across that boundary. Store the key values needed to restart the browse, then reopen the cursor on the next task and fetch from a known key.

For example, instead of keeping a cursor open while the user thinks, save LAST-CUST-NO in the commarea or channel container, then reopen with a predicate such as WHERE CUST_NO > :LAST-CUST-NO for the next page. This avoids holding Db2 resources while the terminal is idle.

Watch for Queries That Force Materialization

A scrollable cursor often needs Db2 to preserve cursor position and result table behavior. If a query contains joins, grouping, ordering, expressions, or other clauses that make the result table read-only or materialized, a sensitive dynamic cursor may not be valid.

When the program fails at OPEN, do not patch the COBOL loop first. Check the cursor declaration and the SELECT. A dynamic scrollable cursor with a query that Db2 cannot keep sensitive is a design issue, not a fetch-loop issue.

Test These SQLCODE Paths

Scrollable cursor code has more positioning cases than a forward-only cursor. Add test cases for these return codes and states:

• SQLCODE +100 on FETCH NEXT after the last row.
• SQLCODE +100 on FETCH PRIOR before the first row.
• FETCH ABSOLUTE 0 or invalid relative movement, if the program can build those values.
• Deleted or changed rows when using a sensitive static cursor.
• OPEN failure when the SELECT is not valid for the requested sensitivity.

Use the program's existing SQLCA handling and keep the diagnostic output specific. The related SQLCA and SQLCODE guide is a useful refresher when adding these checks.

Scrollable Cursor Review Checklist

• Can the program work with a forward-only cursor? If yes, use the simpler cursor.
• Does the user or batch logic need PRIOR, FIRST, LAST, ABSOLUTE, or RELATIVE movement?
• Has the developer selected INSENSITIVE, SENSITIVE STATIC, or SENSITIVE DYNAMIC on purpose?
• Does the SELECT make a sensitive dynamic cursor invalid?
• Does a CICS program close the cursor before returning control to the terminal?
• Are +100, open errors, update holes, and delete holes tested?
• Would multi-row fetch or rowset positioning solve the real performance problem better?

FAQ

Is a scrollable cursor faster than a normal Db2 cursor?

No. A scrollable cursor is for movement through a result set, not a speed feature. If the program only reads forward, a normal cursor is usually a better starting point.

Can a scrollable cursor see rows inserted by another program?

A sensitive dynamic cursor can reflect committed inserts when Db2 can support that cursor type. A sensitive static cursor does not add inserted rows to the result set after the cursor opens.

Should CICS programs keep scrollable cursors open between screens?

No. In pseudo-conversational CICS, close the cursor before returning control and save enough key data to restart the browse on the next task.

What is the safest scrollable cursor type for a browse screen?

INSENSITIVE SCROLL is often the safest first choice when the screen needs stable forward and backward movement through the same result set.

The best scrollable cursor is the one whose movement and sensitivity match a real program requirement. If the code only reads the next row until SQLCODE +100, keep the cursor forward-only.