SCROLL vs NO SCROLL Cursors in DB2

By default, cursors are moved by 1 row at a time in the result table in sequential order starting from the first row. To remove this limitation SCROLL clause is used to indicate whether the cursor moves sequentially through the result table or can move randomly through the result table. You can declare row-positioned or rowset-positioned cursors in a number of ways. These cursors can be scrollable or not scrollable, held or not held, or returnable or not returnable.

DECLARE SCROLL – NO SCROLL Cursor Syntax:

►►─ DECLARE cursor-name ─┬─ NO SCROLL ◄ ──────────────────────────┬─ CURSOR ──►
                          ├─ ASENSITIVE ◄ ──────────────┬─ SCROLL ─┘
                          ├─ INSENSITIVE ───────────────┤
                          └─ SENSITIVE ─┬─ DYNAMIC ◄ ─┬─┘
                                        └─ STATIC ────┘
 ►──┬─────────────┬─ FOR ─┬─ select-statement ─┬──────────────────────────►◄
    └─ WITH HOLD ─┘       └─ statement-name ───┘

Non-scrollable cursor

A non-scrollable cursor can be either row-positioned or rowset-positioned. 

• A row-positioned non-scrollable cursor moves forward through its result table one row at a time.
• A rowset-positioned non-scrollable cursor moves forward through its result table one rowset at a time.

Note: Rows are sensitive to changes if a temporary table is not used. If scrolling is not specified, the default is NO SCROLL.

Processing: A non-scrollable cursor always moves sequentially forward in the result table. 

• When the application opens the cursor, the cursor is positioned before the first row (or first rowset) in the result table.
• When the application executes the first FETCH, the cursor is positioned on the first row (or first rowset). When the application executes subsequent FETCH statements, the cursor moves one row ahead (or one rowset ahead) for each FETCH. After each FETCH statement, the cursor is positioned on the row (or rowset) that was fetched.
• After the application executes a positioned UPDATE or positioned DELETE statement, the cursor stays at the current row (or rowset) of the result table. You cannot retrieve rows (or rowsets) backward or move to a specific position in a result table with a non-scrollable cursor.

Scrollable cursor

A scrollable cursor can be either row-positioned or rowset-positioned. With scrollable cursors, you can move directly to the rows you want without having to FETCH every other row returned by the cursor. 

If you want to order the rows of the cursor’s result set, and you also want the cursor to be updatable, you need to declare the cursor as scrollable, even if you use it only to retrieve rows (or rowsets) sequentially.

Declaring a scrollable cursor

INSENSITIVE scroll cursor

EXEC SQL DECLARE CURSOR-01 INSENSITIVE SCROLL CURSOR FOR             
     SELECT EMPNO, DEPTNO        
     FROM PROD.EMPDEPT 
     ORDER BY EMPNO
END-EXEC.

The size, the order of the rows, and the values for each row of the result table do not change after the cursor opens. 

This is the read Only cursor where the positioned updates/deletes are not allowed and even the updates by other applications to the table do not affect the result table. Rows that are inserted into the underlying table are not added to the result table & rows in the result table remain the same with the same values. 

SENSITIVE static scroll cursor

EXEC SQL DECLARE CURSOR-02 SENSITIVE STATIC SCROLL CURSOR FOR
     SELECT EMPNO, DEPTNO
     FROM PROD.EMPDEPT
     ORDER BY EMPNO
END-EXEC.

The size of the result table, order of the rows remains the same after the cursor opens i.e. 

• Rows that are inserted after cursor is opened are not added to the result table.
• Rows that are positioned updated/deleted after cursor is opened are visible in the result table.So if any result table row gets updated and after update it got disqualified for the selection, then it gets removed from the result table. Also deleted rows gets are not visible.

SENSITIVE dynamic scroll cursor:

EXEC SQL DECLARE CURSOR-02 SENSITIVE DYNAMIC SCROLL CURSOR FOR
     SELECT EMPNO, DEPTNO 
     FROM PROD.EMPDEPT 
     ORDER BY EMPNO
END-EXEC.

The size and contents of the result table can change with every fetch e.g. another program changes & commits the data.

The order of the rows can change after the cursor is opened e.g. columns that are in the ORDER BY clause are updated after the cursor is opened, the order of the rows in the result table changes.

Positioned UPDATE and DELETE statement changes are visible. Also committed insert, update, or delete operations outside the cursor but impacting the result table are visible.

Note: As the FETCH statement executes against the base table, the cursor needs no temporary result table.

ASENSITIVE scroll cursor

EXEC SQL DECLARE CURSOR-02 ASENSITIVE SCROLL CURSOR FOR
     SELECT EMPNO, DEPTNO
     FROM PROD.EMPDEPT 
     ORDER BY EMPNO
END-EXEC.

Specifies that the cursor is SENSITIVE DYNAMIC if a temporary table is not used. Otherwise, it is INSENSITIVE. This is the same as NO SCROLL except that scroll options are available with the FETCH statement. It will never be a STATIC cursor

FETCH SCROLL – NO SCROLL Cursor Syntax:

►►─ FETCH ─┬───────────────┬──┤ fetch-orientation ├──┬──────────┬─ cursor-name ─►
            ├─ INSENSITIVE ─┤                         └─ FROM ◄ ─┘
            └─ SENSITIVE ───┘
 ►──┬──────────────────────┬─────┬───────────────────────────────────┬──────────►◄
    └─┤ single-row-fetch ├─┘     ├        ┌─ , ─────────────┐        ┤
                                 ├─ INTO ─▼─ host-variable ─┴────────┤
                                 └─ USING DESCRIPTORdescriptor-name ─┘
  
 Expansion of fetch-orientation
 ├──┬── BEFORE ──────────┬──────────────────────────────────────┤
    ├── AFTER ───────────┤
    └─┤ row-positioned ├─┘
  
 Expansion of row-positioned
  
 ├──┬── NEXT ────────────────────────────┬───────────────────────┤
    ├── PRIOR ───────────────────────────┤
    ├── FIRST ───────────────────────────┤
    ├── LAST ────────────────────────────┤
    ├── ABSOLUTE──┬─ host-variable ────┬─┤
    ├             └─ integer-constant ─┘ ┤
    └── RELATIVE ─┬─ host-variable ────┬─┘
                  └─ integer-constant ─┘
  
 Expansion of single-row-fetch
  
 ├──┬───────────────────────────────────┬─────────────────────────┤
    ├        ┌─ , ─────────────┐        ┤
    ├─ INTO ─▼─ host-variable ─┴────────┤
    └─ INTO DESCRIPTOR descriptor-name ─┘

• The default (INSENSITIVE or SENSITIVE) in the FETCH operation depends on the sensitivity of the cursor specified in the DECLARE CURSOR.
• If SENSITIVE or INSENSITIVE is specified in the FETCH statement, single-row-fetch must also be specified.
• If BEFORE or AFTER is specified, do not specify SENSITIVE, INSENSITIVE, or the accompanying single-row-fetch.
• For a scrollable cursor, all FETCH statements with the single-row-fetch must specify the same number of columns.
• INTO and USING are optional, as shown, if a SCROLL CURSOR is being used. For non-SCROLL CURSOR operations, however, specifying either INTO or USING is required.

The following keywords are supported when fetching data from a scrollable cursor:

• NEXT – will FETCH the next row
• FETCH – statement functioned
• PRIOR – will FETCH the previous row
• FIRST – will FETCH the first row in the results set
• LAST – will FETCH the last row in the results set
• CURRENT – will re-FETCH the current row from the result set
• BEFORE – positions the cursor before the first row of the results set
• AFTER – positions the cursor after the last row of the results set
• ABSOLUTE n– will FETCH the row that is n rows away from the first row in the results set
• RELATIVE n– will FETCH the row that is n rows away from the last row fetched

ABSOLUTE: The cursor is positioned to the row specified from the beginning, or end if negative, of the result set. If the position is zero, the cursor is positioned before the first row of the set. If the position value is greater than the number of rows in the result set, a warning is issued and the position is changed to after the last row.

Examples with a set of 100 rows:

• 0 – cursor is positioned before first row of set, no data returned.
• 10 – cursor is positioned on the tenth row of the set, data returned (unless a hole with the static sensitive cursor mode).
• -1 – cursor is positioned on the last row of the set.
• 101 – cursor is positioned after the last row of the set, no data returned, warning issued.
• -101 – cursor is positioned before the first row of the set, no data returned, warning issued.

FETCH ABSOLUTE 100 EMPNO, DEPTNO
INTO :EMPNO, :DEPTNO

RELATIVE: The cursor is positioned from the current position. Negative value positions backward, and a positive value forwards. Zero returns the current row; however, if the current row has been deleted or updated such that it no longer qualifies, then the next row of the set is returned for the dynamic sensitive cursor mode.

• 0 – the same row is returned. If it has been deleted or no longer qualifies for a sensitive dynamic cursor, then the next row is returned.
• +10 – cursor is moved forward 10 rows, and data returned (unless a hole with the static sensitive cursor mode). If position is after the last row of the set, a warning is returned and no data returned.
• -1 – cursor is positioned backwards one row.
• +101 – cursor is positioned after the last row of the set, no data returned, warning issued.
• -101 – cursor is positioned before the first row of the set, no data returned, warning issued.

FETCH RELATIVE 100 EMPNO, DEPTNO
INTO :EMPNO, :DEPTNO

Read DB2 blogs : Click Here IBM DB2 Manual :Click Here