Select Page

How and When to Use DB2 Scrollable Cursors

Author: Craig Mullins | | May 19, 2015

Sometimes 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:


OPEN csr1;


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

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Data Types: The Importance of Choosing the Correct Data Type

Most DBAs have struggled with the pros and cons of choosing one data type over another. This blog post discusses different situations.

Craig Mullins | October 11, 2017

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.