Z/OS DB2 V10 table disappears from explain after a rewrite of SQL???

Dave Nance

Z/OS DB2 V10 table disappears from explain after a rewrite of SQL???
Hi all any help appreciated.
Have this query that someone wrote that was incorrectly written and performed very poorly. When I rewrote the query to correct the logic error, one of the tables no longer is in the access path. Actually, this is second query this week I have seen this on and do not really understand why.
SELECT DISTINCT A.TPJ_TITLE_NO                          , A.TPJ_TITLE_NAME                         , A.TPJ_SHARE                         , B.PRF_US_FEA_RADIO                          , D.DVL_NAME       FROM PARISP.TTTL_TPJ_JUNCTION A LEFT JOIN PARISP.TTTL_PRF_CUM_PERF B        ON A.TPJ_TITLE_NO = B.PRF_TITLE_NO LEFT JOIN PARISP.TTTL_AWD_AWARD C        ON A.TPJ_TITLE_NO = C.AWD_TITLE_NO INNER JOIN PARISP.TBMI_DVL_VALUE D        ON C.AWD_AWARD_TYPE = D.DVL_VALUE WHERE A.TPJ_PART_ACCT_NO = 923935    AND B.PRF_YYYYQ = '99999'      OR B.PRF_YYYYQ IS NULL     AND D.DVL_DCL_SQ = 72      OR D.DVL_DCL_SQ IS NULL    AND A.TPJ_SHARE = 100    AND A.TPJ_TITLE_NO NOT IN (SELECT DISTINCT E.TTL_TITLE_NO                                                     FROM PARISP.TTTL_TTL_DETAIL E                                                INNER JOIN PARISP.TTTL_TPJ_JUNCTION F                                                        ON E.TTL_TITLE_NO = F.TPJ_TITLE_NO                                               WHERE E.TTL_LEGAL_STATUS NOT IN ('A','P')                                                     AND F.TPJ_PART_ACCT_NO = 923935 ) FOR FETCH ONLY;
As you can see, there are some obvious missing parenthesis in this query. I rewrote the query and changed the NOT IN to a NOT EXISTS. Now the explain has no row for the PARISP.TTTL_TTL_DETAIL  table.
SELECT DISTINCT A.TPJ_TITLE_NO                         , A.TPJ_TITLE_NAME                         , A.TPJ_SHARE                         , B.PRF_US_FEA_RADIO                         , D.DVL_NAME    FROM PARISP.TTTL_TPJ_JUNCTION A LEFT JOIN PARISP.TTTL_PRF_CUM_PERF B      ON A.TPJ_TITLE_NO = B.PRF_TITLE_NO      AND B.PRF_YYYYQ = '99999'LEFT JOIN PARISP.TTTL_AWD_AWARD C      ON A.TPJ_TITLE_NO = C.AWD_TITLE_NO  INNER JOIN PARISP.TBMI_DVL_VALUE D      ON C.AWD_AWARD_TYPE = D.DVL_VALUE      AND D.DVL_DCL_SQ = 72WHERE A.TPJ_PART_ACCT_NO = 923935  AND A.TPJ_SHARE = 100  AND NOT EXISTS (SELECT 1                                   FROM PARISP.TTTL_TTL_DETAIL E                              INNER JOIN PARISP.TTTL_TPJ_JUNCTION F                                   ON E.TTL_TITLE_NO = F.TPJ_TITLE_NO                             WHERE E.TTL_LEGAL_STATUS NOT IN ('A','P')                                 AND F.TPJ_PART_ACCT_NO = 923935                                 AND A.TPJ_TITLE_NO = E.TTL_TITLE_NO )WITH UR FOR FETCH ONLY

;

 Dave Nance 

Chris Tee

Z/OS DB2 V10 table disappears from explain after a rewrite of SQL???
(in response to Dave Nance)
Dave


Is PARISP.TTTL_TTL_DETAIL a table, i.e not a view or alias? Perhaps you could post the explain output.


regards


Chris


________________________________
From: Dave Nance <[login to unmask email]>
Sent: 07 September 2017 21:03
To: Lizette Koehler
Subject: [DB2-L] - Z/OS DB2 V10 table disappears from explain after a rewrite of SQL???

Hi all any help appreciated.

Have this query that someone wrote that was incorrectly written and performed very poorly. When I rewrote the query to correct the logic error, one of the tables no longer is in the access path. Actually, this is second query this week I have seen this on and do not really understand why.

SELECT DISTINCT A.TPJ_TITLE_NO
, A.TPJ_TITLE_NAME
, A.TPJ_SHARE
, B.PRF_US_FEA_RADIO
, D.DVL_NAME
FROM PARISP.TTTL_TPJ_JUNCTION A
LEFT JOIN PARISP.TTTL_PRF_CUM_PERF B
ON A.TPJ_TITLE_NO = B.PRF_TITLE_NO
LEFT JOIN PARISP.TTTL_AWD_AWARD C
ON A.TPJ_TITLE_NO = C.AWD_TITLE_NO
INNER JOIN PARISP.TBMI_DVL_VALUE D
ON C.AWD_AWARD_TYPE = D.DVL_VALUE
WHERE A.TPJ_PART_ACCT_NO = 923935
AND B.PRF_YYYYQ = '99999'
OR B.PRF_YYYYQ IS NULL
AND D.DVL_DCL_SQ = 72
OR D.DVL_DCL_SQ IS NULL
AND A.TPJ_SHARE = 100
AND A.TPJ_TITLE_NO NOT IN (SELECT DISTINCT E.TTL_TITLE_NO
FROM PARISP.TTTL_TTL_DETAIL E
INNER JOIN PARISP.TTTL_TPJ_JUNCTION F
ON E.TTL_TITLE_NO = F.TPJ_TITLE_NO
WHERE E.TTL_LEGAL_STATUS NOT IN ('A','P')
AND F.TPJ_PART_ACCT_NO = 923935 )
FOR FETCH ONLY
;

As you can see, there are some obvious missing parenthesis in this query. I rewrote the query and changed the NOT IN to a NOT EXISTS. Now the explain has no row for the PARISP.TTTL_TTL_DETAIL table.

SELECT DISTINCT A.TPJ_TITLE_NO
, A.TPJ_TITLE_NAME
, A.TPJ_SHARE
, B.PRF_US_FEA_RADIO
, D.DVL_NAME
FROM PARISP.TTTL_TPJ_JUNCTION A
LEFT JOIN PARISP.TTTL_PRF_CUM_PERF B
ON A.TPJ_TITLE_NO = B.PRF_TITLE_NO
AND B.PRF_YYYYQ = '99999'
LEFT JOIN PARISP.TTTL_AWD_AWARD C
ON A.TPJ_TITLE_NO = C.AWD_TITLE_NO
INNER JOIN PARISP.TBMI_DVL_VALUE D
ON C.AWD_AWARD_TYPE = D.DVL_VALUE
AND D.DVL_DCL_SQ = 72
WHERE A.TPJ_PART_ACCT_NO = 923935
AND A.TPJ_SHARE = 100
AND NOT EXISTS (SELECT 1
FROM PARISP.TTTL_TTL_DETAIL E
INNER JOIN PARISP.TTTL_TPJ_JUNCTION F
ON E.TTL_TITLE_NO = F.TPJ_TITLE_NO
WHERE E.TTL_LEGAL_STATUS NOT IN ('A','P')
AND F.TPJ_PART_ACCT_NO = 923935
AND A.TPJ_TITLE_NO = E.TTL_TITLE_NO )
WITH UR
FOR FETCH ONLY


;



Dave Nance


-----End Original Message-----

Joe Geller

RE: Z/OS DB2 V10 table disappears from explain after a rewrite of SQL???
(in response to Chris Tee)

Also, is there any referential integrity between the tables?

Joe

In Reply to Chris Tee:

Dave


Is PARISP.TTTL_TTL_DETAIL a table, i.e not a view or alias? Perhaps you could post the explain output.


regards


Chris


________________________________
From: Dave Nance <[login to unmask email]>
Sent: 07 September 2017 21:03
To: Lizette Koehler
Subject: [DB2-L] - Z/OS DB2 V10 table disappears from explain after a rewrite of SQL???

Hi all any help appreciated.

Have this query that someone wrote that was incorrectly written and performed very poorly. When I rewrote the query to correct the logic error, one of the tables no longer is in the access path. Actually, this is second query this week I have seen this on and do not really understand why.

SELECT DISTINCT A.TPJ_TITLE_NO
, A.TPJ_TITLE_NAME
, A.TPJ_SHARE
, B.PRF_US_FEA_RADIO
, D.DVL_NAME
FROM PARISP.TTTL_TPJ_JUNCTION A
LEFT JOIN PARISP.TTTL_PRF_CUM_PERF B
ON A.TPJ_TITLE_NO = B.PRF_TITLE_NO
LEFT JOIN PARISP.TTTL_AWD_AWARD C
ON A.TPJ_TITLE_NO = C.AWD_TITLE_NO
INNER JOIN PARISP.TBMI_DVL_VALUE D
ON C.AWD_AWARD_TYPE = D.DVL_VALUE
WHERE A.TPJ_PART_ACCT_NO = 923935
AND B.PRF_YYYYQ = '99999'
OR B.PRF_YYYYQ IS NULL
AND D.DVL_DCL_SQ = 72
OR D.DVL_DCL_SQ IS NULL
AND A.TPJ_SHARE = 100
AND A.TPJ_TITLE_NO NOT IN (SELECT DISTINCT E.TTL_TITLE_NO
FROM PARISP.TTTL_TTL_DETAIL E
INNER JOIN PARISP.TTTL_TPJ_JUNCTION F
ON E.TTL_TITLE_NO = F.TPJ_TITLE_NO
WHERE E.TTL_LEGAL_STATUS NOT IN ('A','P')
AND F.TPJ_PART_ACCT_NO = 923935 )
FOR FETCH ONLY
;

As you can see, there are some obvious missing parenthesis in this query. I rewrote the query and changed the NOT IN to a NOT EXISTS. Now the explain has no row for the PARISP.TTTL_TTL_DETAIL table.

SELECT DISTINCT A.TPJ_TITLE_NO
, A.TPJ_TITLE_NAME
, A.TPJ_SHARE
, B.PRF_US_FEA_RADIO
, D.DVL_NAME
FROM PARISP.TTTL_TPJ_JUNCTION A
LEFT JOIN PARISP.TTTL_PRF_CUM_PERF B
ON A.TPJ_TITLE_NO = B.PRF_TITLE_NO
AND B.PRF_YYYYQ = '99999'
LEFT JOIN PARISP.TTTL_AWD_AWARD C
ON A.TPJ_TITLE_NO = C.AWD_TITLE_NO
INNER JOIN PARISP.TBMI_DVL_VALUE D
ON C.AWD_AWARD_TYPE = D.DVL_VALUE
AND D.DVL_DCL_SQ = 72
WHERE A.TPJ_PART_ACCT_NO = 923935
AND A.TPJ_SHARE = 100
AND NOT EXISTS (SELECT 1
FROM PARISP.TTTL_TTL_DETAIL E
INNER JOIN PARISP.TTTL_TPJ_JUNCTION F
ON E.TTL_TITLE_NO = F.TPJ_TITLE_NO
WHERE E.TTL_LEGAL_STATUS NOT IN ('A','P')
AND F.TPJ_PART_ACCT_NO = 923935
AND A.TPJ_TITLE_NO = E.TTL_TITLE_NO )
WITH UR
FOR FETCH ONLY


;



Dave Nance


-----End Original Message-----

Walter Jani&#223;en

AW: Z/OS DB2 V10 table disappears from explain after a rewrite of SQL???
(in response to Dave Nance)
Hi Dave

Which table is pruned off? Is it PARISP.TTTL_AWD_AWARD? If the INNER JOIN with this table wouldn’t be there, then it is clear to me, but in this case, I’ve no idea

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Donnerstag, 7. September 2017 22:04
An: Lizette Koehler
Betreff: [DB2-L] - Z/OS DB2 V10 table disappears from explain after a rewrite of SQL???

Hi all any help appreciated.

Have this query that someone wrote that was incorrectly written and performed very poorly. When I rewrote the query to correct the logic error, one of the tables no longer is in the access path. Actually, this is second query this week I have seen this on and do not really understand why.

SELECT DISTINCT A.TPJ_TITLE_NO
, A.TPJ_TITLE_NAME
, A.TPJ_SHARE
, B.PRF_US_FEA_RADIO
, D.DVL_NAME
FROM PARISP.TTTL_TPJ_JUNCTION A
LEFT JOIN PARISP.TTTL_PRF_CUM_PERF B
ON A.TPJ_TITLE_NO = B.PRF_TITLE_NO
LEFT JOIN PARISP.TTTL_AWD_AWARD C
ON A.TPJ_TITLE_NO = C.AWD_TITLE_NO
INNER JOIN PARISP.TBMI_DVL_VALUE D
ON C.AWD_AWARD_TYPE = D.DVL_VALUE
WHERE A.TPJ_PART_ACCT_NO = 923935
AND B.PRF_YYYYQ = '99999'
OR B.PRF_YYYYQ IS NULL
AND D.DVL_DCL_SQ = 72
OR D.DVL_DCL_SQ IS NULL
AND A.TPJ_SHARE = 100
AND A.TPJ_TITLE_NO NOT IN (SELECT DISTINCT E.TTL_TITLE_NO
FROM PARISP.TTTL_TTL_DETAIL E
INNER JOIN PARISP.TTTL_TPJ_JUNCTION F
ON E.TTL_TITLE_NO = F.TPJ_TITLE_NO
WHERE E.TTL_LEGAL_STATUS NOT IN ('A','P')
AND F.TPJ_PART_ACCT_NO = 923935 )
FOR FETCH ONLY
;

As you can see, there are some obvious missing parenthesis in this query. I rewrote the query and changed the NOT IN to a NOT EXISTS. Now the explain has no row for the PARISP.TTTL_TTL_DETAIL table.

SELECT DISTINCT A.TPJ_TITLE_NO
, A.TPJ_TITLE_NAME
, A.TPJ_SHARE
, B.PRF_US_FEA_RADIO
, D.DVL_NAME
FROM PARISP.TTTL_TPJ_JUNCTION A
LEFT JOIN PARISP.TTTL_PRF_CUM_PERF B
ON A.TPJ_TITLE_NO = B.PRF_TITLE_NO
AND B.PRF_YYYYQ = '99999'
LEFT JOIN PARISP.TTTL_AWD_AWARD C
ON A.TPJ_TITLE_NO = C.AWD_TITLE_NO
INNER JOIN PARISP.TBMI_DVL_VALUE D
ON C.AWD_AWARD_TYPE = D.DVL_VALUE
AND D.DVL_DCL_SQ = 72
WHERE A.TPJ_PART_ACCT_NO = 923935
AND A.TPJ_SHARE = 100
AND NOT EXISTS (SELECT 1
FROM PARISP.TTTL_TTL_DETAIL E
INNER JOIN PARISP.TTTL_TPJ_JUNCTION F
ON E.TTL_TITLE_NO = F.TPJ_TITLE_NO
WHERE E.TTL_LEGAL_STATUS NOT IN ('A','P')
AND F.TPJ_PART_ACCT_NO = 923935
AND A.TPJ_TITLE_NO = E.TTL_TITLE_NO )
WITH UR
FOR FETCH ONLY


;



Dave Nance


-----End Original Message-----
Attachments

  • image001.png (2.6k)