db2 z/os V10 --- Access path oddity

Dave Nance

db2 z/os V10 --- Access path oddity
Hi all,

    Playing with a query trying to get the correct index access and ran across this.
FROM MATCH MWHERE m.PROGRAM_NO IN (SELECT DISTINCT(A.PROGRAM_NO) FROM PROGRAM A                                           WHERE a.PROGRAM_CD <> '36'                                                and not exists (select 1 FROM PROGRAM b                                                               WHERE A.PROGRAM_NO = B.PROGRAM_NO                                                                   AND B.PROGRAM_CD = '36'))
The explain on this query shows the sub-select being materialized as DSNWFQB(02), which is what I was after. The oddity is that it shows this block as being a correlated sub-query to my MATCH table. Inside this sub-select it is correlated, but the join to the MATCH table should be Non-Correlated, not sure why its showing it as Correlated. Wondering if anyone might have an idea as to why its showing up this way. If, I remove the distinct, it goes away, but performs tablespace scan on my larger MATCH table and index access to my PROGRAM table. Actually, either way I get that tablespace scan on my larger table, and that's what I'm trying to change. Thanks.

Dave Nance




Muthuraj Kumaresan

db2 z/os V10 --- Access path oddity
(in response to Dave Nance)
Hope you know that DB2 can change correlated to non correlated or vice versa based on number of output values in sub query.. when there will be large number of values in output of subquery it will try to perform correlated subquery..

Do you see correlated subquery or join?.. it makes sense to see a join as you said removing distinct changes the access path behavior..

How many records these tables have?
What's Cardinality of program_no (in both the tables ) and program_cd ?

How many records do you anticipate in the output ?

Muthu
Sent from my iPhone

> On 16 Dec 2016, at 2:20 AM, Dave Nance <[login to unmask email]> wrote:
>
> Hi all,
>
>
> Playing with a query trying to get the correct index access and ran across this.
>
> FROM MATCH M
> WHERE m.PROGRAM_NO IN (SELECT DISTINCT(A.PROGRAM_NO) FROM PROGRAM A
> WHERE a.PROGRAM_CD <> '36'
> and not exists (select 1 FROM PROGRAM b
> WHERE A.PROGRAM_NO = B.PROGRAM_NO
> AND B.PROGRAM_CD = '36'))
>
> The explain on this query shows the sub-select being materialized as DSNWFQB(02), which is what I was after. The oddity is that it shows this block as being a correlated sub-query to my MATCH table. Inside this sub-select it is correlated, but the join to the MATCH table should be Non-Correlated, not sure why its showing it as Correlated. Wondering if anyone might have an idea as to why its showing up this way. If, I remove the distinct, it goes away, but performs tablespace scan on my larger MATCH table and index access to my PROGRAM table. Actually, either way I get that tablespace scan on my larger table, and that's what I'm trying to change. Thanks.
>
>
> Dave Nance
>
>
>
>
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
> ---> Anaheim, California, April 30 - May 04, 2017 <---
> http://www.idug.org/na
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Walter Jani&#223;en

AW: db2 z/os V10 --- Access path oddity
(in response to Dave Nance)
Hi Dave

I would guess that the workfile points to the fact that DB2 changed the non-correlated IN-subquery to a correlated EXISTS-subquery. What is the accesstype for that workfile. If it’s ‘O’ then I am convinced that the subquery was changed to an EXISTS.

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, 15. Dezember 2016 21:50
An: Venkat Srinivasan
Betreff: [DB2-L] - db2 z/os V10 --- Access path oddity

Hi all,

Playing with a query trying to get the correct index access and ran across this.

FROM MATCH M
WHERE m.PROGRAM_NO IN (SELECT DISTINCT(A.PROGRAM_NO) FROM PROGRAM A
WHERE a.PROGRAM_CD <> '36'
and not exists (select 1 FROM PROGRAM b
WHERE A.PROGRAM_NO = B.PROGRAM_NO
AND B.PROGRAM_CD = '36'))

The explain on this query shows the sub-select being materialized as DSNWFQB(02), which is what I was after. The oddity is that it shows this block as being a correlated sub-query to my MATCH table. Inside this sub-select it is correlated, but the join to the MATCH table should be Non-Correlated, not sure why its showing it as Correlated. Wondering if anyone might have an idea as to why its showing up this way. If, I remove the distinct, it goes away, but performs tablespace scan on my larger MATCH table and index access to my PROGRAM table. Actually, either way I get that tablespace scan on my larger table, and that's what I'm trying to change. Thanks.


Dave Nance




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

  • image001.png (2.6k)

Terry Purcell

RE: AW: db2 z/os V10 --- Access path oddity
(in response to Walter Janißen)

Dave,

I agree with the 2 replies that DB2 can choose to correlate or de-correlate a subquery from V9 onwards. This correlation/de-correlation only applies to SELECT statements - and I don't know if your access path investigation here is related to your other post regarding a DELETE statement. Often someone will test with a SELECT before applying that to a DELETE (or UPDATE, or INSERT/SELECT).

If this is a SELECT, you can always code the subquery as a join or nested table expression (if DISTINCT is mandatory) - but if this is a DELETE (as per your other post) - then it has to be a subquery.

Unfortunately there isn't enough information to help get you the access path you are looking for.

Regards

Terry Purcell

In Reply to Walter Janißen:

Hi Dave

I would guess that the workfile points to the fact that DB2 changed the non-correlated IN-subquery to a correlated EXISTS-subquery. What is the accesstype for that workfile. If it’s ‘O’ then I am convinced that the subquery was changed to an EXISTS.

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, 15. Dezember 2016 21:50
An: Venkat Srinivasan
Betreff: [DB2-L] - db2 z/os V10 --- Access path oddity

Hi all,

Playing with a query trying to get the correct index access and ran across this.

FROM MATCH M
WHERE m.PROGRAM_NO IN (SELECT DISTINCT(A.PROGRAM_NO) FROM PROGRAM A
WHERE a.PROGRAM_CD <> '36'
and not exists (select 1 FROM PROGRAM b
WHERE A.PROGRAM_NO = B.PROGRAM_NO
AND B.PROGRAM_CD = '36'))

The explain on this query shows the sub-select being materialized as DSNWFQB(02), which is what I was after. The oddity is that it shows this block as being a correlated sub-query to my MATCH table. Inside this sub-select it is correlated, but the join to the MATCH table should be Non-Correlated, not sure why its showing it as Correlated. Wondering if anyone might have an idea as to why its showing up this way. If, I remove the distinct, it goes away, but performs tablespace scan on my larger MATCH table and index access to my PROGRAM table. Actually, either way I get that tablespace scan on my larger table, and that's what I'm trying to change. Thanks.


Dave Nance




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

Brett Sinclair

db2 z/os V10 --- Access path oddity
(in response to Muthuraj Kumaresan)
Thank you; we have about 350,000 records. Management has decided to engage an IBM resource to look into this—I will do some investigation on my own ‘under the covers’.







The most important thing is to keep the most important thing the most important thing

Brett Sinclair
AXA Technology Services
Telephone: 1-908-204-0144

From: Muthu [mailto:[login to unmask email]
Sent: Thursday, December 15, 2016 11:01 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: db2 z/os V10 --- Access path oddity

Hope you know that DB2 can change correlated to non correlated or vice versa based on number of output values in sub query.. when there will be large number of values in output of subquery it will try to perform correlated subquery..

Do you see correlated subquery or join?.. it makes sense to see a join as you said removing distinct changes the access path behavior..

How many records these tables have?
What's Cardinality of program_no (in both the tables ) and program_cd ?

How many records do you anticipate in the output ?

Muthu
Sent from my iPhone

On 16 Dec 2016, at 2:20 AM, Dave Nance <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Hi all,

Playing with a query trying to get the correct index access and ran across this.

FROM MATCH M
WHERE m.PROGRAM_NO IN (SELECT DISTINCT(A.PROGRAM_NO) FROM PROGRAM A
WHERE a.PROGRAM_CD <> '36'
and not exists (select 1 FROM PROGRAM b
WHERE A.PROGRAM_NO = B.PROGRAM_NO
AND B.PROGRAM_CD = '36'))

The explain on this query shows the sub-select being materialized as DSNWFQB(02), which is what I was after. The oddity is that it shows this block as being a correlated sub-query to my MATCH table. Inside this sub-select it is correlated, but the join to the MATCH table should be Non-Correlated, not sure why its showing it as Correlated. Wondering if anyone might have an idea as to why its showing up this way. If, I remove the distinct, it goes away, but performs tablespace scan on my larger MATCH table and index access to my PROGRAM table. Actually, either way I get that tablespace scan on my larger table, and that's what I'm trying to change. Thanks.


Dave Nance




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

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


Ce message est confidentiel; Son contenu ne represente en aucun cas
un engagement de la part de AXA Technology Services (AXA Tech) sous
reserve de tout accord conclu par ecrit entre vous et AXA Technology
Services (AXA Tech).Toute publication, utilisation ou diffusion,meme
partielle, doit etre autorisee prealablement. Si vous n'etes pas
destinataire de ce message, merci d'en avertir immediatement l'expe-
diteur.

This message is confidential; its contents do not constitute a
commitment by AXA Technology Services (AXA Tech) except where provi-
ded for in a written agreement between you and AXA Technology
Services (AXA Tech). Any unauthorised disclosure, use or dissemina-
tion, either whole or partial, is prohibited. If you are not the
intended recipient of the message, please notify the sender imme-
diately.