mainframegurukul.com

Posted: **Thu Mar 13, 2014 11:39 pm**

Hi,

Is there anyway to fine tune the below SQL query. It is taking longer time to execute,

EXEC SQL                                                     
  UPDATE ENC_CMSTAT_SUM S                                    
     SET ENC_COUNT = &#40;SELECT COUNT&#40;*&#41;                        
                      FROM &#40;                                 
                        SELECT C.MF_ID, C.IN_INTRCHG_SNDR_ID,
                                C.WTX_CLAIM_REF_NBR,         
                                C.WTX_CLAIM_REV_NBR,         
                                C.PROCESS_STATUS             
                           FROM ENC_837I_CLM C               
                          WHERE C.MF_ID = S.MF_ID            
                            AND C.PROCESS_STATUS =           
                                S.PROCESS_STATUS             
                            AND C.OVERRIDE_IND = 'N'         
                           AND C.IN_INTRCHG_SNDR_ID =        
                               S.INTRCHG_SNDR_ID             
                          GROUP BY c.MF_ID,                  
                                   C.IN_INTRCHG_SNDR_ID,     
                                   C.WTX_CLAIM_REF_NBR,      
                                   C.WTX_CLAIM_REV_NBR,      
                                   C.PROCESS_STATUS                  
                             HAVING MAX&#40;LEFT&#40;C.TRANS_FILE_DT,6&#41;&#41; 
                                   = S.DATE_YRMO                 
                          &#41; TEMP                                 
                        &#41;                                        
    WHERE S.ENC_TYPE = 'I'                                       
       AND S.DATE_IND = '0'                                      
       AND S.MF_ID = &#58;WS-MF-ID

Posted: **Fri Mar 14, 2014 12:43 am**

How many rows are in the tables being used?

Posted: **Sat Mar 15, 2014 1:51 am**

There are one million records in those tables..

Posted: **Sun Mar 16, 2014 3:44 am**

What is shown by an EXPLAIN?

What is meant by "longer time"?

Has some change been made that has caused the performance issue?

Did it ever work properly with full volume?

Posted: **Wed Mar 19, 2014 12:32 am**

The query is taking longer time to execute. More than 2 hours it takes the job to complete which runs this query. I do not have access to PLAN_TABLE hence unable to get the explain details.

I have split the above query, one for selecting the count8 and placing it into a working storage variable and the second query will update the count directly. In the first query instead of subquery I am using JOIN now. I need to test and see whether there are any performance improvement with this approach.

Posted: **Wed Mar 19, 2014 1:03 am**

I do not have access to PLAN_TABLE hence unable to get the explain details.

You should be able to get this info with help from a DBA or other support person. . .

You might ask them what other monitoring tools are installed on the system.

mainframegurukul.com

Db2 Peformance tuning in mainframe

Db2 Peformance tuning in mainframe