Db2 Utilities Guide: COPY, RUNSTATS, REORG, LOAD, UNLOAD, and RECOVER



Use the right utility for the table space state.

A Db2 table space can be perfectly designed and still cause trouble if utilities are skipped. A missing image copy can block recovery, old statistics can lead to bad access paths, and a badly timed REORG can collide with batch work that needs the same object.

Db2 utilities are the operational tools used to load data, collect statistics, reorganize objects, create recovery copies, check consistency, and recover damaged or lost data. On z/OS, these jobs are usually controlled through JCL and utility control statements.

Common Db2 utilities and when to use them

Utility	Main purpose	Typical trigger
COPY	Create an image copy for recovery	Before risky change, after load, or on a backup schedule
RUNSTATS	Update catalog statistics for the optimizer	After large data change, new index, load, or REORG
REORG	Reorganize data or indexes and reclaim space	Poor clustering, high disorganization, or space issues
LOAD	Load high-volume input data into a table	Initial load, refresh, conversion, or warehouse feed
UNLOAD	Extract table data into a sequential data set	Archive, migration, test data, or reload process
RECOVER	Restore an object using image copies and logs	Object damage, application error, or point-in-time recovery plan
CHECK DATA	Check referential and table check constraints	After load, repair, or data movement where integrity needs proof
REBUILD INDEX	Rebuild index structures	Damaged index, recovery action, or index rebuild requirement

COPY is for recovery images

The Db2 COPY utility creates image copies of table spaces or index spaces. It is not the same thing as copying rows from one table to another with SQL. A recovery plan depends on these image copies and the logs that follow them.

//COPYTS  EXEC DSNUPROC,SYSTEM=DSN1,UID='MF.COPY'
//SYSIN   DD *
  COPY TABLESPACE APPDB.TSORD
       FULL YES
       SHRLEVEL CHANGE
/*

Schedule image copies around business risk. For example, take a copy after a successful high-volume load so recovery does not have to replay a large amount of log activity from an older copy.

RUNSTATS supports access path choices

RUNSTATS updates catalog statistics that the Db2 optimizer uses for access path selection. A COBOL program with static SQL normally needs bind or rebind activity before changed statistics can affect the package access path.

//RSTAT   EXEC DSNUPROC,SYSTEM=DSN1,UID='MF.RUNSTATS'
//SYSIN   DD *
  RUNSTATS TABLESPACE APPDB.TSORD
       TABLE(ALL)
       INDEX(ALL)
       SHRLEVEL CHANGE
/*

Run it after major data shifts, new indexes, table reorganizations, and bulk loads. Do not treat it as decoration at the end of a job stream; stale statistics can send the optimizer toward a costly path.

REORG cleans up physical layout

REORG can restore clustering order, reclaim space, and rebuild object layout. It is often paired with RUNSTATS and sometimes followed by package rebind when access paths should be reviewed.

//REORG   EXEC DSNUPROC,SYSTEM=DSN1,UID='MF.REORG'
//SYSIN   DD *
  REORG TABLESPACE APPDB.TSORD
       SHRLEVEL CHANGE
/*

Plan REORG around availability and logging impact. A small reference table and a multi-billion-row transaction table do not have the same utility window.

LOAD and UNLOAD move data at scale

LOAD is used when a large input data set has to be inserted into Db2 faster than ordinary row-by-row application processing. UNLOAD extracts data to a sequential data set for archive, migration, testing, or reload processing.

//LOADTS  EXEC DSNUPROC,SYSTEM=DSN1,UID='MF.LOAD'
//SYSREC  DD DSN=APP.INPUT.ORDERS,DISP=SHR
//SYSIN   DD *
  LOAD DATA INDDN SYSREC
       INTO TABLE APP.ORDERS
/*

After a LOAD, check whether the object needs COPY, RUNSTATS, constraint checks, and package rebind. The right answer depends on the options used and local recovery rules.

RECOVER is the restore path

RECOVER uses image copies and logs to restore Db2 objects. It should be rehearsed before the outage, not first learned during one. Keep utility JCL, copy availability, log retention, and recovery point rules visible in the runbook.

//RECOV   EXEC DSNUPROC,SYSTEM=DSN1,UID='MF.RECOVER'
//SYSIN   DD *
  RECOVER TABLESPACE APPDB.TSORD
/*

For point-in-time work, coordinate application owners, dependent objects, RI relationships, and downstream files. Recovering one object can be technically correct and still break a business process if related data is out of sync.

Utility checklist before production

• Confirm object name, database, table space, and subsystem before submitting utility JCL.
• Check whether the utility needs outage time or can run with SHRLEVEL CHANGE.
• Review image copy requirements before and after high-risk utilities.
• Run or schedule RUNSTATS when data distribution or indexes changed.
• Check whether static SQL packages need rebind after statistics or index changes.
• Keep utility output with the change record so failures and warnings are not lost.

Related DB2 topics

Utilities connect directly to Db2 Table Spaces, Db2 Indexing, Db2 Optimizer, Db2 Binding and Rebinding, and Db2 Commands Quick Reference.

FAQ

Is Db2 COPY used to copy rows between tables?

No. In the Db2 utility context, COPY creates image copies used for recovery. Row movement between tables is handled by SQL, LOAD/UNLOAD processes, or application logic.

Should RUNSTATS be run after every LOAD?

Often yes, especially when row counts or data distribution changed. The exact schedule depends on local standards, LOAD options, and whether related static packages will be rebound.

Does REORG always improve SQL performance?

No. REORG can help when physical layout, clustering, or space use is hurting access paths, but the value depends on the object and workload. Review utility reports and EXPLAIN evidence.

For utility work, the safest habit is simple: know the object state before the job, read the utility output after the job, and record what changed.