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.

Tables degrade physically as rows are inserted, updated and deleted. These utilities are how that is repaired, and they are run together for a reason.

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 restores physical order. Over time, updated rows no longer fit where they were and get relocated, deleted rows leave gaps, and the clustering order the table was designed around decays.

    before REORG:  rows scattered, pointers to relocated rows,
                   free space exhausted
    after REORG:   rows back in clustering order, gaps reclaimed

The effect is direct: a scan reads fewer pages, and an index lookup no longer follows a pointer to somewhere else. A heavily updated table left unreorganised for months is a common and entirely preventable cause of performance getting gradually worse.

RUNSTATS refreshes what the optimiser knows. Table size, column distribution, index characteristics — all written into the catalog.

The optimiser has no other source. If the catalog says ten thousand rows and the table holds ten million, it chooses a plan that suited ten thousand.

REBIND is what makes the statistics count. Access paths are frozen at bind time, so fresh statistics change nothing until the packages are bound again.

Which is why the three run in sequence.

    REORG     ->  fix the physical layout
    RUNSTATS  ->  measure it
    REBIND    ->  let the optimiser choose with the new numbers

Skip the REORG and you gather statistics about a fragmented table. Skip the RUNSTATS and the rebind uses old numbers. Skip the REBIND and the first two changed nothing the optimiser can act on.

LOAD puts bulk data in fast. Far quicker than issuing individual inserts, because it bypasses much of the per-row processing:

    LOAD REPLACE   clear the table first
    LOAD RESUME    add to what is there

UNLOAD is the counterpart, extracting to a sequential file — for archives, for moving data between environments, or to be reloaded after a structural change.

One practical note on LOAD. It can leave a table space in a restricted state — for example CHECK PENDING when constraints were not validated during the load. The table is there and cannot be used until a CHECK DATA runs. A load that "worked" and a table nobody can query is usually this.

CHECK PENDINGCHECK DATA

What the interviewer is probing.1. "What does REORG actually fix?" Probing: the purpose. Stalls: "Performance." Moves up: physical disorder — rows out of clustering sequence and space from deletes, which make scans read more pages than necessary.

2. "When do you run RUNSTATS?" Probing: the trigger. Stalls: "Weekly." Moves up: after significant data change and before rebinding, because the optimiser chooses from those statistics.

3. "What is the difference between LOAD RESUME and LOAD REPLACE?" Probing: the detail. Stalls: "Nothing." Moves up: RESUME appends to existing data; REPLACE empties the table space first — confusing them is how a production table gets wiped.

4. "What does a utility leaving a table space in COPY PENDING mean?" Probing: practical knowledge. Stalls: "An error." Moves up: DB2 will not allow updates until an image copy is taken, because recoverability was broken by a non-logged operation.