Pagination in DB2

[kottav]

Hi ,

I need help.

1: Im executing a query which throws some 1000 rows. Out of which only 20 rows shuld be displayed per page on the Front end at a time.
So i need to apply the concept of pagination here. Which means these 1000 rows should be distributed into number of pages (each containing 20 rows). I should display only those rows in the page which the user has selected.
How do i do this. Please give me some example also please.

[docdiesel]

Hi,

if your SELECT is rather static, you could create a view first:

Create View
  blog.entries_last1000
as
  Select
    ROW_NUMBER() OVER(order by date,time desc) as ROWNO,
    date,time,title
  From
    blog.entries_all
  Fetch
    first 1000 rows only;

Then do a select based on multiples of 20, e.g for the 11th page.

Select
  rowno,date,time,title
from
  blog.entries_last1000
where
  rowno > 20 * (11 - 1)
fetch
  first 20 rows only ;
 

(Besides, if frequently read while data is changing infrequently, I'd rather set up a cronjob and store those 1000 rows in a separate table.)

Regards,

Bernd