How and When to Use DB2 Scrollable Cursors

By | In Database Administration, DB2 | May 19th, 2015

db2_cursorsSometimes I will use this blog to post answers to questions that have been sent to me. The question being answered today, is this: I have a select query that returns 1000 rows and I want to display the rows from 200 to 500. Could you please let me how to code after the where clause? I know how to get the first 100 rows using — FETCH FIRST 100 rows only clause. 

If you are using DB2 V7 or higher, consider using scrollable cursors. With scrollable cursors, you can move directly to the rows you want without having to FETCH every other row returned by the cursor.

In DB2 V7, scrollable cursors require you to use declared temporary tables, another new feature of DB2 Version 7. If you are using DB2 V8, dynamic scrollable cursors are available and thus temporary tables are not required.

In V7, 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 re-position the cursor before fetching the data. For example, let’s consider your problem of a cursor that returns 1000 rows, but you only want rows 200 through 500.

Consider the following cursor logic:

I used the FETCH FIRST 1000 ROWS ONLY clause to ensure that no more than 1,000 rows were returned. This clause is, of course, optional (and if not specified, DB2 will not limit the result set returned by the cursor). Then I open the cursor and FETCH row 200. This positions the cursor just after the 200 result row that was just fetched. After that, all you would need would be to create a loop that just issues FETCH NEXT 300 times and that would retrieve only rows 200 through 500.

Basically, 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. Also, be sure to discuss this with your DBAs before implementing as there will probably be some setup work required of the DBA group to facilitate this solution.

This post was originally published on Craig Mullins’ blog at http://db2portal.blogspot.com.

Contact Us
Craig Mullins
Consultant at Mullins Consulting, Inc
Craig S. Mullins is working with Datavail and its DB2 practice to expand offerings. He is president and principal consultant at Mullins Consulting, Inc. and the publisher of The Database Site. Mullins has 30 years of experience in all facets of database management and is the author of two books: “DB2 Developer’s Guide” currently in its 6th edition and “Database Administration: The Complete Guide to DBA Practices and Procedures,” the industry’s only guide to heterogeneous DBA.

Leave a Reply

Your email address will not be published.
Required fields are marked (*).