Db2 12 z/OS - PTF UI68424 and default ZPARMs

Aurora Emanuela Dellanno

Db2 12 z/OS - PTF UI68424 and default ZPARMs

Hi y'all,

 

today we updated our TEST Db2 subsystems to add scary PTF UI68424, which changes a lot of defaults for ZPARMs, however we decided that we did not want the new defaults for some items, and therefore went to change them by hand in the newly created DSNTIJUZ member.

 

Lo and behold, the following new defaults were exactly the same as the old defaults (yes we did double check, the PTF is in our code):

 

* NPGTHRSH (this we did not like and wanted the old default)

- theory: default changed from 0 to 1.

- practice: 

NPGTHRSH=0 – default NOT changed (cannot be changed via CLIST)

 

* INLISTP (this we did like, wanted the new default BUT won't tell our developers, ahem)

- theory: default changed from 50 to 1000.  

- practice:

INLISTP=50 – default NOT changed (cannot be changed via CLIST) - so we changed it by hand in JUZ...

 

Anybody else seen this, should we open a PMR, is there something we should know?

 

TIA. 

 

Keep safe and healthy everybody (stay at home, save lives).

 

Aurora *wearing a rather fetching mask*

Joe Geller

RE: Db2 12 z/OS - PTF UI68424 and default ZPARMs
(in response to Aurora Emanuela Dellanno)

Hi Aurora,

I can't comment on the difficulty of changing the default, but I will give you my thoughts on NPGTHRSH.  I had a discussion with Terry Purcell a year or two ago and I strongly agree with the change in default.  There is another change made that also relates to that.

The importance relates to tables which have default stats.  The default for number of pages is -1 (but Db2 uses 501 for access path costing) and for comparing to NPGTHRSH Db2 used 501 prior to V11.  Often tables have default stats because either someone forgot to run runstats or more often that the table is starting off small but will grow large.  For this type of table you would want index access to be favored because the table may grow to much more than 501 pages.  With NPGTHRSH = 0 Db2 will choose the cheapest access path which may be a table scan.

With V12, Db2 will use -1 for the comparison, but not if NPGTHRSH = 0.  If you change it to 1 (or another number), then index access will be favored for all tables with no stats.  For a tiny table the tablescan might be faster, but using the index will not be that much slower.  For a larger table, index access will probably be better.

Now, for table with stats that are very small, you might want the tablescan and NPGTHRSH = 0 gave you that (if Db2 determines it is the cheapest access path).  Specifying NPGTHRSH = 100 (for example) would not (e.g. a 3 page table would then get index access).  But using NPGTHRSH = 1 still protects you because a 3 page table is not smaller than NPGTHRSH = 1, so it wouldn't apply and Db2 will pick the cheapest access path.

(Whew, with a little lookup to refresh my memory I avoided the embarrassment of not having completely memorized everything that Terry says).

Joe

In Reply to Aurora Emanuela Dellanno:

Hi y'all,

 

today we updated our TEST Db2 subsystems to add scary PTF UI68424, which changes a lot of defaults for ZPARMs, however we decided that we did not want the new defaults for some items, and therefore went to change them by hand in the newly created DSNTIJUZ member.

 

Lo and behold, the following new defaults were exactly the same as the old defaults (yes we did double check, the PTF is in our code):

 

* NPGTHRSH (this we did not like and wanted the old default)

- theory: default changed from 0 to 1.

- practice: 

NPGTHRSH=0 – default NOT changed (cannot be changed via CLIST)

 

* INLISTP (this we did like, wanted the new default BUT won't tell our developers, ahem)

- theory: default changed from 50 to 1000.  

- practice:

INLISTP=50 – default NOT changed (cannot be changed via CLIST) - so we changed it by hand in JUZ...

 

Anybody else seen this, should we open a PMR, is there something we should know?

 

TIA. 

 

Keep safe and healthy everybody (stay at home, save lives).

 

Aurora *wearing a rather fetching mask*

Walter Janißen

RE: Db2 12 z/OS - PTF UI68424 and default ZPARMs
(in response to Joe Geller)

I can add for default statt that if there are multiple indexes possible then the index with the most matching columns is chosen. Two years ago I did a presentation with Terry in Malta, where discussed this a little but.

Aurora Emanuela Dellanno

RE: Db2 12 z/OS - PTF UI68424 and default ZPARMs
(in response to Joe Geller)

Hi Joe,

 

thank you - evaluating the new defaults is on our to-do-after-the-migration list: right now we do not want to change this to the new behaviour without having a look at our environments and seeing the possible negative effects.

 

my query, however, is slightly different: the PTF is supposed to change the default values for a number of ZPARMs, but it does NOT for the two I mentioned.

 

We think the PTF does not work as designed (i.e. changing all these defaults, some yeah, all no)...

Aurora Emanuela Dellanno

RE: Db2 12 z/OS - PTF UI68424 and default ZPARMs
(in response to Aurora Emanuela Dellanno)

not a lot of replies on this one - I suppose it's because we are currently in the first part of migrating DB2 11 to Db2 12 and therefore we are keeping very current on maintenance (the APAR for the above-mentioned PTF was closed on 03/25, last).

 

Anyway, to keep this thread current for anyone interested or possibly affected, we have opened a PMR since we think the PTF is not WAD.

 

Thanks. Keep healthy and safe.

 

Aurora

Aurora Emanuela Dellanno

RE: Db2 12 z/OS - PTF UI68424 and default ZPARMs
(in response to Aurora Emanuela Dellanno)

just a quick update on the conclusion to this: the text of the HOLD ACTION in the APAR / PTF is misleading, if you get to apply this please be aware.

 

We closed the PMR and this is IBM's conclusion:

 

"I will leave the case closed and put following information in the close text of this  case. This will help the search for future reference.

>>>>>>>>>>>>

In the Holdaction section of UI68424, detailed instruction of Step 2. All customers are recommended to do following:

Please check and update any other settings as in step (1) above in your DSNTIDxx input member.  Use the rows below as a model.  The 6th field specifies the setting."

 

The current text tells customers only to update any values that were customised, which leads to possibly undesired values in case you are using your own DSNTIDxx input member (even if generated by the CLIST), and not the one from the latest SDSNSAMP: the values in the fields was calculated and therefore will not be overwritten with the new defaults.

 

Thanks.

 

Aurora

Michael Hannan

RE: Db2 12 z/OS - PTF UI68424 and default ZPARMs
(in response to Joe Geller)



In Reply to Joe Geller:

Hi Aurora,

I can't comment on the difficulty of changing the default, but I will give you my thoughts on NPGTHRSH.  I had a discussion with Terry Purcell a year or two ago and I strongly agree with the change in default.  There is another change made that also relates to that.

The importance relates to tables which have default stats.  The default for number of pages is -1 (but Db2 uses 501 for access path costing) and for comparing to NPGTHRSH Db2 used 501 prior to V11.  Often tables have default stats because either someone forgot to run runstats or more often that the table is starting off small but will grow large.  For this type of table you would want index access to be favored because the table may grow to much more than 501 pages.  With NPGTHRSH = 0 Db2 will choose the cheapest access path which may be a table scan.

With V12, Db2 will use -1 for the comparison, but not if NPGTHRSH = 0.  If you change it to 1 (or another number), then index access will be favored for all tables with no stats.  For a tiny table the tablescan might be faster, but using the index will not be that much slower.  For a larger table, index access will probably be better.

Now, for table with stats that are very small, you might want the tablescan and NPGTHRSH = 0 gave you that (if Db2 determines it is the cheapest access path).  Specifying NPGTHRSH = 100 (for example) would not (e.g. a 3 page table would then get index access).  But using NPGTHRSH = 1 still protects you because a 3 page table is not smaller than NPGTHRSH = 1, so it wouldn't apply and Db2 will pick the cheapest access path.

(Whew, with a little lookup to refresh my memory I avoided the embarrassment of not having completely memorized everything that Terry says).

Joe

Thanks for the insights Joe.

Terry also sent me some details about NPGTHRSH, and I found the benefits greatly out weighed any dangers. Any small number for NPGTHRSH should be better than not having it. Not yet sure just how high is worth setting it. A low value will do. However a big fan of this already.

We particularly want to be saved from Tablespace scans on Objects with zero Stats or with Default Stats that actually have grown much larger and a good index is available.  We also get protect from highly volatile table where somehow Stats got collected just after the table was emptied. For various complex reasons that Terry explained, NPGTHRSH tends to be better at choosing an access path than making Table Volatile. If NPGTHRSH finds two indexes with same highest Match Cols (and the very low Stats of course) it can still pick the best index by costs.

There is a possibility still of zero Stats but table has grown large and the only matching column index is very badly clustered and very poor filtering. It can't protect against everything. There will be odd case for a larger table when TS Scan is way better, but this is not common.  I can tolerate that. Using index on a small table when could have been and should have been  TS Scan does not cost much. If repeated many times, Index Lookaside can probably help the index to win.

Its best to monitor your realtime stats. If any object has grown dramatically in total number of rows, collect some new Table Stats, or even update them (NPAGESF and CARDF) using the RTS. If any large object has suddenly been emptied, do not recollect Stats, unless you have NPGTHRSH protection against a new REBIND. Your application performance could blowout , and I saw one just last week after Stats had been put back to zero after regular clean out of table rows (highly volatile table) and a prompt REBIND. This was in a Dev environment. NPGTHRSH is now on to protect against these silly incidents.

BTW, I think most sites REBIND far too much. Access path can't go bad if you don't REBIND. REBIND sparingly!

I like Reorg to improve performance, but I don't like it to be followed by REBIND (whether new stats got collected or not).

 

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ May 10, 2020 - 12:32 AM (Australia/Melbourne)

Peter Backlund

Db2 12 z/OS - PTF UI68424 and default ZPARMs
(in response to Michael Hannan)
It has been some time since I retired (actually five years, time flies), but I wholeheartedly support Michael : " Rebind sparingly"
Peter in quarantine in Spain

Skickat från min iPhone

> 9 maj 2020 kl. 16:30 skrev Michael Hannan <[login to unmask email]>:
>
> 
>
>
> In Reply to Joe Geller:
>
> Hi Aurora,
>
> I can't comment on the difficulty of changing the default, but I will give you my thoughts on NPGTHRSH. I had a discussion with Terry Purcell a year or two ago and I strongly agree with the change in default. There is another change made that also relates to that.
>
> The importance relates to tables which have default stats. The default for number of pages is -1 (but Db2 uses 501 for access path costing) and for comparing to NPGTHRSH Db2 used 501 prior to V11. Often tables have default stats because either someone forgot to run runstats or more often that the table is starting off small but will grow large. For this type of table you would want index access to be favored because the table may grow to much more than 501 pages. With NPGTHRSH = 0 Db2 will choose the cheapest access path which may be a table scan.
>
> With V12, Db2 will use -1 for the comparison, but not if NPGTHRSH = 0. If you change it to 1 (or another number), then index access will be favored for all tables with no stats. For a tiny table the tablescan might be faster, but using the index will not be that much slower. For a larger table, index access will probably be better.
>
> Now, for table with stats that are very small, you might want the tablescan and NPGTHRSH = 0 gave you that (if Db2 determines it is the cheapest access path). Specifying NPGTHRSH = 100 (for example) would not (e.g. a 3 page table would then get index access). But using NPGTHRSH = 1 still protects you because a 3 page table is not smaller than NPGTHRSH = 1, so it wouldn't apply and Db2 will pick the cheapest access path.
>
> (Whew, with a little lookup to refresh my memory I avoided the embarrassment of not having completely memorized everything that Terry says).
>
> Joe
>
> Thanks for the insights Joe.
>
> Terry also sent me some details about NPGTHRSH, and I found the benefits greatly out weighed any dangers. Any small number for NPGTHRSH should be better than not having it. Not yet sure just how high is worth setting it. A low value will do. However a big fan of this already.
>
> We particularly want to be saved from Tablespace scans on Objects with zero Stats or with Default Stats that actually have grown much larger and a good index is available. We also get protect from highly volatile table where somehow Stats got collected just after the table was emptied. For various complex reasons that Terry explained, NPGTHRSH tends to be better at choosing an access path than making Table Volatile. If NPGTHRSH finds two indexes with same highest Match Cols (and the very low Stats of course) it can still pick the best index by costs.
>
> There is a possibility still of zero Stats but table has grown large and the only matching column index is very badly clustered and very poor filtering. It can't protect against everything. There will be odd case for a larger ta I can tolerate that.ble when TS Scan is way better, but this is not common. Using index on a small table when could have been and should have been TS Scan does not cost much. If repeated many times, Index Lookaside can probably help the index to win.
>
> Its best to monitor your realtime stats. If any object has grown dramatically in total number of rows, collect some new Table Stats, or even update them (NPAGESF and CARDF) using the RTS. If any large object has suddenly been emptied, do not recollect Stats, unless you have NPGTHRSH protection against a new REBIND. Your application performance could blowout , and I saw one just last week after Stats had been put back to zero after regular clean out of table rows (highly volatile table) and a prompt REBIND. This was in a Dev environment. NPGTHRSH is now on to protect against these silly incidents.
>
> BTW, I think most sites REBIND far too much. Access path can't go bad if you don't REBIND. REBIND sparingly!
>
> I like Reorg to improve performance, but I don't like it to be followed by REBIND (whether new stats got collected or not).
>
>
>
> Michael Hannan,
> DB2 Application Performance Specialist
>
>
> 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]
> Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
> DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
> http://www.ESAIGroup.com/IDUG
>
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>