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.



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


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.

I would like to use the second approach, however the listing is alphabetical and I am worried that the id> check might cause a full scan...
If it's sorted alphabetically, *that's* your ID field. Although, since it's probably not unique, you'd have to use >= so as to not miss any. This will give you duplicates (last entries on prev page, first entries on curr page) unless you add some more intellinence to remove them in the JSP code.
+1  A: 

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

  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;
Do you know how well this performs? I tried it outside of my program and it took a while to give me the results...
It performs a tiny bit slower than inner query by itself; and it gets slower (again, a tiny bit) as the rownumber values get bigger. It most certainly is faster than using FETCH FIRST X rows. This approach is what Hibernate uses for DB2 pagination; I'm pretty sure that if there was something better (faster) they'd have found it :-)