kfogarty.com – Database Consultants

The audit trace is useful for installations that must meticulously track specific types of DB2 events. Not every shop needs the audit trace. However, those wanting to audit by authid, specific table accesses, and other DB2 events will find the audit trace invaluable. Eight categories of audit information are provided:

• All instances in which an authorization failure occurs, for example, if USER1 attempts to SELECT information from a table for which he or she has not been granted the appropriate authority
• All executions of the DB2 data control language GRANT and REVOKE statements
• Every DDL statement issued for specific tables created by specifying AUDIT CHANGES or AUDIT ALL
• The first DELETE, INSERT, or UPDATE for an audited table
• The first SELECT for only the tables created specifying AUDIT ALL
• DML statements encountered by DB2 when binding
• All authid changes resulting from execution of the SET CURRENT SQLID statement
• All execution of DB2 utilities

This type of data is often required of critical DB2 applications housing sensitive data, such as payroll or billing applications.
Estimated overhead: Approximately 5 percent CPU overhead per transaction is added when all audit trace classes are started.

Using Audit Trace

If the site standard is to create tables with the AUDIT parameter, all audit trace classes should be started. If there are no audited tables, use the DSNZPARMs at DB2 startup to start only audit classes 1, 2 and 7 to audit authorization failures, DCL and utility execution. Aside from these types of processing, audit classes 1, 2 and 7 add no additional overhead. As must transactions do not result in authorization failures or issue GRANT, REVOKE or utilities, running these traces is cost-effective.