Statistics the DB2 optimizer uses

Garbage in, garbage out

If COLCARDF still says -1, Db2 uses a default filter factor (classically 1/25 for equality). If CARDF says 1,000 rows but the table has 80 million, nested loop looks cheap and will not be. If you never collected frequencies for a status column that is 95% 'A', the optimizer treats STATUS = 'A' like STATUS = 'Z'. Statistics are not optional decoration; they are the cost model’s input.

After you collect stats, static SQL does not change path until REBIND. Dynamic SQL uses new stats on the next PREPARE once the statement cache entry is invalidated or missing.

Catalog tables the optimizer reads

Consistency rules matter. CARDF in SYSTABLES must be at least as large as any column or COLGROUP cardinality. FIRSTKEYCARDF of a single-column index should match COLCARDF of that column. LOW2KEY and HIGH2KEY should be filled when COLCARDF is a real (non-default) value. Conflicting numbers generate statistics feedback rows.

Table statistics

Table statistics answer “how big is this thing?” CARDF is row count (floating). NPAGES / NPAGESF estimate data pages. Partitioned tables also have SYSTABSTATS so the optimizer can cost page-range access per partition. PCTROWCOMP reflects compression, which changes how many rows fit on a page and therefore I/O estimates.

1
2
3
4
SELECT NAME, CARDF, NPAGES, STATSTIME
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'HR'
   AND NAME = 'EMPLOYEE';

Column statistics and data distribution

COLCARDF is the number of distinct values. It drives the default equality filter factor 1/COLCARDF. HIGH2KEY and LOW2KEY are the second-highest and second-lowest values, used to interpolate range predicates without being fooled by a single extreme outlier.

Uniform distribution is the assumption when you have only COLCARDF. Real data is rarely uniform. That is why distribution statistics exist.

Frequency statistics

Frequency statistics (SYSCOLDIST TYPE = F) store specific COLVALUE bytes and FREQUENCYF (share of rows, 0.0–1.0). RUNSTATS FREQVAL COUNT n MOST (or LEAST) collects the n most (or least) common values. Indexes automatically contribute leading column frequencies when you RUNSTATS INDEX with FREQVAL. Popular values then get an honest filter factor instead of 1/COLCARDF.

Histogram statistics

Histogram statistics (TYPE = H) split the value range into approximately equal-depth quantiles. You do not pick the bucket boundaries; you pick NUMQUANTILES and Db2 fills QUANTILENO, LOWVALUE, HIGHVALUE, CARDF, and FREQUENCYF per bucket. Histograms help BETWEEN and range predicates when values clump. They go stale quickly on always-increasing keys (invoice numbers, timestamps). IBM warns against one-off histogram collection; put them in the regular RUNSTATS job or they will lie.

RUNSTATS TABLESPACE does not collect histograms for LOB or XML table spaces. RUNSTATS INDEX histograms require prefix columns in the same order (no mixed ASC/DESC key mix).

COLGROUP statistics

COLGROUP collects cardinality and optional FREQVAL/HISTOGRAM on a list of columns that are queried together. That is how you teach Db2 about column correlation: STATE and CITY, DEPTNO and LOCATION, ACCOUNT_TYPE and STATUS. TYPE = C rows hold the number of distinct combinations.

1
2
3
4
5
6
7
8
9
RUNSTATS TABLESPACE HRDB.HRTS
  TABLE (HR.EMPLOYEE)
  COLUMN (WORKDEPT, LASTNAME)
  COLGROUP (WORKDEPT, LOCATION)
    FREQVAL COUNT 10 MOST
    HISTOGRAM NUMQUANTILES 100
  INDEX (ALL)
  SHRLEVEL CHANGE
  REPORT YES;

COLGROUP is powerful and expensive. Over-collecting hundreds of groups inflates catalog size and PREPARE time. Prefer groups the optimizer requested in SYSSTATFEEDBACK or that you know appear in WHERE/JOIN clauses.

Stale SYSCOLDIST is a real operational bug: if you once ran FREQVAL or HISTOGRAM and later run RUNSTATS without those keywords, some old frequency/histogram rows can remain while leading-column frequencies are replaced. Review SYSCOLDIST when plans look “haunted.”

Index statistics and clustering

Index statistics describe the b-tree: NLEAF (leaf pages), NLEVELS, FIRSTKEYCARDF (distinct leading-column values), FULLKEYCARDF (distinct full keys). Matching index cost uses these plus filter factors on matching columns.

Clustering is whether data page order follows index key order. CLUSTERRATIOF (0–1, often shown as a percentage in tools) is the classic measure. Low clustering makes a matching index scan jump randomly between data pages; the optimizer then prefers list prefetch, a different index, or a tablespace scan. DATAREPEATFACTORF is a later clustering-related statistic that estimates how often data pages repeat as you walk the index—useful when rows with the same key cluster in a few pages even if the whole index is not perfectly clustered.

REORG to restore clustering, then RUNSTATS, then REBIND. RUNSTATS alone does not physically recluster rows.

Real-time statistics

Real-time statistics (RTS) live in SYSIBM.SYSTABLESPACESTATS and SYSIBM.SYSINDEXSPACESTATS. Db2 maintains counters as the system runs: inserts, updates, deletes, TOTALROWS, space, unclustered inserts, and more. The DSNACCOX stored procedure uses RTS to recommend REORG or RUNSTATS.

RTS are not a full substitute for optimizer distribution statistics. They do not fill SYSCOLDIST frequencies or histograms. Some optimizer functions consult RTS (and SYSCOLSTATS has a narrow role around parallelism and bounding filter factors), but your access path quality still depends on RUNSTATS (or inline statistics from LOAD/REORG).

Statistics recommendations and profiles

During bind, rebind, prepare, and EXPLAIN, Db2 records statistics recommendations when values are missing or conflict:

• SYSIBM.SYSSTATFEEDBACK — catalog; cleaned when RUNSTATS collects the requested stats
• DSN_STAT_FEEDBACK — EXPLAIN table; not cleaned by RUNSTATS

A statistics profile stores RUNSTATS options per table in SYSIBM.SYSTABLES_PROFILES (PROFILE_TEXT). RUNSTATS USE PROFILE replays them. If STATFDBK_PROFILE is YES, Db2 can create or update profiles from SYSSTATFEEDBACK so autonomic or scripted RUNSTATS keep collecting what the optimizer asked for. Only one profile exists per table—design it carefully.

1
2
3
4
RUNSTATS TABLESPACE HRDB.HRTS
  TABLE (HR.EMPLOYEE)
  USE PROFILE
  SHRLEVEL CHANGE;

RUNSTATS options you will actually use

• TABLESPACE vs INDEX — tablespace form can include tables, columns, and indexes; INDEX form is indexes only
• COLUMN — which columns get COLCARDF / HIGH2KEY / LOW2KEY
• COLGROUP — correlated groups; optional FREQVAL and HISTOGRAM
• FREQVAL COUNT n MOST | LEAST — frequencies (also NUMCOLS on indexes)
• HISTOGRAM NUMQUANTILES n — requires COLGROUP on TABLESPACE form
• SAMPLE / TABLESAMPLE SYSTEM AUTO — reduce CPU on huge tables at the cost of sampling error
• SHRLEVEL CHANGE | REFERENCE — concurrency during collection
• INVALIDATECACHE YES — drop matching dynamic statements so they reprepare with new stats
• SET PROFILE / UPDATE PROFILE / USE PROFILE — profile maintenance

Inline statistics on LOAD and REORG can refresh catalog stats in the same utility that changes the data—useful after a replace load. Clones are not collected by RUNSTATS. Always follow a stats change that should affect static SQL with a planned REBIND (PLANMGMT EXTENDED if you need a fallback).

Explain It Like I'm Five

The optimizer is guessing how many jellybeans are red before opening the jar. Table stats say how many jellybeans exist. Column stats say how many colors. Frequency stats say “almost all are red.” Histograms say “the big scoop on the left is mostly red, the right scoop is mixed.” COLGROUP says “red ones are almost always in the big jar labeled Illinois.” Clustering says whether red ones sit together on the shelf or are scattered. RUNSTATS is counting. If you counted last year, today’s guess is a fib.

Exercises

1. Compare SYSTABLES.CARDF to SELECT COUNT(*) for a small test table. Then check SYSTABLESPACESTATS.TOTALROWS. Explain any differences.
2. Query SYSCOLDIST for a skewed column. Compute 1/COLCARDF versus FREQUENCYF of the top value.
3. Identify two correlated columns in your schema and write a RUNSTATS COLGROUP statement. EXPLAIN a query that filters both columns before and after.
4. Read SYSSTATFEEDBACK for one table and translate each TYPE into RUNSTATS keywords.
5. Check CLUSTERRATIOF for a heavily updated clustering index. Decide whether REORG or RUNSTATS is the next step and why.