Jsp paging with DB2 as backend
I am fetching thousands of rows from a database, but I only want to display about 200 per jsp. I am using pagination creating links to the following lines.
Currently using DB2 the only thing I can do is FETCH FIRST 200 rows. So by the time I get to the 5th page I am doing a FETCH FIRST of 1000 lines and then I am showing 800 to 1000.
Can anyone suggest a better solution, either from jsp but preferably from a DB perspective?
These are read-only entries, that is, I only show them. Also, these pages don't go into session or whatever, so I don't want to keep cursors around.
Thank.
You can use the "row_number () over ()" construct, but it's a little cumbersome:
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;
I think it would be better to use cursors yourself so that you only execute one request. You are probably not using a DB2 mainframe, but the LUW version (Linux / Unix / Windows) may also have functionality to timeout "poorly managed" sessions (those containing inactive resource locks), which will prevent the accumulation of orphan cursors.
In DB2 / z, you must set IDTHTOIN to the number of seconds you would like to use as a timeout, and COMTSTAT = INACTIVE to protect threads containing resources that have shown some activity.
But as I said, you are probably using the LUW version and I have no idea if this option has similar functionality (I would be surprised if not).
If you have a unique ID that you can use for each displayed entry, you can simply remember the ID of the last one in your current list, rather than:
select * from tbl fetch first 200 rows only
using:
select * from tbl where id > id_of_last fetch first 200 rows only
This will at least reduce the traffic sent over the wire.