DB2 Logging

DB2 logging is the safety net of the database. Every INSERT, UPDATE and DELETE is recorded in the DB2 log before the change is written to the data page. If DB2 crashes or you need to undo work, the log is what brings the data back.

Active logs and archive logs

• Active logs - A set of DASD datasets (usually 3 or more) that DB2 writes to in a ring. When one fills up, DB2 moves to the next.
• Archive logs - When an active log fills, DB2 copies it to an archive log dataset (often on tape) and reuses the active log. Old changes stay recoverable.
• BSDS (bootstrap dataset) - Records which log datasets are active, which are archived, and the log range each one covers. DB2 keeps two copies.
• Dual logging is common: every log record is written to two copies at once, so one bad disk cannot lose the log.

How logging protects your data

• Write-ahead logging - The log record is forced to disk before the changed data page. This rule is never broken.
• COMMIT - Makes your changes permanent by forcing the log records to disk. Until commit, ROLLBACK can undo everything using the log.
• ROLLBACK - Uses the log to undo all changes since the last commit point, restoring the old values.
• Restart recovery - After a crash, DB2 reads the log during restart: committed changes are redone, uncommitted changes are undone. Your tables come back consistent.
• Checkpoints - Periodic markers in the log that shorten restart time, because recovery only needs to look back to the last checkpoint.

Useful log commands

• Example:-
  -DISPLAY LOG -SET LOG SUSPEND -SET LOG RESUME
• -DISPLAY LOG shows the active log datasets and how full they are. -SET LOG SUSPEND pauses log writing briefly (used before taking a volume-level backup); -SET LOG RESUME starts it again.
• Never leave the log suspended - while suspended, no updates can happen and applications will wait.