Fetch for Limited Rows

Team-Fly

---

 

	DB2 Universal Database for OS/390 v7.1 Application Certification Guide By Susan Lawson
	Table of Contents
	Chapter 7.  Application Program Features

---

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 specifies that it does not want to retrieve more than some fixed number of rows, regardless of how many rows qualified for the result set.

FETCH FIRST and OPTIMIZE FOR

The OPTIMIZE FOR n ROWS clause, which provides additional information to the optimizer about the intent, is different from the FETCH FIRST clause. The OPTIMIZE FOR clause could allow the entire answer set to be retrieved. In contrast, the FETCH FIRST n ROWS ONLY stops processing after the specified number of rows. This provides direct control to the application programmer over the size of the result table. For example, an implementation limit on the number of rows displayed for an online screen can now be enforced. The following example shows the use of the FETCH FIRST n ROWS ONLY clause with the OPTIMIZE FOR n ROWS.

 EXEC SQL  DECLARE FIRST_FIVE CURSOR FOR   SELECT EMPNO   FROM EMPLOYEE   ORDER BY EMPNO   FETCH FIRST 5 ROWS ONLY   OPTIMIZE FOR 5 ROWS END-EXEC. 

Using FETCH FIRST for Existence Checking

The FETCH FIRST clause is valid for singleton SELECTs, whereas the OPTIMIZE clause is not. This makes the FETCH FIRST clause a perfect solution for optimal existence checking. It will perform a maximum of only one fetch to determine existence, and there is no internal second fetch for the 811 return code required. It automatically implies OPTIMIZE FOR 1 ROW and therefore discourages sequential prefetch, multi-index access and list prefetch. This technique requires minimal coding to support singleton select, and no cursor logic is required. It can also be coded within an application program or dynamically (Query Management Facility, DSNTEP2, etc.). The following is an example of FETCH FIRST for existence checking:

 EXEC SQL    SELECT 1 INTO :hv-check   FROM TABLE   WHERE COL1 = :hv1   FETCH FIRST 1 ROW ONLY END-EXEC. 

---

Team-Fly

Top