MVSFORUMS.com :: View topic - DB2 Query Tuning

MVSFORUMS.com
A Community of and for MVS Professionals

FAQ

Quick Manuals

Profile

DB2 Query Tuning

MVSFORUMS.com Forum Index -> Database

View previous topic :: View next topic

Author

Message

OSCORP
Beginner

Joined: 15 Oct 2004
Posts: 29
Topics: 8

Posted: Sun Jun 24, 2007 2:01 pm Post subject: DB2 Query Tuning

Hi,

I need to tune the following two queries.
Currently queries take @ 9 hrs to execute I want to reduce the time to @ 1 hr.

Can anyone please assist?

SQL1

Code:

SELECT
PTA.PROV_ID
,PTA.ADR_ID
,PR.PROV_TYP_CD
,PTA.EPD_ADDR_SEQ
,PTA.TAX_ID_NBR
,PTA.ADR_TYP_CD
,PTA.TAX_ID_TYP_CD
,PR.FST_NM
,PR.LST_NM
,PR.MDL_NM
,PR.NM_SUFX_CD
,PR.PROV_GDR_CD
,PR.ORG_TYP_CD
,AD.ADR_LN_1_TXT
,AD.CTY_NM
,AD.CNTY_CD
,AD.ST_CD
,AD.ZIP_CD
,AD.ZIP_PLS_4_CD
,AD.HNCP_ACSBL_IND
,PDE.PDE_EFF_DATE
,PDE.PDE_DIV
,PDE.PDE_PROV_TYPE
,PDE.PDE_PROV_NUM
FROM (
SELECT PDE_PPI_NUM
,PDE_EFF_DATE
,PDE_DIV
,PDE_PROV_TYPE
,PDE_PROV_NUM
,PDE_BILL_SEQ
,CASE WHEN PDE_EXP_DATE = '12/31/9999'
THEN 'A' ELSE 'I' END
AS ACTIVE_IND
FROM PDE
WHERE PDE_PPI_NUM > 0
) PDE
JOIN PROV_TIN_ADR PTA
ON PDE.PDE_PPI_NUM = PTA.PROV_ID
AND PTA.EPD_ADDR_SEQ = PDE_BILL_SEQ
AND PTA.DIR_IND = 'Y'
AND PTA.ADR_TYP_CD = 'D'
AND PTA.CANC_DT = '12/31/9999'
AND PDE.ACTIVE_IND = 'A'

JOIN ADR AD
ON AD.ADR_ID = PTA.ADR_ID
JOIN PROV PR
ON PR.PROV_ID = PDE.PDE_PPI_NUM

SQL2

Code:

SELECT
PTA.PROV_ID
,PTA.ADR_ID
,PR.PROV_TYP_CD
,PTA.EPD_ADDR_SEQ
,PTA.TAX_ID_NBR
,PTA.ADR_TYP_CD
,PTA.TAX_ID_TYP_CD
,PR.FST_NM
,PR.LST_NM
,PR.MDL_NM
,PR.NM_SUFX_CD
,PR.PROV_GDR_CD
,PR.ORG_TYP_CD
,AD.ADR_LN_1_TXT
,AD.CTY_NM
,AD.CNTY_CD
,AD.ST_CD
,AD.ZIP_CD
,AD.ZIP_PLS_4_CD
,AD.HNCP_ACSBL_IND
,PDE.PDE_EFF_DATE
,PDE.PDE_DIV
,PDE.PDE_PROV_TYPE
,PDE.PDE_PROV_NUM
FROM
(
SELECT PDE_PPI_NUM
,PDE_EFF_DATE
,PDE_DIV
,PDE_PROV_TYPE
,PDE_PROV_NUM
,PDE_BILL_SEQ
,CASE WHEN PDE_EXP_DATE = '12/31/9999' THEN
'A' ELSE 'I' END AS ACTIVE_IND
FROM PDE
WHERE PDE_PPI_NUM > 0
) PDE

JOIN PROV_TIN_ADR PTA1
ON PTA1.EPD_ADDR_SEQ = PDE.PDE_BILL_SEQ
AND PTA1.PROV_ID = PDE.PDE_PPI_NUM

JOIN PROV_TIN_ADR PTA
ON PTA.PROV_ID = PTA1.PROV_ID
AND PTA.TAX_ID_NBR = PTA1.TAX_ID_NBR
AND PTA.TAX_ID_TYP_CD = PTA1.TAX_ID_TYP_CD
AND PTA.BIL_ADR_ID = PTA1.ADR_ID
AND PTA.BIL_ADR_TYP = PTA1.ADR_TYP_CD
AND PTA.CANC_DT = '12/31/9999'
AND PTA.DIR_IND = 'Y'
AND PTA.ADR_TYP_CD = 'L'
JOIN PROV PR
ON PR.PROV_ID = PTA.PROV_ID

JOIN ADR AD
ON PTA.ADR_ID = AD.ADR_ID

Regs
Oscorp

bauer
Intermediate

Joined: 10 Oct 2003
Posts: 317
Topics: 50
Location: Germany

Posted: Mon Jun 25, 2007 12:50 am Post subject:

OSCORP, use EXPLAIN in the first step ?

kolusu
Site Admin

Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose

Posted: Mon Jun 25, 2007 7:23 am Post subject:

Quote:

Currently queries take @ 9 hrs to execute I want to reduce the time to @ 1 hr.

OSCORP,

We have no clue as to what the indexes are and you want us to optimize the query. Sad

A brief look at the queries, I think a program with a cursor open on the PDE table and then fetch the data from the other tables will be much more efficient with checkpoint restart logic. I am pretty sure that it won't take 9 hours with this logic.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

Display posts from previous:

	MVSFORUMS.com Forum Index -> Database	All times are GMT - 5 Hours
Page 1 of 1

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum