DB2 PERFORMANCE TUNING TIPS

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
User avatar
Krishna
Site Admin
Posts: 1052
Joined: Fri Jan 27, 2006 7:50 am

DB2 PERFORMANCE TUNING TIPS

Post by Krishna » Mon Nov 20, 2006 3:11 pm

DB2 PERFORMANCE TUNING TIPS

This thread is dedicated to discussion on DB2 PERFORMANCE TUNNING TIPS.
I am requesting members of this board to post performane tuning tips here.

Regards,
Krishna
www.geocities.com/srcsinc
www.ibmmainframeguru.com
www.jacharya.com

kumar_log@hotmail.com
Member
Posts: 6
Joined: Wed Jan 28, 2009 7:08 pm
Location: Singapore

Post by kumar_log@hotmail.com » Wed Jan 28, 2009 7:49 pm

Hi,

Please help me in my below DB2 query to run faster. Currently it is taking around 1 hour to complete.

SELECT A.C_AC_ID_C
,A.C_N
,A.C_AC_PRNT_ID_C
,A.C_AC_EF_D
,E.OFCE_ID_C
,B.DOC_RQR_D
,B.DOC_TYP_C
,C.DOC_TYP_T
,D.INTL_ID_C
,E.DEPT_ID_C
,B.DOC_STAT_TYP_C

FROM CAC.TC_AC_COMBD A,
CAC.TDOC_STAT B,
CAC.TDOC_REF C,
CAC.TC_AC_INTL_ID_XREF D,
PRD.TSR_OLD_ID E
WHERE A.C_AC_T_ACTY_STAT_I = ' ' AND
A.C_AC_EF_D > 20011004 AND
A.C_AC_ID_C = B.C_AC_ID_C AND

((B.DOC_STAT_TYP_C = 'R' AND
B.DOC_TYP_C = C.DOC_TYP_C AND
B.DOC_TYP_C IN ('01', '02', 'PAR'))
OR
(B.DOC_STAT_TYP_C = 'R' AND
A.C_AC_ID_C BETWEEN '7 ' AND '799999' AND
B.DOC_TYP_C NOT IN ('ACAP', 'OBFN', 'PAR', 'QB',
'Q13', 'RA', 'TAOP', '03', '10',
'14', '38', '45', '77', '8BEN',
'94', '95') AND
B.DOC_TYP_C = C.DOC_TYP_C)
OR
(B.DOC_STAT_TYP_C = 'F' AND
B.DOC_TYP_C = C.DOC_TYP_C AND
B.DOC_TYP_C = 'PAC')) AND

(A.C_AC_ID_C = D.C_AC_ID_C AND
D.INTL_ID_TYP_C = 'L1') AND
SUBSTR(D.INTL_ID_C,1,6) = E.SR_OLD_ID_C AND
E.EM_GRP_C = 'E'

ORDER BY E.OFCE_ID_C, E.DEPT_ID_C, D.INTL_ID_C,
A.C_AC_ID_C, B.DOC_TYP_C




One more similar query is there

SELECT A.C_AC_ID_C
,A.C_N
,A.C_AC_PRNT_ID_C
,A.C_AC_EF_D
,E.OFCE_ID_C
,B.DOC_RQR_D
,B.DOC_TYP_C
,C.DOC_TYP_T
,A.SR_PRIM_ID_C
,E.DEPT_ID_C
,B.DOC_STAT_TYP_C

FROM CAC.TC_AC_COMBD A,
CAC.TDOC_STAT B,
CAC.TDOC_REF C,
PRD.TSR_OLD_ID E

WHERE A.C_AC_T_ACTY_STAT_I = ' '
AND A.C_AC_EF_D > 20020606
AND A.C_AC_ID_C = B.C_AC_ID_C

AND ((B.DOC_STAT_TYP_C = 'R'
AND B.DOC_TYP_C IN ('01', '02', 'PAR'))
OR
(B.DOC_STAT_TYP_C = 'R'
AND A.C_AC_ID_C BETWEEN '7 ' AND '799999'
AND B.DOC_TYP_C NOT IN ('ACAP', 'OBFN', 'PAR', 'QB',
'Q13', 'RA', 'TAOP', '03',
'10', '14', '38', '45', '77',
'8BEN', '94', '95'))
OR
(B.DOC_STAT_TYP_C = 'F'
AND B.DOC_TYP_C = 'PAC'))

AND B.DOC_TYP_C = C.DOC_TYP_C
AND E.SR_OLD_ID_C = A.SR_PRIM_ID_C
ORDER BY E.OFCE_ID_C,
E.DEPT_ID_C,
A.SR_PRIM_ID_C,
A.C_AC_ID_C,
B.DOC_TYP_C



Please help me out to run faster. What kind of performance tuning process can be done.
Thanks and Regards,
L.Kumar

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

db2 performance tuning tips

Post by Natarajan » Thu Jan 29, 2009 10:31 am

Hi,

welcome to mainframegurukul.com.

My suggestions to improve above queries are :

1. Define index on the fields used in Where clause.
2. some of the comparisons can be done after fetching the record.
i.e., instead having those many conditions in WHERE clause
code important conditions in query. rest of the conditions , you
can code in application program, after fetching the record.





Suggestion: Please dont reply to the existing thread for new questions.
Please start a new topic by clicking "POST" button.
Natarajan
Chennai

kumar_log@hotmail.com
Member
Posts: 6
Joined: Wed Jan 28, 2009 7:08 pm
Location: Singapore

Re: db2 performance tuning tips

Post by kumar_log@hotmail.com » Fri Feb 13, 2009 12:00 am

Thanks for the response. It works for me.
Thanks and Regards,
L.Kumar

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

Post by Natarajan » Thu Apr 09, 2009 2:43 pm

DB2 V8 feature multi-row FETCH reduce 50% CPU time. You can observe good CPU time reduction, if you are fetching more records.
Natarajan
Chennai

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

Post by Natarajan » Thu Apr 09, 2009 2:44 pm

Testing has shown up to a 40% CPU time reduction when using multi-row INSERT
Natarajan
Chennai

kumar_log@hotmail.com
Member
Posts: 6
Joined: Wed Jan 28, 2009 7:08 pm
Location: Singapore

Post by kumar_log@hotmail.com » Fri May 08, 2009 7:26 pm

Hi Natarajan,

When i ran the above query as in SPUFI, it ran in 40 sec, rows retrieved was 2.
But when i ran the same query in COBOL program, it took 2+ hours to complete.

I'm planning to reduce down the "where" condition.
Do you have any further idea.
Thanks and Regards,
L.Kumar

User avatar
Natarajan
Moderator
Posts: 537
Joined: Fri Oct 10, 2008 12:57 pm
Location: chennai
Contact:

Post by Natarajan » Sun May 10, 2009 8:08 am

That much time difference between SPUFI & COBOL wont be there.
Try using with UR at the end of the select in COBOL
Update your BIND card for proper locking mechanisms.

Yes. decreasing where clause will definitely will help in increasing performance.
Natarajan
Chennai

arunaa27
Member
Posts: 2
Joined: Wed Jul 08, 2009 8:18 pm
Location: Bloomington,IL,USA

Post by arunaa27 » Sat Aug 01, 2009 2:05 am

Hi,
You can consider the following for better performance in general aspects. I am posting this message even though it is not related to your question...

1) You can always use DISTINCT clause (unless your requeirement needs duplicate values).
2) Please avoid DB2 built-in functions like SUBSTR, RTRIM, etc in an non-indexed fields.

Thanks,
Arun
Arun

academyindia4

Topic deleted by Admin

Post by academyindia4 » Sat Jan 30, 2016 10:14 pm

<< Content deleted By Admin >>

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