DB2 Health Monitor can cause Log Full Errors: SQLCODE: -964, SQLSTATE: 57011

Resolving The Problem

When running performance measurements against WebSphere Lombardi Edition 7.1.0 we have occasionally seen DB2 errors related to full transaction logs (which causes new transactions to be rolled back). The standard response to this situation is to increase the log space available (either using LOGFILSIZ or LOGPRIMARY or the like). We have done this, but as WebSphere Lombardi Edition 7.1.0 is made to run faster and faster, we notice that more and more log file space is required, which indicates the presence of a systematic problem in log file space.

While there can be many potential sources of a full database transaction log condition, this particular situation is just one instance:

DB2 contains a back ground service, DB2 Health Monitor, that periodically updates database statistics which are useful to the DB2 optimizer. WebSphere itself contains code that, at startup, acquires a share lock on a table, SIBOWNER. The presence of this lock causes the DB2 Health Monitor's statistics collection to pause with an open record in the transaction log. If mainline WebSphere Lombardi Edition 7.1.0 processing consumes all of the available log space while Health Monitor is waiting for the lock on SIBOWNER, transactions begin to rollback. This scenario is described in more detail in this WebSphere Application Server technote.

The solution is simply to exclude SIBOWNER from the automatic statistics collection driven by the Health Monitor. This can be accomplished using the DB2 Control Center GUI.

You can also use the command line interface as described in the following DB2 Information Center Topic.