Fetch for Limited Rows

Limited-Row FETCH

During cursor processing, the ability to tell DB2 to fetch only the first, or top, n rows is a requirement in many applications. SQL and the application program can do this but not efficiently. 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, the result is terrible performance because DB2 will have to read rows proportional to the 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 a fixed number of rows, regardless of how many rows qualified for the result set.

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 can allow the entire answer set to be retrieved. In contrast, the FETCH FIRST n ROWS ONLY stops processing after the specified number of rows. Thus, the application programmer has direct control 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. 

The FETCH FIRST clause is valid for singleton SELECTs, whereas the OPTIMIZE clause is not. The FETCH FIRST clause is thus 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. The clause 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. The clause can also be coded within an application program or dynamically: Query Management Facility, DSNTEP2, and so on. 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. 

Multirow FETCH and Multirow INSERT

Multirow FETCH and INSERT take advantage of arrays in which each element in the array contains a value for the same column. These host-variable arrays are allowed in COBOL, C++, PL/1, and, in some cases, assembler.

The ability to perform a multirow INSERT or FETCH can greatly improve the performance of distributed applications. It allows for multiple rows to be inserted or fetched in a single API call. Multirow FETCH and INSERT are supported by static, dynamic, nonscrollable, and scrollable cursors.

The multirow FETCH capability is done by declaring a cursor to be able to retrieve a row set with a single FETCH statement. Then the FETCH will specify the group of rows to be returned by fetching the row set. The FETCH controls how many rows are returned. Up to 32,767 rows can make up a single row set.

Following is an example of multirow FETCH:

 DECLARE CUR1 CURSOR WITH ROWSET POSITIONING FOR SELECT COL1, COL2 FROM TABLE1; OPEN CUR1; FETCH NEXT ROWSET FOR 3 ROWS FROM CUR1 FOR :hv ROWS INTO :values1, :values2; 

The multirow FETCH is useful for queries that perform large table space scans.

There is also support for positioned UPDATEs and DELETEs for cursors using multirow FETCH. Following is an example of a positioned UPDATE with a cursor defined with a row set:

 UPDATE TABLE1     SET COL1 = :newvalue     FOR CURSOR CUR1     FOR ROW :rownum OF ROWSET 

With multirow inserts, multiple rows can be inserted into a table or view using values that were supplied in the host variable array. Each array represents multiple rows for a single column. Up to 32,767 values can be specified.

An example of a multirow INSERT follows:

 INSERT INTO TABLE1 VALUES (:values1, values2) FOR 20 ROWS ATOMIC