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

Technote (troubleshooting)


Problem(Abstract)

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.

Symptom

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'.



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:


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.


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

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.7

Operating system(s):

AIX, Linux, Windows

Reference #:

1624333

Modified date:

2013-02-05

Translate my page

Machine Translation

Content navigation