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.
Rate this page:
Copyright and trademark information
IBM, the IBM logo and ibm.com are trademarks of International Business Machines Corp., registered in many jurisdictions worldwide. Other product and service names might be trademarks of IBM or other companies. A current list of IBM trademarks is available on the Web at "Copyright and trademark information" at www.ibm.com/legal/copytrade.shtml.