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 employee 
but it gave me the following error:
SQL0104N  An unexpected token "(" was found following "".  
Expected tokens may include:  ", FROM INTO ".  SQLSTATE=42601
Can you please help me out?

    Requires Free Membership to View

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

This was first published in December 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: