IBM Support

How to determine what DB2 transaction log size to set to avoid DB2 transaction log being full

Troubleshooting


Problem

How to determine what DB2 transaction log size to set to avoid DB2 transaction log being full

Symptom

In a IBM Content Collector (ICC) environment, where task routes are running to archive documents into Content Manager 8 and the Content Collector Full Text Indexer is also running in parallel, sometimes the system will run into DB2 Transaction Log Full error.

Cause

DB2 transaction log getting full.

Environment

IBM Content Collector with Content Manager 8 and DB2.

Diagnosing The Problem

To determine how much transaction log size is required, perform the following steps:

  • Use the following command to find out the current transaction log settings:

    • DB2 GET DATABASE CONFIGURATION

      Look for the following parameters:


        Log file size (4KB) (LOGFILSIZ) = 12000
        Number of primary log files (LOGPRIMARY) = 20
        Number of secondary log files (LOGSECOND) = 4
  • Use the following command to set LOGSECOND to -1:

    • DB2 UPDATE DATABASE CONFIGURATION FOR icmnlsdb USING LOGSECOND -1

    This allows unlimited number of secondary log files so we can perform the calculation later.
    Restart ICC and afuIndexer so a new connection is made to the database.
  • Allow ICC task route archiving and full text indexer run as usual for a period of time.
  • At the end of the period, use the following command to find out transaction log usage:

    • DB2 GET SNAPSHOT FOR ALL DATABASES

    Look for the following parameters:

      Maximum secondary log space used (Bytes) = 4,783,589,480
      Maximum total log space used (Bytes) = 5,761,829,480

Resolving The Problem

The calculation below uses the values provided by the example above:

Log space for each log file
= LOGFILSIZ * 4KB = 12,000 * 4,000 = 48,000,000

Maximum secondary log space used (based on snapshot output)
= 4,783,589,480

Number of secondary log files required
= total space required in all secondary log files / log space for each log file
= 4,783,589,480 / 48,000,000
= 99.6
= about 100

To give about 10% more log space, number of secondary logs required
= 100 + 100*10%
= 110

Use the following command to set LOGSECOND to a finite number:


    DB2 UPDATE DATABASE CONFIGURATION FOR icmnlsdb USING LOGSECOND 110

[{"Product":{"code":"SSAE9L","label":"Content Collector"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"All Content Collector products","Platform":[{"code":"PF033","label":"Windows"}],"Version":"2.1;2.1.1;2.2;3.0;4.0","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
17 June 2018

UID

swg21678133