Db2 Peformance tuning in mainframe

Ask question on - DataBase Concepts, IBM Mainframe DB2, DB2/UDB, DB2 Tools, SQL , DB2 programming with languages like COBOL, PL1.

Moderators: Kalicharan, Moderator Group

Post Reply
anoop.dev
Member
Posts: 3
Joined: Thu Mar 13, 2014 11:36 pm
Location: Woodland Hills, CA

Db2 Peformance tuning in mainframe

Post by anoop.dev » 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,

Code: Select all

EXEC SQL                                                     
  UPDATE ENC_CMSTAT_SUM S                                    
     SET ENC_COUNT = (SELECT COUNT(*)                        
                      FROM (                                 
                        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(LEFT(C.TRANS_FILE_DT,6)) 
                                   = S.DATE_YRMO                 
                          ) TEMP                                 
                        )                                        
    WHERE S.ENC_TYPE = 'I'                                       
       AND S.DATE_IND = '0'                                      
       AND S.MF_ID = :WS-MF-ID
Dev

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Fri Mar 14, 2014 12:43 am

How many rows are in the tables being used?
Have a good one

anoop.dev
Member
Posts: 3
Joined: Thu Mar 13, 2014 11:36 pm
Location: Woodland Hills, CA

Post by anoop.dev » Sat Mar 15, 2014 1:51 am

There are one million records in those tables..
Dev

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » 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?
Last edited by DikDude on Wed Mar 19, 2014 12:59 am, edited 1 time in total.
Have a good one

anoop.dev
Member
Posts: 3
Joined: Thu Mar 13, 2014 11:36 pm
Location: Woodland Hills, CA

Post by anoop.dev » 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.
Dev

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » 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.
Have a good one

Post Reply

FREE TUTORIALS

Tutorials
Free tutorials from mainframegurukul
  • JCL Tutorial
    Covers all important JCL concepts.
  • Cobol Tutorial
    This tutorials covers all Cobol Topics from STRING to COMP-3.
  • DB2 Tutorial
    DB2 Tutorial focuses on DB2 COBOL Programming.
  • SORT Tutorial
    This Tutorial covers all important aspects of DFSORT with examples
  • CICS Tutorial
    This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions



Other References
Mainframe Tools and others