DB2 Hands-on: Auditing SQL Statements for A Specific Table

Steps

Step 1: Define and Apply Audit Policy
First, create a policy that targets the EXECUTE category with data capturing.

CONNECT TO <dbname>;
CREATE AUDIT POLICY AUDIT_SQL CATEGORIES EXECUTE WITH DATA STATUS BOTH ERROR TYPE NORMAL;
AUDIT TABLE <schema>.<table> USING POLICY AUDIT_SQL;
COMMIT;

Step 2: Configure Database Level Auditing
Set up the environment variables and configure the db2audit tool to point to the desired log paths.

# Define environment variables
DBNAME="<dbname>"
AUDITPATH="<path_for_audit_files>"

# Reset and configure audit settings
db2audit stop
db2audit configure reset
db2audit configure scope checking status none
db2audit configure archivepath $AUDITPATH
db2audit configure datapath $AUDITPATH
db2audit start
>>>>> Now your database is under database auditing by the AUDIT_SQL policy

Step 3: Reproduction and Data Extraction
After the SQL statements are executed, follow these steps to extract the human-readable log.

db2audit flush
db2audit archive database $DBNAME
db2audit stop

# Extract the archived file
db2audit extract file db2audit.db.out from path $AUDITPATH files <archived_audit_file>
(e.g., db2audit extract file db2audit.db.out from path $AUDITPATH files db2audit.db.SAMPLE.log.0.20260401121400)

Step 4: Reviewing and Analyzing the Extracted Audit Records
Once you open the db2audit.db.out file, you can verify the captured SQL details. The record below shows a successful audit of an UPDATE statement, including the exact SQL text and the user who executed it:

timestamp=2026-04-01-12.13.30.164749;
 category=EXECUTE;
 audit event=STATEMENT;
 event correlator=7;
 event status=0;
 database=TST;
 userid=v11590;
 authid=V11590;
 session authid=V11590;
 application id=*LOCAL.v11590.260401011231;
 application name=db2bp;
 package schema=V11590;
 package name=SQLC2P31;
 package section=203;
 local transaction id=0x934c030000000000;
 global transaction id=0x0000000000000000000000000000000000000000;
 uow id=2;
 activity id=1;
 statement invocation id=0;
 statement nesting level=0;
 activity type=WRITE_DML;
 statement text=update tsttbl set c1=2000 where c1=1;
 statement isolation level=CS;
 Compilation Environment Description
   isolation: CS
   query optimization: 5
   min dec div 3: NO
   degree: 1
   SQL rules: DB2
   refresh age: +00000000000000.000000
   resolution timestamp: 2026-04-01-12.13.30.000000
   federated asynchrony: 0
   temporal business time: 0000-00-00-00.00.00.000000000000
   temporal system time: 0000-00-00-00.00.00.000000000000
   reopt: NONE
   blocking: BLOCK
   SQL math warning: NO
   static read only: NO
   bus time sensitive: YES
   sys time sensitive: YES
   string units: SYSTEM
   extended indicators: NO
   intra parallel: FALSE
   schema: V11590
   maintained table type: SYSTEM
   tenantID: 0;
 rows modified=1;
 rows returned=0;
 local start time=2026-04-01-12.13.30.164593;
 instance name=v11590;
 hostname=xxxxx.xxx.com;