DB2 statistics and RUNSTATS concepts

Two kinds of “statistics”

Beginners mix up two catalogs of numbers:

• Optimizer / catalog statistics — distribution of the data. Collected by RUNSTATS or inline stats. Used at BIND and PREPARE to estimate filter factors and pick indexes versus scans.
• Real-time statistics (RTS) — activity since the object was created or last REORG/LOAD REPLACE/REBUILD. Stored in SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS. Used to decide when to REORG or COPY, and by autonomic utilities. Not a substitute for RUNSTATS on access paths.

If TOTALROWS in RTS says the table grew 40 percent but CARDF in SYSTABLES is still last year’s number, the optimizer is planning with a lie. Collect RUNSTATS after significant data change; use RTS to know when that change happened.

What RUNSTATS collects

RUNSTATS TABLESPACE gathers table-space statistics and, optionally, table, column, and index stats. RUNSTATS INDEX gathers index stats only. RUNSTATS does not collect statistics for clone tables. The utility writes to the catalog; it does not change your data pages.

1
2
3
4
5
6
7
RUNSTATS TABLESPACE DBHR.TSHEMP
  TABLE(HR.EMPLOYEE)
  COLUMN(WORKDEPT, LASTNAME)
  COLGROUP(WORKDEPT, JOB) FREQVAL COUNT 10 MOST
  COLGROUP(SALARY) HISTOGRAM NUMQUANTILES 100
  INDEX(ALL)
  SHRLEVEL CHANGE

Table and table-space statistics

SYSIBM.SYSTABLES holds CARDF (row count as a float) and NPAGESF (pages that contain rows). SYSIBM.SYSTABLESPACE holds space-level numbers such as NACTIVEF (active pages). The optimizer uses table cardinality as the starting population before it applies predicates. If CARDF is 1,000 and the table has 10 million rows, every join estimate is off by four orders of magnitude.

Column statistics

For a single column, RUNSTATS stores:

• COLCARDF — number of distinct values (cardinality)
• HIGH2KEY / LOW2KEY — second-highest and second-lowest values (so a single outlier does not own the whole range)

Those live in SYSIBM.SYSCOLUMNS (table level) and SYSIBM.SYSCOLSTATS (partition level when you collect by partition). A predicate COL = literal is estimated as 1/COLCARDF if there are no frequencies. That is fine for EMPNO (unique). It is terrible for STATUS with values Y and N if 99 percent of rows are Y.

Distribution statistics: frequency, COLGROUP, histogram

Distribution statistics live mainly in SYSIBM.SYSCOLDIST.

Frequency statistics

Frequency is the share of rows that hold a given value (or a concatenated COLGROUP value). FREQUENCYF is a floating value between 0 and 1. You ask for them with FREQVAL COUNT n MOST (or LEAST or BOTH). Collect frequencies on low-cardinality columns used as COL op literal, and on columns with a skewed default.

COLGROUP statistics

COLGROUP(col1, col2, ...) tells RUNSTATS to treat those columns as a set. You get multi-column cardinality (TYPE C) and optionally frequencies. Without COLGROUP, Db2 multiplies single-column filter factors as if CITY and STATE were independent — they are not. Join columns are the other classic COLGROUP target.

Histogram statistics and quantiles

Frequencies describe a handful of popular values. A range predicate such as SALARY BETWEEN 40000 AND 55000 needs the shape of the whole distribution. HISTOGRAM NUMQUANTILES n (with COLGROUP) asks Db2 to split the sorted values into equal-depth buckets: each quantile holds about the same percentage of rows. You do not pick the bucket edges; QUANTILENO, LOWVALUE, and HIGHVALUE describe each interval. CARDF in that row is distinct values inside the quantile. Histograms are not collected for LOB or XML table spaces; on indexes they apply to prefix columns with the same order.

Index and clustering statistics

SYSIBM.SYSINDEXES (and partition-level SYSINDEXSTATS) hold:

• FIRSTKEYCARDF / FULLKEYCARDF — distinct leading-column and full-key values
• NLEAF / NLEVELS — leaf pages and tree height (I/O for matching index access)
• CLUSTERRATIOF — how well table row order matches the index. A clustering index with a poor ratio makes list prefetch or a tablespace scan look cheaper than “matching index + random getpages”

KEYCARD as a RUNSTATS keyword is deprecated; key cardinality collection is default for RUNSTATS INDEX. You still care about the catalog columns.

Statistics profiles, inline statistics, and cleanup

A statistics profile is one row in SYSIBM.SYSTABLES_PROFILES per table. PROFILE_TEXT stores the RUNSTATS options. Autonomic statistics and USE PROFILE on RUNSTATS, LOAD, or REORG TABLESPACE reuse that list. If no profile exists, defaults such as COLUMN ALL INDEX ALL apply depending on whether you named a table.

Inline statistics are the same catalog numbers collected during LOAD or REORG while the utility already reads every page. They save a second scan. They still need a later REBIND of static packages if you want new access paths.

RUNSTATS replaces only what you specify. Old HISTOGRAM or COLGROUP rows with an older STATSTIME stay in SYSCOLDIST and can confuse the optimizer. USE PROFILE (function level 507 and related) collects what is in the profile and deletes statistics that are not. That is the supported cleanup path without racing dynamic SQL.

Statistics feedback and SYSSTATFEEDBACK

When the optimizer notices that a query might have needed better stats (missing COLGROUP, missing frequencies), it can write a recommendation to SYSIBM.SYSSTATFEEDBACK. If STATFDBK_PROFILE is YES, Db2 can create or modify the table’s statistics profile from that feedback. You still run RUNSTATS (or autonomic RUNSTATS) to collect the recommended stats. Feedback is a to-do list, not the statistics themselves.

1
2
3
SELECT TBOWNER, TBNAME, COLNAME, TYPE, REASON
  FROM SYSIBM.SYSSTATFEEDBACK
 FETCH FIRST 50 ROWS ONLY;

After you collect

Static SQL keeps the access path from the last BIND until you REBIND. Dynamic SQL can pick up new stats at the next prepare (subject to the dynamic statement cache). Collecting stats and never rebinding is a common “RUNSTATS did nothing” ticket.

Explain It Like I'm Five

The optimizer is a cook who never looks in the fridge. Instead it reads a shopping list (catalog statistics) that says “we have about 1,000 apples, most are red, a few are green.” RUNSTATS is the person who counts the fruit and rewrites the list. Real-time statistics are a sticky note on the door: “we put 200 more apples in since the last tidy-up” — useful for deciding when to clean the fridge (REORG), not for writing the recipe. Frequencies say “almost every apple is red.” Histograms slice the fruit bowl into equal piles so “apples between this size and that size” is not a wild guess. SYSSTATFEEDBACK is the cook scribbling “please count the pears next time.”

Exercises

1. Query SYSTABLES.CARDF and SYSTABLESPACESTATS.TOTALROWS for one table and explain a large gap.
2. For a STATUS column with two values, explain why COLCARDF alone mis-estimates WHERE STATUS = 'Y'.
3. Write a RUNSTATS COLGROUP for (CITY, POSTCODE) with FREQVAL COUNT 10 MOST and say which SYSCOLDIST TYPE rows you expect.
4. Explain equal-depth histograms versus “top 10 frequencies” for a BETWEEN predicate.
5. Describe how USE PROFILE can remove a leftover HISTOGRAM that a later RUNSTATS no longer requested.