Auditing specific tables

You can issue the CREATE TABLE or ALTER TABLE statement to audit a specific table.

About this task

For the CREATE TABLE statement, the default audit option is NONE. For the ALTER TABLE statement, no default option exists. If you do not use the AUDIT clause in an ALTER TABLE statement, the audit option for the table is unchanged.

When CREATE TABLE statements or ALTER TABLE statements affect the audit of a table, you can audit those statements. However, the results of those audits are in audit class 3, not in class 4 or class 5. Use audit class 3 to determine whether auditing was turned off for a table for an interval of time.

If an ALTER TABLE statement turns auditing on or off for a specific table, any packages that use the table are invalidated and must be rebound. If you change the auditing status, the change does not affect packages, or dynamic SQL statements that are currently running. The change is effective only for packages or dynamic SQL statements that begin running after the ALTER TABLE statement has completed.

To audit your table, choose any of the following options:

• To audit a table whenever the audit trace is on, include the AUDIT CHANGES option when you create the table.

  CREATE TABLE DSN8C10.DEPT
        (DEPTNO    CHAR(3)           NOT NULL,
         DEPTNAME  VARCHAR(36)       NOT NULL,
         MGRNO     CHAR(6)                   ,
         ADMRDEPT  CHAR(3)           NOT NULL,
         LOCATION  CHAR(16)                  ,
         PRIMARY KEY (DEPTNO)                )
    IN DSN8D12A.DSN8S12D
    AUDIT CHANGES;

  Because this statement includes the AUDIT CHANGES option, Db2 audits the table for each access that inserts, updates, or deletes data (trace class 4).

• To also audit the table for read accesses (class 5), issue the following statement:

  ALTER TABLE DSN8C10.DEPT
    AUDIT ALL;

  The statement is effective regardless of whether the table was previously chosen for auditing.