This Db2 for z/OS News from the Lab blog entry was originally published on 2018-04-19.

By Terry Purcell and Paul McWilliams.

Db2 12 improves how the Db2 optimizer uses the NPGTHRSH subsystem parameter for default statistics. However, we've found that very few clients are even familiar with the NPGTHRSH subsystem parameter, and the current default value disables its function. Many clients are familiar with the VOLATILE table attribute, which was introduced in Version 8 to address the same problem: Db2 often scans a table space, even though index access is possible.

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 SYSIBM.SYSTABLES catalog table no longer represent the actual volume of data in the table. The result is that the inaccurate statistics indicate a low cost for the table space scan, when in reality it is likely to be the more costly option.

NPGTHRSH was introduced to address this problem as a system-wide subsystem parameter in Version 7, and it is a predecessor to the VOLATILE table attribute. When NPGTHRSH is set to a non-zero value, Db2 prioritizes matching index access over a table space scan, if the statistics in the Db2 catalog indicate fewer data pages in the table than the NPGTHRSH value. For example, if you set NPGTHRSH=10, and optimizer encounters a table where the catalog indicates any value of 9 or less for NPAGES, the Db2 optimizer prefers a matching index access, using the index that has the most matching columns, regardless of what the cost estimate says.

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. Paul McWilliams is a technical writer for Db2 for z/OS.