Active log data sets storage requirements

Before function level 500 or higher is activated, the maximum data set size is 4 GB. If you use 3390 volumes, in the DEFINE CLUSTER command, define the data set with a primary quantity that is big enough to hold the entire active log, and a secondary quantity of 0. Ensure that the allocation for each active log data set does not exceed the following values:

For 3390-9 volumes:

• 87375 tracks, if allocated in units of tracks
• 5825 cylinders, if allocated in units of cylinders

For 3390-A volumes:

• 5817 cylinders. 3390-A volumes are extended address volumes (EAV), for which the space allocation unit can be 21 cylinders, instead of one cylinder. For more information, see DEFINE CLUSTER command.

After function level 500 or higher is activated, the maximum data set size is 768 GB. In most cases, the best data set size is considerably less than 768 GB. When you define a data set that is greater than 4 GB, you need to specify Extended Format (EF) in the SMS data class definition.

You must also ensure that each archive log data set is at least as large as your active log data sets. In Db2 12, subsystem parameter ALCUNIT is removed, and the allocation unit for the PRIQTY and SECQTY subsystem parameters is always cylinders, so you might need to adjust those values. Set subsystem parameters PRIQTY and SECQTY so that the archive log can extend to contain the active log. If the active log data set size is greater than 4 GB, you need to specify Extended Addressability in the SMS data class definition for the archive log data sets. Doing this allows a maximum number of 123 extents for each volume, instead of 16 extents for a data set type of BASIC or LARGE. If your SMS data class attribute Override Space is set to YES, the data class space settings can override the Db2 subsystem parameter settings. In that case, you need to ensure the space quantities that you specify for the data class allow the archive logs to contain the active logs.

If the archive logs are written to tape. a large data set might require two tapes instead of one. For real tape, space might also be wasted on the tapes. Therefore, you must adjust your data set sizes that are based on the size of your real or virtual tapes. Most tape products use compaction. You can also use DFSMS to compress the archive log data sets.

When Db2 fills up one active log data set, it must offload the log data to an archive log data set. At the same time, Db2 begins writing to the next active log data set. When Db2 reaches the last active log data set, it starts again with the first active log data set. However, if the applications catch up to the offload process, the applications are suspended until the offload process can finish writing the next active log data set. Such application suspensions often happen because of the following situations:

• Large batch jobs that insert, update, or delete data.
• Frequent inserts.
• Logging of large LOBs.
• The offload operation is too slow.

To prevent such application suspensions, you can use any of the following approaches:

• Enable fast offload operations. For example, you might place the archive log data sets on a disk device that has fast channels. (You might move the data sets to tape later.)
• Allocate a large amount of active log space to support sustained bursts of insert, update, delete activity. After function level 500 or higher is activated in Db2 12, Db2 supports as many as 93 active log data sets with a maximum size of 768 GB each. This limit provides up to 71,424 GB of active log space. You must double the space calculation for dual logging. In many cases, fewer than the maximum number of data sets can be used. Two data sets might be sufficient. However, the more log space that you allocate, the larger a burst of insert, update, and delete operations can be without causing performance problems.
  During startup, Db2 allocates the BSDS data set, all of the active log data sets, and, when dual logging is used, all secondary log data sets. When in a data sharing group, Db2 also allocates peer BSDS data sets and active log data sets as necessary during execution. To avoid exhausting the space in the MVS™ task I/O table (TIOT) for the Db2 system services address space (ssnmMSTR), which can cause Db2 to fail, perform the following actions:

  1. Set the MVS TIOT size to 64K in the ALLOCxx PARMLIB member.
  2. Specify NON_VSAM_XTIOT=YES in the DEVSUPxx PARMLIB member to enable Db2 to use the MVS extended task I/O table (XTIOT) when allocating offload data sets.
  3. Specify a dynamic volume count (DVC) of 0 for any SMS data set class that is used to allocate a Db2 BSDS, active log, or secondary log data set.
  4. Reallocate any existing BSDS, active log, or secondary log data sets that were allocated with a DVC greater than 0.

Also, as a rule, reading active log data sets is faster than reading archive log data sets, even if the archive log data sets are stored on disk. (A possible exception to this rule is if you use DFSMS compression for the archive logs.) If your recovery performance depends on the speed of reading the logs, then recovery performance can benefit from having as many active log data sets as possible.

Consider the amount of log data that is written and archived each day. For example, assume you want the ability to restore the database to a point in time 48 hours ago and apply the log records from the past 48 hours with good performance. In that case, you must retain 48 hours of log data on disk. If your applications generate 100 GB of log data per day, you need 200 GB of log space on disk, to apply two days of log records from disk. All 200 GB can be saved in active log data sets if you allocate enough space. If you do not allocate enough space, then the rest of the log data must be read from archive log data sets.

How you plan to manage database recovery and how you manage your archive log data sets are closely related. If your archive log data sets are on tape (no matter whether they are virtual or real tapes), two recover jobs cannot allocate the same tape at the same time. Contention is worse if multiple data sets are stored on a single tape. For that reason, it is often best if the archive logs are stored on real disk devices instead of tape.

However, you can use the following alternative approaches to reduce the cost of storing the archive logs:

• Use inexpensive nearline drives for the archive logs.
• Store the archive log data sets on tape, and recall the archive log data sets from tape to disk before you submit recovery jobs.
• Tolerate slow recovery.

However, remember as you plan for managing Db2 logs, that it is too late to change your approach when you must recover your Db2 database.