Use a non-zero NPGTHRSH setting for better access paths when statistics show empty tables or defaults
Paul_McWilliams 110000JT36 Comment (1) Visits (5956)
Db2 12 improves how the Db2 optimizer uses the NPGT
For example, a table might be small or even empty when the statistics were collected. Later, data was inserted and statistics such as the NPAGESF column in the SYSI
NPGTHRSH was introduced to address this problem as a system-wide subsystem parameter in Version 7, and it is a predecessor to the VOLA
Also, NPGTHRSH is not only applicable to the entire table, it also applies at the partition level. That is, if NPGTHRSH is set to a non-zero value and the statistics show that a partition is currently empty, any SQL statement determined to access a single partition can now use NPGTHRSH to preference matching index access rather than a partition level scan.
So, what is the Db2 12 improvement? Before Db2 12, for default NPAGES (-1) values, Db2 assumed that no statistics were collected and used 501 pages for the comparison with the NPGTHRSH value. Db2 12 now actually uses the -1 statistics values for the NPGTHRSH comparison, if NPGTHRSH is set to a non-zero value. Similar to the cited example above where statistics may have been collected on a small or empty object that has since grown, default statistics are often kept on the objects because of the difficulties of collecting accurate statistics at a representative time. The Db2 12 improvement to NPGTHRSH (if set to a non-zero value) now covers both of these scenarios.
However, the current default NPGTHRSH value is 0, which disables this function. Nevertheless, one major ERP vendor has set the NPGTHRSH=10 for all of their customers for a dozen or more years now, with very good results, and no real complaints. A conservative recommendation from Db2 development is to set NPGTHRSH=1 so that matching index access can be chosen when statistics for a table space or partition show that it is empty (in Db2 12 and earlier) or when statistics show the default (Db2 12 only).
Terry Purcell is the lead designer for the DB2 for z/OS Optimizer at IBM's Silicon Valley Lab and is recognized worldwide for his expertise in query optimization and performance with DB2 for z/OS.