Chapter 17: DB2 Utilities — REORG, RUNSTATS, COPY, RECOVER, LOAD, UNLOAD: The DBA's Essential Toolkit

A database without regular maintenance is a ticking time bomb. These six utilities are how you defuse it.

Every production database degrades over time. Rows are inserted, updated, and deleted in patterns that fragment storage. The optimizer makes decisions based on statistics that no longer reflect reality. Backup gaps widen until a single disk failure becomes an unrecoverable disaster. The six core DB2 utilities — REORG, RUNSTATS, COPY (BACKUP), RECOVER, LOAD, and UNLOAD (EXPORT) — are the DBA's essential toolkit for fighting this entropy. Master them, and your databases hum along at peak performance with bulletproof recoverability. Neglect them, and you will eventually face that 2 AM phone call that every DBA dreads.

This chapter marks our entry into Part IV: Administration Fundamentals. We are now firmly in DBA territory. The syntax is precise, the JCL is real, and the consequences of mistakes are measured in hours of downtime and gigabytes of lost data. We will cover each utility in depth on both z/OS and LUW platforms, building toward a complete maintenance plan for Meridian National Bank.

17.1 Why Utilities Matter

Before we examine individual utilities, you need to understand what happens when you don't run them. Consider a table that processes 50,000 transactions per day — roughly the volume of Meridian Bank's core ACCOUNTS table. After six months without maintenance:

Fragmentation accumulates. Every DELETE leaves a gap in the tablespace. Every variable-length UPDATE that increases row size forces the row to a new location, leaving a pointer in the old slot. After millions of such operations, a tablespace that should occupy 200 cylinders now consumes 340, and sequential scans read 70% more pages than necessary. Index leaf pages become half-empty, turning three-level index probes into four-level probes.

Statistics go stale. The optimizer chose a nested-loop join six months ago when the TRANSACTIONS table had 2 million rows. Now it has 14 million rows, but the catalog still says 2 million. The optimizer continues choosing nested-loop joins when a merge-scan join would be 50 times faster. A query that ran in 2 seconds now takes 90 seconds, and nobody changed the SQL.

Backup gaps widen. If you last took a full image copy three weeks ago and have no incrementals since, recovery requires replaying three weeks of log records. For a high-volume tablespace, that could mean 18 hours of recovery time — assuming the logs still exist.

Recovery becomes impossible. Archive logs are recycled after their retention period. If your last backup predates the oldest available archive log, you cannot recover that tablespace at all. The data is gone.

These are not theoretical risks. They are certainties for any production database that lacks a disciplined maintenance regimen. The utilities in this chapter are that regimen.

The Core Maintenance Cycle

The fundamental pattern is straightforward:

1. REORG — Eliminate fragmentation, restore physical order
2. RUNSTATS — Update catalog statistics so the optimizer has current data
3. COPY/BACKUP — Capture a clean, reorganized image for recovery
4. RECOVER — (Tested regularly, invoked when disaster strikes)
5. LOAD/UNLOAD — Move data in and out at maximum speed

Steps 1 through 3 form a chain that should execute in sequence. You reorganize, then collect statistics on the reorganized data, then back up the clean state. Breaking this chain — for example, running RUNSTATS before REORG — produces suboptimal results because the statistics describe a fragmented layout that you are about to change.

17.2 [z/OS] REORG TABLESPACE and REORG INDEX

The z/OS REORG utility physically reorganizes data in a tablespace or index, eliminating fragmentation and restoring rows to their clustering sequence. It is the single most impactful utility for sustaining query performance.

SHRLEVEL Options

The SHRLEVEL parameter controls concurrent access during reorganization:

SHRLEVEL NONE is the fastest because no concurrent access is permitted. The utility unloads data, sorts it, and reloads it into the original dataset. Applications receive -904 (resource unavailable) if they attempt access.

SHRLEVEL REFERENCE uses a shadow dataset approach. The utility builds the reorganized data in a shadow copy while the original remains available for read access. At the end, a brief SWITCH phase swaps the shadow into place. Write access is denied during the entire operation.

SHRLEVEL CHANGE is the most complex but allows full read/write access during most of the REORG. The utility captures log records for changes made during reorganization and applies them during a brief drain phase at the end. This is the standard choice for Meridian Bank's 24x7 OLTP environment.

Key Parameters

REORG TABLESPACE dbname.tsname
  SHRLEVEL CHANGE
  FASTSWITCH YES
  MAPPINGTABLE schema.mapping_table
  LOG YES
  SORTDATA YES
  SORTKEYS YES
  STATISTICS TABLE(ALL) INDEX(ALL)
  REPORTONLY NO

FASTSWITCH YES — Uses the SWITCH phase to swap shadow datasets rather than copying data back. Requires that DEFINE NO was used when creating the tablespace (letting DB2 manage VSAM datasets). This is the default for most modern configurations and dramatically reduces the final outage window.

MAPPINGTABLE — Required for SHRLEVEL CHANGE. This table tracks the correspondence between old and new RIDs (Record Identifiers) during reorganization. You must create this table before running REORG:

CREATE TABLE MERIDIAN.REORG_MAP_ACCOUNTS
  (TYPE     SMALLINT,
   SOURCE   ROWID,
   TARGET   ROWID)
  IN MERIDIANDB.REORGMAP_TS;

LOG YES/NO — Controls whether the RELOAD phase is logged. LOG YES means the REORG itself is recoverable — if it fails partway through, you can recover the tablespace. LOG NO is faster but requires a full image copy immediately after REORG completes because the tablespace is in COPY-pending status.

STATISTICS TABLE(ALL) INDEX(ALL) — Inline statistics collection. This eliminates the need for a separate RUNSTATS step, saving a full pass through the data. For large tablespaces, this can save hours.

SORTDATA YES / SORTKEYS YES — Sorts data rows into clustering order and sorts index keys during rebuild. Both should be YES for optimal results.

Sample JCL for DSNUPROC

The standard approach on z/OS uses the DSNUPROC cataloged procedure:

//REORGTS  EXEC DSNUPROC,SYSTEM=DB2P,
//            UID='REORG.ACCTS',UTPROC=''
//DSNUPROC.SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(100,50))
//DSNUPROC.SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(100,50))
//DSNUPROC.SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(100,50))
//DSNUPROC.SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(100,50))
//DSNUPROC.SYSREC   DD DSN=MERIDIAN.REORG.ACCOUNTS.SYSREC,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(200,100))
//DSNUPROC.SORTOUT  DD DSN=MERIDIAN.REORG.ACCOUNTS.SORTOUT,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(200,100))
//DSNUPROC.SYSIN DD *
  REORG TABLESPACE MERIDIANDB.ACCOUNTS_TS
    SHRLEVEL CHANGE
    FASTSWITCH YES
    MAPPINGTABLE MERIDIAN.REORG_MAP_ACCOUNTS
    LOG YES
    SORTDATA YES
    SORTKEYS YES
    STATISTICS TABLE(ALL) INDEX(ALL)
      KEYCARD
      FREQVAL COUNT 20
/*

The SORTWK datasets provide workspace for the external sort. Size them at roughly 1.5 to 2 times the tablespace size for safe margins. SYSREC holds unloaded data during the REORG process, and SORTOUT holds sorted data before reload.

Understanding REORG Phases

A SHRLEVEL CHANGE REORG proceeds through distinct phases, each with different resource consumption characteristics:

1. UNLOAD — Reads all rows from the tablespace and writes them to the SYSREC work dataset. During this phase, the utility reads every page in the tablespace sequentially. CPU and I/O are the primary resources consumed.

2. SORT — Sorts the unloaded rows into clustering index order using DFSORT. This phase is CPU-intensive and requires the SORTWK datasets. The amount of sort work space needed is approximately 1.5 to 2 times the size of the unloaded data.

3. RELOAD — Writes the sorted rows back into the tablespace (or shadow dataset for SHRLEVEL REFERENCE/CHANGE). Free space is distributed according to the tablespace PCTFREE and FREEPAGE settings. This phase generates significant I/O and, if LOG YES is specified, substantial log volume.

4. BUILD — Rebuilds all indexes on the tablespace from the reloaded data. Index keys are sorted and leaf pages are constructed sequentially. This is typically faster than the UNLOAD and RELOAD phases because indexes are smaller than the table data.

5. LOG (SHRLEVEL CHANGE only) — Reads the log records generated by concurrent application activity during the preceding phases and applies them to the reorganized data. The duration of this phase depends on the transaction volume during the REORG.

6. SWITCH — Swaps the shadow dataset into place as the active dataset (when FASTSWITCH YES). This is the only phase that briefly suspends all access. For well-configured systems, the SWITCH phase typically completes in under 10 seconds.

Understanding these phases helps you estimate REORG duration, size work datasets appropriately, and communicate realistic expectations to application teams about availability impacts.

REORG INDEX

Index-only reorganization is lighter weight and useful when index fragmentation is high but the tablespace data is still well-clustered:

REORG INDEX MERIDIAN.IX_ACCT_CUSTID
  SHRLEVEL REFERENCE
  SORTKEYS YES
  LOG YES

This rebuilds the specified index without touching the tablespace. Multiple indexes can be listed, or use REORG INDEX (ALL) TABLESPACE dbname.tsname to rebuild all indexes on a tablespace.

Monitoring REORG Progress on z/OS

Use the DISPLAY UTILITY command to check progress:

-DB2P DISPLAY UTILITY(REORG.ACCTS)

Output shows the current phase (UNLOAD, SORT, RELOAD, BUILD, SWITCH, LOG) and the percentage complete for phases that support progress reporting.

REPORTONLY Mode

Before committing to a full REORG, run with REPORTONLY to assess fragmentation:

REORG TABLESPACE MERIDIANDB.ACCOUNTS_TS
  REPORTONLY
  OFFPAGESNUM 10

This produces a report showing the current disorganization percentage, near/far page offloads, and other metrics without actually reorganizing. Use this to prioritize which tablespaces need REORG most urgently.

17.3 [LUW] REORG TABLE and REORG INDEXES

DB2 LUW provides both classic (offline) and inplace (online) reorganization. The concepts parallel z/OS but the syntax and mechanics differ substantially.

Classic REORG (Offline)

REORG TABLE meridian.accounts
  INDEX meridian.ix_acct_custid
  USE TEMP_TS1
  LONGLOBDATA
  RESETDICTIONARY
  KEEPDICTIONARY;

Classic REORG rebuilds the table entirely. The table is unavailable for the duration. It unloads data in clustering index order, rebuilds the table, and rebuilds all indexes.

USE tempspace — Specifies a system temporary tablespace to use for the intermediate data. Without this, REORG uses the tablespace where the table resides, requiring sufficient free space.

LONGLOBDATA — Includes LOB and LONG data in the reorganization. Without this, only the base table rows are reorganized.

RESETDICTIONARY vs KEEPDICTIONARY — Controls whether the compression dictionary is rebuilt. RESETDICTIONARY rebuilds it from current data (better compression if data patterns have changed); KEEPDICTIONARY preserves the existing dictionary (faster).

Inplace REORG (Online)

REORG TABLE meridian.accounts INPLACE
  ALLOW WRITE ACCESS
  START;

Inplace REORG allows concurrent read and write access while reorganizing. It works in phases:

1. Truncation — Reclaims empty pages at the end of the table
2. Compaction — Moves rows from sparse pages to fill gaps
3. Rebuild — Rebuilds indexes to reflect new row locations

Monitor progress with:

SELECT REORG_PHASE, REORG_STATUS, REORG_COMPLETION
FROM SYSIBMADM.SNAPTAB_REORG
WHERE TABNAME = 'ACCOUNTS' AND TABSCHEMA = 'MERIDIAN';

You can also pause and resume inplace REORG:

REORG TABLE meridian.accounts INPLACE PAUSE;
REORG TABLE meridian.accounts INPLACE RESUME;

This is invaluable during business hours when you need to temporarily yield resources to production workloads.

Using ADMIN_CMD

Both classic and inplace REORG can be invoked through the ADMIN_CMD stored procedure, which is useful for scripting and remote execution:

CALL SYSPROC.ADMIN_CMD('REORG TABLE meridian.accounts INPLACE ALLOW WRITE ACCESS START');

REORG INDEXES

To reorganize all indexes on a table without touching the table data:

REORG INDEXES ALL FOR TABLE meridian.accounts
  ALLOW WRITE ACCESS;

This is equivalent to the z/OS REORG INDEX capability and is useful when index fragmentation is the primary concern.

Reclaiming Extents

DB2 LUW can reclaim unused extents without a full REORG:

ALTER TABLESPACE accounts_ts REDUCE MAX;

This returns unused high-water-mark extents to the storage manager. It is a lightweight alternative when the goal is reclaiming disk space rather than optimizing row clustering.

LOAD Pending After Failed LOAD (LUW)

If a LOAD operation fails or is interrupted on LUW, the table may be left in LOAD PENDING state. No SQL access is permitted until the state is resolved. You have two options:

-- Option 1: RESTART — continue the load from the last consistency point
LOAD FROM '/data/meridian/daily_transactions.csv' OF DEL
  RESTART INTO meridian.transactions;

-- Option 2: TERMINATE — roll back the load and restore the table
LOAD FROM '/dev/null' OF DEL
  TERMINATE INTO meridian.transactions;

RESTART is preferred when the input data is still available and the load was interrupted by a transient error (network timeout, disk full). TERMINATE discards the partially loaded data and returns the table to its pre-LOAD state, but it requires sufficient log records to undo the partial load.

Determining When to REORG (LUW)

Query the REORGCHK catalog function to assess reorganization need:

CALL SYSPROC.REORGCHK_TB_STATS('T', 'MERIDIAN.ACCOUNTS');

This produces a report with formulas (F1 through F8) that flag whether table or index statistics suggest reorganization is advisable. An asterisk (*) next to a formula indicates the threshold is exceeded.

17.4 [z/OS] RUNSTATS

RUNSTATS collects statistical information about data in tablespaces and indexes and stores it in the DB2 catalog. The optimizer depends entirely on these statistics to choose access paths. Stale statistics are among the most common causes of poor query performance in DB2 environments.

Basic Syntax

RUNSTATS TABLESPACE MERIDIANDB.ACCOUNTS_TS
  TABLE(ALL)
  INDEX(ALL)
  KEYCARD
  FREQVAL COUNT 20 MOST
  HISTOGRAM NUMQUANTILES 100
  UPDATE ALL
  REPORT YES

Key Parameters Explained

TABLE(ALL) — Collect statistics for all tables in the tablespace. You can also specify individual tables with TABLE(schema.tablename).

INDEX(ALL) — Collect index statistics for all indexes defined on tables in the tablespace. Alternatively, specify individual indexes.

KEYCARD — Collects full key cardinality information. This tells the optimizer how many distinct values exist for each key prefix. For example, on a compound index (BRANCH_ID, ACCOUNT_TYPE, ACCOUNT_ID), KEYCARD records the number of distinct values for (BRANCH_ID), (BRANCH_ID, ACCOUNT_TYPE), and (BRANCH_ID, ACCOUNT_TYPE, ACCOUNT_ID). This is critical for the optimizer to estimate selectivity of predicates that use leading subsets of compound index keys.

FREQVAL COUNT 20 MOST — Collects the 20 most frequently occurring values for each column. This helps the optimizer recognize data skew. If 40% of transactions have STATUS='ACTIVE', the optimizer needs to know this rather than assuming uniform distribution. Without FREQVAL, the optimizer divides cardinality evenly — it would assume each STATUS value covers roughly 14% of rows if there are 7 distinct values, missing the fact that 'ACTIVE' covers 40%.

HISTOGRAM NUMQUANTILES 100 — Collects histogram statistics with 100 quantile divisions. Histograms provide the optimizer with data distribution information beyond simple min/max values. They reveal clusters, gaps, and skew patterns that dramatically improve range predicate estimation.

UPDATE ALL — Updates all statistics columns in the catalog, replacing previous values. The alternative UPDATE ACCESSPATH updates only the statistics used for access path selection, which is faster but less complete.

REPORT YES — Produces a statistics report in the SYSPRINT output. Useful for review and auditing.

Column Group Statistics

For correlated columns, individual column statistics are insufficient. Consider a table with COUNTRY and CURRENCY columns — knowing each independently does not tell the optimizer that COUNTRY='US' almost always implies CURRENCY='USD'. Column group statistics capture these correlations:

RUNSTATS TABLESPACE MERIDIANDB.TRANS_TS
  TABLE(MERIDIAN.TRANSACTIONS)
    COLGROUP(COUNTRY, CURRENCY)
      FREQVAL COUNT 50 MOST
    COLGROUP(BRANCH_ID, ACCOUNT_TYPE)
      FREQVAL COUNT 30 MOST
  INDEX(ALL)
  KEYCARD

The optimizer uses column group statistics to estimate the combined selectivity of multi-column predicates, avoiding the dangerous multiplication of individual selectivities that can underestimate result sizes by orders of magnitude.

Inline RUNSTATS with REORG

As shown in Section 17.2, you can embed RUNSTATS within a REORG operation using the STATISTICS keyword. This is the recommended approach because:

1. It eliminates a separate pass through the data, saving elapsed time
2. Statistics are collected on the freshly reorganized data, reflecting the new physical layout
3. The maintenance window is shorter

REORG TABLESPACE MERIDIANDB.ACCOUNTS_TS
  SHRLEVEL CHANGE
  STATISTICS TABLE(ALL) INDEX(ALL)
    KEYCARD
    FREQVAL COUNT 20 MOST
    HISTOGRAM NUMQUANTILES 100

Sampling

For very large tablespaces where a full RUNSTATS would take hours, you can use sampling:

RUNSTATS TABLESPACE MERIDIANDB.BIGTABLE_TS
  TABLE(ALL) SAMPLE 25
  INDEX(ALL)
  KEYCARD

SAMPLE 25 means RUNSTATS scans only 25% of the data pages and extrapolates statistics. Accuracy decreases with lower sample rates, but even 10% sampling produces statistics that are far better than stale or missing statistics.

RUNSTATS JCL

//RUNSTATS EXEC DSNUPROC,SYSTEM=DB2P,
//            UID='RUNSTATS.ACCTS',UTPROC=''
//DSNUPROC.SYSIN DD *
  RUNSTATS TABLESPACE MERIDIANDB.ACCOUNTS_TS
    TABLE(ALL)
    INDEX(ALL)
    KEYCARD
    FREQVAL COUNT 20 MOST
    HISTOGRAM NUMQUANTILES 100
    UPDATE ALL
    REPORT YES
/*

RUNSTATS requires no sort work datasets or intermediate storage — it reads the tablespace/index data and writes directly to catalog tables. This makes its JCL much simpler than REORG JCL.

17.5 [LUW] RUNSTATS

DB2 LUW's RUNSTATS serves the same purpose as its z/OS counterpart — updating the system catalog with current statistics — but uses different syntax and offers automatic statistics collection as an alternative.

Basic Table and Index Statistics

RUNSTATS ON TABLE meridian.accounts
  WITH DISTRIBUTION
  AND SAMPLED DETAILED INDEXES ALL
  ALLOW WRITE ACCESS;

WITH DISTRIBUTION — Collects distribution statistics (histograms) for all columns. This is the LUW equivalent of z/OS HISTOGRAM.

AND SAMPLED DETAILED INDEXES ALL — Collects detailed index statistics using sampling. DETAILED provides extended statistics including cluster ratio and page fetch estimates. SAMPLED uses sampling for efficiency on large indexes.

ALLOW WRITE ACCESS — Permits concurrent write access during statistics collection. The alternative ALLOW READ ACCESS blocks writes but is rarely necessary.

Column-Specific Statistics

You can target specific columns for distribution statistics:

RUNSTATS ON TABLE meridian.transactions
  ON COLUMNS (
    trans_date NUM_FREQVALUES 30 NUM_QUANTILES 100,
    trans_type NUM_FREQVALUES 20 NUM_QUANTILES 50,
    branch_id NUM_FREQVALUES 50 NUM_QUANTILES 100
  )
  AND DETAILED INDEXES ALL
  ALLOW WRITE ACCESS;

NUM_FREQVALUES — Number of most/least frequent values to collect. Equivalent to z/OS FREQVAL COUNT.

NUM_QUANTILES — Number of quantile divisions for the histogram. More quantiles provide finer granularity but increase catalog storage.

Column Group Statistics (LUW)

RUNSTATS ON TABLE meridian.transactions
  ON COLUMNS (
    (country, currency) NUM_FREQVALUES 50,
    (branch_id, account_type) NUM_FREQVALUES 30
  )
  AND DETAILED INDEXES ALL;

Enclosing column names in parentheses creates a column group, just like z/OS COLGROUP.

Automatic Statistics Collection

DB2 LUW supports automatic statistics collection through the AUTO_RUNSTATS database configuration parameter:

-- Enable automatic statistics
UPDATE DB CFG FOR meridiandb USING AUTO_RUNSTATS ON;

-- Verify configuration
GET DB CFG FOR meridiandb SHOW DETAIL | grep -i auto_runstats;

When enabled, DB2 monitors data modification activity and automatically runs RUNSTATS on tables whose statistics appear stale. This is convenient for development and small production environments, but for Meridian Bank's critical systems, we recommend explicit RUNSTATS with controlled scheduling. Automatic collection may run at inopportune times and may not collect the specific distribution statistics your most critical queries need.

Real-Time Statistics (LUW)

DB2 LUW maintains real-time statistics that are updated incrementally as data is modified:

-- Check real-time statistics
SELECT TABNAME, STATS_TIME, CARD AS CATALOG_CARD,
       STATS_ROWS_MODIFIED
FROM SYSCAT.TABLES
WHERE TABSCHEMA = 'MERIDIAN'
  AND STATS_ROWS_MODIFIED > 0;

Real-time statistics supplement catalog statistics between RUNSTATS runs. The optimizer can use them to detect significant data growth even when RUNSTATS has not been run recently.

Using ADMIN_CMD for RUNSTATS

CALL SYSPROC.ADMIN_CMD(
  'RUNSTATS ON TABLE meridian.accounts WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS'
);

This is the preferred method for scripted or scheduled RUNSTATS execution.

17.6 [z/OS] COPY Utility

The COPY utility creates image copies of DB2 tablespaces on z/OS. Image copies are the foundation of the recovery strategy — without them, recovery from a media failure is impossible.

Full Image Copy

A full image copy captures every page in the tablespace:

COPY TABLESPACE MERIDIANDB.ACCOUNTS_TS
  COPYDDN(COPY1, COPY2)
  SHRLEVEL REFERENCE
  FULL YES

COPYDDN(COPY1, COPY2) — Specifies up to four output datasets. COPY1 is the primary copy; COPY2 is the backup copy. Both are recorded in the SYSIBM.SYSCOPY catalog table. Best practice: write COPY1 to DASD for fast local recovery and COPY2 to tape for offsite disaster recovery.

SHRLEVEL REFERENCE — Allows read access during the copy. The tablespace is drained of writers at the start, the copy is taken, and writers are re-enabled. This produces a consistent point-in-time image.

SHRLEVEL CHANGE — Allows both read and write access during the copy. The copy includes pages modified during the copy process, and a log range is recorded to identify in-flight changes. Recovery from a SHRLEVEL CHANGE copy requires additional log application.

Incremental Image Copy

An incremental copy captures only pages modified since the last full or incremental copy:

COPY TABLESPACE MERIDIANDB.ACCOUNTS_TS
  COPYDDN(COPY1)
  SHRLEVEL REFERENCE
  FULL NO

FULL NO triggers an incremental copy. Incremental copies are much smaller and faster than full copies but increase recovery time because RECOVER must apply multiple incrementals on top of a full copy.

Concurrent Copy with FlashCopy

When running on storage subsystems that support FlashCopy (IBM DS8000, for example), DB2 can leverage instant copy technology:

COPY TABLESPACE MERIDIANDB.ACCOUNTS_TS
  COPYDDN(COPY1)
  SHRLEVEL REFERENCE
  FLASHCOPY YES

FLASHCOPY YES instructs DB2 to use the storage subsystem's instant copy feature. The copy completes in seconds regardless of tablespace size because it creates a point-in-time snapshot at the storage level. Data is physically copied in the background through copy-on-write mechanics.

This is transformative for large tablespaces. A 500 GB tablespace that takes 2 hours for a traditional copy completes its FlashCopy snapshot in under 10 seconds.

COPY JCL

//FULLCOPY EXEC DSNUPROC,SYSTEM=DB2P,
//            UID='COPY.ACCTS',UTPROC=''
//DSNUPROC.COPY1 DD DSN=MERIDIAN.COPY.ACCOUNTS.FULL.D260316,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(300,100))
//DSNUPROC.COPY2 DD DSN=MERIDIAN.COPY.ACCOUNTS.FULL.D260316.TAPE,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=TAPE
//DSNUPROC.SYSIN DD *
  COPY TABLESPACE MERIDIANDB.ACCOUNTS_TS
    COPYDDN(COPY1, COPY2)
    SHRLEVEL REFERENCE
    FULL YES
/*

COPY Pending Status

If a tablespace enters COPY-pending (COPY) status — for example, after a LOAD REPLACE LOG NO or REORG LOG NO — the tablespace is readable but not recoverable. You must take a full image copy to clear this status:

-DB2P DISPLAY DATABASE(MERIDIANDB) SPACENAM(ACCOUNTS_TS) RESTRICT

This shows any restricted states including COPY-pending. Taking a full image copy clears the pending status.

Copy Strategies

For Meridian Bank, we recommend a tiered approach:

17.7 [LUW] BACKUP DATABASE

DB2 LUW uses the BACKUP DATABASE command rather than a tablespace-level COPY utility. The conceptual goals are identical — create a recoverable image — but the granularity and mechanics differ.

Full Database Backup

BACKUP DATABASE meridiandb
  TO /db2backup/meridiandb
  WITH 4 BUFFERS
  BUFFER 4096
  PARALLELISM 4
  COMPRESS
  WITHOUT PROMPTING;

TO path — Specifies the backup destination. Can be a local directory, a network path, or a storage manager (TSM/Spectrum Protect).

WITH 4 BUFFERS / BUFFER 4096 — Controls I/O buffering. More buffers and larger buffer sizes improve throughput for large databases.

PARALLELISM 4 — Number of parallel backup agents. Increase this on systems with multiple I/O paths.

COMPRESS — Compresses the backup image. Typically achieves 50-70% compression, dramatically reducing storage requirements and backup time (CPU trades for I/O).

WITHOUT PROMPTING — Suppresses interactive prompts, required for scripted/scheduled execution.

Online Backup

For 24x7 availability, use ONLINE backup:

BACKUP DATABASE meridiandb ONLINE
  TO /db2backup/meridiandb
  INCLUDE LOGS
  WITH 4 BUFFERS
  BUFFER 4096
  PARALLELISM 4
  COMPRESS
  WITHOUT PROMPTING;

ONLINE — Allows full read/write access during backup. The database must be configured with LOGARCHMETH1 set to an archival method (not LOGRETAIN alone) for online backup.

INCLUDE LOGS — Includes the necessary archive logs in the backup image so that the backup is self-contained for recovery. Without this, you need the archive logs separately.

Incremental and Delta Backups

DB2 LUW supports two levels of incremental backup:

-- Incremental: all changes since last full backup
BACKUP DATABASE meridiandb ONLINE INCREMENTAL
  TO /db2backup/meridiandb
  INCLUDE LOGS COMPRESS WITHOUT PROMPTING;

-- Delta: changes since last backup of any type
BACKUP DATABASE meridiandb ONLINE INCREMENTAL DELTA
  TO /db2backup/meridiandb
  INCLUDE LOGS COMPRESS WITHOUT PROMPTING;

INCREMENTAL — Captures all pages modified since the last full backup. Recovery requires the full plus one incremental.

INCREMENTAL DELTA — Captures pages modified since the last backup of any kind (full, incremental, or delta). Recovery requires the full, the most recent incremental, and all deltas since that incremental.

Enable incremental backup support:

UPDATE DB CFG FOR meridiandb USING TRACKMOD ON;

Tablespace-Level Backup (LUW)

Unlike z/OS where COPY is inherently tablespace-level, LUW backup is database-level by default. However, you can back up individual tablespaces:

BACKUP DATABASE meridiandb
  TABLESPACE(accounts_ts, transactions_ts)
  ONLINE
  TO /db2backup/meridiandb
  INCLUDE LOGS COMPRESS WITHOUT PROMPTING;

Backup to Cloud/TSM

For enterprise environments using IBM Spectrum Protect (TSM):

BACKUP DATABASE meridiandb ONLINE
  TO TSM
  INCLUDE LOGS COMPRESS WITHOUT PROMPTING;

This requires the TSM client to be configured with proper server stanzas in the dsm.opt file.

Backup Verification

Always verify backups:

db2ckbkp -h /db2backup/meridiandb/MERIDIANDB.0.DB2.DBPART000.20260316120000.001

-- Or for a quick test restore:
RESTORE DATABASE meridiandb FROM /db2backup/meridiandb
  TAKEN AT 20260316120000
  INTO verify_restore_db
  WITHOUT ROLLING FORWARD
  WITHOUT PROMPTING;

17.8 [z/OS] RECOVER Utility

The RECOVER utility restores a tablespace or index to a consistent state after a media failure, data corruption, or logical error. It is the utility you hope to never use in anger but must be prepared to execute flawlessly.

Full Recovery (to Current)

The most common recovery scenario restores a tablespace to its most recent consistent state:

RECOVER TABLESPACE MERIDIANDB.ACCOUNTS_TS

This command triggers the following process:

1. DB2 searches the SYSIBM.SYSCOPY catalog to find the most recent full image copy
2. The full copy is restored to the tablespace
3. Any incremental copies since the full are applied in chronological order
4. Archive log records from the copy point forward are applied
5. Active log records are applied to bring the tablespace fully current

The result is a tablespace that is current as of the last committed transaction.

Point-in-Time Recovery

Sometimes you need to recover to a specific point — for example, to undo a disastrous DELETE statement:

RECOVER TABLESPACE MERIDIANDB.ACCOUNTS_TS
  TOCOPY MERIDIAN.COPY.ACCOUNTS.FULL.D260315

TOCOPY — Recovers to the point of a specific image copy. No log records are applied after the copy. This is the simplest point-in-time recovery.

RECOVER TABLESPACE MERIDIANDB.ACCOUNTS_TS
  TOLOGPOINT 00000123ABC4DEF0

TOLOGPOINT — Recovers to a specific log sequence number (LRSN in data sharing, RBA otherwise). This provides precise point-in-time recovery to any committed transaction boundary.

RECOVER TABLESPACE MERIDIANDB.ACCOUNTS_TS
  TORBA X'00000123ABC4DEF0'

TORBA — Recovers to a specific Relative Byte Address in the log. Used in non-data-sharing environments. Functionally equivalent to TOLOGPOINT for non-data-sharing.

Index Recovery

Indexes can be recovered independently:

RECOVER INDEX MERIDIAN.IX_ACCT_CUSTID

However, if the tablespace was recovered to a point in time, all associated indexes must be rebuilt:

REBUILD INDEX(ALL) TABLESPACE MERIDIANDB.ACCOUNTS_TS

After a point-in-time recovery, indexes are in REBUILD-pending (RBDP) status and cannot be used until rebuilt.

Recovery JCL

//RECOVER  EXEC DSNUPROC,SYSTEM=DB2P,
//            UID='RECOVER.ACCTS',UTPROC=''
//DSNUPROC.COPY1 DD DSN=MERIDIAN.COPY.ACCOUNTS.FULL.D260316,
//            DISP=OLD
//DSNUPROC.SYSIN DD *
  RECOVER TABLESPACE MERIDIANDB.ACCOUNTS_TS
/*

For recovery from tape, the JCL must include the tape unit and volume specifications for each copy dataset the RECOVER utility needs.

Recovery Considerations

Log availability is the most critical factor. If archive logs between your last image copy and the failure point have been deleted or are unavailable, full recovery is impossible. Meridian Bank maintains archive logs for 90 days and image copies for 30 days, with a rule that the oldest retained image copy must predate the oldest available archive log by at least one full backup cycle.

Recovery time depends on the volume of log records to apply. A tablespace with a day-old full copy and moderate transaction volume might recover in 20 minutes. The same tablespace with a week-old copy and heavy transaction volume could take 6 hours. This is why frequent image copies directly reduce recovery time.

Recover pending status (RECP) indicates that a tablespace requires recovery before it can be used. This status is set after a media failure is detected. Access attempts receive SQLCODE -904.

Fallback recovery is another important concept. If your primary image copy is damaged or unavailable, DB2 can use an alternate copy (COPY2) for recovery. This is why the dual-copy strategy — COPY1 to DASD, COPY2 to tape — is so valuable. If the DASD copy is on the same storage subsystem that experienced the failure, you still have the tape copy for recovery.

System-level restart recovery is distinct from the RECOVER utility. When DB2 restarts after an abnormal termination (crash), it automatically performs restart recovery — rolling forward committed transactions and rolling back uncommitted transactions from the active log. This does not require the DBA to invoke RECOVER. The RECOVER utility is specifically for media failures (lost or corrupted VSAM datasets) and logical errors (accidental data modifications).

[LUW] RECOVER / RESTORE and ROLLFORWARD

On LUW, recovery is a two-step process:

-- Step 1: Restore from backup
RESTORE DATABASE meridiandb
  FROM /db2backup/meridiandb
  TAKEN AT 20260316120000
  WITHOUT PROMPTING;

-- Step 2: Roll forward to point in time or end of logs
ROLLFORWARD DATABASE meridiandb
  TO 2026-03-16-14.30.00
  AND COMPLETE
  OVERFLOW LOG PATH(/db2archlog/meridiandb);

RESTORE reads the backup image and recreates the database (or specific tablespaces) as of the backup time.

ROLLFORWARD applies archive and active log records to advance the database forward in time. TO END OF LOGS rolls forward to the last available log record. A specific timestamp provides point-in-time recovery.

-- Roll forward to end of logs (most common for disaster recovery)
ROLLFORWARD DATABASE meridiandb TO END OF LOGS AND COMPLETE;

17.9 LOAD — High-Speed Data Ingestion

The LOAD utility provides the fastest method for inserting large volumes of data into DB2 tables. It bypasses the SQL engine and writes directly to tablespace pages, achieving throughput rates 5 to 20 times faster than equivalent INSERT statements.

[z/OS] LOAD Utility

LOAD DATA INDDN(SYSREC) LOG YES
  RESUME YES
  INTO TABLE MERIDIAN.TRANSACTIONS
  (TRANS_ID      POSITION(1)   CHAR(20),
   ACCOUNT_ID    POSITION(21)  CHAR(15),
   TRANS_DATE    POSITION(36)  DATE EXTERNAL,
   TRANS_TYPE    POSITION(46)  CHAR(3),
   AMOUNT        POSITION(49)  DECIMAL EXTERNAL(13,2),
   DESCRIPTION   POSITION(62)  CHAR(100))
  ENFORCE CONSTRAINTS
  STATISTICS TABLE(ALL) INDEX(ALL)
    KEYCARD
    FREQVAL COUNT 20

INDDN(SYSREC) — Specifies the DD name for the input dataset containing the data to load.

LOG YES — Log the load operation for recoverability. LOG NO is faster but puts the tablespace in COPY-pending status.

RESUME YES — Appends data to existing rows in the table. RESUME NO (the default) replaces all existing data — equivalent to a TRUNCATE plus LOAD.

REPLACE — An alternative to RESUME NO, explicitly replacing existing data:

LOAD DATA INDDN(SYSREC) LOG NO REPLACE
  INTO TABLE MERIDIAN.STAGING_TRANSACTIONS
  ...

ENFORCE CONSTRAINTS — Validates referential integrity constraints during the load. Without this, the table enters CHECK-pending status and you must run CHECK DATA afterward.

Field specifications — Each field in the input file is mapped to a column with its position, data type, and length. This fixed-position format is traditional for z/OS flat files.

z/OS LOAD JCL

//LOADDATA EXEC DSNUPROC,SYSTEM=DB2P,
//            UID='LOAD.TRANS',UTPROC=''
//DSNUPROC.SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(50,25))
//DSNUPROC.SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(50,25))
//DSNUPROC.SYSREC DD DSN=MERIDIAN.DAILY.TRANSACTIONS,
//            DISP=SHR
//DSNUPROC.SYSERR DD DSN=MERIDIAN.LOAD.TRANS.ERRORS,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(10,5))
//DSNUPROC.SYSMAP DD DSN=MERIDIAN.LOAD.TRANS.MAP,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(10,5))
//DSNUPROC.SYSIN DD *
  LOAD DATA INDDN(SYSREC) LOG YES
    RESUME YES
    INTO TABLE MERIDIAN.TRANSACTIONS
    WHEN(46:48) = 'DEP' OR
    WHEN(46:48) = 'WDR' OR
    WHEN(46:48) = 'TRF'
    (TRANS_ID      POSITION(1)   CHAR(20),
     ACCOUNT_ID    POSITION(21)  CHAR(15),
     TRANS_DATE    POSITION(36)  DATE EXTERNAL,
     TRANS_TYPE    POSITION(46)  CHAR(3),
     AMOUNT        POSITION(49)  DECIMAL EXTERNAL(13,2),
     DESCRIPTION   POSITION(62)  CHAR(100))
    ENFORCE CONSTRAINTS
/*

The WHEN clause filters input records, loading only those matching the specified conditions. Records that fail the condition are discarded (written to the SYSERR dataset for review).

[LUW] LOAD

DB2 LUW's LOAD command has a different syntax but the same high-performance objectives:

LOAD FROM '/data/meridian/daily_transactions.csv' OF DEL
  MODIFIED BY COLDEL, DATEFORMAT="YYYY-MM-DD" TIMESTAMPFORMAT="YYYY-MM-DD HH:MM:SS"
  METHOD P (1, 2, 3, 4, 5, 6)
  MESSAGES '/logs/load_trans.msg'
  INSERT INTO meridian.transactions
  (trans_id, account_id, trans_date, trans_type, amount, description)
  FOR EXCEPTION meridian.trans_load_exceptions
  ALLOW READ ACCESS
  STATISTICS USE PROFILE;

FROM path OF DEL — Loads from a delimited file. Other formats: ASC (fixed-position), IXF (DB2 exchange format), CURSOR (load from a query result).

MODIFIED BY — Specifies format modifiers. COLDEL sets the column delimiter (comma by default for DEL format). Date and timestamp formats control parsing.

METHOD P — Positional method, mapping file columns by position (1st column to 1st listed column, etc.). METHOD N maps by column name.

INSERT — Appends to existing data. Alternatives: REPLACE (truncate and reload), RESTART (restart a failed load), TERMINATE (roll back a failed load).

FOR EXCEPTION — Rows that violate constraints are written to the exception table rather than causing the load to fail.

ALLOW READ ACCESS — Permits concurrent read access during the load. ALLOW NO ACCESS is more restrictive but marginally faster.

STATISTICS USE PROFILE — Collects statistics using the statistics profile defined for the table. This eliminates a separate RUNSTATS step.

Loading from a Cursor (LUW)

One of LUW's most powerful features is loading from a cursor — the result of a SELECT statement:

DECLARE staging_cursor CURSOR FOR
  SELECT trans_id, account_id, trans_date, trans_type, amount, description
  FROM meridian.staging_transactions
  WHERE load_batch = '2026-03-16';

LOAD FROM staging_cursor OF CURSOR
  INSERT INTO meridian.transactions
  (trans_id, account_id, trans_date, trans_type, amount, description)
  ALLOW READ ACCESS
  STATISTICS USE PROFILE;

This is ideal for ETL processes where data is staged, validated, and then loaded into production tables. It avoids the overhead of writing to and reading from intermediate files.

LOAD Performance: LOAD vs. INSERT

For Meridian Bank's daily transaction load of approximately 50,000 records:

The difference becomes dramatic at scale. For a 10-million-row initial load, INSERT might take 18 hours while LOAD completes in 40 minutes.

LOAD achieves this performance by: - Writing directly to tablespace pages, bypassing buffer pool management - Building indexes in a single sort pass rather than incrementally - Generating minimal log records (or none with LOG NO) - Using parallel I/O for input reading and tablespace writing

17.10 UNLOAD and EXPORT

UNLOAD (z/OS) and EXPORT (LUW) extract data from DB2 tables into flat files or exchange formats for migration, reporting, archival, or cross-platform transfers.

[z/OS] UNLOAD Utility

UNLOAD TABLESPACE MERIDIANDB.ACCOUNTS_TS
  FROM TABLE MERIDIAN.ACCOUNTS
  HEADER NONE
  (ACCOUNT_ID,
   CUSTOMER_ID,
   ACCOUNT_TYPE,
   BALANCE,
   OPEN_DATE,
   STATUS)

This produces a fixed-format sequential dataset with the specified columns. The output layout matches the LOAD utility's expected input format, making UNLOAD/LOAD a natural pair for data movement.

z/OS UNLOAD with Selection

UNLOAD TABLESPACE MERIDIANDB.TRANS_TS
  FROM TABLE MERIDIAN.TRANSACTIONS
  HEADER NONE
  WHEN(TRANS_DATE >= '2025-01-01' AND
       TRANS_DATE <  '2026-01-01')
  (TRANS_ID,
   ACCOUNT_ID,
   TRANS_DATE,
   TRANS_TYPE,
   AMOUNT)

The WHEN clause filters rows during unload, extracting only the desired subset. This is far more efficient than unloading the entire table and filtering afterward.

z/OS UNLOAD JCL

//UNLOAD   EXEC DSNUPROC,SYSTEM=DB2P,
//            UID='UNLOAD.ACCTS',UTPROC=''
//DSNUPROC.SYSREC DD DSN=MERIDIAN.UNLOAD.ACCOUNTS.DATA,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,SPACE=(CYL,(100,50))
//DSNUPROC.SYSPUNCH DD DSN=MERIDIAN.UNLOAD.ACCOUNTS.SYSPUNCH,
//            DISP=(NEW,CATLG,CATLG),
//            UNIT=SYSDA,SPACE=(TRK,(5,5))
//DSNUPROC.SYSIN DD *
  UNLOAD TABLESPACE MERIDIANDB.ACCOUNTS_TS
    FROM TABLE MERIDIAN.ACCOUNTS
    HEADER NONE
/*

The SYSPUNCH dataset receives a generated LOAD control statement that can be used to reload the data — a convenient feature for round-trip data movement.

[LUW] EXPORT

EXPORT TO '/data/meridian/accounts_export.csv' OF DEL
  MODIFIED BY COLDEL, NOCHARDEL TIMESTAMPFORMAT="YYYY-MM-DD HH:MM:SS"
  MESSAGES '/logs/export_accounts.msg'
  SELECT account_id, customer_id, account_type, balance, open_date, status
  FROM meridian.accounts
  WHERE status = 'ACTIVE';

OF DEL — Delimited format. Alternatives: IXF (recommended for DB2-to-DB2 transfers, preserves metadata), ASC (fixed-position), WSF (worksheet format).

MODIFIED BY — Format modifiers. NOCHARDEL suppresses character delimiters (quotation marks around strings). Useful when the downstream system does not expect them.

MESSAGES — File for export messages and row counts.

The SELECT statement provides full SQL flexibility for filtering, joining, and transforming data during export.

IXF Format for Cross-Platform Migration

For moving data between z/OS and LUW (or between LUW instances), IXF format preserves column data types and metadata:

-- Export from source
EXPORT TO '/data/meridian/accounts.ixf' OF IXF
  MESSAGES '/logs/export_accounts.msg'
  SELECT * FROM meridian.accounts;

-- Import at destination (creates table if needed)
IMPORT FROM '/data/meridian/accounts.ixf' OF IXF
  COMMITCOUNT 5000
  MESSAGES '/logs/import_accounts.msg'
  CREATE INTO meridian.accounts_imported;

Note that IMPORT uses the SQL engine (INSERT under the covers) and is significantly slower than LOAD. For large datasets, export to IXF, then use LOAD FROM ... OF IXF for maximum performance.

17.11 Utility Chaining and Scheduling

Individual utilities are powerful, but their true value emerges when chained into automated maintenance workflows that execute during planned maintenance windows.

The Standard Maintenance Chain

The canonical sequence for a tablespace maintenance cycle is:

REORG → RUNSTATS → COPY

Each step depends on the previous: - REORG eliminates fragmentation and restores clustering order - RUNSTATS captures statistics on the freshly reorganized data - COPY creates a backup of the clean, reorganized tablespace with current statistics

Running them out of order produces suboptimal results: - RUNSTATS before REORG: Statistics describe fragmented data that is about to change - COPY before REORG: The backup captures a fragmented state; after REORG, you need a new copy anyway - COPY before RUNSTATS: Less impactful, but if you discover statistics-related access path problems and need to rebind, you want the copy to postdate the statistics

[z/OS] Chaining with LISTDEF and TEMPLATE

Modern z/OS DB2 provides LISTDEF and TEMPLATE utilities to streamline multi-tablespace maintenance:

//MAINTAIN EXEC DSNUPROC,SYSTEM=DB2P,
//            UID='MAINT.WEEKLY',UTPROC=''
//DSNUPROC.SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(200,100))
//DSNUPROC.SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(200,100))
//DSNUPROC.SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(200,100))
//DSNUPROC.SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(200,100))
//DSNUPROC.SYSIN DD *
  LISTDEF CRITICAL_TS
    INCLUDE TABLESPACE MERIDIANDB.ACCOUNTS_TS
    INCLUDE TABLESPACE MERIDIANDB.TRANS_TS
    INCLUDE TABLESPACE MERIDIANDB.CUSTOMER_TS

  TEMPLATE SYSREC
    DSN(MERIDIAN.REORG.&SN..SYSREC)
    UNIT(SYSDA) DISP(NEW,CATLG,CATLG)
    SPACE(CYL,(200,100))

  TEMPLATE COPY1
    DSN(MERIDIAN.COPY.&SN..D&DATE..FULL)
    UNIT(SYSDA) DISP(NEW,CATLG,CATLG)
    SPACE(CYL,(300,100))

  REORG TABLESPACE LIST CRITICAL_TS
    SHRLEVEL CHANGE
    FASTSWITCH YES
    LOG YES
    SORTDATA YES
    SORTKEYS YES
    STATISTICS TABLE(ALL) INDEX(ALL)
      KEYCARD
      FREQVAL COUNT 20

  COPY TABLESPACE LIST CRITICAL_TS
    COPYDDN(COPY1)
    SHRLEVEL REFERENCE
    FULL YES
/*

LISTDEF defines a named list of tablespaces. The REORG and COPY utilities reference this list, processing each tablespace in sequence.

TEMPLATE defines dynamic dataset name patterns using substitution variables: - &SN. — Tablespace name - &DATE. — Current date - &TIME. — Current time

This eliminates the need to code separate JCL for each tablespace.

Note that in this example, RUNSTATS is handled inline by the REORG STATISTICS clause. The COPY follows to back up the reorganized data.

[LUW] Scripted Maintenance with Shell Scripts

On LUW, maintenance is typically automated through shell scripts and system schedulers (cron, Tivoli Workload Scheduler, or similar):

#!/bin/bash
# meridian_weekly_maintenance.sh
# Weekly maintenance for critical Meridian Bank tables

DB2INST=db2inst1
DATABASE=meridiandb
LOGFILE=/db2logs/maintenance/weekly_$(date +%Y%m%d).log

echo "=== Meridian Bank Weekly Maintenance ===" >> $LOGFILE
echo "Start: $(date)" >> $LOGFILE

# Connect to database
db2 connect to $DATABASE >> $LOGFILE 2>&1

# Array of critical tables
TABLES=("meridian.accounts" "meridian.transactions" "meridian.customers")

for TABLE in "${TABLES[@]}"; do
    echo "--- Processing $TABLE ---" >> $LOGFILE

    # REORG
    echo "REORG $TABLE ..." >> $LOGFILE
    db2 "REORG TABLE $TABLE INPLACE ALLOW WRITE ACCESS START" >> $LOGFILE 2>&1

    # Wait for inplace reorg to complete
    REORG_STATUS="STARTED"
    while [ "$REORG_STATUS" != "COMPLETED" ]; do
        sleep 30
        REORG_STATUS=$(db2 -x "SELECT VARCHAR(REORG_STATUS,20) FROM SYSIBMADM.SNAPTAB_REORG WHERE TABSCHEMA='MERIDIAN' AND TABNAME='$(echo $TABLE | cut -d. -f2 | tr '[:lower:]' '[:upper:]')'")
    done
    echo "REORG complete for $TABLE" >> $LOGFILE

    # RUNSTATS
    echo "RUNSTATS $TABLE ..." >> $LOGFILE
    db2 "RUNSTATS ON TABLE $TABLE WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL ALLOW WRITE ACCESS" >> $LOGFILE 2>&1
    echo "RUNSTATS complete for $TABLE" >> $LOGFILE
done

# Full database backup after maintenance
echo "--- Full database backup ---" >> $LOGFILE
db2 "BACKUP DATABASE $DATABASE ONLINE TO /db2backup/$DATABASE INCLUDE LOGS WITH 4 BUFFERS BUFFER 4096 PARALLELISM 4 COMPRESS WITHOUT PROMPTING" >> $LOGFILE 2>&1

db2 connect reset >> $LOGFILE 2>&1

echo "End: $(date)" >> $LOGFILE
echo "=== Maintenance Complete ===" >> $LOGFILE

Schedule with cron for every Sunday at 2 AM:

0 2 * * 0 /db2scripts/meridian_weekly_maintenance.sh

Maintenance Windows

For Meridian Bank, we define three maintenance tiers:

Tier 1 — Daily (11 PM to 1 AM) - RUNSTATS on high-churn tables (TRANSACTIONS, DAILY_BALANCES) - Incremental image copy / incremental backup of all critical tablespaces - Archive log management

Tier 2 — Weekly (Sunday 1 AM to 5 AM) - REORG with inline statistics on critical OLTP tables - Full image copy / full database backup - Index rebuild for indexes with high leaf page splits

Tier 3 — Monthly (First Sunday 12 AM to 6 AM) - REORG all tablespaces (including reference and archive) - Full statistics collection with histograms and column groups - Full image copy to both DASD and tape - Test recovery of one critical tablespace to verify backup chain integrity

17.12 The Meridian Bank Maintenance Plan

Let us now synthesize everything into a concrete maintenance plan for Meridian National Bank. This plan covers both the z/OS mainframe environment (core banking) and the LUW environment (analytics and digital channels).

Environment Summary

z/OS Maintenance Schedule

Daily — RUNSTATS + Incremental Copy (10:30 PM)

//DAILY    EXEC DSNUPROC,SYSTEM=DB2P,
//            UID='DAILY.MAINT',UTPROC=''
//DSNUPROC.SYSIN DD *
  LISTDEF DAILY_TS
    INCLUDE TABLESPACE MERIDIANDB.TRANS_TS
    INCLUDE TABLESPACE MERIDIANDB.DAILYBAL_TS

  TEMPLATE COPY1
    DSN(MERIDIAN.COPY.&SN..D&DATE..INCR)
    UNIT(SYSDA) DISP(NEW,CATLG,CATLG)
    SPACE(CYL,(100,50))

  RUNSTATS TABLESPACE LIST DAILY_TS
    TABLE(ALL)
    INDEX(ALL)
    KEYCARD
    FREQVAL COUNT 20
    UPDATE ALL

  COPY TABLESPACE LIST DAILY_TS
    COPYDDN(COPY1)
    SHRLEVEL REFERENCE
    FULL NO
/*

Weekly — REORG + Stats + Full Copy (Sunday 1:00 AM)

//WEEKLY   EXEC DSNUPROC,SYSTEM=DB2P,
//            UID='WEEKLY.MAINT',UTPROC=''
//DSNUPROC.SORTWK01 DD UNIT=SYSDA,SPACE=(CYL,(200,100))
//DSNUPROC.SORTWK02 DD UNIT=SYSDA,SPACE=(CYL,(200,100))
//DSNUPROC.SORTWK03 DD UNIT=SYSDA,SPACE=(CYL,(200,100))
//DSNUPROC.SORTWK04 DD UNIT=SYSDA,SPACE=(CYL,(200,100))
//DSNUPROC.SYSIN DD *
  LISTDEF WEEKLY_TS
    INCLUDE TABLESPACE MERIDIANDB.ACCOUNTS_TS
    INCLUDE TABLESPACE MERIDIANDB.TRANS_TS
    INCLUDE TABLESPACE MERIDIANDB.CUSTOMER_TS
    INCLUDE TABLESPACE MERIDIANDB.LOAN_TS
    INCLUDE TABLESPACE MERIDIANDB.DAILYBAL_TS

  TEMPLATE SYSREC
    DSN(MERIDIAN.REORG.&SN..SYSREC)
    UNIT(SYSDA) DISP(NEW,CATLG,CATLG)
    SPACE(CYL,(300,100))

  TEMPLATE SORTOUT
    DSN(MERIDIAN.REORG.&SN..SORTOUT)
    UNIT(SYSDA) DISP(NEW,CATLG,CATLG)
    SPACE(CYL,(300,100))

  TEMPLATE COPY1
    DSN(MERIDIAN.COPY.&SN..D&DATE..FULL)
    UNIT(SYSDA) DISP(NEW,CATLG,CATLG)
    SPACE(CYL,(400,150))

  TEMPLATE COPY2
    DSN(MERIDIAN.COPY.&SN..D&DATE..FULL.TAPE)
    UNIT(TAPE) DISP(NEW,CATLG,CATLG)

  REORG TABLESPACE LIST WEEKLY_TS
    SHRLEVEL CHANGE
    FASTSWITCH YES
    LOG YES
    SORTDATA YES
    SORTKEYS YES
    STATISTICS TABLE(ALL) INDEX(ALL)
      KEYCARD
      FREQVAL COUNT 20
      HISTOGRAM NUMQUANTILES 100

  COPY TABLESPACE LIST WEEKLY_TS
    COPYDDN(COPY1, COPY2)
    SHRLEVEL REFERENCE
    FULL YES
/*

LUW Maintenance Schedule

Daily — RUNSTATS + Incremental Backup (11:00 PM)

#!/bin/bash
# meridian_daily_maintenance_luw.sh

DB=meridiandb
LOG=/db2logs/maintenance/daily_$(date +%Y%m%d).log

echo "Daily maintenance started: $(date)" >> $LOG
db2 connect to $DB >> $LOG 2>&1

# RUNSTATS on high-churn tables
for TBL in meridian.sessions meridian.transfers meridian.notifications; do
    db2 "RUNSTATS ON TABLE $TBL WITH DISTRIBUTION AND SAMPLED DETAILED INDEXES ALL ALLOW WRITE ACCESS" >> $LOG 2>&1
done

# Incremental backup
db2 "BACKUP DATABASE $DB ONLINE INCREMENTAL TO /db2backup/$DB INCLUDE LOGS COMPRESS WITHOUT PROMPTING" >> $LOG 2>&1

db2 connect reset >> $LOG 2>&1
echo "Daily maintenance completed: $(date)" >> $LOG

Weekly — REORG + Full Backup (Sunday 2:00 AM)

#!/bin/bash
# meridian_weekly_maintenance_luw.sh

DB=meridiandb
LOG=/db2logs/maintenance/weekly_$(date +%Y%m%d).log

echo "Weekly maintenance started: $(date)" >> $LOG
db2 connect to $DB >> $LOG 2>&1

# REORG critical tables (inplace for online access)
for TBL in meridian.sessions meridian.transfers meridian.notifications \
           meridian.fact_transactions meridian.dim_accounts; do
    db2 "REORG TABLE $TBL INPLACE ALLOW WRITE ACCESS START" >> $LOG 2>&1
    # Monitor completion
    while true; do
        STATUS=$(db2 -x "SELECT REORG_STATUS FROM SYSIBMADM.SNAPTAB_REORG WHERE TABSCHEMA='$(echo $TBL | cut -d. -f1 | tr a-z A-Z)' AND TABNAME='$(echo $TBL | cut -d. -f2 | tr a-z A-Z)'" 2>/dev/null | tr -d ' ')
        [ "$STATUS" = "COMPLETED" ] || [ -z "$STATUS" ] && break
        sleep 15
    done
    # RUNSTATS after REORG
    db2 "RUNSTATS ON TABLE $TBL WITH DISTRIBUTION AND DETAILED INDEXES ALL ALLOW WRITE ACCESS" >> $LOG 2>&1
done

# Full online backup
db2 "BACKUP DATABASE $DB ONLINE TO /db2backup/$DB INCLUDE LOGS WITH 4 BUFFERS BUFFER 4096 PARALLELISM 4 COMPRESS WITHOUT PROMPTING" >> $LOG 2>&1

db2 connect reset >> $LOG 2>&1
echo "Weekly maintenance completed: $(date)" >> $LOG

Nightly ETL Load Process (Analytics)

The analytics environment receives a nightly load from the core banking system:

#!/bin/bash
# meridian_nightly_etl.sh

DB=meridiandb
DATADIR=/data/meridian/etl
LOG=/db2logs/etl/nightly_$(date +%Y%m%d).log

echo "Nightly ETL started: $(date)" >> $LOG
db2 connect to $DB >> $LOG 2>&1

# Load daily transactions into fact table
db2 "LOAD FROM $DATADIR/daily_transactions_$(date +%Y%m%d).csv OF DEL
  MODIFIED BY COLDEL, DATEFORMAT=\"YYYY-MM-DD\" TIMESTAMPFORMAT=\"YYYY-MM-DD HH:MM:SS\"
  MESSAGES /db2logs/etl/load_trans_$(date +%Y%m%d).msg
  INSERT INTO meridian.fact_transactions
  FOR EXCEPTION meridian.fact_trans_exceptions
  ALLOW READ ACCESS
  STATISTICS USE PROFILE" >> $LOG 2>&1

# Check for exceptions
EXCEPTIONS=$(db2 -x "SELECT COUNT(*) FROM meridian.fact_trans_exceptions" 2>/dev/null)
if [ "$EXCEPTIONS" -gt 0 ]; then
    echo "WARNING: $EXCEPTIONS exception rows found" >> $LOG
    # Alert DBA team
    mail -s "ETL Exception Alert: $EXCEPTIONS rows" dba-team@meridianbank.com < /dev/null
fi

db2 connect reset >> $LOG 2>&1
echo "Nightly ETL completed: $(date)" >> $LOG

Monitoring and Alerting

Effective maintenance requires monitoring. Key metrics to track:

Fragmentation indicators: - z/OS: REORG REPORTONLY output, NEARINDREF/FARINDREF in RTS - LUW: REORGCHK output, OVERFLOW and F1-F8 flags

Statistics freshness: - z/OS: SYSIBM.SYSTABLESPACESTATS.REORGINSERTS / REORGUPDATES / REORGDELETES - LUW: SYSCAT.TABLES.STATS_TIME, STATS_ROWS_MODIFIED

Backup recency: - z/OS: SYSIBM.SYSCOPY (latest ICTYPE='F' and ICTYPE='I' timestamps) - LUW: db2adutl query (list backup images and timestamps)

Recovery window: - Time between last full backup and now - Estimated recovery time based on log volume since last backup

Meridian Bank's monitoring triggers: - ALERT if any critical tablespace has not had RUNSTATS in 48 hours - ALERT if any critical tablespace has not had a full image copy in 8 days - ALERT if REORG REPORTONLY shows disorganization > 30% - CRITICAL if any tablespace is in COPY-pending, CHECK-pending, or RECOVER-pending status

Spaced Review: Connections to Earlier Chapters

From Chapter 3: Your First Queries

In Chapter 3, you wrote your first SELECT statement and marveled at how quickly DB2 returned results. Now you understand why it was fast — the optimizer chose an efficient access path based on catalog statistics. When those statistics go stale, the same simple query can become inexplicably slow. RUNSTATS is what keeps Chapter 3's queries fast.

From Chapter 14: Physical Storage — Tablespaces, Buffer Pools, and Storage Groups

Chapter 14 introduced tablespaces as the physical containers for your data. You learned about page sizes, extent sizes, and buffer pools. REORG operates directly on these physical structures — it reorganizes data within tablespace pages, reclaims fragmented extents, and restores the clustering sequence you designed in Chapter 14. A well-designed physical layout loses its benefits without regular REORG.

From Chapter 15: Indexing Strategies

Chapter 15 explored index types, clustering, and access path selection. Every index you designed there depends on two things: the physical ordering maintained by REORG and the statistical descriptions maintained by RUNSTATS. A clustering index on TRANS_DATE provides optimal range scan performance only if the data is physically clustered (REORG) and the optimizer knows this (RUNSTATS KEYCARD and cluster ratio statistics).

Chapter Summary

The six utilities covered in this chapter — REORG, RUNSTATS, COPY/BACKUP, RECOVER, LOAD, and UNLOAD/EXPORT — form the operational backbone of DB2 administration. They are not optional maintenance tasks that you will get around to someday; they are the essential practices that separate a well-run production database from a slow, fragile system waiting to fail.

Key principles to carry forward:

1. The maintenance chain is REORG, then RUNSTATS, then COPY. Always in that order. Inline statistics with REORG can combine the first two steps.

2. SHRLEVEL CHANGE (z/OS) and INPLACE / ONLINE (LUW) enable maintenance without outages. There is no excuse for skipping maintenance because "we can't afford the downtime."

3. Statistics drive the optimizer. Stale statistics cause bad access paths. RUNSTATS with KEYCARD, FREQVAL, and HISTOGRAM gives the optimizer the information it needs.

4. Backups are meaningless without tested recovery. Take copies regularly, but also practice recovery. Know your recovery time for each critical tablespace before disaster strikes.

5. LOAD outperforms INSERT by 5-20x for bulk operations. Use it for any data movement exceeding a few thousand rows.

6. Automate everything. Manual maintenance is sporadic maintenance. JCL with LISTDEF/TEMPLATE (z/OS) and shell scripts with cron (LUW) ensure consistency.

In the next chapter, we will build on this foundation to explore monitoring, performance tuning, and the diagnostic tools that help you identify which tablespaces need attention before problems reach production.