Utilities, backup and recovery
DB2 utilities keep data healthy and recoverable: REORG restores order, COPY takes image copies, LOAD brings data in and RECOVER rebuilds objects from copies and the log. Knowing what each one does to availability, and which pending states it can leave behind, is essential for production work.
The core utilities
| Utility | Purpose | Watch out for |
|---|---|---|
| RUNSTATS | Collect statistics for the optimizer | Needs REBIND to affect static SQL |
| REORG | Restore clustering order, reclaim space, apply some schema changes | Availability during the run; space for shadow datasets |
| COPY | Take full or incremental image copies | Copies must exist before you need to recover |
| LOAD | Bulk load data, replacing or adding to a table | Can leave COPY-pending or CHECK-pending states |
| UNLOAD | Extract data to a sequential dataset | Fast, but not a substitute for image copies |
| RECOVER | Restore from image copies and apply the log | Point-in-time recovery needs related objects kept consistent |
| QUIESCE | Record a point where a set of objects is consistent | Briefly drains writers |
Utilities run as batch jobs, usually through the DSNUTILB program and a site procedure, with control statements in SYSIN. Most sites generate utility jobs with tools or templates rather than writing them by hand.
REORG
Over time inserts land out of clustering order, deleted space is not reused well and indexes fragment. REORG unloads and reloads the data in clustering order. The SHRLEVEL option controls availability:
| SHRLEVEL | Applications during REORG |
|---|---|
| NONE | No access while the data is reloaded |
| REFERENCE | Read access; works on shadow datasets, then switches |
| CHANGE | Read and write access; DB2 applies logged changes to the shadow copy, with a brief drain at the switch |
Online REORG is widely used, but the final drain needs applications to commit regularly. A long-running unit of work that holds claims can make the REORG time out.
Image copies and the log
DB2 recovery rests on two things: image copies and the log. COPY writes a full copy of a table space or index space, or an incremental copy of changed pages, and records it in the catalog table SYSIBM.SYSCOPY. Every change is written to the active log, which is offloaded to archive logs. The bootstrap dataset (BSDS) records which log datasets cover which log ranges.
RECOVER to current restores the latest copy and applies all later log records. Point-in-time recovery uses TOLOGPOINT (or TORBA) to stop at a chosen point, or TOCOPY to go back to a copy. Indexes then need RECOVER or REBUILD INDEX, and objects related by referential integrity or LOBs should go back to the same point. A QUIESCE taken before a risky batch run gives a clean point to return to. Systems with suitable storage can also use BACKUP SYSTEM and RESTORE SYSTEM for system-level recovery, depending on configuration.
Restrictive states
Some operations leave an object in a state that blocks normal use until something is done. You see them with -DISPLAY DATABASE(dbname) SPACENAM(*) RESTRICT.
| State | Typical cause | Usual resolution |
|---|---|---|
| COPY (copy pending) | LOAD or REORG with LOG NO and no inline copy | Take an image copy |
| CHKP (check pending) | LOAD without enforcing referential or check constraints | Run CHECK DATA |
| RECP (recover pending) | A utility such as LOAD, REORG or RECOVER that ended abnormally or was terminated | RECOVER the object |
| RBDP (rebuild pending) | Index out of step after a recovery or load | REBUILD INDEX |
| REORP / AREO* | Schema change that needs or advises a REORG | Run REORG |
-DISPLAY DATABASE(PAYDB) SPACENAM(*) RESTRICT NAME TYPE PART STATUS PAYTS01 TS RW,COPY PAYTS02 TS RW,CHKP ******* DISPLAY OF DATABASE PAYDB ENDED
LOAD
LOAD with REPLACE empties the table space first; with RESUME YES it adds to existing data. LOG NO is much faster but leaves the object in COPY pending unless an inline copy is taken, because DB2 cannot recover it from the log. Rows that fail conversion or constraints can be written to a discard dataset.
Common mistakes
RECOVER cannot use an unload file and it has no link to the log. Image copies registered in SYSCOPY are what recovery depends on.
Related tables, indexes and LOBs must be consistent. Use REPORT TABLESPACESET to find the set and recover them together, ideally to a QUIESCE point.
REPAIR can remove the flag but not the reason. A table loaded with LOG NO and no copy cannot be recovered until a copy is taken.
What you will see at work
- DBAs and operations agree utility windows, because REORG, LOAD and COPY affect availability and batch timing.
- Production support learns to recognise -904 and restricted states and to check -DISPLAY DATABASE before escalating.
- Recovery tests, where a table space is actually recovered from copies and logs, are part of many audit and disaster recovery plans.
Key terms
Check your understanding.
Take this lesson's quiz and save your progress. Free.