1

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 existin 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 variouscomponents encompassing the Db2 for z/OS log. It discusses the purpose of each log component and summarizes its use by the DBMS, most specificallyduring 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 andconsistency 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.
  • Isolationis 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.
  • Backout 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, aDb2 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 Db2log 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 Db2data 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 Db2log components. For the purposesof 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 Db2MSTR address space. These buffers store log records written by Db2. The Db2ZPARM OUTBUFF specifies the number of Log Buffers in a Db2 subsystem. The max storage sizefor 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. Db2first writes all new log records to an available Log Buffer in memory.

When certain events occur, Db2 externalizes (writes) in-useLog 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 Db2subsystem.

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).

Db2stores 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 examplebelow) 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, theyare 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 LogBuffers (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 thecurrent 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 primaryreasons 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 anumber … 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 Db2may 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 otherDb2catalog/directory objects not included in this discussion.

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

SYSIBM.SYSCOPY stores information on a variety of Db2 events, primarilyDb2 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:

AALTER

BREBUILD INDEX

CCREATE

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

ERECOVER (to current point)

FCOPY FULL YES

ICOPY FULL NO

JREORG TABLESPACE or LOAD REPLACEcompression dictionary write to log

LSQL (type of operation)

MMODIFY RECOVERY utility

PRECOVER TOCOPY or RECOVER TORBA (partial recovery point)

QQUIESCE

RLOAD REPLACE LOG(YES)

SLOAD REPLACE LOG(NO)

TTERM UTILITY command

VREPAIR VERSIONS utility

WREORG LOG(NO)

XREORG LOG(YES)

YLOAD LOG(NO)

ZLOAD 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 Db2Directory 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.