IBM Support

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

Technote (troubleshooting)


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)
language sql
declare i int;
set i=1;
while i<p do
insert into tab1 values;
set i=i+1;
end while;

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.

Related information

Audit Facility behavior

Document information

More support for: DB2 for Linux, UNIX and Windows
Install/Migrate/Upgrade - Install/Setup - Command Line

Software version: 9.7

Operating system(s): AIX, Linux, Windows

Reference #: 1624333

Modified date: 18 November 2015

Translate this page: