Problem(Abstract)
Slow transaction commit times may be seen occasionally when the recovery history file grows very large.
Cause
Slow transaction commit times may sometimes be caused because of a slowdown when writing to the History File.
If the database History File size has grown to a large size (symptoms may start appearing at 10MB and up), then it might start hampering the commit performance by a few seconds.
In general, the mechanism of commit is as follows:
1) Thread doing Commit puts in the request to write the log record and waits
2) if MINCOMMIT=1 the db2loggw thread takes the record and writes the record to disk
3) if it is the end of log file, it requests a new log file to be set up and db2loggr fulfills the request
4) db2loggr has to write into the history file that the log file has been cut
5) Also, writes the same info in the backup file as well.
6) Sends a request to db2logmgr to archive the log file
7) db2loggw resumes writing
If by any chance the history file has become large enough that it cannot be held into memory any longer, it has to write to disk to update it. And since it has to do it serially, it has to read all the records in a serial fashion and append to the history file, this can cause a slowdown.
Resolving the problem
1) Prune the History File as much as possible using the PRUNE HISTORY command and see if the performance becomes consistent.
2) Consider lowering the value of REC_HIS_RETENTN
http://www-01.ibm.com/support/docview.wss?uid=swg21646712&myns=swgimgmt&mynp=OCSSEPGG&mync=E