This Db2 News from the Lab blog entry was originally published on 2019-07-15.

By Kate Wheat and Koshy John.

When the RUNSTATS utility gathers statistics, the utility can either do row sampling after all the rows for the table are read or page sampling wherein all the rows from select pages in the table space are read. Page sampling is typically faster in both elapsed time and CPU time. In Db2 12 function level 500, APAR PH07220 introduces a new subsystem parameter (or zparm) called STATPGSAMP that you can use to make page sampling the default behavior for RUNSTATS.

The new STATPGSAMP zparm applies to universal table spaces only. You can set STATPGSAMP to YES or NO. YES means that page sampling is the default behavior. In other words, RUNSTATS always executes as if the TABLESAMPLE SYSTEM AUTO utility option is specified.

STATPGSAMP also has a third value: SYSTEM. SYSTEM is the default value. SYSTEM means that IBM decides the default sampling method. Prior to function level 505 (FL 505), SYSTEM is the same as NO, meaning that page sampling is not the default behavior. Beginning in FL 505, the meaning of SYSTEM is changed. When FL 505 is activated, SYSTEM is the same as YES. Page sampling is the default behavior for all RUNSTATS jobs. Because page sampling is typically faster, IBM recommends that you keep page sampling turned on by default.

You can override this behavior for individual RUNSTATS jobs by specifying TABLESAMPLE SYSTEM NONE. Then, you can specify SAMPLE to get row sampling or no sample options to have RUNSTATS read every page.

You should be aware that beginning in FL 505, your RUNSTATS jobs will automatically start using page sampling unless you turn it off by either overriding the value on an individual RUNSTATS statement or setting the zparm STATPGSAMP to NO. For more information, see PAGE-LEVEL SAMPLING field (STATPGSAMP subsystem parameter) and RUNSTATS TABLESPACE syntax and options.

Koshy John is the lead developer for RUNSTATS in Db2 Utilities development, and Kate Wheat is the information developer for Db2 Utilities.