TopicTrick Logo
Topictrick
HomeCoursesYouTubeBlogUtilitiesJuniorShopPricing
Loading...
MainframeDatabaseDB2 Complete Reference

DB2 RUNSTATS and REORG: Complete Maintenance Guide with JCL Examples

TT
TopicTrick Team
•
DB2 RUNSTATS and REORG: Complete Maintenance Guide with JCL Examples

DB2 RUNSTATS and REORG: Complete Maintenance Guide

Database maintenance is not optional — it is the difference between a DB2 system that stays fast over years of operation and one that slowly degrades until queries that once ran in seconds start taking minutes. Two utilities drive the core of DB2 maintenance: RUNSTATS (statistics collection) and REORG (physical reorganisation). This guide covers both in depth for DB2 LUW and DB2 for z/OS.

Why Statistics and Reorganisation Matter

The Role of Statistics

The DB2 optimizer chooses query access paths based on statistics stored in the catalog — information about table cardinalities, column value distributions, index clustering ratios, and page counts. When data changes significantly after the last RUNSTATS run, these statistics become outdated. The optimizer may then choose a suboptimal access path — for example, opting for a tablespace scan when a highly selective index exists, because outdated statistics suggest the index is less selective than it actually is.

The Role of Reorganisation

As rows are inserted, updated, and deleted, the physical layout of data in a tablespace degrades:

  • Free space fragments: Deleted rows leave gaps scattered throughout pages
  • Page splits: Index leaf pages split, creating disorganised trees
  • Clustering degrades: New rows for a clustering index key range are inserted wherever space is found rather than in order

REORG physically rebuilds the tablespace and indexes, restoring optimal layout.

RUNSTATS — DB2 LUW

Basic RUNSTATS Command

bash
# Connect to the database
db2 CONNECT TO mydb

# Run statistics on a specific table
db2 RUNSTATS ON TABLE empschm.employee WITH DISTRIBUTION AND DETAILED INDEXES ALL

# Run on all tables in a schema
db2 "SELECT 'RUNSTATS ON TABLE ' || RTRIM(TABSCHEMA) || '.' || TABNAME
     || ' WITH DISTRIBUTION AND DETAILED INDEXES ALL;'
     FROM SYSCAT.TABLES WHERE TABSCHEMA = 'EMPSCHM' AND TYPE = 'T'" > runstats.sql
db2 -tf runstats.sql

RUNSTATS Options

bash
# Basic table statistics only
db2 RUNSTATS ON TABLE empschm.employee

# With column distribution statistics (needed for range predicates)
db2 RUNSTATS ON TABLE empschm.employee WITH DISTRIBUTION

# With index statistics
db2 RUNSTATS ON TABLE empschm.employee AND INDEXES ALL

# Full statistics — recommended
db2 RUNSTATS ON TABLE empschm.employee WITH DISTRIBUTION AND DETAILED INDEXES ALL

# On specific columns and indexes
db2 "RUNSTATS ON TABLE empschm.employee ON COLUMNS(workdept, salary, hiredate)
     AND DETAILED INDEXES ALL"

Automatic Statistics Collection (DB2 LUW)

DB2 LUW can collect statistics automatically via the Auto Stats Maintenance feature:

bash
# Enable auto stats
db2 UPDATE DB CFG FOR mydb USING AUTO_MAINT ON AUTO_TBL_MAINT ON AUTO_RUNSTATS ON

Auto RUNSTATS analyses statistics staleness and triggers collection when needed, based on the percentage of rows changed since the last collection.

RUNSTATS — DB2 for z/OS (JCL)

Comprehensive RUNSTATS JCL

jcl
//RUNSTATS EXEC PGM=DSNUTILB,PARM='DSN1,RUNSTATS'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD *
  RUNSTATS TABLESPACE EMPDB.EMPTS01
    TABLE(ALL)
    INDEX(ALL KEYCARD)
    SHRLEVEL CHANGE
    UPDATE ALL
    REPORT YES
/*

Key options:

  • TABLE(ALL) — collect statistics on all tables in the tablespace
  • INDEX(ALL KEYCARD) — collect key cardinality statistics on all indexes
  • SHRLEVEL CHANGE — run online (no outage)
  • UPDATE ALL — update both table and index statistics
  • REPORT YES — generate a statistics report in SYSPRINT

RUNSTATS with Column Groups

For predicates on multiple columns:

jcl
//SYSIN DD *
  RUNSTATS TABLESPACE EMPDB.EMPTS01
    TABLE(EMPSCHM.EMPLOYEE
        COLUMN(WORKDEPT, SALARY, HIREDATE, JOB)
        COLGROUP(WORKDEPT, JOB)
        COLGROUP(WORKDEPT, HIREDATE)
    )
    INDEX(ALL KEYCARD)
    SHRLEVEL CHANGE
/*

Column group statistics help the optimizer estimate selectivity for multi-column predicates more accurately.

REORGCHK — DB2 LUW

Before running REORG, use REORGCHK to determine whether it is actually needed:

bash
# Check with current statistics
db2 REORGCHK CURRENT STATISTICS ON TABLE empschm.employee

# Collect fresh statistics first, then check
db2 REORGCHK UPDATE STATISTICS ON TABLE empschm.employee

# Check all tables in a schema
db2 REORGCHK UPDATE STATISTICS ON SCHEMA empschm

Sample REORGCHK output:

text
Table: EMPSCHM.EMPLOYEE
  CARD  OV  NP  FP  ACTBLK  TBL  F1  F2  F3
 12345   0   8   2     105    N    -   -   *

* indicates REORG is recommended for formula F3

The asterisk next to F3 means the table has excessive overflow rows and needs REORG.

REORG — DB2 LUW

Online REORG (Recommended)

bash
# Online REORG — table stays available
db2 REORG TABLE empschm.employee INPLACE

# With index rebuild
db2 REORG TABLE empschm.employee INPLACE ALLOW WRITE ACCESS

# Offline REORG (faster, but table unavailable)
db2 REORG TABLE empschm.employee

# REORG a specific index
db2 REORG INDEXES ALL FOR TABLE empschm.employee

Checking REORG Status

bash
# Monitor REORG progress
db2pd -reorg -db mydb

REORG — DB2 for z/OS (JCL)

Online REORG with SHRLEVEL CHANGE

jcl
//REORG    EXEC PGM=DSNUTILB,PARM='DSN1,REORG'
//STEPLIB  DD DSN=DSN.SDSNEXIT,DISP=SHR
//         DD DSN=DSN.SDSNLOAD,DISP=SHR
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(500,200),RLSE)
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(500,200),RLSE)
//SYSIN    DD *
  REORG TABLESPACE EMPDB.EMPTS01
    SHRLEVEL CHANGE
    DEADLINE NONE
    SORTDEVT SYSDA
    SORTNUM 6
    LOG YES
/*

REORG with Space Parameters

jcl
//SYSIN DD *
  REORG TABLESPACE EMPDB.EMPTS01
    SHRLEVEL CHANGE
    FREEPAGE 5
    PCTFREE 10
    DEADLINE NONE
/*

FREEPAGE 5 leaves every 5th page empty for future inserts. PCTFREE 10 leaves 10% free space on each page.

REORG Index

jcl
//SYSIN DD *
  REORG INDEX EMPSCHM.EMP_DEPT_IDX
    SHRLEVEL CHANGE
/*

Recommended Maintenance Workflow

The optimal maintenance sequence for DB2 is:

text
1. COPY (image copy — backup before maintenance)
2. REORG (reorganise if needed based on REORGCHK)
3. RUNSTATS (collect fresh statistics on reorganised data)
4. REBIND (apply updated statistics to package access plans)
5. COPY (post-maintenance image copy)

Always take an image copy before REORG. If REORG fails partway through, recovery requires the pre-REORG image copy.

Maintenance JCL Procedure (Combined)

jcl
//MAINT    PROC DB2SSID=DSN1,DBNAME=EMPDB,TSNAME=EMPTS01
//COPY1    EXEC PGM=DSNUTILB,PARM='&DB2SSID,COPY'
//SYSIN    DD *
  COPY TABLESPACE &DBNAME..&TSNAME FULL YES SHRLEVEL CHANGE
/*
//REORG1   EXEC PGM=DSNUTILB,PARM='&DB2SSID,REORG'
//SYSIN    DD *
  REORG TABLESPACE &DBNAME..&TSNAME SHRLEVEL CHANGE
/*
//STATS1   EXEC PGM=DSNUTILB,PARM='&DB2SSID,RUNSTATS'
//SYSIN    DD *
  RUNSTATS TABLESPACE &DBNAME..&TSNAME TABLE(ALL) INDEX(ALL KEYCARD)
    SHRLEVEL CHANGE UPDATE ALL
/*
//         PEND

Automating Maintenance

On DB2 LUW, wrap maintenance in a shell script scheduled with cron:

bash
#\!/bin/bash
# Weekly DB2 maintenance script
DB=mydb

db2 CONNECT TO $DB

# Collect stats and check if REORG needed
for table in $(db2 "SELECT RTRIM(TABSCHEMA)||'.'||TABNAME FROM SYSCAT.TABLES WHERE TABSCHEMA='EMPSCHM' AND TYPE='T'" | grep EMPSCHM); do
    echo "Processing $table"
    db2 "RUNSTATS ON TABLE $table WITH DISTRIBUTION AND DETAILED INDEXES ALL"
    db2 "REORG TABLE $table INPLACE"
done

db2 CONNECT RESET

Next Steps

With statistics and reorganisation under control, your DB2 environment will maintain peak performance over time. The next topic covers protecting your data with backup and recovery strategies. Read the DB2 Backup and Recovery guide. For the full DB2 learning path, visit the DB2 Mastery course hub.