Troubleshooting
Problem
The logs of a DB2 Version 9.5 database become full when it is used as the data store for a messaging engine in a service integration bus, and automatic statistics collection is enabled in the database.
Symptom
The symptoms for this issue are the same as when the workload placed on the database is too large for the size of the DB2 circular log. However, this issue occurs under a load that should be within the capacity of the log.
FFDCs logged by the default messaging provider include com.ibm.ws.sib.msgstore.PersistenceException exceptions caused by DB2 exceptions with SQLCODE: -964, SQLSTATE: 57011 and the following message:
| SQL0964C: The transaction log for the database is full. |
Cause
The problem is caused by one of the issues described in the following DB2 Version 9.5 APARs:
IZ37191: AUTOMATIC STATISTICS COLLECTION MAY SOMETIMES SLOW DOWN TRANSACTION PROCESSING
IZ62631: INTERNAL LOCKS ARE IGNORING LOCK TIMEOUT SETTING
The default messaging provider for WebSphere Application Server is susceptible to this APAR due to the lock a messaging engine holds on a table in the database whenever it is running.
This lock prevents any RUNSTATS operation from completing on the SIBOWNER table against which the lock is held.
See the "Data store exclusive access" section of the WebSphere Application Server Information Center for more information on the lock held by the messaging engine.
When the auto_runstats configuration parameter is enabled in DB2, RUNSTATS operations occur automatically against all tables in the database.
The automatic statistics collection is designed to handle the case where statistics cannot be collected on a table due to an active lock. However, IZ37191 causes a long running database transaction to be created by the automatic statistics collection tool while it is attempting to gather the statistics, which in turn can cause the database logs to become full.
Environment
This issue affects users of the default messaging provider for WebSphere Application Server, who have configured a messaging engine within a service integration bus to use a DB2 Version 9.5 database as a data store.
Diagnosing The Problem
Database transaction logs can become full for a number of reasons that are not covered by this technote. Refer to the documentation for your database for additional guidance.
Resolving The Problem
Configure an automatic maintenance policy for the database that excludes the SIBOWNER table from the automatic statistics collection.
The automatic maintenance policy can be configured using the Configure Automatic Maintenance wizard in the DB2 Control Center. An example is provided in the following developerWorks article:
Automatic table maintenance in DB2, Part 1
Alternatively, see the Configuring an automated maintenance policy using SYSPROC.AUTOMAINT_SET_POLICY or SYSPROC.AUTOMAINT_SET_POLICYFILE topic of the DB2 Version 9.5 Information Center.
Was this topic helpful?
Document Information
Modified date:
15 June 2018
UID
swg21377796