DB2 REORG INDEX utility

What REORG INDEX is for

REORG INDEX reorganizes one index (or a partition of a partitioned index). Typical reasons:

• Leaf pages are poorly ordered (high LEAFDIST in SYSIBM.SYSINDEXPART)
• Many pseudo-deleted entries after heavy DELETE/UPDATE
• You want to move the index to new volumes or apply encryption by allocating new data sets (unless REUSE)
• Pending index definition changes need materialization (SHRLEVEL REFERENCE or CHANGE)
• The index is in REORG-pending and you can use SHRLEVEL NONE to reset that state

If the table is unclustered, REORG INDEX will not put data rows back in clustering order. That is REORG TABLESPACE (with SORTDATA). If the index is missing or in rebuild-pending (RBDP) after a recover or CREATE INDEX DEFER, use REBUILD INDEX, which extracts keys from the table.

Control statement shape

1
2
3
4
5
6
7
8
9
10
//RIX     EXEC PGM=DSNUTILB,PARM='DB2A,RIXEMP'
//STEPLIB  DD DISP=SHR,DSN=DSN.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSIN    DD *
  REORG INDEX HR.IXEMPNO
    SHRLEVEL CHANGE
    STATISTICS
    SORTDEVT SYSDA
/*

You can name INDEX creator.name, INDEXSPACE database.indexspace, PART n, LIST listdef-name, or (with the documented syntax) operate on indexes for a table space. Always include SORTDEVT (or SORTNUM) unless your shop forces UTSORTAL-style dynamic sort allocation globally.

SHRLEVEL

NONE reloads into the original area. Applications can read during unload and have no access during reload. IBM states that NONE is the only access level that resets REORG-pending. With NONE, REUSE logically resets Db2-managed data sets without delete/define; omit REUSE and Db2 deletes and redefines those data sets.

REFERENCE reloads into a shadow. Read-only access during unload and reload, then a brief period of no access at SWITCH.

CHANGE keeps read and write for most of the job. Db2 records changes and applies log records iteratively, then switches. You cannot specify the UNLOAD parameter with CHANGE — unload always continues. REORG INDEX SHRLEVEL CHANGE is not restartable until the SWITCH phase; SHRLEVEL REFERENCE with NOSYSUT1 requires a phase restart from the beginning of UNLOAD if unload/build fails.

Drain options (DRAIN ALL versus DRAIN WRITERS), MAXRO, RETRY, DEADLINE, SWITCHTIME, FORCE READERS/ALL, and LASTLOG YES/NO match the online-REORG family you met on REORG TABLESPACE. Use them when SWITCH cannot break in.

Pending definition changes

REORG INDEX with SHRLEVEL REFERENCE or CHANGE materializes pending alterations on the index and can reset AREOR on that index. Pending changes to the table space or table stay pending — run REORG TABLESPACE for those.

If you REORG INDEX at partition level, pending definition changes are not materialized. SHRLEVEL NONE proceeds without materializing pending index DDL.

When pending changes are materialized, index statistics are updated even if you omit STATISTICS (default STATISTICS UPDATE ALL HISTORY ALL). Explicit STATISTICS keywords override that default.

STATISTICS, LEAFDIST, and CLONE

STATISTICS collects index statistics and can REPORT them to SYSPRINT and/or UPDATE the catalog. Histogram statistics from some index rebuild paths are only rough estimates; RUNSTATS remains the way to get precise histograms.

LEAFDISTLIMIT compared LEAFDIST in SYSINDEXPART and could REORG-or-recommend (REPORTONLY). The option is deprecated; IBM’s alternative is DSNACCOX. Node ID, auxiliary, hash, and XML indexes store LEAFDIST −2, so LEAFDISTLIMIT never selected them.

CLONE reorganizes clone indexes only. LISTDEF CLONED YES is not sufficient.

UNLOAD CONTINUE is normal. UNLOAD PAUSE and UNLOAD ONLY are deprecated; IBM points you at DIAGNOSE plus TERM UTIL if you must stop after keys are unloaded.

FASTSWITCH, FlashCopy, PREFORMAT

• FASTSWITCH YES/NO — SWITCH method; YES not for catalog or directory
• FLASHCOPY YES/CONSISTENT with FCCOPYDDN — inline FlashCopy; if FlashCopy is forced to traditional I/O, the outage can lengthen because copies happen in SWITCH
• PREFORMAT — preformat remaining pages; ignored with UNLOAD ONLY
• PARALLEL — parallel subtasks for eligible work

REORG INDEX versus REORG TABLESPACE versus REBUILD

• REORG TABLESPACE — data pages (and usually indexes as a by-product). Clustering, pending table-space DDL, DISCARD.
• REORG INDEX — this index’s leaf/nonleaf structure only.
• REBUILD INDEX — reconstruct from table rows; the fix when the index does not match the table or does not exist yet.

After a point-in-time recover, CHECK INDEX (next pages) may show mismatches; REBUILD INDEX, not REORG INDEX, is the usual repair.

Explain It Like I'm Five

The table is a bookshelf of stories. The index is a card catalog. REORG TABLESPACE reshelves the books. REORG INDEX only restacks the cards so they sit in tidy drawers again. REBUILD INDEX throws the cards away and writes a new catalog by walking every book. SHRLEVEL NONE means the library closes the catalog room while you restack. CHANGE means people still check out books while you build a second catalog, then you swap drawers in one quick move.

Exercises

1. Query SYSIBM.SYSINDEXPART for LEAFDIST and NLEAF on a busy index. Decide whether REORG INDEX is more appropriate than REORG TABLESPACE.
2. Write REORG INDEX SHRLEVEL NONE REUSE STATISTICS for a nonpartitioned index.
3. Explain why SHRLEVEL CHANGE would not reset REORP, and which SHRLEVEL would.
4. After CREATE INDEX DEFER, which utility do you run — REORG INDEX or REBUILD INDEX?
5. Confirm with operations whether FASTSWITCH YES is allowed on the indexes you care about (not catalog/directory).