too many tables join

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
cubabu
Member
Posts: 3
Joined: Mon Jul 30, 2012 9:31 pm

too many tables join

Post by cubabu » Mon Jul 30, 2012 9:40 pm

Hi, could someone please help me in tuning the below query, This query took approximately 15 hours Machine resource to complete.

SELECT
DISTINCT
A.BPP_POLICY_ID_NBR AS POLICY
, A.BPP_STATE_CD AS STATE
, A.BPP_COMPANY_CD AS COMPANY
, A.BPP_LINE_CD AS LINE
, P.PCY_POL_STATUS_CD AS STATUS
, P.PCY_EXPIRATION_DT AS EXPIRE_DATE
, A.BPP_EFFECTIVE_DT AS EFFECTIVE_DTE
, R.RPY_REL_POL_ID_NBR AS RELATED_PLCY
, R.RPY_REL_LINE_CD AS RELATED_LINE
, C.BPI_POL_BAL_AMT AS BAL_AMT
, B.POD_CMPRSK_VHL_CNT AS VHL_CNT

FROM POT_POLICY_CONTROL A
, POT_POLICY P
, NWT_REL_POLICY R
, AIT_POLICY_ACCTG C
, NWT_POL_DATA B
WHERE

P.PCY_POL_STATUS_CD = '2'
AND A.BPP_STATE_CD = 'IL'
AND A.BPP_COMPANY_CD = '010'
AND A.BPP_LINE_CD = '010'
AND P.PCY_MRP_IND = 'P'
AND A.BPP_EFFECTIVE_DT > '2012-06-30'
AND C.BPI_POL_BAL_AMT > 0
AND B.POD_CMPRSK_VHL_CNT = '4'

AND A.BPP_PART_RANGE_NBR BETWEEN '000' AND '999'
AND P.PCY_PART_RANGE_NBR BETWEEN '000' AND '999'
AND R.RPY_PART_RANGE_NBR BETWEEN '000' AND '999'
AND C.BPI_PART_RANGE_NBR BETWEEN '000' AND '999'
AND B.POD_PART_RANGE_NBR BETWEEN '000' AND '999'

AND A.BPP_PART_RANGE_NBR = P.PCY_PART_RANGE_NBR
AND A.BPP_POLICY_ID_NBR = P.PCY_POLICY_ID_NBR
AND A.BPP_PART_RANGE_NBR = R.RPY_PART_RANGE_NBR
AND A.BPP_POLICY_ID_NBR = R.RPY_POLICY_ID_NBR

AND C.BPI_PART_RANGE_NBR = P.PCY_PART_RANGE_NBR
AND C.BPI_POLICY_ID_NBR = P.PCY_POLICY_ID_NBR

AND C.BPI_PART_RANGE_NBR = B.POD_PART_RANGE_NBR
AND C.BPI_POLICY_ID_NBR = B.POD_POLICY_ID_NBR

AND EXISTS
(SELECT 1
FROM POT_POLICY X
, NWT_POL_DATA Y
, POT_POLICY_CONTROL Z
, AIT_POLICY_ACCTG W
, NWT_POL_DATA Q

WHERE X.PCY_POLICY_ID_NBR = R.RPY_REL_POL_ID_NBR
AND X.PCY_PART_RANGE_NBR = R.RPY_REL_RANGE_NBR
AND Y.POD_POLICY_ID_NBR = R.RPY_REL_POL_ID_NBR
AND Y.POD_PART_RANGE_NBR = R.RPY_REL_RANGE_NBR
AND Z.BPP_POLICY_ID_NBR = R.RPY_REL_POL_ID_NBR
AND Z.BPP_PART_RANGE_NBR = R.RPY_REL_RANGE_NBR
AND W.BPI_POLICY_ID_NBR = R.RPY_REL_POL_ID_NBR
AND W.BPI_PART_RANGE_NBR = R.RPY_REL_RANGE_NBR
AND Q.POD_POLICY_ID_NBR = R.RPY_REL_POL_ID_NBR
AND Q.POD_PART_RANGE_NBR = R.RPY_REL_RANGE_NBR
AND Z.BPP_STATE_CD = 'IL'
AND Z.BPP_COMPANY_CD = '010'
AND Z.BPP_LINE_CD = '010'
AND Z.BPP_EFFECTIVE_DT > '2012-06-30'
AND W.BPI_POL_BAL_AMT > 0
AND B.POD_CMPRSK_VHL_CNT = '4'
AND X.PCY_POL_STATUS_CD = '2'
AND X.PCY_MRP_IND = 'S')

AND P.PCY_MODIFIED_TS =
(SELECT MAX(G.PCY_MODIFIED_TS)
FROM POT_POLICY G
WHERE G.PCY_PART_RANGE_NBR = P.PCY_PART_RANGE_NBR
AND G.PCY_POLICY_ID_NBR = P.PCY_POLICY_ID_NBR)

AND C.BPI_MODIFIED_TS =
(SELECT MAX(H.BPI_MODIFIED_TS)
FROM AIT_POLICY_ACCTG H
WHERE H.BPI_PART_RANGE_NBR = P.PCY_PART_RANGE_NBR
AND H.BPI_POLICY_ID_NBR = P.PCY_POLICY_ID_NBR)

ORDER BY POLICY

WITH UR
FOR FETCH ONLY;

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Mon Jul 30, 2012 11:24 pm

How many rows need to be accessed to run this query? How are the indexes defined for these tables? What does an EXPLAIN show for this query?

Sometimes, a bit of code is a much better alternative to issuing such a query . . .
Last edited by DikDude on Tue Jul 31, 2012 9:03 pm, edited 1 time in total.
Have a good one

cubabu
Member
Posts: 3
Joined: Mon Jul 30, 2012 9:31 pm

Post by cubabu » Tue Jul 31, 2012 10:23 am

Thanks for the response ...
This query is actually trying to fetch data from PRODUCTION, there are millions of rows!!! POLICY NUMBER is defined as unique index in all the above tables.

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Tue Jul 31, 2012 9:05 pm

There might be millions of rows, but how many need to be accessed by one execution of the query?

Also,
What does an EXPLAIN show for this query?
Have a good one

cubabu
Member
Posts: 3
Joined: Mon Jul 30, 2012 9:31 pm

Post by cubabu » Wed Aug 01, 2012 10:37 am

We need 50 records, I am not sure about EXPLAIN usage in SPUFI.

Thanks,

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

Post by dbzTHEdinosauer » Wed Aug 01, 2012 2:30 pm

talk to your dba's.

there is nothing we can do to help without
  • knowing your environment
  • knowing your tables
  • having an explain output
any suggestions would be simply guesses and a waste of time.

since this is production, you need access to production db2
and the ability to create your own plan table or use production region plan table.

either way, your dba's are the route.

if you don't have access to a dba, and you have no co-workers,
you can not finish the assignment.

nothing we can do without a very long back-and-forth conversation
which Dikdude may wish to do.
Dick Brenholtz
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.

DikDude
Moderator
Posts: 1001
Joined: Fri Jul 22, 2011 8:39 am
Location: usa

Post by DikDude » Wed Aug 01, 2012 9:09 pm

nothing we can do without a very long back-and-forth conversation which Dikdude may wish to do.
Gee, thanks, but probably not. . .

I'll keep an eye out and if somethng is posted where i believe i might be able to contribute, i will. Probably would not try to set a new record for "topic with most replies". If we aren't provided with "stuff" we can use, no reason to labor on :wink:
Have a good one

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

Post by dbzTHEdinosauer » Wed Aug 01, 2012 10:17 pm

:twisted: Sorry DikDude :P
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