How DB2 Keeps Logs for Changes to Tables

When changes are made to the data in tables, subsequently changing the table space and index spaces, DB2 writes one or more records to its log so that a blackout of the changes can be performed if the unit of work fails. DB2 can also use this information to reapply changes that may have been lost when recovering to a previous point in time.

The primary purpose of the active log is to record all changes—inserts, updates, and deletes—made to DB2 objects.

  • The DML statements are recorded in the log as follows:
    • INSERT: Entire after-image of the record is logged; called a redo record
    • DELETE: The before-image is recorded; called an undo record
    • UPDATE: Both the before and after images—undo and redo record—are recorded

Related: Complete SQL database Training

Log Entries

Each log record has its own unique identifier. In a parallel sysplex DB2 data sharing environment, it is known as the LRSN (log record sequence number). When you are operating in a non-data sharing environment, this number is known as the log RBA, or the offset of the record in the log from the beginning of the log. In a data sharing environment, the LRSN helps to track the sequence of events that happen over multiple members in the data sharing group.

Each member has its own log; if multiple members are making updates to the same data, the logs must be merged during a recovery. Because the LRSN is unique across the sysplex, this type of merging is possible.

Related: Data warehousing and Teradata basics

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.