DB2 pagination for large data volumes

September 3, 2012 in Database

A very common requirement is to have pagination on the level of the database especially if your query result returns thousands of database records. This post shows you how to make database pagination in DB2.

In order to make a database pagination in DB2, you need to use the ROW_NUMBER() function as follows.

WITH CVIEW 
(SELECT column1, column2, column3, 
  ROW_NUMBER() OVER (ORDER BY column1) AS RN
  FROM TABLE(OR VIEW) 
  WHERE column1=xyz1 and column2=xyz2)
SELECT column1, column2, column3 FROM 
CVIEW WHERE RN BETWEEN X AND Y
ORDER BY RN

You can use this template query if you want to retrieve records from X to Y from the TABLE(OR VIEW). The template query shows you how to retrieve column1, column2, and column3 from the TABLE(OR VIEW) with the specified condition column1=xyz1 and column2=xyz2 ordered by column1.

This is all about.

One comment on “DB2 pagination for large data volumes

  1. excellent… was about time db2 came with the oracle rownum variant ;-)

Leave a reply

Your email address will not be published. Required fields are marked *

336,826 Spam Comments Blocked so far by Spam Free Wordpress

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Powered by sweet Captcha