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

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

MSSQL linked server attaching to DB2 LUW

Started by Mike Jett in Application Development and DB2. Last reply by Mike Jett 12 hours ago. 2 Replies

I am trying to create a linked server from MSSQL 2014  to DB2 11.1  I am able to create one linked server and get it to work with one DB.  I have not been able to figure out how to add additional catalogs(DB's).  Is it possible to use just one…Continue

Tags: server, linked

Db2 12 Migration Planning & Customer Experiences - Part 1

Started by Sandy Flippen in What's hot ? Sep 12. 0 Replies

Register Now! NEXT CONTENT! Upcoming double webcast Db2 12 Migration Planning and customer experiences. Live Q &A session! PART 1 http://ibm.biz/BdzKxv 24th Sept 11:00 AM EST PART 2…Continue

PlanetDB2.com [Latest Blogs from the Biggest Names in DB2]

© 2019   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service