DB2 Version 9.7 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 or standby databases, and rollforward a secondary database that has taken over for a failed primary database. To configure these database logging activities according to your needs, 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 DB2® cannot create a new log file in the active log path. Instead, DB2 will attempt to create the log file every five minutes until it succeeds. After each attempt, DB2 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 appear to hang.

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

If blk_log_dsk_ful is set to NO, a transaction that receives a log disk full error will fail and be rolled back. In some cases, the database will come down if a transaction causes a log disk full error.

Failover Archive Path (failarchpath)

Specifies an alternate directory for the archive log files if the log archive method specified fails. 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 log archive method. By moving the log files to this temporary location, log directory full situations might be avoided. This parameter must be a fully qualified existing directory.

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 both of these parameters are specified, each log file is archived twice. This means that you will have two copies of archived log files in two different locations.

Valid values for these parameters include a media type and, in some cases, a target field. Valid values are:

OFF: Specifies that the log archiving method is not to be used. If both logarchmeth1 and logarchmeth2 are set to OFF, the database is considered to be using circular logging and will not be rollforward recoverable. This is the default.
LOGRETAIN: This value can only be used for logarchmeth1 and is equivalent to setting the logretain configuration parameter to RECOVERY. If you specify this value, the logretain configuration parameters will automatically be updated.
USEREXIT: This value is only valid for logarchmeth1 and is equivalent to setting the userexit configuration parameter to ON. If specify this value, the userexit configuration parameter will be automatically updated.
DISK: This value must be followed by a colon(:) and then a fully qualified existing path name where the log files will be archived. For example, if you set logarchmeth1 to DISK:/u/dbuser/archived_logs the archive log files will be placed in a directory called /u/dbuser/archived_logs.
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 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.
If you update the userexit or logretain configuration parameters logarchmeth1 will automatically be updated and vice versa. However, if you are using either userexit or logretain, logarchmeth2 must be set to OFF.

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 these parameters to enable the database manager to retrieve logs that were generated on a different TSM node, by a different TSM user, or in TSM environments using proxy nodes. The string must be provided in one of the following format:

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 by any user when the TSM server is configured to support proxy nodes clients:
```
   "-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 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: 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 results 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.

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 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 2 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 DB2 will create a large file only to have it truncated. You can use the ACTIVATE DATABASE command to avoid this cost, and having the buffer pool primed will also help with performance.

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 takes an entire log. If you use a single large log, you increase the time between archiving. If the medium containing the log fails, some transaction information will probably be lost. Decreasing the log size increases the frequency of archiving but can reduce the amount of information loss in case of a media failure since the smaller logs before the one lost can be used.

Log retain (logretain)

This configuration parameter has been replaced by logarchmeth1. It is still supported for compatibility with previous versions of DB2.

If logretain is set to RECOVERY, archived logs are kept in the database log path directory, and the database is considered to be recoverable, meaning that rollforward recovery is enabled.

Note: The default value for the logretain database configuration parameter does not support rollforward recovery. You must change the value of this parameter if you are going to use rollforward recovery.

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, and error SQL1224N will be returned.

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 and return error SQL0964C. The application can still commit the work completed by previous statements in the unit or work, or it can roll the work completed back to undo the unit of work.

This parameter, along with the num_log_span configuration parameter, can be useful when infinite active logspace 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, DB2 starts to archive the active logs, rather than failing the transaction. This can cause problems if, for instance, there is a long running transactions that has been left uncommitted (perhaps caused by a bad application). If this occurs, the active logspace 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 or 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.

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

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.

New log path (newlogpath)

The database logs are initially created in SQLOGDIR, which is a subdirectory of the database directory. You can change the location in which active logs and future archived logs are 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.

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 if the number of applications connected to the database is greater than the value of this parameter. 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 the specified 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 log 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 configuration, the application will be forced to disconnect from the database and error SQL1224N will be returned.

This parameter, along with the max_log configuration parameter, can be useful when infinite active logspace 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, DB2 starts to archive the active logs, rather than failing the transaction. This can cause problems if, for instance, there is a long running transactions that has been left uncommitted (perhaps caused by a bad application). If this occurs, the active logspace 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 or 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 DB2 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 DB2 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, DB2 will retrieve the log files into the active log path. By specifying this parameter you can provide additional resource for DB2 to store 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 DB2 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 with userexit enabled, DB2 will retrieve the log file. You can also use this parameter to specify a directory for DB2 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.

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 logs (logprimary)

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

A primary log, 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 1. Additional information is logged for LONG VARCHAR and LOB fields in a database enabled for rollforward recovery.

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 DB2 might need to retrieve archived log files.

User exit (userexit)

This configuration parameter has been replaced by logarchmeth1. It is still supported for compatibility with previous versions of DB2.

This parameter causes the database manager to call a user exit program for archiving and retrieving logs. The log files are archived in a location that is different from the active log path. If userexit is set to ON, rollforward recovery is enabled.

The data transfer speed of the device you use to store offline archived logs, and the software used to make the copies, must at a minimum match the average rate at which the database manager writes data in the logs. If the transfer speed cannot keep up with new log data being generated, you might run out of disk space if logging activity continues for a sufficiently long period of time. The amount of time it takes to run out of disk space is determined by the amount of free disk space. If this happens, database processing stops.

The data transfer speed is most significant when using tape or an optical medium. Some tape devices require the same amount of time to copy a file, regardless of its size. You must determine the capabilities of your archiving device.

Tape devices have other considerations. The frequency of the archiving request is important. For example, if the time taken to complete any copy operation is five minutes, the log should be large enough to hold five minutes of log data during your peak work load. The tape device might have design limits that restrict the number of operations per day. These factors must be considered when you determine the log size.

Note:

This value must be set to ON to enable infinite active log space.
The default value for the userexit database configuration parameter does not support rollforward recovery, and must be changed if you are going to use it.