REPAIR utility in DB2 for z/OS

What REPAIR can do

Documented uses include:

• Produce a hexadecimal dump of an area in a table space or index
• Verify and replace data areas; delete a single row or an entire LOB
• Reset a pending status on a table space or index
• Dump LOB pages; rebuild LOB object descriptors (OBDs)
• Manage version numbers; insert missing system pages for version-0 table spaces; align CURRENT_VERSION / OLDEST_VERSION in SYSTABLESPACE
• Check catalog versus data inconsistencies
• Turn Persistent Read Only (PRO) on or off
• Test, diagnose, rebuild, or drop DBDs

Output is one or more modified pages plus dumps. Pages on the logical page list (LPL) cannot be accessed by REPAIR—recover them first. If a page’s log RBA is higher than the current end of log (truncated log), Db2 will not allow modification until DSN1COPY RESET (or equivalent IBM procedure) is applied.

REPAIR SET — pending states

This is the form most operators know. It does not fix RI violations or rebuild keys; it only clears (or sets) the flag.

1
2
3
4
REPAIR SET TABLESPACE PAYDB.PAYTS NOCHECKPEND
REPAIR SET TABLESPACE PAYDB.PAYTS PART 4 NOAUXCHKP
REPAIR SET INDEX (ALL) TABLESPACE PAYDB.PAYTS NORBDPEND
REPAIR SET TABLESPACE PAYDB.PAYTS NOPRO

Prefer CHECK DATA / CHECK LOB / REBUILD INDEX / COPY to clear CHKP, AUXW, RBDP, and COPY the honest way. Use SET when the utility already succeeded but the flag stuck, or IBM Support instructs you to reset a stale state.

REPAIR LOCATE — dump and zap

LOCATE names where. Then DUMP, VERIFY, REPLACE, or DELETE say what. Always DUMP first, VERIFY the old value, REPLACE the new value, DUMP again.

1
2
3
4
5
6
7
8
9
REPAIR OBJECT
  LOCATE TABLESPACE DSN8D81A.DSN8S81D PAGE X'02'
    VERIFY OFFSET 50 DATA X'0A00'
    REPLACE OFFSET 50 DATA X'0D11'
    DUMP OFFSET 50 LENGTH 4
 
REPAIR OBJECT
  LOCATE TABLESPACE PAYDB.PAYTS RID(X'0000000503')
    DELETE

SHRLEVEL CHANGE is available on current releases so some LOCATE work can run with concurrent access; dumps still take locks (S for DUMP, SIX-class locks for REPLACE in classic descriptions). Treat production REPLACE as a change-controlled event with a backout RECOVER point.

Repairing indexes, LOBs, and the catalog

Indexes

You can LOCATE INDEXSPACE or INDEX and DUMP/REPLACE index pages, or SET INDEX … NORBDPEND. Broken unique indexes are usually REBUILD INDEX, not a hand zap of a leaf page. If you DELETE a table row with REPAIR, matching index entries may still exist until you REBUILD or REPAIR the index too—that is how you create CHECK INDEX failures.

LOB and XML

CHECK LOB SHRLEVEL CHANGE can punch REPAIR statements that DELETE invalid LOBs. LOCATE with ROWID and VERSION targets a LOB; DOCID targets XML. You can dump LOB map versus data pages. After deleting a LOB, run CHECK DATA on the base so the column is marked invalid.

Catalog, directory, and versions

Catalog tables cannot be updated with SQL. REPAIR (and IBM recovery procedures) are how you fix catalog/directory inconsistencies. REPAIR VERSIONS TABLESPACE aligns version numbers after DSN1COPY or recovery left SYSTABLES.VERSION at 0 while the space is at a later CURRENT_VERSION. INSERTVERSIONPAGES (with SETCURRENTVERSION on current releases) inserts missing system pages for version-0 format objects.

REPAIR DBD

• TEST — compare the DBD with the catalog without changing it
• DIAGNOSE — diagnostic output for IBM
• REBUILD — rebuild the DBD from the catalog
• DROP DBID — remove a DBD (last resort)

Run DBD TEST before REBUILD. Do this only with a tested procedure; the directory (DSNDB01) is not a sandbox.

Explain It Like I'm Five

Most Db2 tools rearrange whole shelves. REPAIR is a pair of tweezers and a marker pen. DUMP is taking a photo of one page in the book. VERIFY is checking the photo still matches before you write. REPLACE is writing new letters in that spot. DELETE is tearing out one page. SET is peeling a warning sticker off the shelf without fixing why the sticker was there. Tweezers are great for a splinter and terrible for rebuilding the library—use RECOVER and REBUILD for that.

Exercises

1. Write REPAIR SET to reset CHKP on PAYDB.PAYTS partition 2 and RBDP on all indexes of that space.
2. Order the statements DUMP, VERIFY, REPLACE, DUMP for page X'03' offset X'0080' changing 'SP' to 'ST'.
3. Why is REPAIR SET NOCHECKPEND after a failed CHECK DATA a bad idea?
4. A page you need is in the LPL. What must you do before REPAIR LOCATE PAGE?
5. When would you choose REBUILD INDEX instead of REPAIR LOCATE on an index leaf page?