DB2 RUNSTATS utility

What RUNSTATS does

RUNSTATS gathers summary information about data in table spaces, indexes, and partitions. Db2 records the results in the catalog and uses them to select access paths during bind (static) and prepare (dynamic). You also query those catalog tables to decide when a REORG is due. Inline STATISTICS on REORG or LOAD can collect many of the same numbers without a separate RUNSTATS; a dedicated RUNSTATS is still the full-control tool.

Phases are UTILINIT, RUNSTATS (scan and catalog update; extra sort subtasks for COLGROUP or certain FREQVAL), and UTILTERM.

RUNSTATS TABLESPACE and TABLE

1
2
3
4
5
6
7
8
9
10
11
12
//RS      EXEC PGM=DSNUTILB,PARM='DB2A,RSEMP'
//STEPLIB  DD DISP=SHR,DSN=DSN.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  RUNSTATS TABLESPACE DBHR.TSEMP
    TABLE(ALL)
    INDEX(ALL)
    SHRLEVEL CHANGE
    REPORT YES
    UPDATE ALL
    HISTORY NONE
/*

TABLE(ALL) or TABLE(creator.name) selects which tables in the space get column statistics. On a UTS there is one table; on a classic multi-table segmented space you can target one table or all.

Add column-level detail when the optimizer needs it:

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

• COLGROUP(col, col, …) — cardinality of that column combination, even if no index matches it. Powerful for multi-column predicates; expensive if you create hundreds of groups (shops often stay well under 100).
• FREQVAL COUNT n MOST|LEAST — the n most (or least) frequent values in SYSCOLDIST.
• HISTOGRAM NUMQUANTILES n — quantile buckets for range predicates.

FREQVAL COUNT 0 is the documented way to delete frequency statistics for that specification. Simply leaving FREQVAL off a later job does not remove old SYSCOLDIST rows.

RUNSTATS INDEX

1
2
3
4
RUNSTATS INDEX(HR.IXEMPNO)
  FREQVAL NUMCOLS 1 COUNT 10
  SHRLEVEL CHANGE
  UPDATE ALL

NUMCOLS is the number of leading concatenated key columns. NUMCOLS 3 COUNT 10 alone does not give you frequencies for 1-col, 2-col, and 3-col prefixes — specify three FREQVAL clauses if you want all three. KEYCARD is deprecated; key cardinality is collected by default.

You can also hang INDEX(ALL) or named indexes off RUNSTATS TABLESPACE so one job collects both table and index stats.

Sampling: SAMPLE versus TABLESAMPLE

• SAMPLE — older row sampling, intended for non-indexed columns. Even SAMPLE 100 is not the same as a full scan in every case.
• TABLESAMPLE SYSTEM AUTO (or a percent) — page sampling on universal table spaces. Recommended for very large UTS; not for LOB spaces. Ignored on multi-table or segmented non-partitioned spaces (those scan all pages).

ZPARM STATPGSAMP can force TABLESAMPLE SYSTEM AUTO for UTS and override SAMPLE. Use sampling to keep RUNSTATS inside the batch window; verify critical access paths after the first sampled run.

SHRLEVEL, UPDATE, REPORT, HISTORY

SHRLEVEL CHANGE allows read/write during the scan (slightly less consistent snapshot). REFERENCE is read-only for the duration.

REPORT YES writes a readable stats report to SYSPRINT even when UPDATE NONE. HISTORY can write a time series into history catalog tables; many shops use HISTORY NONE on daily jobs to avoid growth.

After UPDATE ACCESSPATH, SPACE, or ALL, rebind static packages that need the new numbers. Dynamic SQL depends on cache invalidation (INVALIDATECACHE YES where available).

Statistics profiles

Profiles in SYSIBM.SYSTABLES_PROFILES store the TABLE/INDEX/COLGROUP recipe. SET PROFILE or UPDATE PROFILE from a RUNSTATS statement, then later:

1
2
3
4
5
RUNSTATS TABLESPACE DBHR.TSEMP
  TABLE(HR.EMPLOYEE)
  USE PROFILE
  SHRLEVEL CHANGE
  UPDATE ALL

If no profile exists, USE PROFILE typically falls back to a COLUMN ALL / INDEX ALL style collection. From function level 507, USE PROFILE can also delete catalog statistics that are not in the profile, which is the supported cleanup for stale COLGROUPs. Do not let auto-updated profiles grow without review.

Inline RUNSTATS and what RUNSTATS does not do

REORG TABLESPACE STATISTICS and LOAD STATISTICS collect many of the same columns. They are convenient after a full reload. They still follow the “only replace what you specified” rule. RUNSTATS remains the place for COLGROUP design, profiles, and index-only jobs.

RUNSTATS does not reorganize data, does not fix CHECK-pending, and does not collect clone statistics. Inaccurate stats can even cause sort failures in CHECK INDEX — keep stats current on large objects you check.

Explain It Like I'm Five

The optimizer is a cook who never walks into the pantry. RUNSTATS is the person who counts the apples, notes that most of them are Granny Smith, and writes the numbers on a chalkboard (the catalog). If nobody updates the chalkboard after a huge delivery, the cook still plans a recipe for “twelve apples” and dinner goes wrong. COLGROUP is counting “apples and cinnamon together.” FREQVAL is “the ten most common apple types.” TABLESAMPLE is tasting every tenth crate instead of every apple when the pantry is enormous.

Exercises

1. Run RUNSTATS TABLESPACE … TABLE(ALL) INDEX(ALL) REPORT YES UPDATE NONE on a sandbox and read SYSPRINT.
2. Compare CARD in SYSTABLES and COLCARDF in SYSCOLUMNS before and after a large INSERT plus RUNSTATS.
3. Write FREQVAL NUMCOLS clauses for a three-column index so you collect 1-, 2-, and 3-column frequencies.
4. Explain to a teammate why a second RUNSTATS without COLGROUP did not remove the old SYSCOLDIST rows.
5. Find whether your shop uses USE PROFILE or a generator that always emits COLUMN ALL INDEX ALL.