DB2 PERFORMANCE TUNING TIPS
Moderators: Kalicharan, Moderator Group
DB2 PERFORMANCE TUNING TIPS
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
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
-
- Member
- Posts: 6
- Joined: Wed Jan 28, 2009 7:08 pm
- Location: Singapore
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.
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
L.Kumar
db2 performance tuning tips
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.
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
Chennai
-
- Member
- Posts: 6
- Joined: Wed Jan 28, 2009 7:08 pm
- Location: Singapore
Re: db2 performance tuning tips
Thanks for the response. It works for me.
Thanks and Regards,
L.Kumar
L.Kumar
-
- Member
- Posts: 6
- Joined: Wed Jan 28, 2009 7:08 pm
- Location: Singapore
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
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
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
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page