Clever Geek Handbook

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.

+2


source to share


2 answers


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;
+5


source


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.

0


source






More articles:


All Articles