Multi-row Fetch Highlights

Allows multiple rows to be returned with a single fetch. Can improve performance when large amounts of data are being retrieved. Data is returned into individual arrays, not structured arrays. A ROWSET is the group rows returned with a single multi-row fetch. The ROWSET size is specified on the FETCH statement and can be a max size of 32,767. The cursor must be declared for multi-row fetching.

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.