RUNSTATS in DB2 for z/OS

What RUNSTATS does

Every bind and dynamic prepare is a guessing game unless the catalog holds good statistics. RUNSTATS samples or reads objects and stores cardinalities, null counts, frequency values, histograms, and index metrics. Without that information—or with numbers from last year—Db2 may pick a tablespace scan when an index would win, or the reverse.

RUNSTATS does not reorganize pages. If rows are physically messy, run REORG (often with inline STATISTICS). If rows are fine but the optimizer is blind, run RUNSTATS. After a bulk LOAD, both concerns often appear together.

Prerequisites

• RUNSTATS privilege (or suitable administrative authority) on the objects
• Correct database and table space (or index) names
• A stats profile or standard options your shop expects (TABLE/INDEX, KEYCARD, HISTOGRAM, SAMPLE)
• Awareness of whether inline stats already ran on a preceding LOAD/REORG
• Plan to rebind sensitive static packages if your process requires it after major stats changes

Steps

1. Decide the scope

Whole table space, specific partitions, specific tables, or indexes only—match the scope to what changed. After loading one partition, partition-level RUNSTATS may be enough.

2. Write the control statement

1
2
3
4
5
6
RUNSTATS TABLESPACE TRAINING.EMPTS
        TABLE(ALL)
        INDEX(ALL)
        KEYCARD
        HISTOGRAM NUMQUANTILES 100
        SHRLEVEL CHANGE

A lighter teaching example:

1
2
3
RUNSTATS TABLESPACE TRAINING.EMPTS
        TABLE(TRAINING.EMPLOYEE)
        INDEX(ALL)

Option meanings beginners should know:

• TABLE(ALL) — statistics for each table in the table space
• INDEX(ALL) — statistics for indexes on those tables
• KEYCARD — collect distinct counts for key columns (richer than basic stats alone)
• HISTOGRAM — distribution buckets for skewed columns
• SHRLEVEL CHANGE / REFERENCE — concurrency while collecting

3. Build simple utility JCL

Compared with REORG, RUNSTATS JCL is often smaller—no sort of reloaded rows—though large shops still wrap it in standard PROCs.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
//RUNSTEMP JOB (ACCT),'DB2 RUNSTATS',CLASS=A,MSGCLASS=X,
//             NOTIFY=&SYSUID
//JOBLIB   DD DISP=SHR,DSN=DSN.V13R1M0.SDSNEXIT
//         DD DISP=SHR,DSN=DSN.V13R1M0.SDSNLOAD
//RUNSTATS EXEC PGM=DSNUTILB,REGION=0M,
//             PARM='DB2T,RUNSTEMP'
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
  RUNSTATS TABLESPACE TRAINING.EMPTS
          TABLE(ALL)
          INDEX(ALL)
          KEYCARD
          SHRLEVEL CHANGE
/*

4. Submit and watch SYSPRINT

Confirm the utility processed the intended objects and completed with an acceptable return code. Save SYSPRINT in change records when the run supports a production promotion.

5. Rebind when your standards require it

Dynamic SQL picks up new stats on new prepares (subject to caching). Static packages may keep old paths until REBIND. Many sites rebind critical plans after significant RUNSTATS on large tables.

Verify results

• Utility return code
• STATSTIME on SYSTABLESPACE / SYSINDEXES (and related catalog tables) refreshed to the run time
• Cardinality fields such as CARDF look plausible versus SELECT COUNT(*)
• Frequency or histogram catalog tables populated when those options were requested
• Explain a problem query before and after to confirm the optimizer changed (or wisely did not)

1
2
3
4
5
6
7
8
9
10
11
12
SELECT NAME, NTABLES, STATSTIME
  FROM SYSIBM.SYSTABLESPACE
 WHERE DBNAME = 'TRAINING'
   AND NAME = 'EMPTS';
 
SELECT NAME, FIRSTKEYCARDF, FULLKEYCARDF, STATSTIME
  FROM SYSIBM.SYSINDEXES
 WHERE TBCREATOR = 'TRAINING'
   AND TBNAME = 'EMPLOYEE';
 
SELECT COUNT(*) AS LIVE_ROWS
  FROM TRAINING.EMPLOYEE;

Common errors

Authority failures

Missing RUNSTATS privilege yields utility authorization errors. Request the privilege or run under an ID your security model allows for DDL/utility work.

Wrong object name

Database/table space typos are common when copying JCL. Prefetch names from the catalog.

Stats collected on empty or tiny test slices

Running RUNSTATS against a 100-row subset then promoting volumes to millions leaves misleading stats if you do not collect again at production scale.

Skipping RUNSTATS after LOAD REPLACE

Row counts and distributions changed completely, but the optimizer still believes old numbers—or sees incomplete inline stats. Always confirm STATSTIME after bulk loads.

Utility incompatibility

Some utilities conflict on the same target. DISPLAY UTILITY and serialize maintenance.

Assuming RUNSTATS fixes physical disorganization

Bad cluster ratios need REORG. RUNSTATS alone cannot put rows back in order.

Good operational habits

• Collect after significant INSERT/UPDATE/DELETE volume or partition rotates
• Prefer inline STATISTICS on REORG/LOAD when it matches your options
• Use richer options (KEYCARD/HISTOGRAM) on skewed columns that drive predicates
• Document which packages need REBIND after stats refresh
• Do not confuse RTS (real-time statistics) with full RUNSTATS catalog depth

Explain It Like I'm Five

Db2 is a librarian who decides which shelf to check first. RUNSTATS is counting how many books are red, how many are blue, and which shelves are crowded so the librarian can guess faster. If you dump in a thousand new books and forget to recount, the librarian still thinks the shelves look like yesterday and may walk to the wrong place. REORG is tidying the shelves; RUNSTATS is updating the inventory card.

Exercises

1. Run RUNSTATS TABLESPACE ... TABLE(ALL) INDEX(ALL) on a training object and record STATSTIME before and after.
2. Compare CARDF-related catalog values to SELECT COUNT(*).
3. LOAD a noticeably different row count, skip RUNSTATS, EXPLAIN a query, then RUNSTATS and EXPLAIN again. Note any access path change.
4. Add KEYCARD and HISTOGRAM on a skewed column and identify which catalog tables your shop queries for those stats.
5. Write a three-line checklist: when to RUNSTATS, when to REORG, when to do both.