Multi-row Fetch Highlights
Performance Considerations
We saw approximately 50% CPU time reduction. Performance improvements are reduced the smaller the ROWSET and the more columns fetched. Performance gains start at a ROWSET of 10 and the best performance increase has been shown to be with a ROWSET of 100.
New SQL statement GET DIAGNOSTICS
Use the GET DIAGNOSTICS with Multi-row fetch to determine the acutal number of rows returned. GET DIAGNOSTICS provides diagnostic information for a entire SQL statement or for multiple conditions. With a Multi-row fetch there is the possibility of having an error for each row of a ROWSET. Multi-row Insert is a similar new feature that can also be used to improve CPU performance.
Additional information can be found in the DB2 UDB for z/OS V8 Application Programming and SQL Guide and the DB2 UDB for z/OS V8 SQL Reference.
Below is a sample of COBOL code using the Multi-row fetch and GET DIAGNOSTICS:
01 AORDERS.
10 AORDERS-ORDER-ID OCCURS 500 TIMES PIC S9(5)V
USAGE COMP-3.
10 AORDERS-CUST-ID OCCURS 500 TIMES PIC X(5).
10 AORDERS-ORDER-DATE OCCURS 500 TIMES PIC X(10).
10 AORDERS-ORDER-TIME OCCURS 500 TIMES PIC X(8).
10 AORDERS-FREIGHT-CHARGES OCCURS 500 TIMES PIC S9(2)V9(2)
USAGE COMP-3.
10 AORDERS-ORDER-SALESMAN OCCURS 500 TIMES PIC X(6).
10 AORDERS-ORDER-POSTED-DATE OCCURS 500 TIMES PIC X(26).
10 AORDERS-ORDER-SHIP-DATE OCCURS 500 TIMES PIC X(8).
******************************************************************
* INDICATOR VARIABLE STRUCTURE *
******************************************************************
01 IORDERS.
10 IORDERS-FREIGHT-CHARGES OCCURS 500 TIMES PIC S9(4)
USAGE COMP.
10 IORDERS-ORDER-SALESMAN OCCURS 500 TIMES PIC S9(4)
USAGE COMP.
.
.
.
EXEC SQL
DECLARE C1 CURSOR
WITH ROWSET POSITIONING FOR
SELECT ORDER_ID, CUST_ID, ORDER_DATE, ORDER_TIME,
FREIGHT_CHARGES, ORDER_SALESMAN,
ORDER_POSTED_DATE, ORDER_SHIP_DATE
FROM PSTDEMO.ORDERS
ORDER BY ORDER_ID
END-EXEC.
.
.
.
EXEC SQL
OPEN C1
END-EXEC
IF SQLCODE NOT EQUAL 0
MOVE 'O1 ERROR OPENING CURSOR C1' TO WS-MSG
GO TO 9998-SQL-ERROR
END-IF
PERFORM 1000-MULTI-ROW-FETCHING
UNTIL SQL-EOF = 'Y'
.
.
.
1000-MULTI-ROW-FETCHING.
EXEC SQL
FETCH NEXT ROWSET FROM C1
FOR :ROWSET-SIZE ROWS
INTO :AORDERS-ORDER-ID, :AORDERS-CUST-ID,
:AORDERS-ORDER-DATE, :AORDERS-ORDER-TIME,
:AORDERS-FREIGHT-CHARGES:IORDERS-FREIGHT-CHARGES,
:AORDERS-ORDER-SALESMAN:IORDERS-ORDER-SALESMAN,
: AORDERS-ORDER-POSTED-DATE, :AORDERS-ORDER-SHIP-DATE
END-EXEC
EVALUATE SQLCODE
WHEN 0
CONTINUE
WHEN 100
MOVE 'Y' TO SQL-EOF
WHEN OTHER
MOVE '02 ERROR FETCHING CURSOR C1' TO WS-MSG
GO TO 9999-GET-DIAGNOSTICS
END-EVALUATE
EXEC SQL
GET DIAGNOSTICS
:ROW-COUNT = ROW_COUNT
END-EXEC
PERFORM VARYING SS FROM 1 BY 1
UNTIL SS > ROW-COUNT
.
.
.
END-PERFORM.
.
.
.
9998-SQL-ERROR.
MOVE SQLCODE TO WS-SQLCODE
STRING 'SQLCODE = ' WS-SQLCODE ' ' WS-MSG
DELIMITED BY SIZE INTO WS-OUT-MSG
DISPLAY WS-OUT-MSG
STRING 'SQLERRMC = ' SQLERRMC
DELIMITED BY SIZE INTO WS-OUT-MSG
DISPLAY WS-OUT-MSG
STOP RUN.
9999-GET-DIAGNOSTICS.
DISPLAY WS-MSG
EXEC SQL
GET DIAGNOSTICS
:COND-COUNT = NUMBER
END-EXEC
PERFORM VARYING SS FROM 1 BY 1
UNTIL SS > COND-COUNT
EXEC SQL
GET DIAGNOSTICS CONDITION :SS
:SQL-CODE = DB2_RETURNED_SQLCODE,
:SQL-STATE = RETURNED_SQLSTATE,
:ROW-NUM = DB2_ROW_NUMBER,
:TOKEN-COUNT = DB2_TOKEN_COUNT,
:MSG-ID = DB2_MESSAGE_ID,
:MSG-TEXT = MESSAGE_TEXT
END-EXEC
MOVE SQL-CODE TO WS-SQLCODE
DISPLAY 'SQL-CODE = ' WS-SQLCODE
DISPLAY 'SQL-STATE = ' SQL-STATE
DISPLAY 'ROW-NUM = ' ROW-NUM
DISPLAY 'TOKEN-COUNT = ' TOKEN-COUNT
DISPLAY 'MSG-ID = ' MSG-ID
MOVE MSG-TEXT(1:60) TO WS-MSG
DISPLAY 'MSG-TEXT = ' WS-MSG
MOVE MSG-TEXT(61:60) TO WS-MSG
DISPLAY ' ' WS-MSG
MOVE MSG-TEXT(121:60) TO WS-MSG
DISPLAY ' ' WS-MSG
MOVE MSG-TEXT(181:60) TO WS-MSG
DISPLAY ' ' WS-MSG
MOVE MSG-TEXT(241:60) TO WS-MSG
DISPLAY ' ' WS-MSG
END-PERFORM
STOP RUN.