pagination on jsp with DB2 as backend

Question

I am retrieving thousands of rows from the database, but I want to display only about 200 on one jsp. I am using pagination by creating links to the next rows.

Currently using DB2, the only thing I can do is a FETCH FIRST 200 rows. So by the time I get to the 5th page, I am doing a FETCH FIRST 1000 rows and then I am displaying 800 to 1000.

Can anyone suggest a better solution, either from the jsp, but preferably from the DB perspective?

These are read-only records i.e I am only displaying them. Also, these pages are not within a session or anything, so I dont want to keep any cursors around.

Thanks.

Answer 1

I think it would be better to use cursors myself so that you're only actually executing one query. You're probably not using mainframe DB2 but the LUW (Linux/Unix/Windows) version may also have the functionality to time out "badly-behaved" sessions (those that hold resource locks without activity), which would prevent orphaned cursors from accumulating.

In DB2/z, you would set IDTHTOIN to the number of seconds you wanted to use as a timeout, and COMTSTAT=INACTIVE to protect resource-holding threads that were showing some activity.

But, as I said, you're probably using the LUW version and I have no idea if that variant has similar functionality (I'd be surprised if it didn't).

If you have a unique ID you can use for each record displayed, you can simply remember the ID for the last in your current list then, instead of:

select * from tbl fetch first 200 rows only

use:

select * from tbl where id > id_of_last fetch first 200 rows only

This would at least reduce the traffic being sent across the wire.

Answer 2

You can use the "row_number() over()" construct, but it's a bit unwieldy:

SELECT * FROM (
  SELECT your_column1, your_column2, ...,
         ROW_NUMBER() OVER (ORDER BY your_order_by ASC) AS rownumber
    FROM your_table
) AS row_
WHERE rownumber between 200 and 400;