DB2 pagination for large data volumes

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.

This entry was posted in Database and tagged , , , , , , by Hazem Saleh. Bookmark the permalink.

About Hazem Saleh

Hazem Saleh has more than eleven years of experience in Cloud, Mobile and Open Source technologies. He worked as a software engineer, technical leader, application architect, and technical consultant for many clients around the world. He is an Apache PMC (Project Management Committee) member and a person who spent many years of his life writing open source software. Beside being the author of the "JavaScript Unit Testing" book, "JavaScript Mobile Application Development" book, "Pro JSF and HTML5" book and the co-author of the "Definitive guide to Apache MyFaces" book, Hazem is also an author of many technical articles, a developerWorks contributing author and a technical speaker in both local and international conferences such as ApacheCon North America, Geecon, JavaLand, JSFDays, CON-FESS Vienna and JavaOne. Hazem is an XIBMer, he worked in IBM for ten years. Now, He is working for Nickelodeon New York as a Mobile Architect. He is also an OpenGroup Master Certified Specialist.

One thought on “DB2 pagination for large data volumes

Comments are closed.