too many tables join
Moderators: Kalicharan, Moderator Group
too many tables join
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;
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;
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 . . .
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
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
talk to your dba's.
there is nothing we can do to help without
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.
there is nothing we can do to help without
- knowing your environment
- knowing your tables
- having an explain output
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.
JCL, SQL and code in programs have an irritating habit of doing what you say,
not what you meant.
Gee, thanks, but probably not. . .nothing we can do without a very long back-and-forth conversation which Dikdude may wish to do.
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
Have a good one
- dbzTHEdinosauer
- Moderator
- Posts: 981
- Joined: Mon Oct 02, 2006 8:31 pm
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