Using an open cursor to scroll forward and backward
Given a table, my application specification requires me to position somewhere in the middle of the set of records and then fetch records either forward (increasing sort key order) or backward depending on user selection.
Is there a straightforward way of doing it in SQL a special way for table or query definition?
The best approach to do this appropriately is to use scrollable cursors. You will need to be running at least DB2 V7 in order to accomplish this. A scrollable cursor provides the ability to scroll forward and backward through the data once the cursor is open. This can be achieved using nothing but SQL -- no host language code (e.g., COBOL, C) is required to facilitate a scrollable cursor in DB2 V7. A scrollable cursor makes navigating through SQL result sets much easier. There are two types of DB2 scrollable cursors:
SENSITIVE -- updateable; can access data changed by the user or other users
INSENSITIVE -- not updateable; will not show changes made
To use scrollable cursors you must use declared temporary tables, another new feature of DB2 Version 7. Declared temporary tables are discussed later in the section on data management. DB2 uses a declared temporary table to hold and maintain the data returned by a scrollable cursor.
Scrollable cursors allow developers to move through the results of a query in multiple ways. The following key words are supported when fetching data from a scrollable cursor:
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
For both ABSOLUTE and RELATIVE, the number n must be an integer. It can be either a positive or a negative number and it can be represented as a numeric constant or as a host variable.
All of the FETCH options for scrollable cursors also reposition the cursor before fetching the data. For example, consider the following cursor logic:
DECLARE csr1 SENSITIVE STATIC SCROLL CURSOR
FOR SELECT FIRSTNAME, LASTNME
ORDER BY LASTNME;
FETCH LAST csr1 INTO :FN, :LN;
Issuing this SQL will declare a scrollable cursor named csr1. Open that cursor and then FETCH the last row from the cursor's results set. The FETCH LAST statement will reposition the cursor to the last row of the results set and then FETCH the results into the host variables as specified. Scrollable cursors reduce the amount of time and effort required to move backward and forward through the results of SQL queries.
But as helpful as scrollable cursors are, do not make every cursor a scrollable cursor. Scrollable cursors require substantially more overhead than a traditional, non-scrollable cursor. Analyze the requirements of your applications and deploy scrollable cursors only where it makes sense to do so.
Be sure to talk to your DBAs before attempting to use scrollable cursors.
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our
This was first published in September 2003