Explain DB2 Utilities: REORG, RUNSTATS, LOAD, UNLOAD in IBM Mainframes

37 / 60

DB2intermediate

DB2 Utilities: REORG, RUNSTATS, LOAD, UNLOAD

Understand the core DB2 maintenance utilities and why they must run on a regular operational cadence for healthy performance.

DB2 tables and their indexes degrade in physical organization over time as rows are inserted, updated, and deleted, and a small set of standard utilities exist specifically to address this — knowing what each one does and when to run it is essential day-two-operations knowledge that interviewers expect from anyone claiming production DB2 experience.

REORG is like reshelving a library's books back into proper order after a busy semester of returns being shoved onto whatever shelf had space; RUNSTATS is updating the library's card catalog to reflect how many books are actually on each shelf now; skipping RUNSTATS after a REORG is like reshelving perfectly but never telling the front desk, so they keep sending patrons to the old locations.

Key Concepts

REORG physically reorganizes a table space (and its indexes) to remove fragmentation, reclaim space from deleted rows, and restore clustering order, which directly improves both scan and index-access performance — a heavily updated table left un-reorganized for months is a common, entirely preventable cause of gradually worsening query performance. RUNSTATS collects statistics about table size, data distribution, and index characteristics into the DB2 catalog, which the optimizer relies on entirely to make good access path decisions during BIND/REBIND — stale statistics after significant data growth or change is one of the most common root causes of a previously-fine query suddenly performing badly.

LOAD is the high-speed bulk data loading utility, used to populate a table from an external sequential file far faster than issuing individual INSERT statements, commonly used for initial population or large-scale refreshes; it supports both REPLACE (clearing existing data first) and RESUME (adding to existing data) modes. UNLOAD extracts data from a DB2 table into a sequential file, the natural counterpart to LOAD, often used for creating extract files, archival copies, or moving data between environments.

A sharp interview answer connects these utilities into an operational rhythm rather than describing them in isolation: a heavily updated table typically gets REORG'd on a schedule (or triggered by reaching a fragmentation threshold), followed by RUNSTATS to refresh statistics, followed by a REBIND of affected packages/plans so the optimizer can actually benefit from the fresh statistics — skipping any one of these three steps undermines the value of doing the others.