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.
