In-Subqueries in DB2 z/OS V9

Walter Janißen

In-Subqueries in DB2 z/OS V9
Hi

Did anybody notice, that the explain-output for non-correlated IN-Subqueries has changed?

Explain in V8 NFM for the following query:

SELECT * FROM DB2.VATB0009
WHERE KNR_1 IN
(SELECT KNR
FROM DB2.VATB0033
WHERE KNR > ?
)

looks like:

Pln Tab I SORT COMP Lo P FN Ix P P J J Pt Stmt
Nr Nr M Tabelle Nr AC MC Index O UJOG UJOG ck F EV Sq M R T C Nr Typ
-- --- -- -------- --- -- -- -------- - ---- ---- -- - -- -- - - - -- -- ------
1 1 0 VATB0009 1 N 1 VAIX0009 N NNNN NNNN IS 0 - - 0 SELECT
2 1 0 VATB0033 2 I 1 VAIX1033 Y NNNN NNNN IS S 0 - - 1 NCOSUB
2 2 3 0 0 N NNNN YNYN 0 - - 1 NCOSUB

But in V9 CM:

Pln Tab I SORT COMP Lo P FN Ix P P J J Pt Stmt
Nr Nr M Tabelle Nr AC MC Index O UJOG UJOG ck F EV Sq M R T C Nr Typ
-- --- -- -------- --- -- -- -------- - ---- ---- -- - -- -- - - - -- -- ------
1 1 0 DSNWFQB( 3 R 0 N NNNN NNNN N S 0 - - 0 SELECT
02)
1 2 1 VATB0009 1 I 1 VAIX0009 N NNNN NNNN IS 0 - - 0 SELECT
2 1 0 VATB0033 2 I 1 VAIX1033 Y NNNN NNNN IS S 0 - - 1 NCOSUB
2 2 3 0 0 N NNNN YNYN 0 - - 1 NCOSUB

So your access paths will contain more tablespace scans, which might be difficult to handle for such tools comparing access-paths if anything has become worse than under V8.
I think, what explain tells us, is the same: The result of the subquery has to be materialized to get sorted.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40198 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrats: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Peter Vanroose

Re: In-Subqueries in DB2 z/OS V9
(in response to Walter Janißen)
[login to unmask email] wrote:
> ...
> Pln Tab I
>Nr Nr M Tabelle Nr AC MC Index O
>-- --- -- -------- --- -- -- -------- -
>1 1 0 VATB0009 1 N 1 VAIX0009 N
...
>1 1 0 WFQB(2) 3 R 0 N
>1 2 1 VATB0009 1 I 1 VAIX0009 N
>So your access paths will contain more tablespace scans,
> which might be difficult to handle for such tools comparing
> access-paths if anything has become worse than under V8.
> I think, what explain tells us, is the same: The result of the
> subquery has to be materialized to get sorted.

Actually, the access path has improved in v9:
instead of doing a repeated index access (access method "N"),
each time in a matching way (MC=1), for the outer query
(probably with an index look-aside, of course),
now the access is through a *single*, 1-matching, index access.
The work file scan (DSNWFQB(2)), running in parallel to the VAIX0009 index
traversal, is just needed to skip over those entries in index VAIX0009 that
don't appear in index VAIX1033. I.e., almost like a merge scan join.

-- Peter Vanroose
ABIS Training & Consulting

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Terry Purcell

Re: In-Subqueries in DB2 z/OS V9
(in response to Peter Vanroose)
Walter is correct.

DB2 9 shows a clearer representation of the access path chosen for this non-
correlated IN subquery. The original form, used from V6-V8, is somewhat
confusing. This change is part of the larger DB2 9 enhancement - Global
Optimization, which allows more flexibility in the access path choices available
for subqueries.

But in this example, there is no actual access path change.

Regards
Terry Purcell

On Thu, 22 Jan 2009 17:39:57 +0000, Peter Vanroose <[login to unmask email]>
wrote:

>[login to unmask email] wrote:
>> ...
>> Pln Tab I
>>Nr Nr M Tabelle Nr AC MC Index O
>>-- --- -- -------- --- -- -- -------- -
>>1 1 0 VATB0009 1 N 1 VAIX0009 N
>...
>>1 1 0 WFQB(2) 3 R 0 N
>>1 2 1 VATB0009 1 I 1 VAIX0009 N
>>So your access paths will contain more tablespace scans,
>> which might be difficult to handle for such tools comparing
>> access-paths if anything has become worse than under V8.
>> I think, what explain tells us, is the same: The result of the
>> subquery has to be materialized to get sorted.
>
>Actually, the access path has improved in v9:
>instead of doing a repeated index access (access method "N"),
>each time in a matching way (MC=1), for the outer query
>(probably with an index look-aside, of course),
>now the access is through a *single*, 1-matching, index access.
>The work file scan (DSNWFQB(2)), running in parallel to the VAIX0009 index
>traversal, is just needed to skip over those entries in index VAIX0009 that
>don't appear in index VAIX1033. I.e., almost like a merge scan join.
>
>-- Peter Vanroose
> ABIS Training & Consulting

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html