ZOS 2.1 DB2 V11 Change in access path with in-list

Gary Snider

ZOS 2.1 DB2 V11 Change in access path with in-list
Hello,

We are in the process of migrating from DB2 V10 to V11 and have our development system in CM. I am running explains on some of our critical queries to identify any access path changes that may be an issue. I have found a query (below) which uses an in-list where the cost seems to have increased with V11 and I am questioning if the change is valid. Under V10, the in-list is placed in a work file and then joined to TABLE.INFO. The cost is category B, 10 SUs. On V11, the in-list is part of two index scans for table TABLE.INFO, an IXAND and a MIXSCAN. The cost is category B, 506 SUs.

Anyone else experience this change with V11?

SELECT GI_INSURED_FIRST, GI_INSURED_LAST,
GI_INSURED_MI, GI_REGION
FROM TABLE.INFO GI
WHERE GI_INSURED_LAST LIKE :DB2-INSURED-LAST
AND
(GI_REGION = :WS-REGION
AND GI_ISSUE_AGT_CODE IN
(:A001,:A002,:A003,:A004,:A005,:A006,
:A007,:A008,:A009,:A010,:A011,:A012,:A013,
:A014,:A015,:A016,:A017,:A018,:A019,:A020,
:A021,:A022,:A023,:A024,:A025,:A026,:A027,
:A028,:A029,:A030,:A031,:A032,:A033,:A034,
:A035,:A036,:A037,:A038,:A039,:A040,:A041,
:A042,:A043,:A044,:A045,:A046,:A047,:A048,
:A049,:A050,:A051,:A052,:A053,:A054,:A055,
:A056,:A057,:A058,:A059,:A060,:A061,:A062,
:A063,:A064,:A065,:A066,:A067,:A068,:A069,
:A070,:A071,:A072,:A073,:A074,:A075,:A076,
:A077,:A078,:A079,:A080)
)
AND GI_LINE_OF_BUS = 'ITV'
AND GI_HITLIST_DEL_DT IS NULL
ORDER BY GI_INSURED_LAST ASC,
GI_INSURED_FIRST ASC, GI.QUOTE_DATE DESC,
GI.QUOTE_TIME DESC
FETCH FIRST 2000 ROWS ONLY

Roy Boxwell

AW: ZOS 2.1 DB2 V11 Change in access path with in-list
(in response to Gary Snider)
Could it be


Max Numb in IN-List (INLISTP)
Allows you to specify the maximum number of elements in an IN-list for certain IN predicate optimizations to occur. The default value for INLISTP is 50.

ZPARM INLISTP in macro DSN6SPRM.


ZPARM?? You have 80 inlist parms...


Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 85

40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert


________________________________
Von: Gary Snider <[login to unmask email]>
Gesendet: Freitag, 24. Februar 2017 15:48
An: '[login to unmask email]'
Betreff: [DB2-L] - ZOS 2.1 DB2 V11 Change in access path with in-list

Hello,

We are in the process of migrating from DB2 V10 to V11 and have our development system in CM. I am running explains on some of our critical queries to identify any access path changes that may be an issue. I have found a query (below) which uses an in-list where the cost seems to have increased with V11 and I am questioning if the change is valid. Under V10, the in-list is placed in a work file and then joined to TABLE.INFO. The cost is category B, 10 SUs. On V11, the in-list is part of two index scans for table TABLE.INFO, an IXAND and a MIXSCAN. The cost is category B, 506 SUs.

Anyone else experience this change with V11?

SELECT GI_INSURED_FIRST, GI_INSURED_LAST,
GI_INSURED_MI, GI_REGION
FROM TABLE.INFO GI
WHERE GI_INSURED_LAST LIKE :DB2-INSURED-LAST
AND
(GI_REGION = :WS-REGION
AND GI_ISSUE_AGT_CODE IN
(:A001,:A002,:A003,:A004,:A005,:A006,
:A007,:A008,:A009,:A010,:A011,:A012,:A013,
:A014,:A015,:A016,:A017,:A018,:A019,:A020,
:A021,:A022,:A023,:A024,:A025,:A026,:A027,
:A028,:A029,:A030,:A031,:A032,:A033,:A034,
:A035,:A036,:A037,:A038,:A039,:A040,:A041,
:A042,:A043,:A044,:A045,:A046,:A047,:A048,
:A049,:A050,:A051,:A052,:A053,:A054,:A055,
:A056,:A057,:A058,:A059,:A060,:A061,:A062,
:A063,:A064,:A065,:A066,:A067,:A068,:A069,
:A070,:A071,:A072,:A073,:A074,:A075,:A076,
:A077,:A078,:A079,:A080)
)
AND GI_LINE_OF_BUS = 'ITV'
AND GI_HITLIST_DEL_DT IS NULL
ORDER BY GI_INSURED_LAST ASC,
GI_INSURED_FIRST ASC, GI.QUOTE_DATE DESC,
GI.QUOTE_TIME DESC
FETCH FIRST 2000 ROWS ONLY

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

Joe Geller

RE: AW: ZOS 2.1 DB2 V11 Change in access path with in-list
(in response to Roy Boxwell)

Gary,

1) The estimated cost is more, but how does the actual performance compare? 

2) If Roy has found your problem, then the fix is easy.  If that is not the case, then it is possible that under V10 the old access path had a cost estimate that was too low and under V11 it is actually higher than the new access path.

3) You didn't say whether the 2 index accesses were for the same index or on different indexes.  What are the key columns of the indexes?  What is Matchcols for the index access?

4) How big is the table?

Joe

In Reply to Roy Boxwell:

Could it be


Max Numb in IN-List (INLISTP)
Allows you to specify the maximum number of elements in an IN-list for certain IN predicate optimizations to occur. The default value for INLISTP is 50.

ZPARM INLISTP in macro DSN6SPRM.


ZPARM?? You have 80 inlist parms...


Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 85

40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert


________________________________
Von: Gary Snider <[login to unmask email]>
Gesendet: Freitag, 24. Februar 2017 15:48
An: '[login to unmask email]'
Betreff: [DB2-L] - ZOS 2.1 DB2 V11 Change in access path with in-list

Hello,

We are in the process of migrating from DB2 V10 to V11 and have our development system in CM. I am running explains on some of our critical queries to identify any access path changes that may be an issue. I have found a query (below) which uses an in-list where the cost seems to have increased with V11 and I am questioning if the change is valid. Under V10, the in-list is placed in a work file and then joined to TABLE.INFO. The cost is category B, 10 SUs. On V11, the in-list is part of two index scans for table TABLE.INFO, an IXAND and a MIXSCAN. The cost is category B, 506 SUs.

Anyone else experience this change with V11?

SELECT GI_INSURED_FIRST, GI_INSURED_LAST,
GI_INSURED_MI, GI_REGION
FROM TABLE.INFO GI
WHERE GI_INSURED_LAST LIKE :DB2-INSURED-LAST
AND
(GI_REGION = :WS-REGION
AND GI_ISSUE_AGT_CODE IN
(:A001,:A002,:A003,:A004,:A005,:A006,
:A007,:A008,:A009,:A010,:A011,:A012,:A013,
:A014,:A015,:A016,:A017,:A018,:A019,:A020,
:A021,:A022,:A023,:A024,:A025,:A026,:A027,
:A028,:A029,:A030,:A031,:A032,:A033,:A034,
:A035,:A036,:A037,:A038,:A039,:A040,:A041,
:A042,:A043,:A044,:A045,:A046,:A047,:A048,
:A049,:A050,:A051,:A052,:A053,:A054,:A055,
:A056,:A057,:A058,:A059,:A060,:A061,:A062,
:A063,:A064,:A065,:A066,:A067,:A068,:A069,
:A070,:A071,:A072,:A073,:A074,:A075,:A076,
:A077,:A078,:A079,:A080)
)
AND GI_LINE_OF_BUS = 'ITV'
AND GI_HITLIST_DEL_DT IS NULL
ORDER BY GI_INSURED_LAST ASC,
GI_INSURED_FIRST ASC, GI.QUOTE_DATE DESC,
GI.QUOTE_TIME DESC
FETCH FIRST 2000 ROWS ONLY

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

Sam Baugh

ZOS 2.1 DB2 V11 Change in access path with in-list
(in response to Gary Snider)
Make sure the variables in the IN list match the GI_INSURED_LAST
definition, such as if GI_INSURED_LAST is CHAR(8), then the variables need
to be CHAR(8) also. We had mismatched types that performed fine under DB2
10, but poorly on DB2 11.

On Fri, Feb 24, 2017 at 8:48 AM, Gary Snider <[login to unmask email]> wrote:

> Hello,
>
> We are in the process of migrating from DB2 V10 to V11 and have our
> development system in CM. I am running explains on some of our critical
> queries to identify any access path changes that may be an issue. I have
> found a query (below) which uses an in-list where the cost seems to have
> increased with V11 and I am questioning if the change is valid. Under V10,
> the in-list is placed in a work file and then joined to TABLE.INFO. The
> cost is category B, 10 SUs. On V11, the in-list is part of two index scans
> for table TABLE.INFO, an IXAND and a MIXSCAN. The cost is category B,
> 506 SUs.
>
> Anyone else experience this change with V11?
>
> SELECT GI_INSURED_FIRST, GI_INSURED_LAST,
> GI_INSURED_MI, GI_REGION
> FROM TABLE.INFO GI
> WHERE GI_INSURED_LAST LIKE :DB2-INSURED-LAST
> AND
> (GI_REGION = :WS-REGION
> AND GI_ISSUE_AGT_CODE IN
> (:A001,:A002,:A003,:A004,:A005,:A006,
> :A007,:A008,:A009,:A010,:A011,:A012,:A013,
> :A014,:A015,:A016,:A017,:A018,:A019,:A020,
> :A021,:A022,:A023,:A024,:A025,:A026,:A027,
> :A028,:A029,:A030,:A031,:A032,:A033,:A034,
> :A035,:A036,:A037,:A038,:A039,:A040,:A041,
> :A042,:A043,:A044,:A045,:A046,:A047,:A048,
> :A049,:A050,:A051,:A052,:A053,:A054,:A055,
> :A056,:A057,:A058,:A059,:A060,:A061,:A062,
> :A063,:A064,:A065,:A066,:A067,:A068,:A069,
> :A070,:A071,:A072,:A073,:A074,:A075,:A076,
> :A077,:A078,:A079,:A080)
> )
> AND GI_LINE_OF_BUS = 'ITV'
> AND GI_HITLIST_DEL_DT IS NULL
> ORDER BY GI_INSURED_LAST ASC,
> GI_INSURED_FIRST ASC, GI.QUOTE_DATE DESC,
> GI.QUOTE_TIME DESC
> FETCH FIRST 2000 ROWS ONLY
>
> -----End Original Message-----
>
>

Gary Snider

RE: AW: ZOS 2.1 DB2 V11 Change in access path with in-list
(in response to Roy Boxwell)

Roy, thanks for the response.  I changed INLISTP=100, but that didn't make a difference.  I did some testing and found that the V11 access path performed as well and in some cases better than the V10 access path.  So, even though the V11 SU's look higher the cost during execution was not.

Thanks to all that replied.