Commit delay may be encountered during DB2 log archive

Problem(Abstract)

A very large db2rhist.asc file can cause performance degradation to DML that modifies tables.

Symptom

Poor performance during inserts, updates and deletes. The application snapshots will show applications in COMMIT-ACTIVE longer than it should

Cause

During a log archive, DB2 writes to the db2rhist.asc file. As the file gets larger, the writes takes longer than usual. During this time any inserts, updates and deletes attempting to COMMIT during a log switch have to wait for the db2rhist.asc update to complete. This can momentarily 'freeze' the system for a few seconds.

This problem usually affects customers who use flash copy to backup the database. As DB2 backup is not invoked directly, the db2rhist.asc does not get pruned despite the RETENTION setting.

Diagnosing the problem

Disable HADR for isolation, as it may cause COMMIT-ACTIVE as well

 

Resolving the problem

Reduce the size of the db2rhist.asc via the prune force command periodically

http://www-01.ibm.com/support/docview.wss?uid=swg21575692

http://dbaspot.com/ibm-db2/195591-commit-active-problem-solved-db2rhist-asc.html