Problem solve Get help with specific problems with your technologies, process and projects.

Paging through DB2 records

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?

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

Dig Deeper on IBM DB2 management