DB2 10.5 for Linux, UNIX, and Windows

logfilsiz - Size of log files configuration parameter

This parameter defines the size of each primary and secondary log file. The size of these log files limits the number of log records that can be written to them before they become full and a new log file is required.

Configuration type

Database

Parameter type

Configurable

Default [range]

UNIX: 1000 [4 - 1 048 572]
Windows: 1000 [4 - 1 048 572]

Note: The default value is subject to change by the DB2® Configuration Advisor after initial database creation.

Unit of measure

Pages (4 KB)

The use of primary and secondary log files as well as the action taken when a log file becomes full are dependent on the type of logging that is being performed:

Circular logging
A primary log file can be reused when the changes recorded in it have been committed. If the log file size is small and applications have processed a large number of changes to the database without committing the changes, a primary log file can quickly become full. If all primary log files become full, the database manager will allocate secondary log files to hold the new log records.
Log retain or archive logging
When a log file is full, it is closed and not overwritten. If archive logging is configured then the log file will be subsequently archived.

Recommendation: You must balance the size of the log files with the number of log files:

The value of the logfilsiz should be increased if the database has a large number of update, delete, or insert transactions running against it which will cause the log file to become full very quickly.
Note: The upper limit of log file size, combined with the upper limit of the number of log files (logprimary + logsecond), gives an upper limit of 1024 GB of active log space.

A log file that is too small can affect system performance because of the processing time of archiving old log files, allocating new log files, and waiting for a usable log file.
The value of the logfilsiz should be reduced if disk space is scarce, since primary logs are preallocated at this size.
A log file that is too large can reduce your flexibility when managing archived log files and copies of log files, since some media might not be able to hold an entire log file.

Also, the size of the log file can impact the frequency of which log files are archived and the amount of time it takes to archive an individual log file. These factors will impact the availability of log files in the archive location for disaster recovery scenarios if something happens to the primary server. The ARCHIVE LOG FOR DATABASE command can be used to truncate and archive log files on a more frequent basis if necessary.

If you are using log retention, the current active log file is closed and truncated when the last application disconnects from a database. When the next connection to the database occurs, the next log file is used. Therefore, if you understand the logging requirements of your concurrent applications, you might be able to determine a log file size that will not allocate excessive amounts of wasted space.