How do I page through DB2 records? I have DB2 7.0 on OS/390 mainframe. I actually need to show 10 records from thhe DB2 table per page. Thus depending on the page number called from the front end, I need to get that particular set of 10 records from the table. From some sites I found that there is a row_number() function that returns the returned row number.
I tried using the ROW_NUMBER() function as follows:
select ROW_NUMBER() over() as rn, emp_id from employeebut it gave me the following error:
SQL0104N An unexpected token "(" was found following "". Expected tokens may include: ", FROM INTO ". SQLSTATE=42601Can you please help me out?
The answer to your problem lies in the use of scrollable cursors. As of V7, DB2 supports scrollable cursors. So, you can define your SQL SELECT statement as a scrollable cursor. Then open the cursor and FETCH anywhere within the answer set. You would need to fetch in groups of ten. So, to start fetching rows 31 through 40, you would say FETCH ABSOLUTE 31... and that would return the 31st row in the result set. Then you would just fetch the next 9 rows.
To use scrollable cursors you must use declared temporary tables, another new feature of DB2 Version 7. DB2 uses a declared temporary table to hold and maintain the data returned by a scrollable cursor. Before you can use declared temporary tables you must create a temporary database and table spaces for them to use. This is accomplished by specifying the AS TEMP clause on a CREATE DATABASE statement. Then, you must create segmented table spaces in the temporary database. Only one temporary database for declared temporary tables is permitted per DB2 subsystem.
Be sure to read up on the functionality of scrollable cursors in the DB2 Application Programming and SQL manual(SC26-9933) and DB2 SQL Reference manual (SC26-9944).
For More Information
- Dozens more answers to tough DB2 questions from Craig Mullins are available.
- The Best IBM DB2 Web Links: tips, tutorials, scripts, and more.
- Have a DB2 tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical DB2 questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in December 2002