RUNSTATS

RUNSTATS collects statistics about tables, indexes and columns and stores them in the DB2 catalog. The DB2 optimizer reads these statistics to choose the cheapest access path for every SQL statement. Without fresh statistics, the optimizer guesses - and guesses are often slow.

What is RUNSTATS

• RUNSTATS scans a table space or index and writes statistics into catalog tables like SYSTABLES and SYSINDEXES.
• It records row counts, page counts, index leaf pages, clustering ratio and column value distribution.
• You can collect statistics for tables (TABLE), indexes (INDEX), or both in one run.
• UPDATE ALL writes the statistics to the catalog; without it, RUNSTATS only produces a report.
• RUNSTATS itself does not change any access path - the new statistics take effect when packages are bound or rebound.

Why RUNSTATS matters

• Stale statistics make the optimizer pick the wrong access path, turning a fast query into a slow one.
• After a LOAD, mass INSERT, DELETE or REORG, the old statistics no longer describe the data.
• Fresh column statistics help the optimizer estimate filter selectivity correctly.
• Many production slowdowns are fixed by simply running RUNSTATS and rebinding the plan.

When to run RUNSTATS

• After any LOAD, REORG, or large batch update that changed the data distribution.
• On a schedule for volatile tables - weekly or monthly depending on change rate.
• When EXPLAIN shows the optimizer expecting far fewer or far more rows than reality.
• Inline during LOAD or REORG with STATISTICS TABLE(ALL) INDEX(ALL) to avoid a separate job.

Example of a RUNSTATS control statement:-