Exploring the Multi-row FETCH Cursor

I have a program that has been in my performance tuning radar ever since we met. High Elapsed time and High wait I/O for 192K fetches. It had a very low DB2 CPU time of 97 seconds. I used to monitor this program and couldn't make out what is wrong. Where is this DB2 elapsed time is coming from ? Table sizes are not that huge and get pages are considerable for this application and buffer hits.



I felt, for those many fetches why do you need to OPEN and CLOSE cursor 79K times ?. Is there a way to reduce the repeated OPEN & CLOSE mechanism with cursors. I did some search of my own and came across the Multi-Row Fetch cursor. When I read about it - I wanted to try it out.



This is how the FETCH statement of normal cursor would look -

EXEC SQL
FETCH P16-CUR
INTO :HV-EMPNO
END-EXEC.

Designing a Multi-Row Fetch cursor for improving application performance -
First, Lets Design the working sotrage section handling for multi-row fetch cursors :

01 EMP-REC.
05 HV-EMPNO         PIC X(10) OCCURS 50 TIMES.
05 HV-EMPNAME   PIC X(20) OCCURS 50 TIMES.

We will have to change the declarations to accommodate the rowset size ( discussed below ). Usually the number in the OCCURS parameter should greater than or equal to the rowset size.

1) Declaring the Multi-row fetch cursor with ROWSET parameter :

EXEC SQL

DECLARE P16-CUR WITH ROWSET POSITIONING FOR

SELECT EMPNO, EMPNAME

FROM PRDNSCH.TABLE00

END-EXEC.

By using the ROWSET parameter the cursor is positioned on rowset of the result table.

2) Opening the row set cursor :

EXEC SQL

  OPEN P16-CUR

END-EXEC.

3) Fetching the data :

EXEC SQL

     FETCH NEXT ROWSET FROM P16-CUR

     FOR 50 ROWS

     INTO :HV-EMPNO, :HV-EMPNAME

END-EXEC.

4) Handling SQL +100 ( end of rowset ) :

If the P16-CUR is fetching 220 rows and you design the rowset size as 50 then

1st first will result in SQLCODE 0 and SQLERRD(3) will be 50

2nd fetch will result in SQLCODE 0 and SQLERRD(3) will be 50

3rd fetch will result in SQLCODE 0 and SQLERRD(3) will be 50

4th fetch will result in SQLCODE 0 and SQLERRD(3) will be 50

5th fetch will result in SQLCODE +100 and SQLERRD(3) will be 20

The 5th fetch has calculated that there are no more rows satisfying the rowset size designed and hence you need to ensure a mechanism to handle those remaining rows ( 20 ).

5) Closing the rowset cursor :

EXEC SQL

   CLOSE P16-CUR

END-EXEC.

I have recommended this option to my application developers who will be setting this up for the program. I will keep you posted on the savings if any with the help of this method.