Cursors static vs Scrollable and popular uses in DB2

DB2 cursors
DB2 cursors

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 FETCH FIRST 1000 ROWS ONLY; 
OPEN csr1;
FETCH ABSOLUTE 200 csr1 INTO :FN, :LN;

If I want to fetch rows between 200 and 500

FETCH 200 ROW
FETCH NEXT 300 TIMES

Basically, scrollable cursors reduce the amount of time and effort required to move backward and forward through the results of SQL queries.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.