Introduction:
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.

Enhancements:
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.

Example:
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

RUNSTATS
SAMPLE 25 is used

RUNSTATS
TABLESAMPLE SYSTEM AUTO is used

COL1

1,000,000

1,000,000

800,711

COL2

367,080

385,024

508,805

COL3

13

13

13



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

Views: 619

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.

Forum

Db2 for z/OS Master class with John Campbell and the SWAT team returns!

Started by Surekha Parekh in What's hot ?. Last reply by carol Goldberg on Thursday. 1 Reply

Db2 for z/OS Master class with John Campbell and the SWAT team returns! June 24-28, 2019 at IBM Hursley near Winchester, UKSeptember 23-27, 2019 at IBM Silicon Valley Lab in San Jose, California…Continue

Tags: Events, MasterClass, JohnCampbell

RBS Shares their Db2 Utilities Experiences

Started by Calene Janacek in What's hot ? Oct 22. 0 Replies

Join Mark Turner, Lead Mainframe Architect and Strategist from RBS and Haakon Roberts, IBM DE as they share Royal Bank of…Continue

Tags: #Db2

© 2019   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service