One of the major challenges for RUNSTATS is the sampling of the data rows e.g. for calculation of the COLCARD value in SYSIBM.SYSCOLSTATS and SYSIBM.SYSCOLUMNS.

The challenge is to balance the CPU resources used by RUNSTATS to calculate column cardinalities with high accuracy versus the likelihood that optimizer selects a bad access path due to the possibly inaccurate statistics values.

RUNSTATS allows a SAMPLE keyword to control the sampling on row level since Db2 V3.
In DB2 10 for z/OS a new keyword clause TABLESAMPLE SYSTEM was introduced which allows to control the sampling on page level. The values for TABLESAMPLE SYSTEM could be AUTO or a user specified numeric value.
The advantage of page level sampling is the much better performance of RUNSTATS.

With PH07220 a new value for TABLESAMPLE SYSTEM can be specified: NONE, which allows only in this case the SAMPLE keyword specification in parallel.
In addition, PH07220 introduces a DSNZPARM keyword STATPGSAMP which allows a subsystem-wide default control of the sampling:

  1. If STATPGSAMP=YES: SAMPLE specification is ignored and TABLESAMPLE SYSTEM AUTO is used if not otherwise specified, e.g. TABLESAMPLE SYSTEM nnn.
  2. If STATPGSAMP=NO: RUNSTATS will not use any default like TABLESAMPLE SYSTEM AUTO.
  3. If STATPGSAMP=SYSTEM (the default):
    • Before function level V12R1M505 this is equal to the setting of NO.
    • Since function level V12R1M505 and later this is equal to the setting of YES.

The override is only done if the table space is a UTS and the TABLE keyword is specified, which is required per syntax for the sample clause whatever flavor.

Refer also to this developer works article.

A table with 3 columns has 1 million rows. No index.
RUNSTATS calculates the following values for COLCARD in SYSIBM.SYSCOLSTATS as illustrated in the table:

Counted by SQL:
number of distinct column values

SAMPLE 25 is used














Consider that the "automatic page sampling" enabled by V12R1M505 and STATPGSAMP=SYSTEM (if not otherwise specified) is indicated by message DSNU1374I.

Views: 671

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Comment by Surekha Parekh on July 19, 2019 at 16:47

Thank you Peter for sharing your knowledge and experiences 

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.


Db2 LUW 11.1 - GET_LINE procedure - NO_DATA_FOUND exception SQLSTATE

Started by Gagan Kakkar in Application Development and DB2. Last reply by Douglas J. Partch, Jr. Jan 6. 1 Reply

Hi there,I am testing this stored procedure using Db2 11.1 on Windows 10 64-bit.Informational tokens are "DB2 v11.1.4050.859", "s1911120100","DYN1911120100WIN64", and Fix Pack "5".For NO_DATA_FOUND exception I am getting SQLSTATE = 'ORANF' but in…Continue

Tags: modules, Built-in, LUW, Db2

Db2 Analytics Accelerator Version 7.5 is now Generally Available!

Started by Patricia Zakhar in What's hot ? Dec 9, 2019. 0 Replies

Now Generally AvailableIBM Db2 Analytics Accelerator Version 7.5! The latest version of the Db2 Analytics Accelerator, which delivers the highly anticipated Integrated Synchronization capability, is now generally available. The Db2 Analytics…Continue

© 2020   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service