Query on Multi row fetch CPU time performance improvements

[MFdevlpr]

Hi,

I had done a bit of research on Multi-row fetching in COBOL-DB2 application programs for CPU time utilization/performance improvement and tried to implement the same in my system.

There is a COBOL-DB2 batch program which reads from DB2 tables and writes into a flat file as an extract of data.
The driving cursor has UNION ALL and its a join of 3 tables. I have verified the joins and the indexes defined, all look good.

In the peak time (something like during Christmas runs) the expectation is load would be more and the job might take too long to execute.

So, I tried Multi-row fetching (with row-set 100 once, with row-set 1000 once) to reduce the CPU time.

Below is the SQL query in cursor definition:

Code: Select all

      SELECT A.Col1                     
            ,A.Col2                          
            ,A.Col3                          
            ,A.Col4                        
            ,B.Col5                     
            ,B.Col6                 
      FROM                                    
      (                                       
       SELECT C.Col1                    
             ,C.Col2                         
             ,C.Col3                         
             ,C.Col4                       
             ,MAX(C.SEQ_NO)   MAXSEQ          
       FROM                                   
      (                                       
       SELECT A.Col1                  
             ,A.Col2                       
             ,A.Col3                       
             ,A.Col4                     
             ,B.SEQ_NO                                  
        FROM  TableA A                                
             ,TableB B                                
             ,TableC C                                
        WHERE C.Col1 BETWEEN :WH-CR-PART-NO-FROM  
                         AND :WH-CR-PART-NO-TO    
          AND  C.Col1      =  A.Col1      
          AND  C.Col2      =  A.Col2           
          AND  A.Col3     >=  C.Col3           
  AND A.Col3 <= SUBSTR&#40;STRIP&#40;C.Col3&#41; || "9999",1,5&#41; 
          AND  A.Col7 <= &#58;WW-PROCESS-DATE     
          AND &#40;A.Col8 >= &#58;WW-PROCESS-DATE     
           OR  A.Col8 IS NULL&#41;                
          AND  C.Col4         =  0                   
          AND  C.SEQ_NO          =  B.SEQ_NO          
          AND  B.Col9 <= &#58;WW-WF-FORMAT-DATE   
          AND  B.ColA >= &#58;WW-WF-FORMAT-DATE   
      UNION ALL                                           
       SELECT A.Col1                             
             ,A.Col2                                  
             ,A.Col3                                  
             ,A.Col4                                
             ,B.SEQ_NO                                 
        FROM  TableA A                               
             ,TableB B                               
             ,TableC C                               
       WHERE  C.Col1 BETWEEN &#58;WH-CR-PART-NO-FROM 
                         AND &#58;WH-CR-PART-NO-TO   
         AND  C.Col1      =   A.Col1     
         AND  C.Col2      =   A.Col2          
         AND  A.Col7 <=  &#58;WW-PROCESS-DATE    
         AND &#40;A.Col8 >=  &#58;WW-PROCESS-DATE    
              OR  A.Col8 IS NULL&#41;            
         AND  C.Col4         =   A.Col4        
         AND  C.SEQ_NO       =   B.SEQ_NO         
         AND  B.Col9 <=  &#58;WW-WF-FORMAT-DATE  
         AND  B.ColA >=  &#58;WW-WF-FORMAT-DATE 
        &#41; C                                              
              GROUP BY C.Col1                      
                      ,C.Col2                           
                      ,C.Col3                           
                      ,C.Col4 &#41; A                     
           ,TableB B                                   
            WHERE B.SEQ_NO = A.MAXSEQ                    
            ORDER BY Col1                          
                    ,Col2                               
                    ,Col4                             
        FOR FETCH ONLY                                   
      WITH UR                                            

I don't see any optimization after the multi-row fetch technique.

Code CPU # of Records Processed
Old 6.61 12068688
New 6.07 12068688

Its a mere 8% improvement on CPU time. As per some books, I read that it would be at least 60% of CPU time improvement.

I am afraid if I have missed anything in the process.
Can someone help me understand what could be the reason for not having much optimization for multi-row fetch technique.

Any help would be much appreciated.

Thanks in advance.

[dbzTHEdinosauer]

maybe because the multirow fetch only has affect on the result table,
which (by looking at the sql) is the resouce user and not the fetch.

i think that you will also find that the documentation concerning multirow fetch does little discussion of complex queries (in the sense of JOIN, UNION, subselects and host varibale usage).

have you tried running this trough an unload utility instead of a COBOL DB2 program and then parse the flat file with the host variables?

My experience with unload utilities is that the dump is so quick, that I can use a second step (COBOL or SORT) to parse the flat file and generate the desired output.

[MFdevlpr]

Hi Dick,

Thanks a lot for your reply.

As this query was a bit complex, I was trying out to improve performance without going to Sort.

The JCL with unload was already built and it was giving a better CPU performance.

To retain the readability and in the perspective of future maintenance, I had attempted to improve performance with multi-row fetch.

Seems like it won't help as much as the Unload dump and sorting does.

Thanks again for your time.

With regards,
Vinay R