DB2 Data Pagination Query (Using FETCH FIRST and OFFSET)

What is Data Pagination Query?

Data pagination query is a technology used to retrieve a specific number of data records, implemented by using FETCH FIRST and OFFSET in SQL statements.

Fetch First

Fetch First is used to directly retrieve the first record. It determines the result set of the last executed SQL statement for the current session.

Advantages:

  • Improves execution speed
  • Reduces memory overhead

Disadvantages:

  • Does not support pagination
  • Can only retrieve one record

OFFSET

OFFSET is used to start retrieval from a specified position in the table. It can be used to implement pagination.

Advantages:

  • Supports pagination
  • Able to return multiple records

Disadvantages:

  • Requires more memory overhead
  • Increases execution time

Combining Fetch First and OFFSET

Combining Fetch First and OFFSET achieves data pagination query. By combining FETCH FIRST with OFFSET, you can retrieve one record from the table and then continue querying from the result set.

Example:

SELECT * 
FROM Customers 
WHERE Country='USA' 
ORDER BY CustomerName 
OFFSET 10 ROWS FETCH FIRST 5 ROWS ONLY;

This example will return the first five records of US citizens, starting from the tenth record.