DB2 query with performance improvements

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
prabhu_mf
Member
Posts: 6
Joined: Tue Oct 28, 2014 2:28 pm

DB2 query with performance improvements

Post by prabhu_mf » Mon Dec 08, 2014 10:18 pm

Hi All

I have a query which takes more time for execution and which leads to my program run for a longer time.

Code: Select all

SELECT DISTINCT T3.FILEDV1,T3.FILEDV2,T3.FIELDV3,T3.FIELDV4,
       T3.DATEID1,T3.INUMBER,T3.FIELDV5                      
   FROM OPR63_MTABLE1 T3,                           
        OPR02_MTABLE2 T2,                             
        OPR29_MTABLE3 T9                              
  WHERE T3.ICUSCNO=:CUSTOMER.CNUMBER                             
    AND T3.LEDREC = '42'                                      
    AND T3.DATEID1 IN &#40;T9.DATEID1, T2.DATEID1&#41;       <-- this line causing performance issue         
    AND T3.INUMBER=&#58;CUSTOMER.INUMBER;
in the query,

Code: Select all

AND T3.DATEID1 IN &#40;T9.DATEID1, T2.DATEID1&#41;  
this line causes the issue and take more time for processing. when code like

Code: Select all

AND T3.DATEID1 IN &#40;T2.DATEID1&#41; 
or

Code: Select all

AND T3.DATEID1 IN &#40;T2.DATEID1&#41;, 
processing time is less than a sec.

could you guide me how I can improve this query.

User avatar
dbzTHEdinosauer
Moderator
Posts: 981
Joined: Mon Oct 02, 2006 8:31 pm

Post by dbzTHEdinosauer » Mon Dec 15, 2014 8:43 pm

have you tried:

Code: Select all

and &#40;  &#40;t3.dateid1 in t9.dateid1&#41; or &#40;t3.dateid1 in t2.dateid1&#41; &#41;
or

Code: Select all

and &#40;  &#40;t3.dateid1 in t2.dateid1&#41; or &#40;t3.dateid1 in t9.dateid1&#41; &#41;
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

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