by I don't understand how DB2 rewrote my query when I looked at the optimized
sql statement in explain. It has added stuff to my SQL Statement that I
didn't even put in originally. Specifically it added
AND ((Q1.LOSS_TRANS IN ('90', '92', '98', '99') and (SELECT
Q2.ACCT_ENTERED_MO
FROM GRANGE.PROCESSING_DATE AS Q2))) OR NOT(Q1.LOSS_TRANS IN ('90', '92',
'98', '99')))
There are more values for LOSS_TRANS than what I referenced or what DB2
added so why did it pick those 4 and how did it know to pick those 4?
My SQL Statement that I explained:
SELECT DISTINCT A.BRANCH, C.BRANCH_DESC, A.ADJ,
B.ADJ_NAME, A.CLM_NO, A.MAJOR_PERIL,
E.MO_NAME, A.ACCT_ENTERED_YR
FROM GRANGE.CLMS_LOSSCRYR A, GRANGE.ADJUSTER B,
GRANGE.BRANCH C, GRANGE.PROCESSING_DATE D,
GRANGE.MONTH_VALUES E
WHERE A.MAJOR_PERIL IN ('114', '170') AND
A.AIA_CD1_2 = 'NF' AND A.LOSS_TRANS IN
('22', '41', '42') AND A.ACCT_ENTERED_YR
= D.CURR_CLOSE_OUT_YR AND
A.ACCT_ENTERED_MO = D.CURR_CLOSE_OUT_MO
AND A.ADJ = B.ADJ AND A.MASTER_CO =
C.MASTER_CO AND A.BRANCH = C.BRANCH AND
A.ACCT_ENTERED_MO = E.MO_NO
ORDER BY 1, 2, 6, 5
The optimized SQL Text:
SELECT DISTINCT Q1.BRANCH AS "BRANCH",
Q6.BRANCH_DESC AS "BRANCH_DESC", Q1.ADJ
AS "ADJ", Q7.ADJ_NAME AS "ADJ_NAME",
Q1.CLM_NO AS "CLM_NO", Q1.MAJOR_PERIL AS
"MAJOR_PERIL", Q4.MO_NAME AS "MO_NAME",
Q1.ACCT_ENTERED_YR AS "ACCT_ENTERED_YR"
FROM GRANGE.CLMS_TRANS2000 AS Q1,
GRANGE.MONTH_VALUES AS Q4,
GRANGE.PROCESSING_DATE AS Q5,
GRANGE.BRANCH AS Q6, GRANGE.ADJUSTER AS
Q7
WHERE (Q1.ACCT_ENTERED_MO = Q4.MO_NO) AND
(Q1.BRANCH = Q6.BRANCH) AND (Q1.MASTER_CO
= Q6.MASTER_CO) AND (Q1.ADJ = Q7.ADJ) AND
(Q4.MO_NO = Q5.CURR_CLOSE_OUT_MO) AND
(Q1.ACCT_ENTERED_YR =
Q5.CURR_CLOSE_OUT_YR) AND (Q1.AIA_CD1_2 =
'NF') AND Q1.LOSS_TRANS IN ('22', '41',
'42') AND Q1.MAJOR_PERIL IN ('114',
'170') AND ((Q1.LOSS_TRANS IN ('90', '92',
'98', '99') AND (Q1.ACCT_ENTERED_MO =
(SELECT Q2.ACCT_ENTERED_MO
FROM GRANGE.PROCESSING_DATE AS Q2))) OR
NOT(Q1.LOSS_TRANS IN ('90', '92', '98',
'99')))
ORDER BY Q1.BRANCH, Q6.BRANCH_DESC,
Q1.MAJOR_PERIL, Q1.CLM_NO