REBUILD INDEX utility in DB2 for z/OS

What REBUILD INDEX does

It reads the referenced table, unloads keys, sorts them, and writes a new index. Unless you specify REUSE, Db2-managed data sets are deleted and redefined (useful with DFSMS encryption). You can create a FlashCopy image copy of the rebuilt indexes and collect STATISTICS inline so a separate RUNSTATS is unnecessary.

How you name what to rebuild

• INDEX(creator.index) — one index; omit creator and Db2 uses the utility job user ID. Quote names that contain blanks. Several names, comma-separated, must all live in the same table space
• INDEXSPACE(db.indexspace) — name from SYSIBM.SYSINDEXES
• INDEX(ALL) TABLESPACE db.ts — all indexes on the base table in that space (not clone indexes unless CLONE)
• LIST listdef — one LIST per statement; all index spaces or all table spaces. Table space lists run once per table space; index lists are grouped by table space
• PART integer — physical partition of a partitioning index or DPSI (1–4096). For an NPSI, PART rebuilds a logical partition. You cannot combine PART with LIST (use LISTDEF PARTLEVEL for partitioning/DPSI; PARTLEVEL is ignored for NPSIs and rebuilds the whole index)
• CLONE — clone-table indexes only; cannot combine with STATISTICS

1
2
3
4
5
6
7
8
9
10
11
REBUILD INDEX(PAYROLL.IXEMPNO)
  SORTDEVT SYSDA
  STATISTICS REPORT YES UPDATE ALL;
 
REBUILD INDEX(ALL) TABLESPACE PAYDB.PAYTS
  SCOPE PENDING
  SORTDEVT SYSDA;
 
REBUILD INDEX(PAYROLL.IXNPSI) PART 4
  SHRLEVEL REFERENCE
  SORTDEVT SYSDA;

Partitioned versus nonpartitioned indexes

A partitioning index or data-partitioned secondary index (DPSI) has a physical data set per partition. REBUILD PART n rebuilds that physical piece.

A nonpartitioned secondary index (NPSI) is one data set covering all partitions. PART n rebuilds the logical partition (keys for that table partition) except when:

• The index was created with DEFER YES
• The whole index must be rebuilt (typical disaster-recovery rebuild)
• The index is in page set REBUILD-pending (PSRBD)

In those cases rebuild the entire NPSI. Prefer one job that lists multiple indexes on the same table space so Db2 can build them in parallel (SORTBLD), rather than parallel jobs fighting over the same table.

SHRLEVEL REFERENCE and CHANGE

IBM’s restriction: use SHRLEVEL CHANGE to fix a broken or restricted index, to build after DEFER, or to (un)encrypt data sets—not to move volumes (use REORG INDEX). CHANGE on a unique index will not allow INSERT, DELETE, or updates that affect that unique index. Additional CHANGE options:

• MAXRO — seconds for the last log-iteration (applications read-only). Default IRLMRWT
• LONGLOG CONTINUE|TERM|DRAIN — if log apply is not catching up, after DELAY (default 1200 seconds): keep going, terminate, or drain writers
• DRAIN_WAIT / RETRY / RETRY_DELAY — drain timing, same idea as other online utilities

SCOPE, REUSE, copies, and statistics

• SCOPE ALL — rebuild the named indexes
• SCOPE PENDING — only RBDP, RBDP*, PSRBD, RBDPM, RECP, or AREO*
• REUSE — reset managed data sets in place. Do not use after media failure. Extents are not released
• FLASHCOPY NO|YES|CONSISTENT and FCCOPYDDN — optional FlashCopy of the rebuilt index
• PARALLEL(n) — parallel subtasks; 0 means Db2 chooses
• RBALRSN_CONVERSION EXTENDED|NONE — convert page RBA/LRSN format during rebuild when your shop is moving to extended format
• STATISTICS — inline index stats. UPDATE ALL / ACCESSPATH / SPACE / NONE; REPORT YES/NO; HISTORY; INVALIDATECACHE; FORCEROLLUP. FREQVAL NUMCOLS/COUNT and HISTOGRAM options match RUNSTATS INDEX. KEYCARD is deprecated and always collected. Cannot collect inline stats on catalog/directory indexes or clones

CREATE INDEX DEFER YES leaves the index empty until REBUILD. Dynamic SQL will not choose the index until it is built. After RECOVER TABLESPACE, dependent indexes are often RBDP; REBUILD INDEX(ALL) SCOPE PENDING is the usual cleanup.

Explain It Like I'm Five

An index is the library card catalog. If the catalog burns, you do not photocopy ash—you walk every shelf (the table) and write a new catalog (REBUILD). SHRLEVEL REFERENCE means visitors may read books but nobody reshelves until you finish. SHRLEVEL CHANGE means people still reshelve while you copy the shelves onto a scratch pad and catch up from the diary (the log)—but a unique catalog cannot promise “no duplicate cards” until it is done, so new unique cards are refused. REUSE is using the same catalog drawers; without REUSE you get brand-new drawers (better after a smashed cabinet).

Exercises

1. Write REBUILD INDEX for all indexes on PAYDB.EMPTS that are in a pending state, with SORTDEVT SYSDA and inline STATISTICS UPDATE ALL.
2. An NPSI is PSRBD after a volume failure. Can you REBUILD PART 1? What should you specify about REUSE?
3. Why would SHRLEVEL CHANGE on a unique payroll SSN index break overnight inserts?
4. Contrast REBUILD INDEX with RECOVER INDEX and REORG INDEX for (a) RBDP after table recover, (b) moving an index to new volumes, (c) COPY YES index restore.
5. Three indexes, SORTNUM 8, SORTKEYS, no parallelism limits. About how many sort work data sets can be allocated, and what goes wrong if SORTNUM is huge?