DB2 PERFORMANCE TUNING TIPS

Krishna · Site Admin Joined: 27 Jan 2006 Posts: 922

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 Joined: 28 Jan 2009 Posts: 6 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.
_________________
Thanks and Regards,
L.Kumar

Natarajan · Moderator Joined: 10 Oct 2008 Posts: 424 Location: chennai

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 Joined: 28 Jan 2009 Posts: 6 Location: Singapore

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

Natarajan · Moderator Joined: 10 Oct 2008 Posts: 424 Location: chennai

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

Natarajan · Moderator Joined: 10 Oct 2008 Posts: 424 Location: chennai

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

kumar_log@hotmail.com · Member Joined: 28 Jan 2009 Posts: 6 Location: Singapore

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

Natarajan · Moderator Joined: 10 Oct 2008 Posts: 424 Location: chennai

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 · Posted: Sat Aug 01, 2009 2:05 am Post subject:

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

Topics		Replies	Author	Views	Last Post
Related topics
	Announcement: MainframeTutorials.com	6	Krishna	2748	Mon Dec 21, 2009 4:02 pm Anuj Dhawan
	How we can access PDS and its Members thru COBOL ?	5	narendra_darwani	1011	Mon Aug 10, 2009 10:09 am narendra_darwani
	www.uscareerbank.com	0	prabakarank	1059	Mon Apr 27, 2009 2:59 pm prabakarank
	I need a JCL to delete the last 6 lines of all the members i	0	osurendhra	6402	Tue Feb 21, 2006 2:05 pm osurendhra
	Welcome to ibmmainframeguru.com / mainframegurukul.com	0	Krishna	4344	Mon Jan 30, 2006 8:59 am Krishna

	mainframegurukul.com Forum Index -> DB2 SQL - DB2 PROGRAMMING	All times are GMT + 5 Hours
Page 1 of 1