CHECK LOB in DB2 for z/OS

LOB table space checking versus CHECK DATA

Two different questions get two different utilities:

• Is this LOB table space internally consistent? — CHECK LOB. Broken map pages, invalid LOB versions, and other structure problems live here.
• Do base rows still match their auxiliary LOBs (or XML)? — CHECK DATA with AUX/LOB error options on the base table space.

IBM documents these times to run CHECK LOB:

• The LOB space is in CHECK-pending (CHKP) — look for structural defects; if none, CHKP is reset
• The LOB space is in auxiliary-warning (AUXW) — look for invalid LOBs; if none, AUXW is reset
• After a conditional restart or point-in-time recovery where LOB spaces might not be synchronized with the base
• Before CHECK DATA on a table space that contains at least one LOB column

After a successful run, CHECK LOB resets CHKP and AUXW. Exception: SHRLEVEL CHANGE does not set CHKP or AUXW even if it finds inconsistencies—treat those messages as a report, then follow up with REPAIR, RECOVER, or a REFERENCE-level check.

Authorization and JCL skeleton

You need STATS, DBADM/DBCTRL/DBMAINT on the database, System DBADM, SYSCTRL, SYSADM, or installation SYSOPR. Implicit databases can be authorized on the implicit database or DSNDB04. SHRLEVEL CHANGE also needs authority to run DFSMSdss ADRDSSU and RACF ALTER on the shadow data sets.

1
2
3
4
5
6
7
8
//CHLOB    EXEC DSNUPROC,SYSTEM=DB2A,UID='PAY.CHLOB'
//SYSIN    DD *
  CHECK LOB TABLESPACE PAYDB.PAYLOB1
    SHRLEVEL REFERENCE
    EXCEPTIONS 0
    SORTDEVT SYSDA
    SORTNUM 4
/*

Required DDs: SYSIN, SYSPRINT, UTPRINT. The LOB table space is named in SYSIN; it does not need its own DD. Sort work must not span volumes and must be disk, not tape. Allow about 1.2 times the data to be sorted.

Phases

Options

SHRLEVEL REFERENCE versus CHANGE

REFERENCE (default) lets applications read but not write the object being checked. That is the run that can clear CHKP/AUXW.

CHANGE drains writers, forces buffers to disk, copies the LOB space and its indexes to shadows with DFSMSdss, then checks the shadows while production returns to read-write. FlashCopy is preferred; if FlashCopy is unavailable, DFSMSdss falls back to a slower copy and the drain window grows. Set subsystem parameter CHECK_FASTREPLICATION to REQUIRED if you want the job to fail rather than crawl through a non-FlashCopy copy.

Drain options with CHANGE

• DRAIN_WAIT — seconds to wait for the drain (0–1800). 0 or omitted uses IRLMRWT
• RETRY — 0–255 retries; default UTIMOUT
• RETRY_DELAY — seconds between retries (1–1800); default is the smaller of DRAIN_WAIT × RETRY and DRAIN_WAIT × 10

EXCEPTIONS and PUNCHDDN

Every defect reported by a message counts toward EXCEPTIONS. When the count is reached, the utility ends in the CHECKLOB phase. With SHRLEVEL CHANGE, PUNCHDDN (default SYSPUNCH) receives REPAIR control statements that delete the LOBs named in those messages. After you apply those REPAIR statements, run CHECK DATA on the base table space so the corresponding LOB columns are marked invalid.

1
2
3
4
5
6
7
8
CHECK LOB TABLESPACE PAYDB.PAYLOB1
  CLONE
  SHRLEVEL CHANGE
  DRAIN_WAIT 20
  RETRY 6
  EXCEPTIONS 50
  PUNCHDDN SYSPUNCH
  SORTDEVT SYSDA;

Before you run it

Recover a LOB table space that is in RECOVER-pending first. CHECK LOB no longer needs SYSUT1 and SORTOUT; WORKDDN is ignored so you do not have to edit old JCL. If the base and LOB are both damaged, RECOVER the set to a common point, then CHECK LOB, then CHECK DATA.

DISPLAY DATABASE shows CHKP and AUXW. Do not confuse AUXW (invalid LOB values, often still readable) with CHKP (structure not trusted). CHECK INDEX is a different utility for index keys versus table rows—it does not replace CHECK LOB.

Explain It Like I'm Five

The base table is a photo album with captions. The actual photographs live in a second box (the LOB table space), each tagged so the caption can find it. CHECK LOB opens that photo box and asks: are the photos torn, are the tags nonsense, is the box’s internal stacking broken? CHECK DATA later asks: does every caption still point at a real photo? CHKP is a sticker that says “this box might be smashed.” AUXW says “some photos look wrong.” A clean CHECK LOB peels those stickers off.

Exercises

1. Write CHECK LOB for LOB space HRDB.EMPPHOTO with a limit of 25 exceptions and dynamic sort work on SYSDA.
2. DISPLAY DATABASE shows AUXW on the LOB space and CHKP on the base. Which utility do you run first, and why?
3. Explain why SHRLEVEL CHANGE might report errors yet leave AUXW off, and what you would do with SYSPUNCH.
4. A LOB space is RECP. Why will CHECK LOB not be your first job?
5. When would you add CLONE to CHECK LOB?