Pagination is required when you develop a listing screen which has thousands of records. The best way to implement pagination is to leverage features available on the database. In an Oracle database you can use RANK() function. On a DB2 database you can use ROW_NUMBER() function. Following example shows how to implement pagination on a DB2 database,

SELECT * FROM
  (SELECT a.customer_name, ROW_NUMBER() OVER
    (ORDER BY a.customer_name) AS RN FROM customer a
    WHERE a.customer_status=’ACTIVE’
  )
X WHERE X.RN BETWEEN 0 AND 100

This method won’t work if you are using AS400. On AS400, the only way seems to be iterating through the entire result set and then picking up records needed for the screen. For example, if you want to show records 100 to 200, start from 0, ignore upto 100 and then populate records from 100 to 200 and then stop. Obviously this means that if the resultset is huge and if you navigate to the last page, it might take a lot of time.

This entry was posted on Tuesday, May 8th, 2007 at 7:19 am and is filed under Howtos, Programming. You can leave a response, or trackback from your own site.