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

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.


Creating a function in DB2

Started by Jacob Ruchotzke in What's hot ? Jun 18. 0 Replies

With the help of our IT guy i have sort of gotten an example of how to create a function in our system. Can anyone help me with this? Please see the attached SQL file.Thanks in advanceContinue

Tags: function

Conversion of BLOB to String/ Text

Started by Jitesh Audichya in Application Development and DB2. Last reply by Jitesh Audichya Apr 24. 2 Replies

Hi All,Problem Statement:I have a field with BLOB data type in DB2 database, I want to extract this blob and convert it to Text. The text data after the conversion will be in Japanese characters. How can I write a select with the conversion from…Continue

Tags: on, DB2, conversion, text, to

© 2020   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service