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)

Tommy Petersen

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

First the two queries are the same except from the "NOT IN" and "NOT EXISTS" clauses.

My guess is that the NOT IN version does a table scan on TTTL_TITLE_DETAIL, however, there is an index on TITLE_NO and LEGAL_STATUS which could need a reorg, and therefore an index scan is not used.

The NOT EXISTS version would use such an index as only one row is needed at a time, and the index lookup would be faster than a table scan, almost no matter how bad the index is.

You should see an index used instead of the table with the "NOT EXISTS".

If that does seem to be the case, please post the DDL for the tables and indexes.