Scrollable Cursors In DB2 Mainframes

Scrollable cursors are introduced to overcome the limitation of reading the result table in sequential order.In Normal cursors, the cursor places at the first row of the result set when OPEN cursor statement is executed. And for each Fetch, the cursor gets moved by 1 row forward in the result table and this is the one directional reading that is in sequential order starting from the first row.But it is good to have a facility to move forward, backward and even start the result table data from any specific point. Db2 introduced Scrollable cursors to provide this facility which is useful in many scenarios where the result table needs to be scroll thru.

Scrollable cursors are declared with the key word SCROLL in the cursor declaration. There are two types of Scrollable cursors depending upon their sensitivity with other application updates.

INSENSITIVE Scrollable Cursors

With this type of Cursor, Result table does not change after the cursor gets Open. This is a read Only cursor where the positioned updates/deletes are not allowed and even the updates by other applications to the table does not effect the result table. The rows in the result table remains same with same values. If any row is added which qualifies to be in the result table but is added after the cursor open, then it would not be added to result table.

Declaration:

EXEC SQL
    DECLARE EMP_CUR INSENSITIVE SCROLL CURSOR FOR
       SELECT EMPNO, NAME, ACCNT_ID, AMT
              FROM DBTST.EMP_ACNT
              ORDER BY EMPNO
END-EXEC.

SENSITIVE Scrollable Cursors 

Unlike insensitive cursors these cursors are sensitive to the changes made to the underlying table.
These cursors has 2 different types based on its sensitivity.

Sensitive Static Scrollable:

Updates/Deletes- Positioned updates/deletes 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.

Updates by other applications or cursors: These are visible if the fetch uses sensitive keyword.

Inserts: Inserts are not visible even that the inserted row is qualified. So the size of the result table does not grow.

Order of the rows: Does not change.

Declaration Example:

EXEC SQL
    DECLARE EMP_CUR SENSITIVE STATIC SCROLL CURSOR FOR
       SELECT EMPNO, NAME, ACCNT_ID, AMT
              FROM DBTST.EMP_ACNT
       ORDER BY EMPNO
END-EXEC.

Fetch can be done with FETCH INSENSITIVE or FETCH SENSITIVE statements.

Sensitive Dynamic Scroallble:

These are the most sensitive to all the changes made to the underlying table by its own cursor and other applications.
Inserts: Inserts are visible in the result table. So the size of the result table can grow.

Updates/Deletes: Changes made by other applications are visible.

Order of the rows: Can change after the cursor OPEN.

Declaration Example:

EXEC SQL
    DECLARE EMP_CUR SENSITIVE DYNAMIC SCROLL CURSOR FOR
      SELECT EMPNO, NAME, ACCNT_ID, AMT
             FROM DBTST.EMP_ACNT
      ORDER BY EMPNO
END-EXEC.

Fetching the Rows:

The advantage of scrolling a cursor comes with FETCH as we can move around the Result table and start the result table from a specific location. As we know, when the cursor is opened the cursor is placed at the first row of the result table and Fetch in normal cursors reads thru the Result table sequentially. But scrollable cursors provides a facility to move thru the RT by using the Fetch Orientation keyword in the FETCH.

Below are the different Fetch Orientation Keywords that can be used.

BEFORE          –  Positions the cursor before the first Row of the RT
AFTER             –  Positions the cursor after the last Row of the RT
FIST                 – Cursor on first row
LAST                – ON Last Row
NEXT               – Positions the cursor on Next row. This is the default one.
CURRENT      – Fetches the current row from the result table.
PRIOR             – Fetches the previous row.
ABSOLUTE n – n can be positive,negative value or a Zero.
Fetches the nth row in the result table. it calculates nth row from the starting of the result table.

Example

FETCH ABSOLUTE 100 EMP_CUR into: ww-host

RELATIVE n  – Fetches the nth row from the last fetched row.
For example if the current row being fetched is 50 and if I give ETCH RELATIVE 10 FROM EMP_CUR into: ww-host, THEN it fetches the 60th record from the result table.

*RT –> Result Table

Notes:

• Scrollable cursors need more DB2 processing than Normal Cursors. So use this if the requirement really demands it. If there is a requirement to Scroll thru the result table and this can not be achieved effectively by using Normal cursors then go for it.
• Usage of SENSITIVE & INSENSITIVE in scrollable cursors also affects the performance. Checking whether the Updates made by other applications really requires in the result Table? If required then only declare it with SENSITIVE.
• FETCH INSENSITIVE is not valid for Sensitive Dynamic Scrollable cursors.
• Scrollable cursors can also be used in RowSET fetching(Multi Row).

Common errors that needs to be focused in Scrollable Cursors:

• -222 An UPDATE/DELETE operation was attempted against a hole using the following cursor name
• -223 An UPDATE/DELETE operation was attempted against an update hole using the following cursor name
  Examples”