Multi-Row Fetch - Batch Performance Optimization

"Your overnight inventory update program processes 50 million product records:

Current Implementation:

DECLARE C1 CURSOR FOR
    SELECT PRODUCT_ID, QTY_ON_HAND, REORDER_POINT
    FROM INVENTORY
    WHERE STORE_ID = :WS-STORE

OPEN C1
PERFORM UNTIL SQLCODE = 100
    FETCH C1 INTO :PRODUCT-ID, :QTY, :REORDER-PT
    PERFORM UPDATE-LOGIC
END-PERFORM

Performance:

• Runtime: 4.5 hours (batch window = 5 hours)
• CPU: 85% DB2, 15% COBOL
• 50 million single-row fetches
• Network round-trips killing performance

Requirements:

• Reduce runtime to under 2 hours
• Maintain commit frequency (every 5,000 records)
• No loss of error handling granularity

Redesign using multi-row fetch. Show code and explain performance gains."

• Advanced DB2 programming
• Performance optimization skills
• Understanding of multi-row fetch mechanics
• Ability to balance performance vs. complexity

1. Redesigned Code:

WORKING-STORAGE SECTION.
01  FETCH-SIZE              PIC S9(4) COMP VALUE 1000.
01  ROWS-FETCHED            PIC S9(4) COMP.
01  PRODUCT-ARRAY.
    05  PRODUCT-ENTRY OCCURS 1000 TIMES.
        10  PRODUCT-ID      PIC X(10).
        10  QTY-ON-HAND     PIC S9(7) COMP-3.
        10  REORDER-POINT   PIC S9(5) COMP-3.

PROCEDURE DIVISION.
    DECLARE C1 CURSOR WITH ROWSET POSITIONING FOR
        SELECT PRODUCT_ID, QTY_ON_HAND, REORDER_POINT
        FROM INVENTORY
        WHERE STORE_ID = :WS-STORE
    
    OPEN C1
    PERFORM UNTIL SQLCODE = 100
        FETCH C1 FOR :FETCH-SIZE ROWS
            INTO :PRODUCT-ARRAY
        MOVE SQLERRD(3) TO ROWS-FETCHED
        
        PERFORM VARYING WS-IDX FROM 1 BY 1 
                UNTIL WS-IDX > ROWS-FETCHED
            PERFORM UPDATE-LOGIC
            ADD 1 TO WS-COMMIT-COUNTER
            IF WS-COMMIT-COUNTER = 5000
                COMMIT
                MOVE 0 TO WS-COMMIT-COUNTER
            END-IF
        END-PERFORM
    END-PERFORM.

2. Performance Analysis:

• Before: 50M fetches × 2ms/fetch = 100,000 seconds = 27.7 hours (network overhead)
• After: 50,000 fetches × 10ms/fetch = 500 seconds = 8.3 minutes (actual fetch time)
• Savings: ~96% reduction in network round-trips
• Expected runtime: 1.5 hours (including processing)

3. Key Considerations:

• Rowset size: 1000 is optimal (balance memory vs. network)
• SQLERRD(3): Contains actual rows fetched (may be < 1000 at end)
• Commit frequency: Maintained at 5,000 records
• Error handling: Each row in array processed individually
• Memory: 1000 rows × 25 bytes/row = ~25KB (acceptable)

4. Trade-offs Discussed:

• Pros: Massive performance gain, reduced DB2 workload
• Cons: Slightly more complex code, more memory usage
• Alternative: Use LOAD utility if full table processing

• ❌ Doesn't know about WITH ROWSET POSITIONING
• ❌ Fetches entire table into memory (50M rows)
• ❌ Can't calculate performance improvement
• ❌ Breaks commit frequency requirement
• ❌ Doesn't handle last fetch (partial rowset)

• "What if you have 1,000 rows but only 800 are returned? How do you know?"
• "What's the maximum rowset size allowed?"
• "How do you handle errors for individual rows in the array?"
• "Would you use this technique for online CICS transactions?"