Cursors Static Vs Scrollable – And its Top Uses in DB2

Scroll-able cursor is a new topic. From DB2 Version 7, the concept of Scrollable cursor available. Usually, after getting query result into Temporary table, developers apply this cursor to validate data.

Note-where as in static cursor you cannot search the resultant table back and forth. This is just sequential search.

DECLARE GLOBAL TEMPORARAY TABLE TEMP_PROJ
LIKE DSN8810.PROJ
INCLUDING IDENTITY
ON COMMIT PRESERVE ROWS;

So the mechanism of scrollable cursor works in temporary table. It will be stored in SYSIBM.SYSTABLES. These are not persistent , locking, logging and recovery do not apply.

Indexes can not created. Constraints can not created. Can not be referenced by Utilities. When delete we need to delete all the rows.

Some of the available options:

NEXT – will FETCH the next row, the same way that the pre-V7
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

Example:

DECLARE csr1 SENSITIVE STATIC 
SCROLL CURSOR FOR  
SELECT FIRSTNAME, LASTNME  
FROM DSN8710.EMP ORDER BY LASTNME F
ETCH FIRST 1000 ROWS ONLY;  OPEN csr1; 
FETCH ABSOLUTE 200 csr1 INTO :FN, :LN;

If I want to fetch rows between 200 and 500