Slow INSERTs after migrating from DB2 V9.5 to DB2 V9.7
After a database is migrated from DB2 V9.5 to DB2 V9.7, the INSERT may be slow, due to time being spent in writing audit log records.
This may be illustrated with the help of the following:
db2 “create procedure proc1(in p int)
declare i int;
while i<p do
insert into tab1 values;
From the CLP:
db2 "call proc1(10001)"
The Stored Procedure is called and it inserts 10000 values into the table 'tab1'.
Diagnosing the problem
db2trc with '-t' or '-perfcount' option may be used to collect the timed traces, while the INSERT statements are getting executed. The following functions may be seen to be consuming a lot of time:
These functions correspond to logging of audit records in the audit files of the instance.
Resolving the problem
Two things can be tried to make the INSERTs perform better:
- Prune the audit log files.
- Set the 'audit_buf_sz' dbm cfg parameter to a value greater than 0.