Db2 for z/OS and its ecosystem

 View Only

Db2 13 introduces page sampling for inline statistics

By Kate Wheat posted Wed July 27, 2022 09:24 AM


Db2 13 introduces page sampling for inline statistics
by Koshy John and Kate Wheat

When gathering statistics, Db2 can use page sampling or row sampling. Page sampling is typically faster in both elapsed time and CPU time. The RUNSTATS utility has had the ability to do page sampling since version 10. Now, starting in Db2 13, the LOAD and REORG TABLESPACE utilities can also use page sampling when gathering inline statistics. 

By default, page sampling is used for RUNSTATS beginning in Db2 12 FL 505 and for LOAD and REORG TABLESPACE beginning in Db2 13 FL 500. To change this behavior across your subsystem, use the zparm STATPGSAMP. To override the zparm setting for an individual utility job, use the TABLESAMPLE SYSTEM keyword in the utility statement.

Also by default, the utility determines the sampling rate. If you want to override this rate, specify the TABLESAMPLE SYSTEM keyword with a percentage value. For example, TABLESAMPLE SYSTEM 0.01 represents one one-hundredth of a percent, such that 1 row in 10,000 would be sampled, on average. During page sampling, pages are selected according to the sampling rate and all rows within the selected pages are read. Specifying a very low TABLESAMPLE SYSTEM rate might not yield accurate statistics. You can also specify TABLESAMPLE SYSTEM NONE to turn off page sampling for an individual utility job.

In your job output, look for message DSNU1374I to find information about the sample size that was used.

Page sampling is only supported for universal table spaces (UTS). So, the TABLESAMPLE keyword is not valid for segmented (non-UTS), partitioned (non-UTS), or LOB table spaces.

For more information about this zparm and keyword, see the following information in the online product information:
Db2 Utilities in Db2 13
PAGE-LEVEL SAMPLING field (STATPGSAMP subsystem parameter)
Syntax and options of the LOAD control statement
Syntax and options of the REORG TABLESAPCE control statement


Koshy John is an Advisory Software Engineer, Db2 for z/OS Utilities development.

Kate Wheat is an information developer for Db2 for z/OS Utilities.