Multi row fetch is one of the most powerful features of DB2 V8, which can give significant performance improvements to of your DB2 application. So what is multi row fetch or MRF , as the name suggests it simply mean fetching multiple rows at a time using your cursor.

 

When you use a normal cursor in your application program fetches he rows from the resultant set of the select statement one row at and time.

A multi row fetch fetches multiple rows from a resultant set at a time as a row set. A row set is group of rows from the result set that’s returned in a single fetch. As multiple rows are moved in one fetch it reduces the number of I-O operations and reduced cost and CPU time.

The diagram above shows each fetch will returning 4 rows form the resultant set as compare to one row per fetch

 

When to use multi row fetch

• When 10 or more rows needs to be fetched
• When number of columns fetched is less
• When the underlying select statement is not a complex query
• Best suited when fetching 100 to 1000 rows

 

It should be noted that multi row fetch would require additional coding in your application program as we will see below .

To use multi row fetch you must declare your cursor using the “WITH ROWSET POSITIONING” Clause.

 

DECLARE CUR_MRF CURSOR

WITH ROWSET POSITIONING

FOR SELECT NAME, AGE

FROM TABLE1

 

You can control the no of rows returned in a row set in your program by using the “FOR n ROWS” clause in your FETCH statement. The maximum value of n is 32767 and your fetch statement omits the FOR n ROWS clause the value is defaulted to 1.

 

FETCH NEXT ROWSET FROM CUR_MRF

FOR 10 ROWS INTO

INTO :NAME, :AGE

 

As we are fetching 10 rows at a time the host variable must be declared as an array

01 MRF-HOST-ARRAY

05 NAME PIC X(30) OCCURS 10 TIMES

05 AGE  PIC 9(3)  OCCURS 10 TIMES

 

Since we are using an array as the host variable its important to note that the SQLCA filed SQLERRD(3) return the number of rows retrieved in each fetch of a row set.

If your resultant set contains 10 rows and you are using a multi row fetch to get 4 row on a single fetch , your 3^rd fetch will give you an SQL code 100 with SQLERRD(3) = 2 .

 

 

 

This is total number of rows returned on the last fetch is required if you want to move the data from the host variable array in your program for processing

 

EXEC SQL

FETCH NEXT ROWSET FROM CUR_MRF

FOR 10 ROWS INTO

INTO :NAME, :AGE

END-EXEC.

IF SQLCODE 100 OR 0

     PERFROM 100-MOVE-DATA

             THRU 100-EXIT

     VARYING I FROM 1 BY 1 UNTIL I = SQLERRD(3)

END-IF

100-MOVE-DATA

   DISPLAY ‘EMPLOYEE NAME : ‘ NAME(I)

   DISPLAY ‘EMPLOYEE AGE : ‘ AGE(I)

100-EXIT

   EXIT.

 

There are no change required to your OPEN and CLOSE cursor statements for multi row fetch processing. Please post your comments /suggestions/questions in the comments section below .