Slow INSERTs after migrating from DB2 V9.5 to DB2 V9.7

This may be illustrated with the help of the following:

db2 “create procedure proc1(in p int)
language sql
begin
declare i int;
set i=1;
while i<p do
insert into tab1 values;
set i=i+1;
end while;
end”

From the CLP:
db2 "call proc1(10001)"

The Stored Procedure is called and it inserts 10000 values into the table 'tab1'.

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:

sqlr_audit_context
sqlr_audit
sqlexAuditData::logEvent
sqlexAuditBufferManager::final
sqlexAuditBufferManager::write
sqlexAuditWriteBufferToDisk
..
.....

These functions correspond to logging of audit records in the audit files of the instance.

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.