Bookmark and Share
RSS

Recent Posts

IBM Redbook: Db2 12 for z Optimizer

May 22, 2018

I was recently asked about the Db2 zparm, NPGTHRSH. Admittedly, I knew little about it. The first place I always go to learn about zparms is the Db2 installation and migration manual. Unfortunately, the manual wasn't much help in this case. So I did some searching and found this IBM Redbook: "Db2 12 for z Optimizer."
 
Whether you're a Db2 systems programmer, a Db2 for z/OS developer or a DBA, I encourage you to download this publication. It contains a wealth of information about Db2 performance and provides a high-level overview of performance enhancements available with Db2 12 for z/OS.
 
Here are the sections of this Redbook:
  • Why read further 
  • The performance focus in DB2 12 for z/OS 
  • UNION ALL and outer join performance 
  • Runtime optimizations 
  • Predicate optimizations 
  • User-Defined table function predicate optimizations 
  • Optimizer cost model enhancements 
  • RUNSTATS and optimizer-generated statistics profiles 
  • Static and dynamic plan stability 
  • Summary 
Let's return to the subject of my original search, NPGTHRSH. First, this is from the installation guide:

[NPGTHRSH] specifies whether Db2 is to use special access path selection for tables under a given size. Unless you use SAP, the default value is 0, which means that no special access path selection is used. 

That's it. Nothing about setting the value for NPGTHRSH in a non-SAP environment.  
 
Now check out the Redbook's description of NPGTHRSH:

Extending NPGTHRSH to default statistics
A discussion around zparm NPGTHRSH is unlikely to garner the same immediate understanding compared to a discussion of the VOLATILE table attribute. Both are trying to solve the same problem, which is that there are instances where RUNSTATS collected on an object might be unreliable for one of these reasons:

  • That object is early in an application rollout (meaning it is small but likely to grow quickly)
  • The object size grows and shrinks regularly, making it difficult to collect representative statistics at the right point in time.

The zparm NPGTHRSH was delivered in DB2 V7 to prefer matching index access over other access paths at the subsystem level. And DB2 12 extends the effectiveness of this zparm.

When zparm NPGTHRSH is set, this zparm value is compared with the number of pages for a table This number is the NPAGESF catalog statistic, or at the partition level, it is the number of pages in the partition that is compared to NPGTHRSH. If NPAGESF is less than NPGTHRSH, then the optimizer prefers matching index access for access to that table, if possible.

NPGTHRSH is disabled by default, although there is at least one major ERP vendor that recommends DB2 for z/OS customers set their default to 10. This setting has been effective for many years. No customer complaint of the wanting a table space scan and DB2 choosing matching index access for a 9 (or less) page table has been received.

In DB2 11 and prior, if statistics were not collected on a table, and thus NPAGESF=-1, DB2 did not use -1 for the NPGTHRSH comparison. Instead, and as documented, -1 becomes 501 for all optimizer costing, including the comparison to NPGTHRSH.

DB2 12 instead allows default stats (-1) to apply the NPGTHRSH rules, if enabled. Enabling NPGTHRSH in DB2 12 also applies the rule for preferring matching index access if only the index being considered has default statistics. This scenario is possible if an index was recently created but statistics are not yet collected.

Although this might be considered a minor change, enabling zparm NPGTHRSH to a small value (even setting to 1) can result in the optimizer avoiding table space scans on very small or empty objects that might simply have statistics that are not representative and are actually larger than the statistics demonstrate.

It's just one example, but hopefully you get my point. The information in this Redbook is thorough and detailed. Take the time to download and read it.

 

Posted May 22, 2018 | Permalink

comments powered by Disqus