DB2 multiple-row FETCH

You can enhance the performance of your application programs by using multiple-row FETCH statements to request that DB2 send multiple rows of data, at one time from the database. Using these multiple-row statements in local applications results in fewer accesses of the database.  Using these multiple-row statements in distributed applications results in fewer network operations and a significant improvement in performance. Before Version 8, you needed to use a series of single-row fetches to return many rows of data. Now, in Version 8, you can use multiple fetches within a single SQL statement to accomplish the same task more efficiently.

The guide lines for when to use the construct are as follows:

1.  If it is known that five or more rows will be fetched, use rowset.

2.  If it is known that less than five rows will be fetched, use singleton.

3.  If it is not known how many rows will be fetched, use rowset. 

When we use ROWSET POSITIONING in the cursors, we are only making one call to DB2 to retrieve up to 100 rows vs executing 100 calls to DB2 to retrieve the same results without ROWSET.  This is why we can see significant CPU savings on the cursor operations.   

The cursor declaration contains the ROWSET POSITIONING construct establishing the setting for that cursor.  The FETCH on this cursor then determines the size of the ROWSET returned in one call.  The maximum we want to use is 100 ROWS since any rowset greater than 100 provides diminishing returns.  The setting for the ROWSET size should be determined by how many rows you expect the query to return.  As an example, if the query typically returns 30, set the FETCH to 30 rows.  If the query will be returning several hundred or more rows, we want to set the FETCH to 100 rows.  Remember the guide lines set out above when coding the cursors.   

A little more info on checking SQL codes, namely the SQLCODE and SQLERRD(3).

When you get a +100 while processing single rows, you are done since you are only expecting one row and returning one row.  When you get a +100 while processing a ROWSET, you need to check the SQLERRD(3) field to see how many rows were retrieved.  This coding method will tell you when you have completed processing the full result set for that cursor.

For example if there are 460 rows being fetched and the rowset size is 100:

Fetch 1 will get a zero SQLCODE and the SQLERRD(3) will be 100

Fetch 2 will get a zero SQLCODE and the SQLERRD(3) will be 100

Fetch 3 will get a zero SQLCODE and the SQLERRD(3) will be 100

Fetch 4 will get a zero SQLCODE and the SQLERRD(3) will be 100

Fetch 5 will get a +100 SQLCODE and the SQLERRD(3) will be 60

The basic steps in using a rowset cursor are below

Step 1: Declare the rowset cursor

To enable a cursor to fetch rowsets, use the WITH ROWSET POSITIONING clause in the DECLARE CURSOR statement. The following example shows how to declare a rowset cursor:

EXEC SQL

   DECLARE C1 CURSOR WITH ROWSET POSITIONING FOR

      SELECT EMPNO, LASTNAME, SALARY

        FROM TESTDB.EMP

END-EXEC.

Step 2: Open the rowset cursor

EXEC SQL

  OPEN C1

END-EXEC.

Step 3: Use multiple-row FETCH statement with host variable arrays

The following example shows a FETCH statement that retrieves 20 rows into host variable arrays that are declared in your program:

EXEC SQL

     FETCH NEXT ROWSET FROM C1

       FOR 20 ROWS

      INTO :HVA-EMPNO, :HVA-LASTNAME, :HVA-SALARY :INDA-SALARY

END-EXEC.

When your program executes a FETCH statement with the ROWSET keyword, the cursor is positioned on a rowset in the result table. That rowset is called the current rowset. The dimension of each of the host variable arrays must be greater than or equal to the number of rows to be retrieved.

The host variables should be defined as follows.

01 EMP-REC.

   05 HVA-EMPNO      PIC X(05) OCCURS 20 TIMES.

   05 HVA-LASTNAME   PIC X(10) OCCURS 20 TIMES.

   05 HVA-SALARY     PIC S9(5)V99 COMP-3 OCCURS 20 TIMES.

01 INDA-SALARY       PIC S9(4) COMP OCCURS 20 TIMES.

Step 4: Specify what to do at end-of-data for a rowset cursor

If SQL RC = +100 then the End of result table has been found, but rows could possibly exists within the host variable array to process. SQLERRD(3) contains the actual number of rows

returned.

Step 5: Close the rowset cursor