Scrollable Cursors

Scrollable Cursors

1.       What is scrollable cursors?

When a cursor is declared as scrollable, you can use FETCH statements to:

l         Jump around and directly retrieve a row which is located at any position within the cursor result table

l         Scroll backwards as well as forwards

2.       What happens when a scrollable cursor is opened?

The qualifying rows are copied to a declared temporary table which is automatically created by DB2. Scrolling is performed on the result table.

DB2 deletes the result table when the cursor is closed.

3.       How to scroll within the result table?

l         FETCH BEFORE or FETCH ABSOLUTE 0

l         FETCH FIRST

l         FETCH LAST or FETCH ABSOLUTE -1

l         FETCH PRIOR

l         FETCH CURRENT

l         FETCH or FETCH NEXT

l         FETCH ABSOLUTE 5 or FETCH ABSOLUTE -5

l         FETCH RELATIVE 3 or FETCH RELATIVE -3

4.       Four types of scrollable cursors

4.1 Insensitive Scrollable Cursor

l         The cursor cannot be used to issue positioned updates and deletes

l         FETCH processing on the result table is insensitive to the changes made to the base table after the result table has been built

l         The number and content of the rows stored in the result table is fixed at OPEN CURSOR time and does not change

4.2 Sensitive Static Scrollable Cursor

l         The cursor can be used to issue positioned updates and deletes

l         FETCH processing on the result table is sensitive to the changes made to the base table after the result table has been built

l         The number of rows in the result table does not change but the row content can change

It can cause an Update Hole or Delete Hole in the result table, when another application updates or deletes the row in the base table which is also in the result table.

4.3 Sensitive Dynamic Scrollable Cursor

l         Specifies that size of result table is not fixed at OPEN cursor time

l         Cursor has complete visibility to changes

l         FETCH executed against base table since no temporary result table created

A dynamic scrollable cursor does not materialize the result table at any time. Instead, it scrolls directly on the base table and is therefore sensitive to all committed inserts, updates, and deletes.

4.4 ASENSITIVE

l         DB2 determines sensitivity of cursor

l         If Read-only, it behaves as an insensitive cursor

l         If not ready-only, SENSITVE DYNAMIC is used for maximum sensitivity

l         Mainly for Client applications that don’t care whether or not server supports the sensitivity or scrollability

5.       Cursor Type Comparison

Cursor Type	Result Table	Visibility of Own Changes	Visibility of Others’ Changes	Updatability (*)
Non-Scrollalbe (SQL contains a JOIN or sort, etc)	Fixed,  workfile	No	No	No
Non-Scrollalbe	No workfile, base table access	Yes	Yes	Yes
INSENSITIVE SCROLL	Fixed, declared temp table	No	No	No
SENSITIVE STATIC SCROLL	Fixed, declared temp table	Yes(INSERT not allowed)	Yes(Not INSERT)	Yes
SENSITIVE DYNAMIC SCROLL	No declared temp table, base table access	Yes	Yes	Yes