Fetching records using query in batches from db2

Question

Please let me know if there is any query which can fetch records in batches from db2... for example if there are 10,000 records in table, I need to fetch first 1000 records and then next 1000 records in each query until end of the table. I'm looking for a query not stored procedure with cursors...

Amaldev Amaldev · Accepted Answer · 2015-10-29T05:55:39

SELECT col1,col2 FROM 
   ( SELECT ROW_NUMBER() OVER (ORDER BY col2) AS rowNum, col2, col1 
     FROM  <schema>.table1) AS tab 
WHERE rowNum between 1 and 500

Here rowNum can be modified to fetch all records once we have total record count.

col2 can be identified preferably like creation/update timestamp which can be used to order records ascending/descending. It can be any other column as well for ordering records logically.

Fetching records using query in batches from db2

1 Answers