DB2® Universal Database™ for OS/390™ v7.1 Application Certification Guide

Fetch for Limited Rows

During cursor processing, the ability to tell DB2 to only fetch the first or top n rows is a requirement in many applications. There are ways in SQL and the application program to do this; however, they are not the most efficient. For example, a query could be written as follows:

EXEC SQL 
SELECT *
  FROM EMPLOYEE A
  WHERE 5 >= (SELECT COUNT(*)
     FROM EMPLOYEE B
     WHERE B.EMPNO <= A.EMPNO)
END-EXEC.

However, this results in terrible performance, because DB2 will have to read rows proportional to square of the numbers of table rows.

With the FETCH FIRST clause of a fullselect, DB2 has this functionality with improved performance, as the clause sets a maximum number of rows that can be retrieved through a cursor. The application ...