Tuning the DB2 transaction log size

DB2® transaction log space is defined by the following DB2 parameters:

• LOGFILSIZ
• LOGPRIMARY
• LOGSECOND
• NEWLOGPATH

The output from the following DB2 command includes the current setting of these parameters:

db2 get database configuration for ldapdb2 |
egrep 'LOGFILSIZ|LOGPRIMARY|LOGSECOND|NEWLOGPATH|Path to log files'

The transaction log parameters must be tuned to allow the transaction logs to grow to their maximum required size.The transaction log size is limited by the LOGFILSIZ, LOGPRIMARY, and LOGSECOND DB2 parameter values, and also by the available disk space in the directory specified by the NEWLOGPATH DB2 parameter.

The Tivoli Directory Server uses transaction log disk space for storing uncommitted DB2 transactions from directory update operations.

The transaction log size is limited both by DB2 parameter values and the available disk space. If the transaction logs exceeds the limit, due to the settings of the DB2 size parameter, the transaction is backed out using the information in the transaction logs, and the transaction fails. If the transaction logs exceed the limit, due to a lack of available disk space, the database becomes corrupted, and goes into an unusable state.

If the database becomes corrupted in this way, it is possible, but more complex, to issue DB2 commands to recover the database. Alternatively, the database can be restored from a backup or reloaded.

In general, regular backups help reduce the complexity of recovering from failures, but the best practice is to ensure there is enough disk space to allow the transaction logs to grow to the DB2 parameter limits.

In normal run time use of the Tivoli Directory Server, the transaction log requirements are small. For the bulkload utility, the transaction log requirements are even smaller. Some run time directory operations increases the transaction log requirements for a short period.

Here are a few examples:

• The ldapadd or ldapmodify commands use an increasing amount of transaction log space as the number of multi-valued attributes added to a single LDAP entry in a single command grows, for example as in loading many members into a group.

  The transaction log requirements to load a 3M user group are around 300 MB.

  Note: The Tivoli Directory Server bulkload command with the (-G) option can be used as an alternative to using the ldapadd and ldapmodify commands to add many members to an existing group without using transaction log space.
• An ACL placed on a suffix object can result in that ACL propagating to every entry under that suffix.

  The Tivoli Directory Server performs ACL propagation as one single committed DB2 transaction.

  The transaction log requirements to propagate an ACL to a suffix with 3M Security Access Manager users in the standard (old) data format is around 1.2GB.

  The Security Access Manager fixacls.sh tuning guide script has the same transaction log requirement as ACL propagation, since it performs a similar function.

Using the 1.2GB requirement for the propagation of ACLs to 3M Security Access Manager users, the transaction log requirements are approximately:

1.2*1024MB / 3 million users = 410 bytes per user

By default the DB2 transaction log file size (LOGFILSIZ) is defined to be 2000 blocks of 4 KB in size or 8000 KB per log file. The number of primary logs files (LOGPRIMARY) is defined as 3 and the number of secondary log files (LOGSECOND) is 2.

The following formula shows the default transaction log space limit:

2000 * 4096 * ( 3 + 2 ) = 39.1MB

The default is sufficient for approximately 100 thousand users. In order to increase the DB2 transaction log limits to allow for millions of users, it is necessary to increase the size of the transaction logs (LOGFILSIZ), and increase the number of secondary files (LOGSECOND). It is better to increase the number of secondary files, rather than the number of primary files, because the secondary files periodically get deleted when not in use.

The db2_tuning.sh script can be used to adjust the DB2 transaction log parameters for the worst case of propagating ACLs. This script modifies the transaction log file size (LOGFILSIZE) to 10000 blocks of 4 KB in size or about 39 MB per log file. The script modifies the number of primary log files (LOGPRIMARY) to 2. The script prompts for the number of Security Access Manager users that are loaded into the directory server and sets the number of secondary logs (LOGSECOND) according to the following formula

LOGSECOND = (<num AM users> * 410) / <39MB log file size> - 2 primary log file + 1

This transaction log setting can be performance manually using this DB2 commands:

db2 update database configuration for LOGFILSIZE using 10000
db2 update database configuration for LOGPRIMARY using 2
db2 terminate
db2 force applications all
db2 connect to ldapdb2 db2 get database configuration for ldapdb2 | \
egrep 'LOGFILSIZ|LOGPRIMARY|LOGSECOND|NEWLOGPATH|Path to log files'

For Tivoli Directory Server, you can set the LOGFILSIZ and NEWLOGPATH parameters using the performance tuning tool idsperftune.

If the db2 force applications all command is issued while the Tivoli Directory Server is running, the server becomes partially functional. See the note at the beginning of this section for more details.

The db2_tuning.sh script verifies that the disk space in the directory specified in the NEWLOGPATH DB2 parameters is sufficiently large to allow the transaction logs to grow the limits defined by the DB2 parameters. Use the UNIX df command or check the properties of the folder on a Windows systems to do this manually.

Here is an example of the df command:

df -k /home/idsldap/idsldap/NODE0000/SQL00001/SQLOGDIR/

where /home/idsldap/idsldap/NODE0000/SQL00001/SQLOGDIR/ is the current setting of the NEWLOGPATH DB2 parameter.

The free disk space must be greater than or equal to the size defined by the following formula:

( LOGFILSIZ * 4096 * ( LOGPRIMARY + LOGSECOND ) / 1024 )

This command and formula give the transaction log limits in kilobytes.