Miscellaneous Notes



Db2 for z/OS Log Overview

The Db2 for z/OS log is one of the most important components in the Db2 for z/OS DBMS (Data Base Management System). The log plays a critical role in the maintenance of data integrity within the DBMS. It can be argued that data integrity is the top priority in any DBMS. While a feature rich and high performing DBMS is greatly desired, the ability to maintain the integrity of the data managed by the DBMS is critical to the success of the organization. A failure to maintain the integrity of critical company data would likely be disastrous for many organizations. The ability to maintain data integrity is therefore one of the highest priorities in any DBMS, and the Db2 for z/OS log plays a critical role in the maintenance of data integrity for the data it manages.

While several data integrity related issues exist in Db2 for z/OS, many of which will be noted in this paper, the issue of focus in this paper will be the recovery of lost or damaged data. The Db2 for z/OS log is a primary component in the recovery of data. The paper will briefly identify several other data integrity issues, but the primary purpose of this paper is to focus on the use of the Db2 for z/OS log during data recovery, and to convey a general understanding of Db2 log components.

This paper discusses at a high level the various components encompassing the Db2 for z/OS log. It discusses the purpose of each log component and summarizes its use by the DBMS, most specifically during data recovery.

For the remainder of this discussion, the term Db2 will mean Db2 for z/OS (as opposed to Db2 LUW, etc.). This discussion is exclusively for Db2 for z/OS, where the term Db2 is concerned and used. References to z/OS are exclusively for those services and programs executing in a z/OS operating system.

DISCLAIMER: The following discussion is a personal interpretation of the IBM provided documentation on these topics. Because interpretations of technical documentation may vary from person to person, and are therefore subject to scrutiny, the user should always rely on IBM documentation for the definitive and authoritative explanation of each topic.

This paper is as of Db2 12 for z/OS and z/OS 2.2, and was written on Nov. 10, 2017 by:

Kurt Bohnert

Manager, Db2 Systems Programming

Rocket Software, Inc.

One definition of “data integrity”, and the definition employed in this discussion is as follows: Data integrity refers to the maintenance of data to provide an assurance as to the accuracy and consistency of the data. It is a critical aspect in the design, implementation and usage of any system which stores, processes, or retrieves data (i.e. manages data).

There are many topics in Db2 for z/OS associated with any discussion of data integrity. These topics might include:

• Concurrency is the ability of >1 UOW (Unit-Of-Work) to access the same data at the same time while maintaining data integrity.

• Isolation is the degree to which the operations of one UOW are isolated from the effects of other concurrent update operations.

• Atomic Commit is the ability of a UOR (Unit-Of-Recovery) to commit changes to Db2 for z/OS data in coordination with changes to data in other (non-Db2) data stores within that same UOR.

• Back out is the ability of a UOR to choose to back out (i.e. ROLLBACK) changes to Db2 for z/OS data (as opposed to committing those changes).

• Recovery is the ability to recover lost/damaged data with full data integrity, and is the primary focus of this paper. More specifically, the use of the Db2 log in data recovery will be analyzed.

Note the distinction between a “Unit-Of-Work” (UOW) and a “Unit-Of-Recovery” (UOR). In this paper, a Db2 thread is a UOW (Unit-Of-Work) for any access to Db2. It becomes of UOR (Unit-Of-Recovery) when the first change occurs (e.g. INSERT, UPDATE, DELETE, many others) … i.e. when the first Db2 log record is written.

There are several technical topics either loosely or directly associated with the concept of data integrity in Db2 for z/OS. For all such topics, the Db2 for z/OS log plays a critical role in accommodating each requirement. That is not to say that other components of Db2 do not play a role in a Db2 data integrity discussion (e.g. Locking plays a critical role in concurrency and isolation). It is merely to say that of all the Db2 components supporting the requirement for data integrity, the log is likely the most critical.

This paper will focus on the data integrity topic pertaining to the recovery of data (data recovery). It should be noted that the implementation of data recovery in Db2 for z/OS is primarily embodied in the Db2 RECOVER utility. While there are other topics within Db2 which relate to data recovery (either directly or indirectly), the Db2 RECOVER utility, the Db2 COPY utility, and the Db2 log are arguably the most significant players in the discussion.

Let’s begin with a discussion of Db2 log components. For the purposes of this paper, any component required or employed in the recovery of Db2 managed data (specifically via the use of the Db2 RECOVER utility) will be considered a “log component” or a “log related component” within Db2.

The components of Db2 for z/OS considered to be a part of or related to the Db2 Log for data recovery purposes are as follows:

1. Boot Strap Data Set (BSDS)

2. Log Buffers

3. Active Log Data Sets

4. Archive Log Data Sets

5. One specific Db2 Catalog table … SYSIBM.SYSCOPY

6. One specific Db2 Directory object … DSNDB01.SYSLGRNX

7. Image copies (IC) or other backups of Db2 managed data

These seven (7) components are used in concert with each other to provide recovery capabilities for Db2 managed data. Each is discussed in greater detail below.

BSDS (Boot Strap Data Set)

The BSDS is a VSAM KSDS (Key Sequenced Data Set). It is a repository for many different types of information required by Db2 to perform a variety of functions. The information stored in the BSDS includes, but is not limited to, the following:

1. Active Log inventory

2. Archive Log inventory

3. BACKUP SYSTEM utility history

4. Restart/startup info (e.g. High Written Log RBA, more)

5. Data sharing (DS) info (e.g. all DS member BSDS DSN’s, more)

6. CRESTART (conditional restart) history

7. CHKPT (Checkpoint) inventory

8. ARC LOG command history

9. DDF (Distributed Data Facility) record

10. System CCSID’s

11. BP (BUFFERPOOL) information

12. GBP (Group BUFFERPOOL) information

13. Table of IP addresses

14. Much more …

The BSDS is a critical component for Db2 restart/startup and for Db2 normal operations, and much of the above information is stored in the BSDS for both purposes.

The BSDS is also a critical component in the recovery of Db2 data, and much of the information is stored in the BSDS for that purpose. This is the information on which we will focus in this discussion. Most notably, we will be interested in the Active Log inventory and the Archive Log inventory.

Note: It is a requirement in Db2 that there exist two identical copies of the BSDS, named BSDS01 and BSDS02. If either data set is damaged, it can be easily recovered from the other.

LOG BUFFERS

Log Buffers are in-memory storage located above the 2G bar in the Db2 MSTR address space. These buffers store log records written by Db2. The Db2 ZPARM OUTBUFF specifies the number of Log Buffers in a Db2 subsystem. The max storage size for Log Buffers is 400000K (409600000 bytes).

Db2 writes log records to record a variety of events, most notably events which either change data or change metadata. Db2 first writes all new log records to an available Log Buffer in memory.

When certain events occur, Db2 externalizes (writes) in-use Log Buffers to the “current” Active Log data set (see below for details on the “current” Active Log data set).

Some events which drive Log Buffer externalization include:

➢ COMMIT

➢ CHKPT (Checkpoint)

➢ LWAF (Log Write Ahead Force) … see IBM Db2 doc for details

➢ When all log buffers fill and are “in-use” … see next bullet item

When a Log Buffer fills with new log records which have not yet been externalized, the buffer is said to be in-use (not available for new log records). When a Log Buffer is externalized (written) to the current Active Log data set, the Log Buffer is then once again available to receive/store new log records.

Note that Db2 should be configured with enough Log Buffers such that there is never a case where all Log Buffers are unavailable (i.e. full, in-use). There should always be enough available Log Buffers, and log externalization should be frequent enough, to assure that Db2 never waits on an available Log Buffer to create a new log record.

ACTIVE LOGS

Active Log data sets are VSAM Linear Data Sets (LDS’s) with a 4K CISIZE. A Db2 subsystem is configured with anywhere from two (2) to ninety-three (93) Active Logs. It is however considered bad practice to employ less than three (3) Active Logs in a Db2 subsystem.

Db2 allows for the configuration of two copies of Active Log data sets (LOGCOPY1 and LOGCOPY2). They are identical, and LOGCOPY2 is used as a backup if there is a failure/error in LOGCOPY1.

The maximum size of an Active Log data set is 4GB in Db2 V11, and is increased to 768GB in Db2 V12. This makes the maximum size of the Db2 V11 Active Log 372GB (4GB x 93 = 372GB) and the maximum size of the Db2 V12 Active Log 71424GB (i.e. approx. 70TB … 768GB x 93 = 71424GB).

Db2 stores all new log records in an available Log Buffer (see above). At Log Buffer externalization, these Log Buffers are written to the “current” Active Log data set.

At any point in time, there is one “current” Active Log data set. When the current Active Log data set fills, Db2 switches to the next available Active Log data set (making it the new “current” Active Log data set), and initiates an asynchronous process to write the filled Active Log data set to an Archive Log data set (this is called “log offload”).

Once the filled Active Log data set is written to an Archive Log data set (i.e. log offload completes), that Active Log data set is made available in the Active Log rotation (see example below) to again become the current Active Log data set. Because all the log records in the data set have since been written to an Archive Log data set (i.e. offloaded), they can be overwritten with new log records as Db2 continues to externalize new log records from its Log Buffers.

Example: A Db2 subsystem is configured with five (5) Active Log data sets, named ‘hlq.LOGCOPY1.DS01’ thru ‘hlq.LOGCOPY1.DS05’ (and corresponding ‘hlq.LOGCOPY2.DS01’ thru ‘hlq.LOGCOPY2.DS05’ date sets).

Db2 places new log records into available Log Buffers in memory. When those Log Buffers fill and become in-use, they are unavailable for more new log records until they are externalized to the Active Log.

When one of the previously discussed events occurs, Db2 writes all in-use Log Buffers (which have not yet been externalized) to the current Active Log data set (in this example DS01 is the current Active Log data set). Once the Log Buffers are externalized to DS01, they become available to store new log records.

When the current Active Log data set (DS01) fills, Db2 switches from using DS01 as its current Active Log data set to DS02. Now all new log records are written from the Log Buffers to DS02 (until it fills).

After the current Active Log data set is switched from DS01 to DS02, Db2 initiates an asynchronous process to write DS01 to an Archive Log data set (aka log offload). Once this is completed, DS01 is again available to become the current Active Log data set and receive new log records from the Log Buffers.

When the next current Active Log data set (DS02) fills, Db2 switches from using DS02 as its current Active Log data set to DS03. Now all new log records are written from the Log Buffers to DS03.

The rotation continues until Db2 reaches the final Active Log data set (in this case DS05). When DS05 is filled with new log records, Db2 switches back to the top (DS01). By this time, DS01 should have completed its asynchronous log offload and once again be available for use as the “current” Active Log data set.

Note that Db2 writes the Log Buffers to both copies of the current Active Log (LOGCOPY1 and LOGCOPY2). These writes are synchronous, first to LOGCOPY1 then to LOGCOPY2.

Db2 keeps an inventory of all Active Log data sets for the Db2 subsystem, in the BSDS. This is how Db2 identifies Active Log data sets for the current Active Log data set rotation, discussed above. The BSDS identifies which of the Active Log data sets is the current (“STATUS=NOTREUSABLE”), and which are available (“STATUS=REUSABLE”) to become the next current at log switch.

If for any reason the offload process which writes the filled Active Log data set to an Archive Log data set should fail, Db2 will automatically attempt the offload process again. If the problem cannot be resolved before the rotation returns to this Active Log data set (i.e. all Active Log data sets are filled with log records not yet offloaded to an Archive Log data set), Db2 processing stops and messages are materialized in the Db2 SYSLOG (system log).

ARCHIVE LOGS

Archive Logs are BSAM (Basic Sequential Access Method) data sets, which store 4K logical images of the 4K CI’s in the Active Log. Db2 keeps an inventory of Archive Log data sets in the BSDS. Db2 will keep a maximum of 10000 entries in this inventory (which can be updated and cleaned up using a Db2 utility).

The process of writing a full active log to a new archive log is called “offloading” or “log offload”. When an Active Log data set fills, Db2 switches the current Active Log (per the example above) and immediately initiates a log offload of the full Active Log data set to a new Archive Log data set.

Note that when Db2 offloads an Active Log data set to an Archive Log data set, it also creates a copy of the BSDS. This copy of the BSDS corresponds in time to the timing of the newly created Archive Log data set. While there are several reasons for doing this, the primary reasons are offsite Disaster Recovery (DR) and BSDS recovery (in the event both copies of the BSDS are damaged or lost).

Here is an example of the two data sets created during a single Db2 log offload process:

DC1A.ARCHLOG1.D17299.T1241196.A0028570 . . . the Archive Log data set

DC1A.ARCHLOG1.D17299.T1241196.B0028570 . . . the BSDS copy

Note the reference to ARCHLOG1. One may configure Db2 to create two identical copies of the Archive Log (e.g. they may be named ARCHLOG1 and ARCHLOG2).

Note that the data set name suffix of the two data sets includes a number … e.g. A0028570 and B0028570. The next log offload will then create A0028571 and B0028571, and so on (they are sequential).

Note that the log offload process copies the BSDS first. It then copies the Active Log data set to an Archive Log data set. Consequently, the BSDS copy (B0028570 above) will not contain an entry in its Archive Log inventory for the associated new Archive Log (A0028570). This is by design, because Db2 does not want to create an entry in the BSDS Archive Log inventory for an Archive Log data set which has not yet been created.

SYSIBM.SYSCOPY

Db2 stores information in the Db2 Catalog used for data recovery. Primarily, this data is stored in the Db2 Catalog table SYSIBM.SYSCOPY. Note that there may be other catalog/directory objects Db2 may also use during recovery, but SYSCOPY is the most significant. Following is a discussion on SYSIBM.SYSCOPY. Refer to the IBM Db2 for z/OS documentation for a detailed explanation of this table, and of other Db2 catalog/directory objects not included in this discussion.

SYSIBM.SYSCOPY is a table in the Db2 Catalog which stores information “needed for recovery” (per the Db2 for z/OS SQL Reference Guide).

SYSIBM.SYSCOPY stores information on a variety of Db2 events, primarily Db2 utility executions (but also certain SQL and command operations). Below is a list of the events which result in a row insertion into this table. The letter in blue corresponds to a value stored in the column named ICTYPE for each row of the table, identifying the event which resulted in the creation of the row:

A ALTER

B REBUILD INDEX

C CREATE

D CHECK DATA LOG(NO) (no log records for the range are available)

E RECOVER (to current point)

F COPY FULL YES

I COPY FULL NO

J REORG TABLESPACE or LOAD REPLACE compression dictionary write to log

L SQL (type of operation)

M MODIFY RECOVERY utility

P RECOVER TOCOPY or RECOVER TORBA (partial recovery point)

Q QUIESCE

R LOAD REPLACE LOG(YES)

S LOAD REPLACE LOG(NO)

T TERM UTILITY command

V REPAIR VERSIONS utility

W REORG LOG(NO)

X REORG LOG(YES)

Y LOAD LOG(NO)

Z LOAD LOG(YES)

Db2 uses the information in this table for a variety of processes, but most notably for data recovery (i.e. RECOVER utility processing).

DSNDB01.SYSLGRNX

Db2 stores information in the Db2 Directory object DSNDB01.SYSLGRNX, identifying log ranges by RBA/LRSN for periods of time when a given Db2 object (page set) is open for update.

A record is inserted into SYSLGRNX for each object (i.e. page set … see the discussion on “page set” below) when it is opened for update. It closes that SYSLGRNX record when the page set is closed or pseudo closed. Each record contains an RBA/LRSN signifying the time in the log of the open and the close, for the associated page set.

Consequently, SYSLGRNX has a record of the time frames (log ranges) in which each page set was open for update, and when it was closed for update. Db2 will only write log records pertaining to a given page set when that page set is open for update. Another way of saying this is that events requiring a log record for a given page set only occur when that page set is open for update.

So SYSLGRNX records time frames (log ranges) when log records were written to the log on behalf of a given page set. We will see later in this discussion how Db2 puts this information to use during a data recovery scenario. These log ranges are used in coordination with the BSDS log inventories to identify the log data sets (and ranges) required to perform a recovery of the associated page set.

Image copies (IC) or other backups of Db2 managed data

For the purposes of this paper, “Image copies and other backups of Db2 managed data” can take many forms, including but not limited to the following:

o The result of a Db2 COPY utility (aka “image copy”, or IC)

o The result of a Db2 DSN1COPY utility

o The result of a Db2 BACKUP utility exploiting DFSMS Fast Replication

o The result of a DFDSS DUMP

o Essentially, most any copy/backup of a Db2 VSAM Linear Data Set (VSAM LDS) supporting a Db2 data object (page set) is eligible for use in a Db2 data recovery scenario

The VSAM LDS supporting a Db2 object is often referred to as a “page set”. Note that there can be a one-to-many relationship between a Db2 object and the number of page sets supporting it.

Example: A Db2 TABLESPACE (TS) can be partitioned into 1-4096 PART’s (called a Partitioned TS). Each PART will have its own VSAM LDS in support of that PART. A TS may therefore have as many as 4096 VSAM LDS’s supporting it. Each of these VSAM LDS’s is often referred to as a “page set”.

Any backup of a VSAM LDS (page set) supporting a Db2 object can usually be employed as the backup in a recovery scenario (i.e. the replacement for the damaged/missing page set). This is discussed in greater detail below. The backup may be the result of any of the previously mentioned types of backups (e.g. image copy (IC), DSN1COPY, DFDSS DUMP, etc. … see the list above).

The section on RECOVERY PROCESS below explains how and why any of the previously mentioned backups are eligible to participate in a Db2 data recovery, and how Db2 accommodates that data recovery via the Db2 log.

Now that we have identified and discussed the seven (7) components which are either a part of the Db2 log or are log related, let’s discuss how Db2 employs these components in a recovery scenario.

RECOVERY PROCESS

In most cases, the recovery scenario will involve the use of the Db2 RECOVER utility. This discussion will focus primarily on data recovery via this utility.

The largest unit of data recovery in the RECOVER utility is the table space (TS) or index space (IX). The smallest is the page. You can recover a single object or a list of objects. The RECOVER utility can recover an entire (or multiple) table space(s), index space(s), a partition(s) and/or data set(s), pages within an error range, or a single page.

The recovery can be either a full forward recovery to the current point in time, or it can be a recovery to a prior point in time (PIT). In most cases, when a recovery is performed it is to the current PIT (also called a full forward recovery), but there are cases where it is either desired or required that the recovery be to a prior PIT. This is discussed in greater detail below.

A recovery to a prior PIT can be as simple as replacing the existing page set (VSAM LDS) with a backup of that page set. When this occurs, the PIT of the recovery is the PIT at which the backup was taken.

It should be noted that such a PIT recovery (above) via the replacement of a page set with a backup carries numerous inherent risks. The list of risks associated with performing such a recovery is long and varied. A few examples of the risks associated with this are: a) recovery to a down-level data set, b) the loss of referential integrity if the related objects are not also recovered to that exact PIT, c) the risks associated with inflight changes occurring during the backup of the page set, … many other risks exist in such a scenario.

The decision to recover a Db2 page set to a prior PIT via the replacement of a page set with a backup is one which can only be made with a complete understanding of both the implications re: Db2 metadata, and the application in which the object resides. One should refer to the large volume of IBM Db2 documentation re: such a prior PIT recovery, and have excellent knowledge of both what it entails from a Db2 metadata perspective, and from a business application perspective.

Another way to recovery a page set to a prior PIT is via the use of the Db2 RECOVER utility. The utility provides options to accommodate this requirement, and these options are documented in the IBM Db2 Utility Reference Guide. While this type of recovery also carries a variety of implications which should be understood before such a recovery is performed, this method of recovery to a prior PIT is more desirable to the previously discussed method of a simple page set replacement.

The reason for this is that when the RECOVER utility is employed in such a PIT recovery, Db2 will automatically resolve some (but not necessarily all) of the associated risks (e.g. the use of the Db2 RECOVER utility can resolve incomplete UOR’s inflight at the time the backup was taken). Other implications pertaining to Referential Integrity (RI), etc., may still exist however. For that reason, the warning in red above still applies, even when using the RECOVER utility to perform a prior PIT recovery. Such a recovery should only be attempted after one has attained a complete understanding of both the implications re: Db2 metadata, and the application in which the object resides.

There will be no further discussion in this paper on recovery to a prior PIT. The topic is too long and varied, and the implications too diverse to address here. The majority of non-DR (Disaster Recovery) real world data recoveries are to the current PIT (i.e. a full forward recovery). The discussion moving forward in this paper will therefore only address full forward recovery scenarios.

When such a recovery occurs to the current PIT, at least the following two actions must (usually) take place to accommodate the recovery:

1. The damaged/missing VSAM LDS supporting the object being recovered must be replaced with a backup taken at some prior PIT.

2. Db2 must read the log forward from that prior PIT to the end of the log (i.e. the current PIT), applying log records to accommodate any changes made after the backup was taken.

Following is an explanation of how Db2 does this.

Every Db2 page set contains a “page set header” section. This header section is one of the first sections you will see in the page set header page (the first page of the page set) if you print out a Db2 page set for review.

The page set header contains a variety of information. One of the most important pieces of information in this header is the RB-RBA. RB-RBA stands for Recovery-Base Relative-Byte-Address. It is the log RBA of the highest written log record in the Db2 log, for that page set.

When Db2 externalizes a log record for a given page set (i.e. it writes a log record to the log for a change which occurred in that page set), Db2 updates the RB-RBA in the page set header to identify the RBA of that log record. That newly written log record is now the highest written (and therefore most recent) log record for that page set.

If you think of the Db2 log as a horizontal timeline, when you travel from left to right along the timeline you travel in time. Each subsequent log record is identified by an ever-increasing Log RBA.

In data sharing it is an LRSN (Log Record Sequence Number) instead of an RBA, but that is beyond the scope of this discussion. Suffice it to say that the two are functionally equivalent (RBA and LRSN) for the sake of this discussion. They both perform the same function. The RBA and the LRSN both identify a PIT in the log timeline, and each log record is identified by a unique RBA/LRSN.

The larger the RBA/LRSN, the further out in time on the log timeline (moving from left to right) the logged event (e.g. change) occurred. The higher the RBA/LRSN the more recent the event being logged.

The page set RB-RBA therefore identifies the most recent change recorded in the log for that page set. The change is usually a data change, but it could be a metadata change (e.g. the object represented by the page set was altered in some way, and that was also logged).

The page set RB-RBA logically represents the starting point in the log from which Db2 must read forward to the end of the log, to capture and apply all log records associated with that page set. Each log record identifies the page set for which it is created. Db2 can read the RB-RBA of a given page set (which had been placed in service in place of a damaged/missing page set … i.e. the new page set is one of the previously discussed backups taken at a prior PIT). Db2 can then read the log forward from that RB-RBA and apply all log records associated with that page set, to the current PIT. The page set (and supported object) are then said to be recovered to current … i.e. a full forward recovery.

Db2 can do this in several different ways.

Db2 can be directed (via RECOVER utility options) to identify the most recent image copy (IC) taken for the page set, replace the damaged/missing page set with the IC, and then traverse the log from the RB-RBA of the IC (which is also stored in SYSIBM.SYSCOPY for that IC) to the end of the log. In such a case, Db2 would access the SYSIBM.SYSCOPY table to identify the most recent IC to be used as the backup for recovery processing.

Another option is to replace the damaged page set (with a backup) outside of Db2’s control (e.g. DFDSS RESTORE of a backup taken via a prior DFDSS DUMP), and then direct Db2 (via RECOVER utility options) to only perform log processing. This is called a LOGONLY recovery. Db2 does not identify a usable backup and replace the damaged/missing page set with that backup. Db2 assumes in a LOGONLY recovery that this action was taken outside of Db2’s control. Db2 is directed with the LOGONLY keyword (in the RECOVER utility) to simply read the RB-RBA from the current (newly replaced) page set and process the log from that RB-RBA to the end of the log, for that page set.

There are many different options provided in the Db2 RECOVER utility, giving the user great latitude in determining how best to perform the recovery. See the IBM Db2 Utility Reference Guide for a detailed discussion of each RECOVER utility option.

As Db2 is processing the log forward in time during a data recovery, it uses the data stored in DSNDB01.SYSLGRNX to identify the log ranges when that page set was open for update. It is only during those time frames (log ranges) that any log records could have been written for the page set. In this way, Db2 can expedite the log forward process by skipping log ranges in which the page set was not open for update, and therefore could not have had log records written on its behalf.

As Db2 is processing the log forward in time during a data recovery, it uses the log inventories located in the BSDS. These inventories of active and archive logs are delineated by RBA/LRSN. In effect, the BSDS identifies in its log inventory the start and end RBA/LRSN associated with each active and archive log data set. In this way, Db2 can use the BSDS log inventories as a directory to identify the log data sets it must read in the recovery process, and just as importantly the log data sets Db2 does not have to read to complete the recovery. Db2 knows which log ranges it must read, courtesy of DSNDB01.SYSLGRNX, and it can find the associated log data sets in which those log ranges reside in the BSDS log inventories.

While beyond the scope of this discussion, Db2 employs a variety of internal resources and processes to accurately perform the recovery in the most timely manner possible. Some of those internal Db2 resources and processes have already been discussed. Others not yet discussed include as one example, a process called Fast Log Apply. This feature is documented in IBM Db2 documentation, and can greatly expedite the log forward recovery process. There are many such features embedded in Db2 (but not addressed in this discussion) to expedite recovery processing, and they are all documented in IBM Db2 doc. Suffice it to say IBM Db2 has made great strides not only in the ease and accuracy of data recovery, but also in the timeliness (speed) of recovery.

The discussion above summarized at a high level those components either identified as part of the Db2 log, or closely related to the Db2 log, and used for data recovery via the Db2 RECOVER utility. This topic is so complex and encompassing, with such a wide horizon of associated Db2 features and facilities, it would have been impossible to summarize all related Db2 topics (much less drill down into a great deal of detail).

For a complete understanding of all related topics, see the wide breadth of IBM Db2 documentation available for review.

Finally, let’s review a few “side notes” (miscellaneous notes) which are of interest in this discussion. These notes are simply FYI material, related to the above discussion. I have included some that I feel are most interesting. As per my constant notice above re: the complexities of this overall topic, these notes are just a few of what could be many.

For each of the following notes, it is likely that you will need to refer to IBM Db2 doc for explanation and details. They are merely intended as FYI (teaser) material, for your further investigation.

• When Db2 requires a log record for normal processing (e.g. to perform a ROLLBACK operation), the search path for that log record is:

1. Log Buffers

2. Active Log

3. Archive Log

If Db2 can find the log record in a Log Buffer, Db2 uses it.

If Db2 cannot find the log record in a Log Buffer, DB2 attempts to find it in an Active Log data set.

If Db2 cannot find the log record in an Active Log data set, Db2 goes to the archive log to find it.

• What gets logged at row UPDATE is often dependent on a variety of factors. These factors include questions such as (but not limited to) the following:

1. Is the row a fixed length row or variable length row?

Does the table contain variable length columns?

If not, then it is likely a fixed length row.

If so, then it is likely a variable length row.

What gets logged is often impacted by the answer to this question.

2. Is the row formatted in Basic Row Format (BRF) or Reordered Row Format (RRF)?

Effective with Db2 9, Db2 offered the user the option of changing the way the data in a row is organized. The pre-V9 method is called “Basic Row Format” (BRF). The new V9 method was called “Reordered Row Format” (RRF). RRF provides a variety of advantages in terms of storage and performance. Changing from BRF to RRF requires an ALTER to the table and a REORG of the page set to accommodate the change.

It should be noted that BRF is being deprecated by IBM Db2, and support will be removed in a future Db2 version/release.

3. Was the data that was changed in a table created with DATA CAPTURE CHANGES?

CREATE|ALTER TABLE … DATA CAPTURE CHANGES …

This is a CREATE|ALTER TABLE option which directs Db2 to “Write additional data about SQL updates to the log”. See IBM Db2 doc for details on what gets logged in such a scenario.

4. There are many more influences on what gets logged, not included in these notes.

• Remember that for INSERT/DELETE, the entire row is logged. For UPDATE, Db2 only logs what it must (usually a partial row).

• Logging is expensive!! IBM will tell you this. As a result, data base design and application design, particularly in heavily updated Db2 data stores and applications, is critical to performance.

• All Log Records are assigned an identifier which identifies the UOR for which the Log Record was written. This identifier is called the URID, and is the RBA of the BEGIN_UR Log Record (see below).

• This paper delineates log records into three (3) types (categories). These are not categories per IBM Db2 doc, but a manifestation (observation) of this paper only:

1. UOR Log Records (Unit-Of-Recovery)

2. CHKPT Log Records (Checkpoint)

3. Page Set Control Log Records (i.e. other)

Each Log Record has a header which tells its “type”.

UOR Log Records (summary)

1. BEGIN_UR (one for each UOR) Log Record

2. UNDO/REDO (many) log records identifying one change per log record

3. END_UR (one for each UOR) Log Record signifying UOR termination

One BEGIN_UR Log Record is written at first change (the first time a change in the UOW requires a log record). This is what elevates the thread from a UOW to a UOR.

One-to-many UNDO/REDO Log Records are written … one for each change made during the UOR.

One END-UR Log Record is written at UOR termination (usually either COMMIT or ROLLBACK).

UOR Log Records are “bundled” by URID. The URID is the RBA of the BEGIN_UR log record. The first change (INSERT, UPDATE, DELETE, etc.) drives the creation of the BEGIN_UR log record. All subsequent log records associated with this UOR include this URID RBA as its “identifier”.

Each UNDO/REDO log record identifies one change to data or index. Separate changes (separate inserts, updates, deletes) are not combined in UNDO/REDO log records. Each individual change gets its own UNDO/REDO log record.

Note that the UNDO and REDO portion of a change can either be recorded in a single UNDO/REDO log record, or in an UNDO log record and a separate REDO log record. Db2 usually decides whether to put the UNDO and REDO portions in the same or separate log records based upon varying factors, such as the amount of data which must be logged for each portion.

Each UNDO/REDO Log Record identifies the page set (PSID), the page/row (RID) in the page set, and the changed data:

o The page set (PSID)

o The RID (page # and row ID) for which the change applies

o The data changed

Each row of data in a table is uniquely identified by a RID, so Db2 can match the UNDO/REDO Log Records to the correct table/row.

A RID is usually five (5) bytes, consisting of a four (4) byte page number and a one (1) byte row ID within that page. Remember that a REORG can change the RID of a row. This has implications for IC’s taken prior to the REORG.

CHKPT Log Records (summary)

• BEGIN_CHKPT One per CHKPT.

• UR_SUMMARY One per incomplete UOR.

• PAGESET_SUMMARY One per R/W page set.

• PAGESET/UR SUMMARY (xref) Many-to-Many

• PAGESET EXCEPTION SUMMARY One for each page set w/ EXCP status

• END_CHKPT One per CHKPT

A CHKPT is a summary of log/Db2 activity. The primary purpose of a CHKPT is to speed Db2 subsystem restart time. See IBM Db2 doc for an explanation of each type of CHKPT Log Record and its various uses in Db2 processing.

Page Set Control Records (summary)

Page Set Control Records are written to the log to record certain page set “events”. Those events include but are not limited to the following:

o Create / Delete data set (page set)

o Allocate / Deallocate data set (page set)

o Open / Close data set (page set)

o Exception (aka DBET Log Records … DB Exception Table)

o Utility activity on SYSCOPY, DBD01, SYSUTILX

o Many others (essentially, everything else not discussed above)

When referencing an event identifying a page set exception state, these records are often referred to as DBET’s because they are said to be stored in the DB Exception Table.

SYSIBM.SYSCOPY records utility activity for utilities executed against page sets (see above and IBM Db2 doc for summary/details). This includes page sets supporting the Db2 Catalog and Directory. The exception to this is for the Db2 Catalog SYSIBM.SYSCOPY table itself, and for the Db2 Directory objects DSNDB01.DBD01 and DSNDB01.SYSUTILX. Utilities impacting these three system objects are not recorded in SYSIBM.SYSCOPY. They are instead recorded in the Db2 Log as Page Set Control Records. Db2 does this for reasons beyond the scope of this discussion, but this topic is well documented in IBM Db2 doc.

• Db2 provides a utility to enable one to view Log Records. It is the DSN1LOGP utility, and is documented in detail in the IBM Db2 Utility Reference Guide. Below are a few notes on this utility.

DSN1LOGP formats the contents of the recovery log for display. The utility has many options for choosing which log records to materialize, and how to materialize them. Examples:

STARTRBA(xxxxxxxxxxxx)

LRSNSTART(xxxxxxxxxxxx)

ENDRBA(xxxxxxxxxxxx)

LRSNEND(xxxxxxxxxxxx)

SUMMARY(NO|YES|ONLY)

NO Generates only a detail report

YES Generates both a detail and a summary report

ONLY Generates only a summary report

There are many more options in DSN1LOGP.

DSN1LOGP with SUMMARY(YES) option using a CHKPT RBA (which can be found in the CHKPT Inventory of the BSDS) will print a formatted CHKPT report.

When DSN1LOGP encounters a log gap, it reports it to the utility output DD with an error message identifying the gap.

• There is an entire topic on the use of DFDSS Fast Replication (FR) in the creation of page set backups and IC’s documented in IBM Db2 doc. This topic is far beyond the scope of this discussion. Suffice it to say that FR is yet another facility of many which can be employed, either via Db2 mechanisms or outside of the control of Db2, to backup and recover Db2 data.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download