DB2 CHECK DATA utility

What CHECK DATA is for

CHECK DATA TABLESPACE database.tablespace (database defaults to DSNDB04) inspects dependent tables in that space. It verifies:

• Referential constraints — every foreign key has a parent (except informational RI, which is not checked)
• Table check constraints — CHECK (SALARY > 0) and friends
• LOB and XML relationships when SCOPE includes them or you use INCLUDE XML TABLESPACES

You cannot name a LOB table space on the main TABLESPACE keyword. PART n limits the check to one physical partition (1–4096).

Common CHKP causes: LOAD without ENFORCE CONSTRAINTS, recovering a child independently of its parent, adding a FOREIGN KEY to a table that already has rows, CHECK DATA that found violations with DELETE NO.

A starter job

1
2
3
4
5
6
7
8
9
10
11
12
13
//CHK     EXEC PGM=DSNUTILB,PARM='DB2A,CHKDEPT'
//STEPLIB  DD DISP=SHR,DSN=DSN.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//UTPRINT  DD SYSOUT=*
//SYSERR   DD SYSOUT=*
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(10,10))
//SORTOUT  DD UNIT=SYSDA,SPACE=(CYL,(10,10))
//SYSIN    DD *
  CHECK DATA TABLESPACE DBHR.TSDEPT
    SCOPE PENDING
    SHRLEVEL REFERENCE
    SORTDEVT SYSDA
/*

Run CHECK INDEX on the same objects first if you plan DELETE YES. CHECK DATA walks indexes to match parents and children.

SCOPE

Prefer PENDING for routine CHKP cleanup. Use ALL when you want a full audit even though CHKP is off. REFONLY skips auxiliary LOB/XML column checks when you only care about RI and CHECK constraints.

Referential checking and exception tables

When CHECK DATA finds a row that breaks a foreign key or check constraint it reports the violation. With DELETE NO (default) the row stays and the space can remain CHKP. With DELETE YES the utility deletes violating rows from the source. Pair it with:

1
2
3
4
CHECK DATA TABLESPACE DBHR.TSEMP
  FOR EXCEPTION IN HR.EMPLOYEE USE HR.EMPLOYEE_EXC
  DELETE YES LOG YES
  SCOPE ALL

The exception table must look like the checked table plus two extra columns: one for the RID of the offending row and one for the timestamp of this CHECK DATA run. LOG YES (default with DELETE YES) logs the deletes; LOG NO is faster but leaves you with copy-pending considerations — follow your shop’s recoverability rules.

EXCEPTIONS n can stop the utility after n violations so a wildly broken load does not run for hours. ERRDDN (default SYSERR) holds diagnostic output. PUNCHDDN (SYSPUNCH) receives generated statements.

CHECK-pending resolution

Successful CHECK DATA with no remaining violations resets CHKP. DELETE YES that removes every violator also resets it. If you cannot fix the data tonight, operations sometimes use REPAIR SET TABLESPACE … NOCHECKPEND — that clears the flag without proving RI. Treat that as an emergency bypass, not a substitute for CHECK DATA.

Auxiliary CHECK-pending (ACHKP) is the LOB/XML cousin. AUXERROR REPORT warns and can set ACHKP; INVALIDATE marks the auxiliary value invalid. CHECK LOB is a separate utility for LOB table spaces themselves.

SHRLEVEL REFERENCE versus CHANGE

REFERENCE allows readers, not writers. The utility may write (for example DELETE YES or INVALIDATE). You cannot combine REFERENCE with DELETE YES or XMLERROR/AUXERROR/LOBERROR INVALIDATE on archive-enabled or system-period temporal tables.

CHANGE allows read and write on the live objects. CHECK DATA then:

1. Drains writers and forces buffers to disk
2. Invokes DFSMSdss to copy the object and its indexes to shadows (FlashCopy if available)
3. Enables read/write again on the real objects
4. Runs the check against the shadows only

It does not change the real table during processing. Instead it writes REPAIR LOCATE DELETE statements to PUNCHDDN. Review those statements carefully on versioned or history tables — historic rows could be deleted if you apply them blindly. CHECK_FASTREPLICATION REQUIRED forces FlashCopy; without FlashCopy the copy can take a long read-only window or the utility can fail.

DRAIN_WAIT, RETRY, and RETRY_DELAY override IRLMRWT/UTIMOUT for the drain before the copy.

XML, clones, and SORT

INCLUDE XML TABLESPACES ALL (or named XML columns/spaces) adds XML document completeness, node-ID index versus document ID, and optional XMLSCHEMA validation. XML indexes themselves are not verified here — run CHECK INDEX on those indexes.

CLONE checks clone tables versus corresponding LOB data only (clones cannot have referential constraints).

WORKDDN (SYSUT1, SORTOUT) and SORTDEVT/SORTNUM support the sorts CHECK DATA needs to match keys.

Explain It Like I'm Five

CHECK DATA is the teacher who walks the classroom roster. Every child row must have a parent on the parent list, and every “must be positive” rule on the homework must still be true. CHKP is a lock on the classroom door until the teacher finishes. SCOPE PENDING means “only check the rooms we already marked messy.” DELETE YES is collecting the papers that break the rules and putting them in an exception box instead of leaving them on the desks. SHRLEVEL CHANGE photocopies the classroom, checks the photocopy, and writes a list of which papers to throw away later, while kids keep working in the real room.

Exercises

1. DISPLAY DATABASE … SPACENAM … LIMIT(*) and find a CHKP or ACHKP status. Write the CHECK DATA SCOPE PENDING job that would clear it if the data is clean.
2. Design an exception table for HR.EMPLOYEE (same columns plus RID and timestamp).
3. Explain why informational RI would not show up in CHECK DATA output.
4. List two situations that set CHKP and one that sets ACHKP.
5. Compare DELETE YES SHRLEVEL REFERENCE with SHRLEVEL CHANGE plus generated REPAIR statements. When would your shop pick each?