DB2 10.5 for Linux, UNIX, and Windows

Configuration parameters for database logging

A key element of any high availability strategy is database logging. You can use database logs to record transaction information, synchronize primary and secondary (standby) databases, and roll forward a secondary database that has taken over for a failed primary database. To configure these database logging activities, you must set a variety of database configuration parameters.

Archive retry delay (archretrydelay)

Specifies the amount of time (in seconds) to wait between attempts to archive log files after the previous attempt fails. The default value is 20.

Block on log disk full (blk_log_dsk_ful)

This configuration parameter can be set to prevent disk full errors from being generated when the DB2® database manager cannot create a new log file in the active log path. Instead, the DB2 database manager will attempt to create the log file every five minutes until it succeeds. After each attempt, the DB2 database manager will write a message to the administration notification log. The only way to confirm that your application is hanging because of a log disk full condition is to monitor the administration notification log. Until the log file is successfully created, any user application that attempts to update table data will not be able to commit transactions. Read-only queries might not be directly affected; however, if a query needs to access data that is locked by an update request or a data page that is fixed in the buffer pool by the updating application, read-only queries will also be blocked.

Setting blk_log_dsk_ful to YES causes applications to hang when the DB2 database manager encounters a log disk full error. You are then able to resolve the error and the application can continue. A disk full situation can be resolved by moving old log files to another file system, by increasing the size of the file system so that hanging applications can complete, or by investigating and resolving any log archiving failures.

If blk_log_dsk_ful is set to NO, a transaction that receives a log disk full error will fail and be rolled back.

Failover archive path (failarchpath)

Specifies an alternate directory for the archive log files if there is a problem with the normal archive path (for example, if it is not accessible or full). This directory is a temporary storage area for the log files until the log archive method that failed becomes available again, at which time the log files will be moved from this directory to the path specified in the original log archiving. Moving the log files to this temporary location, helps you avoid log directory full situations. This parameter must be a fully qualified existing directory.

Primary log archive compression (logarchcompr1), secondary log archive compression (logarchcompr2)

In certain circumstances, these parameters control whether the database manager compresses archive log files. You can reduce the cost associated with storing log archive files if you use compression on the files.

Valid values for these parameters are as follows:

OFF: This value specifies that log archive files are not compressed. The default value is OFF.
ON: This value specifies that log archive files are compressed. If set dynamically, log files already archived are not compressed.

Note:

If you set the logarchmeth1 configuration parameter to a value other than DISK, TSM, or VENDOR, log archive compression has no effect regardless of the logarchcompr1 configuration parameter setting.
If you set the logarchmeth2 configuration parameter to a value other than DISK, TSM, or VENDOR, log archive compression has no effect regardless of the logarchcompr2 configuration parameter setting.

Log archive method 1 (logarchmeth1), log archive method 2 (logarchmeth2)

These parameters cause the database manager to archive log files to a location that is not the active log path. If you specify both of these parameters, each log file from the active log path that is set by the logpath configuration parameter is archived twice. This means that you will have two identical copies of archived log files from the log path in two different destinations. If you specify mirror logging by using the mirrorlogpath configuration parameter, the logarchmeth2 configuration parameter archives log files from the mirror log path instead of archiving additional copies of the log files in the active log path. This means that you have two separate copies of the log files archived in two different destinations: one copy from the log path and one copy from the mirror log path.

Valid values for these parameters are as follows:

OFF: This value specifies that the log archiving method is not used. If you set both the logarchmeth1 and logarchmeth2 configuration parameters to OFF, the database is considered to be using circular logging and is not rollforward recoverable. The default value is OFF.
LOGRETAIN: Specifies that active log files are retained and become online archive log files for use in rollforward recovery.
USEREXIT: Specifies that log retention logging is performed and that a user exit program should be used to archive and retrieve the log files. Log files are archived when they are full. They are retrieved when the rollforward utility must use them to restore a database.
DISK: You must follow this value with a colon (:) and then a fully qualified existing path name where the log files will be archived. For example, if you set the logarchmeth1 configuration parameter to DISK:/u/dbuser/archived_logs, the archive log files are placed under or in the /u/dbuser/archived_logs/INSTANCE_NAME/DBNAME/NODExxxx/LOGSTREAMxxxx/Cxxxxxxx directory.
Note: If you are archiving to tape, you can use the db2tapemgr utility to store and retrieve log files.
TSM: If specified without any additional configuration parameters, this value indicates that log files should be archived on the local Tivoli® Storage Manager (TSM) server using the default management class. If followed by a colon(:) and a TSM management class, the log files will be archived using the specified management class.
VENDOR: Specifies that a vendor library will be used to archive the log files. This value must be followed by a colon(:) and the name of the library. The APIs provided in the library must use the backup and restore APIs for vendor products.

Note:

If either logarchmeth1 or logarchmeth2 is set to a value other than OFF, the database is configured for rollforward recovery.

Log archive options 1 (logarchopt1), log archive options 2 (logarchopt2)

Specifies a string which is passed on to the TSM API or vendor APIs.

For TSM environments, use this parameter to enable the database to retrieve logs that were generated on a different TSM node, by a different TSM user, or in TSM environments using proxy nodes such as in DB2 pureScale® environments. You must provide the string in one of the following formats:

For retrieving logs generated on a different TSM node when the TSM server is not configured to support proxy node clients:
```
   "-fromnode=nodename"
```
For retrieving logs generated by a different TSM user when the TSM server is not configured to support proxy node clients:
```
   "-fromowner=ownername"
```
For retrieving logs generated on a different TSM node and by a different TSM user when the TSM server is not configured to support proxy node clients:
```
   "-fromnode=nodename -fromowner=ownername"
```
For retrieving logs generated in client proxy nodes configurations, such as in DB2 pureScale environments where there are multiples members working on the same data:
```
   "-asnodename=proxynode"
```
where nodename is the name of the TSM node that originally archived the log files, ownername is the name of the TSM user that originally archived the log files, and proxynode is the name of the shared TSM target proxy node. Each log archive options field corresponds to one of the log archive methods: logarchopt1 is used with logarchmeth1, and logarchopt2 is used with logarchmeth2.

Note:

When the -asnodename TSM option is used, data is not stored using the name of the node (nodename) of each member. The data is stored instead using the name of the shared TSM target node used by all the members within a DB2 pureScale instance.
The -fromnode option and the -fromowner option are not compatible with the -asnodename option and cannot be used together. Use the -asnodename option for TSM configurations using proxy nodes and the other two options for other types of TSM configurations. For more information, see Configuring a Tivoli Storage Manager client.

Log buffer (logbufsz)

This parameter allows you to specify the amount of memory to use as a buffer for log records before writing these records to disk. The log records are written to disk when any one of the following events occurs:

A transaction commits
The log buffer becomes full
Some other internal database manager event occurs.

Increasing the log buffer size can result in more efficient input/output (I/O) activity associated with logging, because the log records are written to disk less frequently, and more records are written each time. However, recovery can take longer with a larger log buffer size value. As well, you may be able to use a higher logbufsz setting to reduce number of reads from the log disk. (To determine if your system would benefit from this, use the log_reads monitor element to check if reading from log disk is significant.

Log file size (logfilsiz)

This parameter specifies the size of each configured log, in number of 4-KB pages.

There is a 1024 GB logical limit on the total active log space per log stream that you can configure. This limit is the result of the upper limit for each log file, which is 4 GB, and the maximum combined number of primary and secondary log files, which is 256.

The size of the log file has a direct bearing on performance. There is a performance cost for switching from one log to another. So, from a pure performance perspective, the larger the log file size the better. This parameter also indicates the log file size for archiving. In this case, a larger log file is size it not necessarily better, since a larger log file size can increase the chance of failure or cause a delay in log shipping scenarios. When considering active log space, it might be better to have a larger number of smaller log files. For example, if there are two very large log files and a transaction starts close to the end of one log file, only half of the log space remains available.

Every time a database is deactivated (all connections to the database are terminated), the log file that is currently being written is truncated. So, if a database is frequently being deactivated, it is better not to choose a large log file size because the DB2 database manager will create a large file only to have it truncated. You can use the ACTIVATE DATABASE command to avoid this cost because it prevents automatic database deactivation when the last client disconnects from the database.

Assuming that you have an application that keeps the database open to minimize processing time when opening the database, the log file size should be determined by the amount of time it takes to make offline archived log copies.

Minimizing log file loss is also an important consideration when setting the log size. Archiving operates on one entire log file at a time. If you configure larger log files, you increase the time between archiving. If the medium containing the log fails, some transaction information will probably be lost. Decreasing the log file size increases the frequency of archiving but can reduce the amount of information loss in case of a media failure because on average less log data is not yet archived at any given point in time.

Maximum log per transaction (max_log)

This parameter indicates the percentage of primary log space that can be consumed by one transaction. The value is a percentage of the value specified for the logprimary configuration parameter.

If the value is set to 0, there is no limit to the percentage of total primary log space that a transaction can consume. If an application violates the max_log configuration, the application will be forced to disconnect from the database, the transaction will be rolled back.

You can override this behavior by setting the DB2_FORCE_APP_ON_MAX_LOG registry variable to FALSE. This will cause transactions that violate the max_log configuration to fail. The application can still commit the work completed by previous statements in the unit of work, or it can roll back the completed work to undo the unit of work.

This parameter, along with the num_log_span configuration parameter, can be useful when infinite active log space is enabled. If infinite logging is on (that is, if logsecond is -1) then transactions are not restricted to the upper limit of the number of log files (logprimary + logsecond). When the value of logprimary is reached, the DB2 database manager starts to archive the active logs, rather than failing the transaction. This can cause problems if, for instance, there is a long running transaction that has been left uncommitted (perhaps caused by an application with a logic error). If this occurs, the active log space keeps growing, which might lead to poor crash recovery performance. To prevent this, you can specify values for either one or both of the max_log and num_log_span configuration parameters.

Note: The following DB2 commands are excluded from the limitation imposed by the max_log configuration parameter: ARCHIVE LOG, BACKUP DATABASE, LOAD, REORG, RESTORE DATABASE, and ROLLFORWARD DATABASE.

Mirror log path (mirrorlogpath)

To protect the logs on the primary log path from disk failure or accidental deletion, you can specify that an identical set of logs be maintained on a secondary (mirror) log path. To do this, change the value of this configuration parameter to point to a different directory. Active logs that are currently stored in the mirrored log path directory are not moved to the new location if the database is configured for rollforward recovery.

The mirrorlogpath parameter also has an effect on log archiving behavior, which you can use to further improve resilience during rollforward recovery: When both mirrorlogpath and logarchmeth2 are set, logarchmeth2 archives log files from the mirror log path instead of archiving additional copies of the log files in the active log path. You can use this log archiving behaviour to improve resilience, because a usable, archived log file from the mirror log path might still be available to continue a database recovery operation, even if a primary log file became corrupted due to a disk failure before archiving.

Because you can change the log path location, the logs needed for rollforward recovery might exist in different directories. You can change the value of this configuration parameter during a rollforward operation to allow you to access log files from a different mirror log path.

You must keep track of the location of the logs.

Changes are not applied until the database is in a consistent state. The configuration parameter database_consistent returns the status of the database.

To turn this configuration parameter off, set its value to DEFAULT.

Note:

This configuration parameter is not supported if the primary log path is a raw device.
The value specified for this parameter cannot be a raw device.
In a DB2 pureScale environment, the first member connecting to or activating the database processes configuration changes to this log path parameter. The DB2 database manager verifies that the path exists and that it has both read and write access to that path. It also creates member-specific subdirectories for the log files. If any one of these operations fails, the DB2 database manager rejects the specified path and brings the database online using the old path. If the specified path is accepted, the new value is propagated to each member. If a member fails while trying to switch to the new path, subsequent attempts to activate it or to connect to it will fail (SQL5099N). All members must use the same log path.

New log path (newlogpath)

The database logs are initially created in the following directory: db_path/instance_name/dbname/NODE0000/LOGSTREAM0000. You can change the location in which active log files are placed (and future log files will be placed) by changing the value of this configuration parameter to point to a different directory or to a device. Active logs that are currently stored in the database log path directory are not moved to the new location if the database is configured for rollforward recovery.

Because you can change the log path location, the logs needed for rollforward recovery might exist in different directories or on different devices. You can change the value of this configuration parameter during a rollforward operation to allow you to access logs in multiple locations.

You must keep track of the location of the logs.

Changes are not applied until the database is in a consistent state. The configuration parameter database_consistent returns the status of the database.

Note: In a DB2 pureScale environment, the first member connecting to or activating the database processes configuration changes to this log path parameter. The DB2 database manager verifies that the path exists and that it has both read and write access to that path. It also creates member-specific subdirectories for the log files. If any one of these operations fails, the DB2 database manager rejects the specified path and brings the database online using the old path. If the specified path is accepted, the new value is propagated to each member. If a member fails while trying to switch to the new path, subsequent attempts to activate it or to connect to it will fail (SQL5099N). All members must use the same log path.

Number of commits to group (mincommit)

This parameter allows you to delay the writing of log records to disk until a minimum number of commits have been performed. This delay can help reduce the database manager overhead associated with writing log records and, as a result, improve performance when you have multiple applications running against a database, and many commits are requested by the applications within a very short period of time.

The grouping of commits occurs only if the value of this parameter is greater than 1 and multiple applications attempt to commit their transactions at about the same time. When commit grouping is in effect, application commit requests are held until either one second has elapsed, or the number of commit requests equals the value of this parameter.

Number of archive retries on error (numarchretry)

Specifies the number of attempts that will be made to archive log files using a configured log archive method before they are archived to the path specified by the failarchpath configuration parameter. This parameter can only be used if the failarchpath configuration parameter is set. The default value is 5.

Number of active logs a transaction can span (num_log_span)

This parameter indicates the number of active log files that an active transaction can span. If the value is set to 0, there is no limit to how many log files one single transaction can span.

If an application violates the num_log_span setting, the application will be forced to disconnect from the database.

This parameter, along with the max_log configuration parameter, can be useful when infinite active log space is enabled. If infinite logging is on (that is, if logsecond is -1) then transactions are not restricted to the upper limit of the number of log files (logprimary + logsecond). When the value of logprimary is reached, the DB2 database manager starts to archive the active logs, rather than failing the transaction. This can cause problems if, for instance, there is a long running transaction that has been left uncommitted (perhaps caused by an application with a logic error). If this occurs, the active log space keeps growing, which might lead to poor crash recovery performance. To prevent this, you can specify values for either one or both of the max_log and num_log_span configuration parameters.

Note: The following DB2 commands are excluded from the limitation imposed by the num_log_span configuration parameter: ARCHIVE LOG, BACKUP DATABASE, LOAD, REORG, RESTORE DATABASE, and ROLLFORWARD DATABASE.

Overflow log path (overflowlogpath)

This parameter can be used for several functions, depending on your logging requirements. You can specify a location for the DB2 database manager to find log files that are needed for a rollforward operation. It is similar to the OVERFLOW LOG PATH option of the ROLLFORWARD command; however, instead of specifying the OVERFLOW LOG PATH option for every ROLLFORWARD command issued, you can set this configuration parameter once. If both are used, the OVERFLOW LOG PATH option will overwrite the overflowlogpath configuration parameter for that rollforward operation.

If logsecond is set to -1, you can specify a directory for the DB2 database manager to store active log files retrieved from the archive. (Active log files must be retrieved for rollback operations if they are no longer in the active log path).

If overflowlogpath is not specified, the DB2 database manager will retrieve the log files into the active log path. By specifying this parameter you can provide an additional storage resource where the DB2 database manager can place the retrieved log files. The benefit includes spreading the I/O cost to different disks, and allowing more log files to be stored in the active log path.

For example, if you are using the db2ReadLog API for replication, you can use overflowlogpath to specify a location for the DB2 database manager to search for log files that are needed for this API. If the log file is not found (in either the active log path or the overflow log path) and the database is configured for log archiving, the DB2 database manager will retrieve the log file. You can also use this parameter to specify a directory for the DB2 database manager to store the retrieved log files. The benefit comes from reducing the I/O cost on the active log path and allowing more log files to be stored in the active log path.

Setting overflowlogpath is useful when infinite logging is configured (i.e., when logsecond is set to -1). The DB2 database manager can store active log files retrieved from the archive in this path. (With infinite logging, active log files may need to be retrieved from archive, for rollback or crash recovery operations, if they are no longer in the active log path.)

If you have configured a raw device for the active log path, overflowlogpath must be configured if you want to set logsecond to -1, or if you want to use the db2ReadLog API.

To set overflowlogpath, specify a string of up to 242 bytes. The string must point to a path name, and it must be a fully qualified path name, not a relative path name. The path name must be a directory, not a raw device.

Note: In a partitioned database environment, the database partition number is automatically appended to the path. This is done to maintain the uniqueness of the path in multiple logical node configurations.

Primary log files (logprimary)

This parameter specifies the number of primary logs of size logfilsiz that will be created.

A primary log file, whether empty or full, requires the same amount of disk space. Thus, if you configure more logs than you need, you use disk space unnecessarily. If you configure too few logs, you can encounter a log-full condition. As you select the number of logs to configure, you must consider the size you make each log and whether your application can handle a log-full condition. The total log file size limit on active log space is 256 GB.

If you are enabling an existing database for rollforward recovery, change the number of primary logs to the sum of the number of primary and secondary logs, plus one.

Secondary logs (logsecond)

This parameter specifies the number of secondary log files that are created and used for recovery, if needed.

If the primary log files become full, secondary log files (of size logfilsiz) are allocated, one at a time as needed, up to the maximum number specified by this parameter. If this parameter is set to -1, the database is configured with infinite active log space. There is no limit on the size or number of in-flight transactions running on the database. Infinite active logging is useful in environments that must accommodate large jobs requiring more log space than you would normally allocate to the primary logs.

Note:

Log archiving must be enabled in order to set logsecond to -1.
If this parameter is set to -1, crash recovery time might be increased since the DB2 database manager might need to retrieve archived log files.