Logging

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 backout 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 changesinserts, updates, and deletesmade 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 imagesundo and redo recordare recorded

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.

Log Data Sets

DB2 physically records changes in log data sets. Every DB2 subsystem will have a predefined set of active logs on disk. The log records are then written to these active log data sets. When the active log data sets become full, DB2 automatically switches to the next-available log data set. After all the active log data sets have been used, DB2 will wrap around to the first active log. However, we do not want to lose log records, because we may want to use them for recovery or backout, so the active log data sets will be offloaded when they become full. Offloaded active logs are called archive log data sets and can be stored on disk or on cartridge tapes. As with the active log, you can have multiple copies of your archive data sets to protect yourself against failure. In the DSNZPARMs, you can specify the medium for archive storage, disk or cartridge, and how many archive copies you want to create.

NOTE

Recovery from disk archive data sets is much faster, and you can take advantage of DB2's ability to process the logs in parallel.

DB2 allows many archive log data sets, and you will want to keep them if a recovery is necessary. The retention period of the archive log data sets depends on your image-copy frequency and how far you want to go back in time during a point-in-time recovery.

DB2 also provides a dual logging capability to ensure that there are two copies of the active log data sets; if one is lost, the other can be used for recovery. During a recovery, DB2 applies from the active log the changes that are required to recover to the specified point in time. If the records needed to recover are no longer on the active log, DB2 will call for the appropriate archive log(s).

NOTE

All production systems should be using dual logs to ensure that data can be recovered successfully.

Bootstrap Data Set

The BSDS is a VSAM data set that contains information about the DB2 logs and the records contained in those logs, as well as other information. The DB2 system will record all the current active log data sets in the BSDS. During offload processing, DB2 will dynamically allocate a new data set with a unique name. After the offload completes successfully, this data set is recorded in the BSDS, and a copy of the BSDS is created; if the archive log is placed on a cartridge, this copy of the BSDS is also placed on the same cartridge as the archive log. During a recovery, DB2 will use the BSDS to find all the available archive logs. The number of records that the BSDS can contain is determined by the MAXARCH parameter in the DSNZPARMs.

The BSDS should be large enough to record all archive logs. You should have the MAXARCH parameter set high enough for your environment and retain your archive log data sets long enough so that in the event of a recovery and you have to go back through older archive logs, the archive log data sets will still exist. If the data set is not recorded in the BSDS anymore but is still physically available, it is possible to place its entry in the BSDS by using the change-log inventory utility (DSNJU003). However, this can be done only when the DB2 subsystem is stopped and therefore will cause an outage. The proper setting for the MAXARCH parameter will depend on how large your archive logs are and the oldest point in time an application is allowed to recover to.

DB2 should always be operating with dual BSDSs. If for some reason one is lost, it will need to be restored, using the following general steps.

1.	Rename the damaged BSDS.
2.	Define a new BSDS.
3.	Use RERPO to copy from the nondamaged BSDS to the new one.
4.	Run DSNJU004 to list contents of replacement BSDS.

These steps must be performed to restore dual BSDS operation. If DB2 has to be stopped, it cannot be restarted until it is reestablished.

SYSIBM.SYSLGRNX

In the DB2 directory is a catalog table, known as SYSIBM.SYSLGRNX, used to determine the log records for a table space and recoverable indexes by recording the periods of updates for these objects. A row exists for the time period that a table space or index spaces had update activity occurring. Recorded in this row are the following:

• DBID (database identifier) and OBID (object identifier) of the object

• LRSN and local log RBA of the first update after open

• LRSN and local log RBA when a pseudoclose occurred, that is, no more update activity

• If data sharing, the member that performed the updates

This information will help DB2 determine what log data sets or parts of the log will be needed in the event of a recovery and speeds up the recovery process because logs that contain no updates for the object being recovered are skipped. The MODIFY utility can be used to delete outdated information from the tables that store image copy information.

The utility should be executed often and on a scheduled basis, depending on system activity. This will help improve performance for several processes that access the SYSIBM.SYSCOPY and SYSIBM.SYSLGRNX catalog tables. Owing to the nature of the data in these tables, they can grow considerably in size and require a good deal of space. It is recommended that, if the SYSCOPY and SYSLGRNX tables are very large, outdated information be deleted by using the MODIFY utility to delete entries by age. For example, it might be best to have one run of the utility remove rows older than 20 months. If you want to keep information longer, it is recommended that you unload the tables and also keep the DDL to recreate these tables. This will allow you to recreate very old data, possibly under a different name. The DB2 recovery information is not intended for these scenarios.