Chapter 18: Backup, Recovery, and Logging — Protecting Data Against Every Failure Mode

Every DBA has two types of days: days when everything works, and the day they need to recover. This chapter prepares you for the second kind.

You have spent the preceding chapters learning how to create databases, write sophisticated SQL, design schemas, tune performance, and run utilities. All of that work — every table, every index, every stored procedure, every row of customer data — is worthless if it can be destroyed by a hardware failure, a software bug, a careless DELETE statement, or a natural disaster and you cannot get it back.

Backup and recovery is the DBA's prime directive. It is not glamorous work. Nobody congratulates you when backups complete successfully at 2:00 AM. Nobody notices when log archival runs without incident for months on end. But the moment a disk fails, the moment someone runs DELETE FROM ACCOUNT without a WHERE clause, the moment an entire data center loses power — that is the moment your career is defined. You either recover the data, or you explain to the CEO why it is gone.

This chapter is the longest and most detailed in the administration section, and deliberately so. We will cover DB2's recovery architecture from first principles — the write-ahead logging protocol that makes everything else possible — and then work through every recovery scenario you are likely to encounter across both z/OS and LUW platforms. We will not hand-wave. You will see actual JCL, actual shell commands, actual SQL, and actual sizing calculations. By the end, you will design a complete backup and recovery strategy for Meridian National Bank.

Take this chapter seriously. Read it twice if you need to. Practice every exercise. The day you need this knowledge, there will be no time to learn it.

Learning Objectives

After completing this chapter, you will be able to:

1. Explain the write-ahead logging (WAL) protocol and articulate why it is the foundation of DB2's entire recovery architecture.
2. Configure active and archive logs on both z/OS and LUW, including sizing calculations based on workload characteristics.
3. Perform full database and tablespace-level backup and recovery on both platforms.
4. Execute point-in-time recovery scenarios and understand the constraints involved.
5. Explain crash recovery and media recovery, including the undo/redo phases of restart processing.
6. Design and document a comprehensive backup/recovery strategy for a production banking application.

18.1 Why Recovery Matters — The DBA's Prime Directive

Before we touch a single configuration parameter or recovery command, we need to establish why this topic deserves an entire chapter and then some. We need to understand what can go wrong, what is at stake, and how the industry measures recovery capability.

18.1.1 The Failure Scenarios

Data loss comes from many directions. Here are the categories every DBA must plan for:

Hardware Failures - A single disk in a RAID array fails (common — happens weekly in large data centers) - An entire storage array becomes unavailable - A memory DIMM fails, corrupting in-flight data - A controller card fails, making an entire I/O path unavailable

Software Failures - DB2 itself crashes due to a bug or resource exhaustion - The operating system panics or crashes - A storage subsystem firmware bug corrupts data silently - An application bug writes garbage data through valid SQL

Human Errors - A developer runs an unqualified DELETE or UPDATE in production - A DBA drops the wrong table or tablespace - A migration script is run against the wrong database - Someone accidentally formats a disk that contains database files

Environmental Failures - Power failure to the data center - Network failure isolating the database server - Fire, flood, or natural disaster destroying the physical facility - Cooling failure causing emergency shutdown

Security Incidents - Ransomware encrypting database files - A malicious insider deliberately corrupting data - SQL injection resulting in data destruction

Each of these scenarios requires a different recovery approach. Some require only that DB2 restart itself. Others require restoring from backup. Still others require activating an entire disaster recovery site hundreds of miles away. A complete recovery strategy addresses all of them.

18.1.2 RPO and RTO — The Language of Recovery

Two acronyms dominate every recovery planning conversation:

RPO — Recovery Point Objective: How much data can you afford to lose? RPO is measured in time. An RPO of one hour means you can tolerate losing up to one hour of transactions. An RPO of zero means you cannot lose a single committed transaction.

RTO — Recovery Time Objective: How long can you afford to be down? RTO is measured in time. An RTO of four hours means the business can survive with the database unavailable for up to four hours. An RTO of five minutes means you need near-instantaneous failover.

For Meridian National Bank, the requirements are stringent:

These numbers drive every architectural decision in this chapter. An RPO of zero requires synchronous logging to redundant storage. An RTO of 15 minutes requires pre-staged recovery environments. An RPO of 24 hours allows simple nightly backups.

18.1.3 The Cost of Data Loss in Banking

The financial services industry faces unique pressures around data loss:

• Regulatory penalties: Banking regulators (OCC, FDIC, PRA) can impose fines for data loss that affects customer accounts. In severe cases, regulators can revoke a banking charter.
• Legal liability: If a customer's account balance is lost and cannot be reconstructed, the bank is liable for that amount plus damages.
• Audit trail requirements: Banks must maintain complete transaction histories for years. Gaps in the audit trail trigger regulatory action.
• Reputational damage: News of data loss at a bank causes customer flight. Trust, once broken, is extraordinarily difficult to rebuild.
• Operational paralysis: If the core banking system is down, the bank cannot process deposits, withdrawals, loans, or payments. Every minute of downtime has a direct revenue impact.

This is why recovery is not an afterthought. It is the foundation on which everything else is built.

18.2 Write-Ahead Logging — DB2's Foundation

Every recovery mechanism in DB2 — crash recovery, point-in-time recovery, media recovery, disaster recovery — depends on a single protocol: Write-Ahead Logging (WAL). If you understand WAL, everything else in this chapter will make sense. If you skip this section, nothing else will.

18.2.1 The WAL Protocol

The write-ahead logging protocol is deceptively simple:

Before any change to a data page is written to disk, the log record describing that change must first be written to the recovery log and forced to stable storage.

That is the entire protocol. Three key points deserve emphasis:

1. The log record is written first. Not simultaneously, not eventually — first. The log record describing the change hits persistent storage before the changed data page hits persistent storage.

2. The log record describes the change, not the result. A log record says "page 4782, offset 200, was changed from value X to value Y." It contains enough information to both redo the change (apply Y) and undo the change (restore X).

3. "Forced to stable storage" means physically written. Not buffered in an operating system cache. Not sitting in a controller write cache (unless that cache is battery-backed). Actually persisted to non-volatile media.

18.2.2 Why Write-Ahead?

Consider what happens without WAL. Suppose DB2 modifies a data page in the buffer pool and writes it to disk, but crashes before writing the corresponding log record. After the crash:

• The data page on disk reflects the change.
• There is no log record of the change.
• DB2 has no way to know whether this change was part of a committed transaction or an uncommitted transaction.
• If it was uncommitted, DB2 cannot undo it because there is no undo information.
• The database is now potentially inconsistent.

With WAL, the scenario is different. If DB2 crashes after writing the log record but before writing the data page:

• The log record is on disk. It describes the change.
• The data page on disk still has the old value.
• During crash recovery, DB2 reads the log, finds this record, and checks whether the transaction committed.
• If it committed, DB2 redoes the change (applies it to the data page).
• If it did not commit, DB2 does nothing (the page already has the old value).
• The database is consistent.

And if DB2 crashes after writing both the log record and the data page:

• During crash recovery, DB2 reads the log, finds the record, and checks the data page.
• The change is already applied. DB2 recognizes this and moves on.
• If the transaction did not commit, DB2 undoes the change using the before-image in the log record.
• The database is consistent.

WAL guarantees that no matter when a crash occurs — between any two I/O operations — the database can be brought back to a consistent state using the log.

18.2.3 Log Record Anatomy

A DB2 log record contains several pieces of information:

+---------------------------------------------------------------+
|  Log Record                                                    |
|---------------------------------------------------------------|
|  Log Sequence Number (LSN / RBA)   — Unique identifier         |
|  Transaction ID                    — Which transaction          |
|  Log Record Type                   — INSERT/UPDATE/DELETE/etc.  |
|  Object Identifier                 — Which table/index/space    |
|  Page Number                       — Which page was changed     |
|  Offset                            — Where on the page          |
|  Before Image (Undo)               — Old value of changed bytes |
|  After Image (Redo)                — New value of changed bytes |
|  Previous Log Record Pointer       — Chain for this transaction |
|  Timestamp                         — When the change occurred   |
+---------------------------------------------------------------+

On z/OS, log records are addressed by RBA (Relative Byte Address) — a monotonically increasing byte offset into the conceptual log stream. On LUW, they are addressed by LSN (Log Sequence Number). The concept is identical: every log record has a unique, ordered address that tells you exactly where it falls in the sequence of all changes ever made to the database.

18.2.4 The Log as a Timeline

Think of the recovery log as a timeline of every change ever made to the database:

Time ─────────────────────────────────────────────────────────►

Log: ┃ T1:BEGIN ┃ T1:INSERT ┃ T2:BEGIN ┃ T1:UPDATE ┃ T2:DELETE ┃ T1:COMMIT ┃ T2:COMMIT ┃
     ┃  RBA 100 ┃  RBA 200  ┃  RBA 350 ┃  RBA 500  ┃  RBA 680  ┃  RBA 800  ┃  RBA 950  ┃

Given this timeline, DB2 can:

• Replay all changes from any point forward (redo) — this is how point-in-time recovery works.
• Reverse uncommitted changes by walking backwards through a transaction's log chain (undo) — this is how rollback works.
• Determine the exact state of the database at any logged point in time.

This is extraordinarily powerful. The log is not just an audit trail — it is a complete, reversible history of every change to every byte of data. It is the foundation of everything that follows in this chapter.

18.2.5 Checkpoints and the Recovery Window

DB2 does not want to replay the entire log from the beginning of time during crash recovery. That could take hours or days. Instead, DB2 periodically takes checkpoints — markers in the log that say "at this point, all committed changes up to this LSN/RBA have been written to disk."

During crash recovery, DB2 only needs to process log records from the most recent checkpoint forward. This is called the recovery window. A shorter recovery window means faster crash recovery but more frequent checkpoint I/O.

On z/OS, the checkpoint frequency is controlled by: - LOGLOAD: Number of log records between checkpoints (default varies, commonly 50,000-200,000) - CHKFREQ: Can also be set based on time or other factors via ZPARM

On LUW, the checkpoint frequency is controlled by: - SOFTMAX: The recovery window target in percent of log space - MINCOMMIT: Minimum commits before a group commit is forced

We will return to these parameters when we discuss platform-specific log configuration.

18.3 [z/OS] Log Architecture

DB2 for z/OS has a sophisticated, multi-layered log architecture designed for the extreme reliability requirements of mainframe computing. Understanding this architecture is essential for every z/OS DBA.

18.3.1 Active Logs

The active log is where DB2 writes log records in real time. On z/OS, the active log consists of one or more active log data sets — VSAM linear data sets (LDS) that DB2 writes to sequentially.

Key characteristics of active logs:

• Circular usage: DB2 cycles through the active log data sets in round-robin fashion. When it fills one, it moves to the next. When it fills the last one, it cycles back to the first — but only after that first data set has been archived.
• Fixed allocation: Active log data sets are pre-allocated at DB2 installation. You decide how many data sets and how large each one is.
• Dual logging: DB2 can maintain two copies of the active log (COPY1 and COPY2), written in parallel to separate devices. If one copy becomes damaged, the other is used for recovery.

A typical active log configuration:

Active Log Data Sets (COPY1)           Active Log Data Sets (COPY2)
┌────────────────────────────┐         ┌────────────────────────────┐
│ DSNCAT.LOGCOPY1.DS001      │         │ DSNCAT.LOGCOPY2.DS001      │
│ DSNCAT.LOGCOPY1.DS002      │         │ DSNCAT.LOGCOPY2.DS002      │
│ DSNCAT.LOGCOPY1.DS003      │         │ DSNCAT.LOGCOPY2.DS003      │
│ DSNCAT.LOGCOPY1.DS004      │         │ DSNCAT.LOGCOPY2.DS004      │
└────────────────────────────┘         └────────────────────────────┘
         │                                       │
         └──── Written in parallel ──────────────┘

18.3.2 Archive Logs

When an active log data set is full, DB2 offloads it to an archive log data set. Archive logs are the permanent historical record of all database changes. They are essential for:

• Point-in-time recovery
• Recovery of objects beyond what the active log contains
• Audit and compliance requirements

Archive logs can be written to: - DASD (disk): Faster to read during recovery but consumes disk space - Tape: Economical for long-term retention but slower to mount during recovery - Both: DB2 supports dual archive logs — one copy to DASD and one to tape

The offload process is triggered automatically when an active log data set fills up. DB2 can also offload on demand via the -DB2P ARCHIVE LOG command.

18.3.3 The Bootstrap Data Set (BSDS)

The BSDS (Bootstrap Data Set) is the master directory of the log. It is a critical data set that contains:

• The names and locations of all active log data sets
• The names and locations of all archive log data sets
• The RBA ranges contained in each log data set
• The current log position (highest written RBA)
• Checkpoint records
• Conditional restart information

DB2 maintains two copies of the BSDS (BSDS1 and BSDS2) for redundancy. If one is damaged, DB2 uses the other.

┌─────────────────────────────────────────────────────────┐
│  BSDS (Bootstrap Data Set)                              │
│─────────────────────────────────────────────────────────│
│  Active Log Inventory:                                  │
│    DS001: RBA 00000000 → 000FFFFF  (COPY1 & COPY2)     │
│    DS002: RBA 00100000 → 001FFFFF  (COPY1 & COPY2)     │
│    DS003: RBA 00200000 → 002FFFFF  (COPY1 & COPY2)     │
│    DS004: RBA 00300000 → CURRENT   (COPY1 & COPY2)     │
│                                                         │
│  Archive Log Inventory:                                 │
│    ARC001: RBA 00000000 → 000FFFFF  (DASD + TAPE)      │
│    ARC002: RBA 00100000 → 001FFFFF  (DASD + TAPE)      │
│    ...                                                  │
│                                                         │
│  Checkpoint Queue:                                      │
│    Most recent: RBA 003A0000  (2024-03-15 14:30:00)     │
│    Previous:    RBA 00390000  (2024-03-15 14:25:00)     │
│    ...                                                  │
│                                                         │
│  System Information:                                    │
│    SSID: DB2P                                           │
│    Member: DB2P                                         │
│    Log wrap status: NORMAL                              │
└─────────────────────────────────────────────────────────┘

The BSDS is managed with the utility DSNJU004 (print log map) and DSNJU003 (change log inventory). We will use both later in this chapter.

18.3.4 Log Sizing on z/OS

Sizing the active log correctly is critical. If the active log fills up before offload completes, DB2 stops processing new work until space becomes available — a catastrophic event for a production system.

Key parameters:

• Number of active log data sets: Minimum 3, maximum 93. More data sets give more time for offload to complete before DB2 wraps around. A typical production system uses 6-12.
• Size of each active log data set: Each can be up to 4 GB (with extended addressing, larger). Common sizes range from 500 MB to 4 GB depending on workload.
• LOGLOAD: Number of log records between checkpoints. Higher values mean less checkpoint overhead but longer crash recovery. Typical production values: 50,000-200,000.

Sizing formula:

Total active log capacity = Number of data sets × Size per data set

Required capacity ≥ (Peak log write rate × Time to offload one data set) × 2
                   + Space for longest running transaction's log records

The "times 2" safety factor accounts for variability in offload time and workload spikes. The longest-running transaction matters because DB2 cannot reuse a log data set if it contains records for an active transaction.

For Meridian Bank's core banking system, handling 5,000 transactions per second with an average of 3 log records per transaction at 500 bytes each:

Log write rate = 5,000 × 3 × 500 = 7.5 MB/second
Offload time   = ~30 seconds per data set
Required capacity ≥ (7.5 MB/s × 30s) × 2 = 450 MB per data set minimum

Configuration: 8 active log data sets × 2 GB each = 16 GB total active log

This gives ample headroom for spikes and long-running transactions.

18.3.5 Dual Logging Configuration

For Meridian Bank's core banking system, dual logging is mandatory. The configuration ensures:

• Each log record is written to two physical devices simultaneously
• COPY1 and COPY2 are on different DASD volumes (ideally different storage controllers)
• If one copy is lost, the other is used seamlessly
• Archive logs are also dual — one copy to DASD for fast recovery, one to tape for DR

The installation panels (DSNTIPL) configure dual logging during DB2 installation. Post-installation changes require the DSNJU003 utility to add or modify log data sets.

18.4 [LUW] Log Architecture

DB2 for LUW uses a fundamentally similar logging concept but with different terminology, configuration, and operational characteristics. The most critical architectural choice on LUW is between circular logging and archive logging — a decision that determines your entire recovery capability.

18.4.1 Circular Logging vs. Archive Logging

This is the single most important configuration decision for DB2 LUW recovery:

Circular Logging (the default): - Log files are reused in a circular fashion - Once a transaction commits and a checkpoint occurs, its log space can be reused - You can perform crash recovery only - You cannot perform point-in-time recovery - You cannot perform online backup - You cannot roll forward from a backup

Archive Logging: - Log files are retained (archived) after use - The complete history of changes is preserved - You can perform crash recovery, point-in-time recovery, and roll-forward recovery - You can perform online backup - You must manage log file retention and deletion

Circular Logging:                     Archive Logging:
┌─────┐ ┌─────┐ ┌─────┐              ┌─────┐ ┌─────┐ ┌─────┐
│ L01 │→│ L02 │→│ L03 │──┐           │ L01 │→│ L02 │→│ L03 │→ ...
└─────┘ └─────┘ └─────┘  │           └──┬──┘ └──┬──┘ └──┬──┘
   ↑                      │              │       │       │
   └──────────────────────┘              ▼       ▼       ▼
   (reused when no longer needed)     ┌─────┐ ┌─────┐ ┌─────┐
                                      │ A01 │ │ A02 │ │ A03 │  (archived)
                                      └─────┘ └─────┘ └─────┘

The golden rule: Never run a production database with circular logging if you need point-in-time recovery capability. For Meridian Bank, every production database uses archive logging. Circular logging is acceptable only for development, test, and disposable databases.

To check the current logging mode:

-- Check database configuration
db2 get db cfg for MERIDIAN | grep -i "log retain"
-- LOGRETAIN = RECOVERY  (archive logging)
-- LOGRETAIN = OFF       (circular logging)

To enable archive logging:

db2 update db cfg for MERIDIAN using LOGRETAIN ON
db2 update db cfg for MERIDIAN using LOGARCHMETH1 DISK:/db2archlog/meridian/
# A full backup is required after this change before the database can be used
db2 backup database MERIDIAN to /db2backups/meridian/

18.4.2 Log File Configuration Parameters

DB2 LUW log files are configured through database configuration parameters:

LOGFILSIZ — Size of each log file in 4 KB pages. - Default: 250 (1 MB) - Production: 10,000-65,536 (40 MB to 256 MB) - Larger files mean fewer file switches, reducing overhead

LOGPRIMARY — Number of primary log files, pre-allocated when the database activates. - Default: 13 - Production: 20-100 depending on workload - These files are always allocated — they consume disk space immediately

LOGSECOND — Number of secondary log files, allocated on demand when primary logs are exhausted. - Default: 12 - Production: 20-100 - Setting to -1 means "allocate as many as needed" (risky — can fill a filesystem) - Secondary log allocation means you are under pressure — investigate the cause

LOGARCHMETH1 — Primary archive method. - OFF — No archiving (circular logging) - LOGRETAIN — Keep log files in the active log directory - DISK:/path/ — Archive to a specific disk directory - TSM:management_class — Archive to IBM Spectrum Protect (Tivoli Storage Manager) - VENDOR:/path/to/library — Archive using a third-party library

LOGARCHMETH2 — Secondary archive method (for dual archiving). - Same options as LOGARCHMETH1 - Provides redundancy for archive logs

Meridian Bank LUW configuration:

db2 update db cfg for MERIDIAN using LOGFILSIZ   25600
db2 update db cfg for MERIDIAN using LOGPRIMARY   30
db2 update db cfg for MERIDIAN using LOGSECOND    20
db2 update db cfg for MERIDIAN using LOGARCHMETH1 DISK:/db2archlog/meridian/node0000/
db2 update db cfg for MERIDIAN using LOGARCHMETH2 TSM:MERIDIAN_LOGS

This gives: - 30 primary logs × 100 MB each = 3 GB of active log space - 20 secondary logs × 100 MB each = 2 GB additional capacity - Archive logs stored to both local disk (fast recovery) and TSM (disaster recovery)

18.4.3 Log Sizing on LUW

The sizing logic is similar to z/OS but expressed differently:

Total active log space = (LOGPRIMARY + LOGSECOND) × LOGFILSIZ × 4 KB

Required space ≥ Peak log rate (MB/s) × Longest transaction duration (seconds) × 2

The longest transaction duration is critical on LUW. A single long-running transaction can prevent log files from being archived and reused. This is the most common cause of "log full" conditions on LUW. We will revisit this in Section 18.11 on common mistakes.

Monitor log consumption with:

-- Current log usage
SELECT * FROM TABLE(MON_GET_TRANSACTION_LOG(-1)) AS T;

-- Per-application log usage
SELECT APPLICATION_HANDLE,
       LOG_DISK_CONSUMED_TOTAL,
       LOG_DISK_CONSUMED_TOTAL / 1024 / 1024 AS LOG_MB
FROM TABLE(MON_GET_CONNECTION(NULL, -1)) AS C
ORDER BY LOG_DISK_CONSUMED_TOTAL DESC;

18.5 [z/OS] Image Copy and Recovery

On z/OS, database backup is performed through image copies, and recovery is performed through the RECOVER utility. These are the workhorses of z/OS backup and recovery.

18.5.1 The COPY Utility

The COPY utility creates an image copy of a tablespace or partition — a bit-for-bit snapshot of the data as it exists at a specific log point.

Types of image copies:

• Full image copy: A complete copy of every page in the tablespace. This is the baseline for recovery.
• Incremental image copy: Only the pages that have changed since the last full or incremental copy. Smaller and faster than a full copy but requires the full copy as a base during recovery.

COPY options:

//FULLCOPY  EXEC DSNUPROC,SYSTEM=DB2P,UID='FULLCOPY'
//SYSIN DD *
  COPY TABLESPACE MERIDIAN.ACCTDATA
       COPYDDN(COPY1,COPY2)
       FULL YES
       SHRLEVEL REFERENCE
/*

Key parameters: - SHRLEVEL REFERENCE: Takes an exclusive lock — no concurrent updates during the copy. Guarantees a consistent point-in-time copy. - SHRLEVEL CHANGE: Allows concurrent updates during the copy. The copy is fuzzy but still usable for recovery — DB2 notes the log range and applies log records during recovery. - COPYDDN: Specifies the DD names for the output data sets. Two DD names (COPY1, COPY2) create dual copies for redundancy. - FULL YES: Creates a full image copy. FULL NO creates an incremental copy.

For online systems like Meridian Bank, SHRLEVEL CHANGE is essential — you cannot lock out transactions during a backup window:

//ONLCOPY  EXEC DSNUPROC,SYSTEM=DB2P,UID='ONLCOPY'
//SYSIN DD *
  COPY TABLESPACE MERIDIAN.ACCTDATA
       COPYDDN(COPY1)
       FULL YES
       SHRLEVEL CHANGE
/*

18.5.2 The RECOVER Utility

The RECOVER utility restores a tablespace (or partition) from an image copy and optionally applies log records to bring it forward in time.

Recovery modes:

RECOVER to the current point (default):

//RECOVER  EXEC DSNUPROC,SYSTEM=DB2P,UID='RECOVER'
//SYSIN DD *
  RECOVER TABLESPACE MERIDIAN.ACCTDATA
/*

This restores from the most recent image copy and applies all available log records, bringing the tablespace to the current state. This is the most common recovery scenario.

RECOVER to a specific image copy (TOCOPY):

//RECTOCPY EXEC DSNUPROC,SYSTEM=DB2P,UID='RECTOCPY'
//SYSIN DD *
  RECOVER TABLESPACE MERIDIAN.ACCTDATA
          TOCOPY DSN=MERIDIAN.ACCTDATA.COPY.D20240315
/*

This restores to the exact point of the specified image copy. No log records are applied. Useful when you want to go back to a known-good state.

RECOVER to a specific log point (TORBA or TOLOGPOINT):

//RECTORBA EXEC DSNUPROC,SYSTEM=DB2P,UID='RECTORBA'
//SYSIN DD *
  RECOVER TABLESPACE MERIDIAN.ACCTDATA
          TOLOGPOINT X'00000003A5F20000'
/*

This restores from an image copy and applies log records up to (but not beyond) the specified RBA. This is point-in-time recovery on z/OS.

18.5.3 Full vs. Incremental Recovery

When you use incremental image copies, recovery requires more steps. DB2 must:

1. Restore the most recent full image copy
2. Apply each incremental image copy in sequence
3. Apply log records from the last incremental copy forward

This is slower than recovering from a single full image copy. The trade-off is that incremental copies are faster and smaller to create. A common strategy:

• Weekly: Full image copy (Sunday night)
• Daily: Incremental image copy (Monday-Saturday)
• Continuous: Archive logs retained for 30 days

With this strategy, the worst-case recovery requires restoring one full copy plus up to six incremental copies plus log application. For a 500 GB tablespace, the full copy takes 2 hours but the incremental might take only 20 minutes.

18.5.4 The DSNJU004 Utility — Print Log Map

DSNJU004 prints the BSDS contents — essential for understanding the current state of the log:

//PRINTLOG EXEC PGM=DSNJU004
//STEPLIB  DD DISP=SHR,DSN=DB2P.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//SYSUT1   DD DISP=SHR,DSN=DSNCAT.BSDS01
//SYSUT2   DD DISP=SHR,DSN=DSNCAT.BSDS02

The output shows all active logs, archive logs, and their RBA ranges. This is the first thing you look at when troubleshooting any log-related issue.

18.6 [LUW] BACKUP and RESTORE

DB2 LUW uses a different set of commands for backup and recovery, but the underlying principles are the same. The primary commands are BACKUP DATABASE, RESTORE DATABASE, and ROLLFORWARD DATABASE.

18.6.1 BACKUP DATABASE

The BACKUP command creates a backup image — analogous to the z/OS image copy but operating at the database level (or tablespace level).

Full offline backup (database not accessible during backup):

db2 backup database MERIDIAN to /db2backups/meridian/

Full online backup (database remains accessible — requires archive logging):

db2 backup database MERIDIAN online to /db2backups/meridian/

Incremental backup (only data changed since last full backup):

db2 backup database MERIDIAN online incremental to /db2backups/meridian/

Delta backup (only data changed since last backup of any type):

db2 backup database MERIDIAN online incremental delta to /db2backups/meridian/

The relationship between backup types:

Full ──────────────────────────────────────────────────
  │
  ├── Incremental (changes since full) ───────────────
  │     │
  │     ├── Delta (changes since last incremental)
  │     ├── Delta (changes since last delta)
  │     └── Delta (changes since last delta)
  │
  ├── Incremental (changes since full) ───────────────
  │     │
  │     ├── Delta
  │     └── Delta
  │
  └── Full ────────────────────────────────────────────
        │
        └── ...

Incremental and delta backups depend on a full backup as the base. You must enable incremental backup support:

db2 update db cfg for MERIDIAN using TRACKMOD YES
# Requires a full offline backup to establish the baseline
db2 backup database MERIDIAN to /db2backups/meridian/

Tablespace-level backup:

db2 backup database MERIDIAN tablespace USERSPACE1, ACCTDATA
    online to /db2backups/meridian/

18.6.2 Backup to Multiple Locations and Parallelism

DB2 LUW can write backups to multiple targets simultaneously for faster throughput:

db2 backup database MERIDIAN online
    to /db2backups/disk1/, /db2backups/disk2/, /db2backups/disk3/
    with 4 buffers buffer 4096 parallelism 3

• WITH n BUFFERS: Number of buffers for the backup operation
• BUFFER size: Size of each buffer in 4 KB pages
• PARALLELISM n: Number of parallel I/O streams

For Meridian Bank's 500 GB online banking database, using 3-way parallelism cuts backup time from 3 hours to about 1 hour.

18.6.3 RESTORE DATABASE

The RESTORE command reconstructs a database from a backup image:

Simple restore from latest backup:

db2 restore database MERIDIAN from /db2backups/meridian/

Restore a specific backup image (by timestamp):

db2 restore database MERIDIAN from /db2backups/meridian/
    taken at 20240315143000

Incremental restore (automatically restores full + incremental chain):

db2 restore database MERIDIAN incremental automatic
    from /db2backups/meridian/

Redirected restore (restore to different paths — essential for cloning databases or recovering when original paths are unavailable):

# Step 1: Generate the redirect script
db2 restore database MERIDIAN from /db2backups/meridian/
    into MERIDIAN_COPY redirect generate script redirect.clp

# Step 2: Edit redirect.clp to change container paths

# Step 3: Execute the redirect
db2 -tvf redirect.clp

Redirected restore is one of the most useful techniques for DBAs. You can use it to: - Clone a production database to a test environment - Move a database to new storage - Recover when original disk paths are destroyed

18.6.4 ROLLFORWARD DATABASE

After a restore, the database is in roll-forward pending state if archive logging is enabled. The ROLLFORWARD command applies archived log records to bring the database forward in time:

Roll forward to end of logs (recover everything):

db2 rollforward database MERIDIAN to end of logs and complete

Roll forward to a specific point in time:

db2 rollforward database MERIDIAN
    to 2024-03-15-14.30.00.000000 using local time and complete

Roll forward and stop (leave in roll-forward pending for further operations):

db2 rollforward database MERIDIAN to end of logs and stop

The ROLLFORWARD command reads archived log files sequentially, applying each change in order. This can take minutes or hours depending on the volume of log data.

Check rollforward status:

db2 rollforward database MERIDIAN query status

This shows: - Current log position - Next log file needed - Timestamp of the most recently applied log record

18.7 Point-in-Time Recovery

Point-in-time recovery (PITR) is the ability to restore a database or tablespace to its exact state at a specific moment in the past. This is the capability that saves you when someone runs an unqualified DELETE at 2:47 PM and you need to get back to 2:46 PM.

18.7.1 When to Use PITR

PITR is appropriate when:

• A logical error has corrupted data (bad DELETE, UPDATE, INSERT)
• You need to undo the effects of a specific batch job
• A data load introduced bad data and you need to go back to before the load
• An application bug modified data incorrectly over a period of time

PITR is not appropriate when: - You need to recover a single row (use application-level logic instead) - The issue is a hardware failure with no data corruption (use standard recovery) - The corruption is in the log itself (you cannot use corrupt logs for recovery)

18.7.2 The Log Apply Process

PITR works by:

1. Restoring from the most recent backup image taken before the target time
2. Applying log records sequentially from the backup's log position up to the target time
3. Undoing any transactions that were active but not yet committed at the target time
4. Marking the database or tablespace as recovered

Backup taken:    2024-03-15 02:00 AM
Bad DELETE:      2024-03-15 14:47 PM
Target recovery: 2024-03-15 14:46 PM

Timeline:
02:00 ────────────────────────────────── 14:46 ── 14:47 ── NOW
  │                                        │         │
  │◄────── Apply log records ─────────────►│         │
  │        (redo committed work)           │         │
  Backup                               Target    Bad event

18.7.3 [z/OS] Tablespace-Level PITR

On z/OS, PITR is performed at the tablespace level using RECOVER with TOLOGPOINT:

//PITR     EXEC DSNUPROC,SYSTEM=DB2P,UID='PITR'
//SYSIN DD *
  RECOVER TABLESPACE MERIDIAN.ACCTDATA
          TOLOGPOINT X'00000003A5F20000'
/*

After this recovery, the tablespace is at the specified log point. However, there is a critical constraint: referential integrity. If the ACCTDATA tablespace has foreign key relationships with other tablespaces (e.g., CUSTDATA, TXNDATA), those other tablespaces are now at a different point in time. The database may be logically inconsistent.

DB2 handles this by placing the recovered tablespace in REBUILD-pending (RBDP) status for related indexes and CHECK-pending status for related tablespaces. You must then either: - Recover the related tablespaces to the same log point - Run CHECK DATA to verify and resolve inconsistencies - Rebuild affected indexes with REBUILD INDEX

This is why PITR should be planned carefully, not executed in a panic. You need to know the dependency graph of your tablespaces before you begin.

18.7.4 [LUW] Database-Level and Tablespace-Level PITR

On LUW, PITR is performed with RESTORE + ROLLFORWARD:

Database-level PITR:

db2 restore database MERIDIAN from /db2backups/meridian/
db2 rollforward database MERIDIAN
    to 2024-03-15-14.46.00.000000 using local time and complete

This recovers the entire database to the specified time. All tablespaces are consistent with each other — there are no referential integrity concerns.

Tablespace-level PITR:

db2 restore database MERIDIAN tablespace ACCTDATA
    from /db2backups/meridian/  online
db2 rollforward database MERIDIAN
    to 2024-03-15-14.46.00.000000 and complete tablespace ACCTDATA online

Tablespace-level PITR on LUW has the same referential integrity constraints as on z/OS. The recovered tablespace must be reconciled with the rest of the database. DB2 LUW places the tablespace in rollforward pending state until the ROLLFORWARD command completes with the AND COMPLETE clause.

18.7.5 Consistency Points and the Minimum Recovery Time

Not every point in time is a valid recovery target. DB2 can only recover to a point where a consistent state is achievable — meaning all committed transactions up to that point are applied and all uncommitted transactions are rolled back.

On z/OS, the minimum recovery granularity is one log record (identified by RBA). On LUW, it is one log record (identified by LSN) or a timestamp. When using timestamps on LUW, DB2 finds the closest LSN that corresponds to the specified time.

18.7.6 Finding the Right Log Point

Before performing PITR, you need to know where in the log to recover to. Techniques include:

On z/OS — using DSN1LOGP: The DSN1LOGP utility scans the log and displays log records. You can filter by tablespace, time range, and record type:

//DSN1LOGP EXEC PGM=DSN1LOGP
//SYSPRINT DD SYSOUT=*
//SYSLOG   DD DISP=SHR,DSN=DSNCAT.LOGCOPY1.DS004
//SYSIN DD *
  SUMMARY YES
  DBID(312) OBID(7)
  RBASTART(00000003A5000000)
  RBAEND(00000003A6000000)
/*

On LUW — using db2flsn or db2readlog API:

# Find the log file containing a specific LSN
db2flsn -db MERIDIAN 000000003A5F2000

# View database history to find backup and log points
db2 list history backup all for MERIDIAN

18.8 Crash Recovery — What Happens When DB2 Restarts

Crash recovery is the process DB2 performs automatically when it restarts after an abnormal termination. This is not something the DBA triggers manually — DB2 does it on its own. But understanding the process is essential for estimating recovery time and diagnosing problems.

18.8.1 Why Crash Recovery Is Needed

When DB2 crashes, the state of data on disk may be inconsistent:

• Some committed transactions may have log records on disk but their data page changes were still in the buffer pool (not yet written to disk). These changes are lost from the data pages but preserved in the log. They need to be redone.

• Some uncommitted transactions may have had their data page changes written to disk (because the buffer pool was under pressure and needed to flush dirty pages). These changes are present on data pages but should not be, because the transaction never committed. They need to be undone.

The crash recovery process fixes both situations.

18.8.2 The Two Phases of Restart

DB2 crash recovery proceeds in two phases:

Phase 1: Redo (Forward Recovery) Starting from the most recent checkpoint, DB2 reads the log forward and reapplies every change — both committed and uncommitted. This brings the data pages to the exact state they were in at the moment of the crash, including in-flight changes.

Checkpoint                                     Crash
    │                                             │
    ▼                                             ▼
    ├── Log record 1 ──► REDO                     │
    ├── Log record 2 ──► REDO                     │
    ├── Log record 3 ──► REDO                     │
    ├── ...                                       │
    └── Log record N ──► REDO ────────────────────┘

After the redo phase, every data page reflects every change that was logged, whether committed or not.

Phase 2: Undo (Backward Recovery) DB2 then identifies all transactions that were active (started but not committed) at the time of the crash. It reads the log backward for each of these transactions and reverses their changes.

    ├── T3: Active at crash ──► UNDO (walk backward, reverse changes)
    ├── T7: Active at crash ──► UNDO (walk backward, reverse changes)
    └── T12: Active at crash ──► UNDO (walk backward, reverse changes)

After the undo phase, the database contains only the effects of committed transactions. It is consistent.

18.8.3 In-Doubt Transactions

In a distributed environment (two-phase commit with DRDA or other transaction managers), some transactions may be in-doubt at the time of crash — they have prepared to commit but are waiting for the final commit or rollback decision from the coordinator.

DB2 handles in-doubt transactions differently: - On z/OS, in-doubt transactions are resolved through RRS (Resource Recovery Services) or manually by the DBA using the -DB2P RECOVER INDOUBT command. - On LUW, in-doubt transactions are resolved through the transaction manager or manually with LIST INDOUBT TRANSACTIONS and COMMIT or ROLLBACK as appropriate.

In-doubt transactions hold locks until resolved. In a production environment, resolving them quickly is essential to avoid lock contention.

18.8.4 [z/OS] Conditional Restart

Normally, DB2 for z/OS performs a full crash recovery from the last checkpoint. But in some situations, you may need a conditional restart — a restart that overrides the normal recovery behavior.

Conditional restart is configured with the DSNJU003 utility before starting DB2:

//CONDRST  EXEC PGM=DSNJU003
//STEPLIB  DD DISP=SHR,DSN=DB2P.SDSNLOAD
//SYSPRINT DD SYSOUT=*
//SYSUT1   DD DISP=SHR,DSN=DSNCAT.BSDS01
//SYSUT2   DD DISP=SHR,DSN=DSNCAT.BSDS02
//SYSIN DD *
  CRESTART CREATE,ENDRBA=X'00000003A5F20000'
/*

When to use conditional restart: - Log damage: If the log is damaged beyond the point DB2 needs for recovery, you can set an ENDRBA to truncate the log and accept some data loss. - Bypass undo: If you want to skip the undo phase for specific work (dangerous — only for extreme situations). - Bypass redo: If you want to skip the redo phase up to a certain point (also dangerous).

Conditional restart is the nuclear option. It can result in data loss. Use it only when normal restart fails and there is no alternative. Document everything you do.

18.8.5 Crash Recovery Time

The time for crash recovery depends on: - The number of log records between the last checkpoint and the crash (determined by LOGLOAD on z/OS, SOFTMAX on LUW) - The speed of the log storage (disk read throughput) - The number of in-flight transactions that need to be undone

For Meridian Bank, with LOGLOAD set to 100,000 and an average of 500 bytes per log record:

Log records to process = 100,000
Data to read = 100,000 × 500 bytes = 50 MB
Disk read speed = 200 MB/s
Redo time ≈ 50 MB / 200 MB/s ≈ 0.25 seconds
Undo time ≈ dependent on active transactions, typically < 5 seconds

Total crash recovery time ≈ seconds to low tens of seconds

This is why crash recovery is usually fast — the checkpoint interval keeps the recovery window small.

18.9 Media Recovery — When Disks Fail

Media recovery is needed when physical storage fails and data pages are lost. Unlike crash recovery, which uses only the log, media recovery requires a backup (image copy on z/OS, backup image on LUW) plus log records.

18.9.1 Disk Failure Scenarios

Media recovery addresses situations where data pages are physically lost:

• A disk in a RAID-5 or RAID-6 array fails during rebuild of another failed disk (double disk failure)
• A storage array controller fails, corrupting data
• A SAN fabric error causes I/O to write to the wrong location
• A firmware bug corrupts data at the storage level
• Fire or flood destroys storage hardware

Modern storage systems (RAID, mirroring, replication) make complete disk failure rare but not impossible. When it happens, you need media recovery.

18.9.2 [z/OS] Recovery from Image Copy + Logs

On z/OS, media recovery for a tablespace proceeds as follows:

1. Identify the damage: DB2 may detect media errors automatically (I/O errors) or a DBA may discover corruption through CHECK DATA or application errors.

2. Stop the tablespace (if not already stopped):

-DB2P STOP DATABASE(MERIDIAN) SPACENAM(ACCTDATA)

3. Recover the tablespace:

//MEDREC   EXEC DSNUPROC,SYSTEM=DB2P,UID='MEDREC'
//SYSIN DD *
  RECOVER TABLESPACE MERIDIAN.ACCTDATA
/*

DB2 automatically: - Finds the most recent usable image copy in the SYSIBM.SYSCOPY catalog table - Restores it - Applies log records from the image copy's log point to the current point - Brings the tablespace to a current, consistent state

4. Start the tablespace:

-DB2P START DATABASE(MERIDIAN) SPACENAM(ACCTDATA)

18.9.3 [z/OS] Recovery with FlashCopy

IBM FlashCopy technology creates an instant, point-in-time copy of a disk volume. When integrated with DB2 (through DFSMSdss), FlashCopy can:

• Create image copies in seconds instead of hours (the actual data copy happens in the background)
• Provide near-instantaneous restore of tablespaces
• Enable consistent copies across multiple tablespaces without quiescing all of them simultaneously

FlashCopy-based backup is standard practice for large Meridian Bank tablespaces. A 500 GB tablespace that takes 2 hours for a traditional COPY utility completes its FlashCopy in under a minute. The trade-off is that FlashCopy requires compatible storage hardware (IBM DS8000 series or equivalent).

18.9.4 [LUW] Restore from Backup

On LUW, media recovery follows the restore + rollforward pattern:

1. If the database is still accessible (only some tablespaces are damaged):

db2 restore database MERIDIAN tablespace ACCTDATA
    online from /db2backups/meridian/
db2 rollforward database MERIDIAN to end of logs
    and complete tablespace ACCTDATA online

2. If the database is completely inaccessible:

db2 restore database MERIDIAN from /db2backups/meridian/
db2 rollforward database MERIDIAN to end of logs and complete

3. If both primary and mirror disks are lost, you restore from the most recent backup stored on separate media (tape, remote storage, cloud):

db2 restore database MERIDIAN from /remote_backup/meridian/
db2 rollforward database MERIDIAN to end of logs and complete
    overflow log path /remote_archlog/meridian/

The OVERFLOW LOG PATH option tells DB2 where to find archive logs if they are not in the expected location. This is critical when the original log archive path is on the failed storage.

18.9.5 Storage-Level Protection

Media recovery is the last line of defense. Before you reach it, multiple layers of storage protection should be in place:

A well-designed system uses multiple layers. Meridian Bank uses RAID-10 (layer 1) plus synchronous mirroring (layer 2) plus DB2 backup (layer 4) plus off-site tape (layer 5).

18.10 Disaster Recovery Planning

Disaster recovery (DR) addresses the worst-case scenario: the complete loss of a data center. Fire, flood, earthquake, extended power failure, or catastrophic infrastructure failure renders the primary site unusable. Every transaction, every backup, every log file at that site may be gone.

18.10.1 Off-Site Copies

The foundation of disaster recovery is maintaining copies of data at a geographically separate location:

• Off-site backup copies: Backup images transported or replicated to a remote site
• Off-site archive logs: Log files transported or replicated to a remote site
• Database replicas: Full database copies maintained at a remote site through replication

The distance between primary and DR sites matters. Regulatory guidance for financial institutions typically requires at least 200-300 miles of separation to protect against regional disasters.

18.10.2 [z/OS] GDPS — Geographically Dispersed Parallel Sysplex

IBM's GDPS (Geographically Dispersed Parallel Sysplex) is the premier disaster recovery solution for z/OS. GDPS provides:

• Synchronous disk mirroring (Metro Mirror / PPRC) between sites up to ~300 km apart
• Asynchronous mirroring (Global Mirror) for greater distances
• Automated failover: GDPS can detect a primary site failure and automatically switch to the secondary site
• DB2 data sharing across sites in an active-active configuration (with Parallel Sysplex)

In a GDPS environment with synchronous mirroring: - RPO = 0 (no committed transaction is lost because every write is mirrored synchronously) - RTO = minutes (automated scripts start DB2 at the DR site)

Meridian Bank's core banking system uses GDPS with Metro Mirror between two data centers 150 km apart. The secondary site maintains a synchronous copy of all DB2 data and logs. If the primary site fails:

1. GDPS detects the failure
2. GDPS activates the mirrored volumes at the secondary site
3. DB2 starts at the secondary site using the mirrored data
4. Crash recovery processes any in-flight transactions
5. The system is operational within 15 minutes

18.10.3 [LUW] HADR — High Availability Disaster Recovery

DB2 LUW provides HADR (High Availability Disaster Recovery) as its built-in replication and failover mechanism. HADR is covered in depth in Chapter 26, but the key concepts relevant to DR planning are:

• Primary database sends log records to a standby database in real time
• Synchronization modes:
• SYNC: Log record must be written to standby disk before primary COMMIT returns (RPO = 0, higher latency)
• NEARSYNC: Log record must be received by standby memory before primary COMMIT returns (RPO near-zero)
• ASYNC: Log records sent asynchronously (RPO = seconds to minutes, lowest latency impact)
• SUPERASYNC: Best effort, no acknowledgment required (RPO = potentially minutes)
• Automatic client reroute: Applications automatically reconnect to the standby after failover
• Takeover: Manual or automatic promotion of standby to primary

For Meridian Bank's online banking system:

# Primary server setup
db2 update db cfg for MERIDIAN using
    HADR_LOCAL_HOST primary-server.meridian.com
    HADR_LOCAL_SVC  55001
    HADR_REMOTE_HOST standby-server.meridian-dr.com
    HADR_REMOTE_SVC  55001
    HADR_REMOTE_INST db2inst1
    HADR_SYNCMODE NEARSYNC
    HADR_PEER_WINDOW 120

db2 start hadr on database MERIDIAN as primary

# Standby server setup (after restoring a backup of the primary)
db2 update db cfg for MERIDIAN using
    HADR_LOCAL_HOST standby-server.meridian-dr.com
    HADR_LOCAL_SVC  55001
    HADR_REMOTE_HOST primary-server.meridian.com
    HADR_REMOTE_SVC  55001
    HADR_REMOTE_INST db2inst1
    HADR_SYNCMODE NEARSYNC
    HADR_PEER_WINDOW 120

db2 start hadr on database MERIDIAN as standby

18.10.4 RPO = 0 Strategies

Achieving RPO = 0 (zero data loss) requires synchronous replication to the DR site. Both GDPS (z/OS) and HADR SYNC mode (LUW) provide this. The trade-off is latency — every COMMIT must wait for the remote site to acknowledge the write. At 300 km distance, the speed-of-light round-trip adds approximately 2 milliseconds of latency per COMMIT.

For Meridian Bank, the 2 ms latency is acceptable for the core banking system (transactions are already complex and multi-step). For the high-frequency trading interface (if one existed), it would not be — and NEARSYNC or ASYNC would be chosen instead, accepting a small RPO in exchange for lower latency.

18.10.5 DR Testing

A disaster recovery plan that has not been tested is not a plan — it is a hope. Meridian Bank conducts DR testing quarterly:

1. Tabletop exercise (quarterly): Walk through the DR runbook step by step without actually executing
2. Component test (quarterly): Test individual components — backup restore, log application, HADR takeover
3. Full failover test (semi-annually): Actually fail over to the DR site and run production workload for 4 hours
4. Surprise test (annually): DR team is notified with no advance warning and must execute the failover within the RTO window

Every test is documented. Every failure is analyzed. Every gap is addressed before the next test.

18.11 Common Recovery Mistakes

In my years as a DBA mentor, I have seen the same recovery mistakes repeated across organizations. Learn from these so you do not make them yourself.

18.11.1 Untested Backups

The mistake: Backups run every night. Nobody has ever tried to restore one. When a recovery is needed, the backup images are corrupt, incomplete, or incompatible with the current database version.

The fix: Schedule monthly restore tests. Restore to a test environment and verify the database is usable. Automate this — do not rely on manual discipline.

# Monthly restore test script (LUW)
#!/bin/bash
BACKUP_DIR=/db2backups/meridian/
TEST_DB=MERIDIAN_RESTORE_TEST
LOG_FILE=/db2admin/logs/restore_test_$(date +%Y%m%d).log

echo "$(date): Starting monthly restore test" >> $LOG_FILE

db2 restore database MERIDIAN from $BACKUP_DIR into $TEST_DB redirect
# ... (redirect containers to test paths)
db2 rollforward database $TEST_DB to end of logs and complete

# Verify by running a sample query
RESULT=$(db2 -x "SELECT COUNT(*) FROM $TEST_DB.MERIDIAN.ACCOUNT")
echo "$(date): Account table row count: $RESULT" >> $LOG_FILE

db2 drop database $TEST_DB
echo "$(date): Restore test complete" >> $LOG_FILE

18.11.2 Log Gaps

The mistake: Archive logs are stored on disk. The disk fills up. Log files are deleted to free space. Later, a recovery is needed that spans the deleted log files. The recovery fails because of the gap in the log chain.

The fix: - Monitor archive log disk usage with alerts at 70%, 80%, 90% full - Use dual archiving (LOGARCHMETH1 + LOGARCHMETH2) so a second copy exists on tape or TSM - Never delete archive logs manually — use DB2's pruning mechanisms or a managed retention policy - On z/OS, use the MODIFY RECOVERY utility to clean up old recovery assets based on a policy

18.11.3 Circular Logging in Production [LUW]

The mistake: A developer creates a database with default settings (circular logging). The database goes to production. Months later, someone needs to recover from a bad UPDATE. Point-in-time recovery is impossible because there are no archived logs.

The fix: Make archive logging a mandatory standard for all production databases. Include a check in your deployment pipeline:

# Pre-deployment check
LOGRETAIN=$(db2 get db cfg for $DBNAME | grep "Log retain" | awk '{print $NF}')
if [ "$LOGRETAIN" = "OFF" ]; then
    echo "ERROR: Database $DBNAME has circular logging. Cannot deploy to production."
    exit 1
fi

18.11.4 Single Copy Strategy

The mistake: One backup copy, stored on the same storage array as the database. The storage array fails. Both the database and the backup are lost.

The fix: The 3-2-1 rule applies to database backups just as it does to personal data: - 3 copies of the data (primary + 2 backups) - 2 different storage media (disk + tape, or disk + cloud) - 1 copy off-site (at the DR location)

18.11.5 Ignoring the Longest Transaction

The mistake (LUW): A reporting query runs for 6 hours against a production OLTP database. During that time, the active log cannot be freed because DB2 must retain log records for the duration of the oldest active transaction. The active log fills up. New transactions fail.

The fix: - Monitor active transaction duration and log consumption - Set MON_ACT_METRICS = BASE to capture transaction-level monitoring data - Use separate connections or federated queries for long-running reports - Consider using the cur_commit isolation level for reports to minimize log retention impact

18.11.6 Not Documenting the Recovery Procedure

The mistake: The senior DBA knows how to recover the database. Nobody else does. The senior DBA is on vacation when the failure occurs. The remaining team members spend hours reading documentation instead of executing recovery.

The fix: Create and maintain a recovery runbook — a step-by-step document that any competent DBA can follow under pressure. Include: - Exact commands (not "restore the database" but the full command with all parameters) - Expected output at each step - Decision points (if X happens, do Y; if Z happens, do W) - Contact information for escalation - Estimated time for each step

18.12 The Meridian Bank Recovery Strategy

Let us pull everything together and design a comprehensive backup and recovery strategy for Meridian National Bank. This is a realistic, production-grade strategy that you can adapt for your own environments.

18.12.1 System Inventory

18.12.2 Core Banking (z/OS) Strategy

Backup Schedule: - Full image copy of all tablespaces: Weekly (Sunday 01:00 AM) with SHRLEVEL CHANGE - Incremental image copy of high-change tablespaces: Daily (01:00 AM) - Full image copy of the DB2 catalog and directory: Weekly - FlashCopy snapshots of critical tablespaces: Every 6 hours for fast recovery

Log Management: - Dual active logs: 8 data sets × 2 GB each, COPY1 and COPY2 on separate controllers - Dual archive logs: Copy 1 to DASD (30-day retention), Copy 2 to tape (1-year retention) - LOGLOAD: 100,000 (checkpoint every ~100K log records) - Archive log offload monitored — alert if offload takes more than 60 seconds

Disaster Recovery: - GDPS with Metro Mirror to DR site (150 km away) - Synchronous mirroring for RPO = 0 - Automated failover script — RTO target 15 minutes - DR testing: Full failover quarterly

Recovery Procedures: - Tablespace-level recovery: Documented runbook for each critical tablespace - Point-in-time recovery: Pre-approved procedure requiring change management approval - Crash recovery: Automatic — DB2 restart procedures documented in automation - Conditional restart: Emergency-only procedure requiring two-DBA approval

18.12.3 Online Banking (LUW) Strategy

Backup Schedule: - Full online backup: Weekly (Sunday 02:00 AM) - Incremental online backup: Daily (02:00 AM) - Delta online backup: Every 6 hours (for faster recovery with less log application) - Tablespace-level backup of ACCTDATA and TXNDATA: Daily

Log Management: - LOGFILSIZ: 25,600 pages (100 MB per log file) - LOGPRIMARY: 30 - LOGSECOND: 20 - LOGARCHMETH1: DISK:/db2archlog/meridian/ (local SSD — fast recovery) - LOGARCHMETH2: TSM:MERIDIAN_LOGS (off-site via Spectrum Protect) - Archive log retention: 30 days on disk, 1 year on TSM

Disaster Recovery: - HADR with NEARSYNC mode to DR site - Automatic client reroute configured for all application connections - HADR takeover procedure documented and tested - RTO target: 30 minutes (including application reconnection)

Recovery Procedures: - Database-level restore + rollforward: Primary recovery method - Tablespace-level online restore: For targeted recovery without full outage - Point-in-time recovery: Requires change management approval; documented with exact commands

18.12.4 Data Warehouse (LUW) Strategy

Backup Schedule: - Full offline backup: Weekly (Saturday 22:00 PM — during ETL window) - No incremental or delta (full backup is sufficient given 24-hour RPO)

Log Management: - LOGFILSIZ: 65,536 pages (256 MB per log file — large to accommodate ETL batch load logs) - LOGPRIMARY: 50 - LOGSECOND: 30 - LOGARCHMETH1: DISK:/db2archlog/warehouse/ - Archive log retention: 7 days on disk (sufficient for weekly recovery cycle)

Disaster Recovery: - No HADR (cost not justified for 4-hour RTO) - Weekly backup replicated to DR site via TSM - Recovery from backup + rebuild from operational systems if needed

18.12.5 Recovery Testing Schedule

18.12.6 Monitoring and Alerting

Recovery infrastructure must be monitored continuously:

• Backup completion: Alert if any scheduled backup does not complete within the expected window
• Archive log space: Alert at 70%, 80%, 90% of allocated disk space
• HADR lag: Alert if HADR replication falls more than 30 seconds behind
• Log offload time (z/OS): Alert if offload exceeds 60 seconds
• Long-running transactions: Alert if any transaction runs longer than 30 minutes (OLTP) or 4 hours (warehouse)
• Backup image integrity: Weekly automated restore test with verification

-- LUW: Monitor HADR status
SELECT HADR_ROLE, HADR_STATE, HADR_SYNCMODE,
       HADR_LOG_GAP, HADR_PEER_WINDOW_END
FROM TABLE(MON_GET_HADR(-1)) AS H;

-- LUW: Monitor log usage
SELECT TOTAL_LOG_USED_KB, TOTAL_LOG_AVAILABLE_KB,
       TOTAL_LOG_USED_KB * 100 /
       (TOTAL_LOG_USED_KB + TOTAL_LOG_AVAILABLE_KB) AS PCT_USED
FROM TABLE(MON_GET_TRANSACTION_LOG(-1)) AS T;

Spaced Review: Connecting to Previous Chapters

From Chapter 3 — DB2 Architecture

In Chapter 3, we introduced the log manager as one of the parallel subsystems in the DB2 architecture diagram. Now you understand exactly what the log manager does: it implements the WAL protocol, manages active and archive logs, and enables every recovery scenario in this chapter. The buffer pool manager and the log manager work in tandem — the buffer pool holds dirty pages in memory for performance, while the log ensures those changes can be recovered even if the buffer pool contents are lost in a crash.

Review question: Why does DB2 separate the buffer pool (dirty pages in memory) from the log (change records on disk)? What would happen if DB2 relied solely on writing dirty pages to disk for durability?

Answer: If DB2 relied solely on flushing dirty pages, every change would require a random I/O to the data page's location on disk — devastating for performance. The log allows DB2 to defer data page writes (sequential, batched) while guaranteeing durability through sequential log writes, which are much faster. If DB2 crashes, the log provides the information to redo changes that were not yet flushed to the data pages.

From Chapter 14 — Locking and Concurrency

In Chapter 14, we discussed how locks protect data from concurrent access. Now consider how locks interact with recovery. During crash recovery, DB2 does not need to acquire locks — no other transactions are running because the database is not yet open. But during online recovery (tablespace-level recovery while the database is active), DB2 must acquire locks on the recovering tablespace to prevent concurrent access to inconsistent data.

Review question: During online tablespace-level recovery on LUW, what happens to applications that try to access the recovering tablespace?

Answer: They receive an error indicating the tablespace is in a pending state (restore pending or rollforward pending). Applications must handle this error gracefully — either retrying after recovery completes or failing over to an alternative data source.

From Chapter 17 — DB2 Utilities

In Chapter 17, we covered the utility infrastructure that backup and recovery depend on. The COPY, RECOVER, CHECK DATA, and REBUILD INDEX utilities introduced in Chapter 17 are the same utilities used in this chapter's recovery procedures. The utility monitoring and management techniques — checking utility status, restarting failed utilities, managing utility resource consumption — apply directly to backup and recovery operations.

Review question: You are performing a RECOVER TABLESPACE on z/OS and the utility fails mid-execution because an archive log tape cannot be mounted. What do you do?

Answer: Check the utility status with -DISPLAY UTILITY. The RECOVER utility supports restart — when the tape issue is resolved, issue -START UTILITY(utility-name) to restart from the point of failure. You do not need to start over from the beginning.

Summary

This chapter covered the complete backup, recovery, and logging architecture of DB2 across both z/OS and LUW platforms. Here is what you should take away:

1. Write-ahead logging is the foundation. Every recovery mechanism depends on the WAL protocol: log records are written to stable storage before data page changes. This simple invariant enables crash recovery, point-in-time recovery, and disaster recovery.

2. z/OS and LUW have different log architectures but the same principles. z/OS uses active log data sets, archive log data sets, and the BSDS. LUW uses primary and secondary log files with configurable archive methods. Both platforms maintain a sequential record of all database changes.

3. Backup types and strategies differ by platform. z/OS uses the COPY/RECOVER utilities with full and incremental image copies. LUW uses BACKUP/RESTORE/ROLLFORWARD with full, incremental, and delta backups. Both support online backup for 24/7 availability.

4. Point-in-time recovery requires archive logging. Without archived logs, you can only recover to the point of the last backup. With archived logs, you can recover to any point in time — but you must manage those logs carefully to avoid gaps.

5. Crash recovery is automatic and usually fast. The redo/undo phases bring the database to a consistent state after any crash. The recovery window is controlled by checkpoint frequency.

6. Disaster recovery requires planning, investment, and testing. GDPS (z/OS) and HADR (LUW) provide automated failover to remote sites. But the technology is worthless without a tested runbook and a team that has practiced the procedure.

7. The most common recovery mistakes are preventable. Test your backups. Use archive logging in production. Maintain off-site copies. Document your procedures. Monitor your infrastructure.

In the next chapter, we turn to security — protecting Meridian Bank's data not from hardware failures or human mistakes, but from unauthorized access, both external and internal.

"The backup you do not take is the one you will need. The recovery you do not test is the one that will fail. The runbook you do not write is the one your team will need at 3 AM on a holiday weekend."