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. To use scrollable cursors you must use declared temporary tables, another new feature of DB2 Version 7. (Unless you are using DB2 V8 where dynamic scrollable cursors are available.) 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, 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:
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;
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. 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. Good luck...
Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our [email protected]/search390>discussion forums.
Dig Deeper on IBM DB2 management
Related Q&A from Craig S. Mullins
To export data from a DB2 table to a flat file, you need to run an export specifying the proper file format. The export utility exports data from a ... Continue Reading
Craig Mullins recommends two specific resources for learning how to create and support Binary Large Objects (BLOB) in DB2. Continue Reading
Craig Mullins explains various definitions of a data mart and sheds some light on the complexity of its relation to a data warehouse. Continue Reading