Pagination with DB2

Modern web pages are widely using pagination, and the most common database in LAMP environment, MySQL, ist supporting this by the LIMIT offset, row_count parameter. With DB2 one has to use a little trickery to come to a similar result.

Imagine a list of blog entries, ordered by time, displaying the latest on top. This list is easily created by a VIEW:

Create View
  blog.vw_entries_500latest
as
  Select
    ROW_NUMBER() OVER(order by date,time desc) as ROWNO,
    id,date,time,title
  From
    blog.tb_entries_all
  order by
    date,time desc
  Fetch
    first 500 rows only;

Now lets load the data for the 11th page, where each page should list 25 blog entry titles:

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

This should do the trick.