DB2 CHECK INDEX utility

Index consistency checking

CHECK INDEX tests whether indexes are consistent with the data they index and issues warning messages when it finds an inconsistency. Consistency means:

• Every index entry points at a real table (or auxiliary) row with that key
• Every table row that should be indexed has a matching index entry
• For an auxiliary table index, each LOB is represented by an index entry and an index entry exists for every LOB

The utility does not rewrite the index. If keys are missing or extra, the usual repair is REBUILD INDEX (or RECOVER INDEX from a good copy plus log). REORG INDEX will not magically invent missing keys from the table.

When to run it

• After a conditional restart or point-in-time recover on table spaces whose indexes might not match the restored data
• Before CHECK DATA, especially DELETE YES — CHECK DATA trusts indexes to find parents and dependents
• After odd SQL results, DSNI messages, or a utility that was terminated in a build phase
• On XML-related indexes separately; CHECK DATA INCLUDE XML TABLESPACES does not verify those XML indexes

Control statement

1
2
3
4
5
6
7
8
9
10
11
//CIX     EXEC PGM=DSNUTILB,PARM='DB2A,CIXEMP'
//STEPLIB  DD DISP=SHR,DSN=DSN.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(20,20))
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(20,20))
//SYSIN    DD *
  CHECK INDEX (HR.IXEMPNO, HR.IXDEPT)
    SHRLEVEL REFERENCE
    SORTDEVT SYSDA
/*

Other shapes: CHECK INDEX (ALL) TABLESPACE db.ts, INDEXSPACE db.is, PART n, LIST listdef-name, CLONE. SORTDEVT/SORTNUM allocate sort work. Inaccurate catalog statistics can cause the sort to fail — RUNSTATS the object if CHECK INDEX dies in SORTCHK on a huge index.

Phases

UNLOAD reads keys from the data. SORTCHK compares that sorted stream with a scan of the index. A mismatch message identifies keys in one place and not the other. DISPLAY UTILITY shows which phase is running.

SHRLEVEL REFERENCE and CHANGE

REFERENCE lets applications read but not write the index, table space, or partition while it is checked. That is the simple, consistent choice for a batch window.

CHANGE lets applications read and write the live objects. The utility copies the table space/partition and indexes to shadow data sets and checks the shadows:

• If objects are Db2-managed and shadows do not exist, Db2 creates them and deletes them at the end
• User-managed data sets can be created or re-created by DFSMSdss; they are not always scratched automatically when CHECK INDEX ends
• FlashCopy is preferred; without it the copy is slower and the read-only drain for the copy lasts longer
• Shadow index data sets typically use a J0001 instance qualifier instead of I0001

For CHANGE, the user ID that drives DFSMSdss needs authority to create those shadows (IBM documents RACF ALTER or equivalent on the shadow data set). Preallocate shadows on the same storage group, EA-enabled if the original is, LINEAR, SHAREOPTIONS(3,3). MODEL the new data set after the original when you define them yourself.

Parallel indexes and logical partitions

CHECK INDEX can process indexes in parallel. For a partitioned table space, check the partitions you care about; for nonpartitioned secondary indexes, logical partitions of the NPSI may need their own shadow pieces when you use SHRLEVEL CHANGE. One logical partition check is supported for diagnosing a single slice without checking the entire NPSI at once.

Reading the output and what to do next

SYSPRINT lists indexes checked and any inconsistent keys. A clean run is your green light for CHECK DATA. A dirty run means:

1. Do not run CHECK DATA DELETE YES yet
2. Confirm you are not looking at a clone versus base mix-up (use CLONE explicitly)
3. REBUILD INDEX from the table if the table is the source of truth, or RECOVER INDEX if a good image copy of the index is more trusted than the table
4. CHECK INDEX again

Termination and restart rules are in the Utility Guide; after a failure, DISPLAY UTILITY and TERM UTILITY if the UID is stuck, then rerun from the correct phase.

CHECK INDEX versus CHECK DATA versus CHECK LOB

• CHECK INDEX — keys versus rows (this page)
• CHECK DATA — RI and check constraints, optional XML/LOB column relationships
• CHECK LOB — structure of a LOB table space itself

A complete integrity sweep after PIT recover is often CHECK INDEX (all related indexes), then CHECK DATA SCOPE ALL (or PENDING), then CHECK LOB if LOB spaces were recovered independently.

Explain It Like I'm Five

The table is a box of toys. The index is a list that says “the red car is on hook 4.” CHECK INDEX walks the box and the list. If the list says hook 4 has a red car but hook 4 is empty, or a blue truck is in the box with no list line, the teacher writes a warning. The teacher does not rewrite the list — that is REBUILD INDEX, which walks every toy and makes a brand-new list. SHRLEVEL CHANGE photocopies box and list, checks the photocopy, and lets kids keep playing with the real toys.

Exercises

1. Write CHECK INDEX (ALL) TABLESPACE for a sandbox database with SORTDEVT and SHRLEVEL REFERENCE.
2. Explain to a teammate why CHECK DATA DELETE YES on a broken index is dangerous.
3. After a PIT recover of a tablespace but not its NPSIs, predict whether CHECK INDEX on the NPSI might fail, and which utility would rebuild it.
4. List three shadow-data-set rules for SHRLEVEL CHANGE (naming, SHAREOPTIONS, cleanup of user-managed shadows).
5. Find a recent CHECK INDEX SYSPRINT in your shop and identify the SORTCHK messages.