The DDL Audit Log
The DDL Audit Log captures and records the execution of all SQL and DSN commands that update the Db2 catalog.
Audited events include the following commands:
- ALTER
- BIND
- COMMENT
- CREATE
- DROP
- FREE
- GRANT
- LABEL
- REBIND
- REVOKE
- SET
- START
- STOP
Catalog Manager automatically maintains this mandatory log. The installer cannot customize the data that is written to the DDL Audit Log.
To browse the DDL Audit Log
- On the Primary Menu panel or any list panel, on the Command line, type MAINTAIN ( MAINT ).
On the Log Maintenance Menu panel, select Browse DDL Audit Log.The Browse Audit Log panel is displayed.
DEMO-R ---------------------- Browse Audit Log -----------------------------
Command ===>
Type browse specifications and press Enter.
Column Operator Value
-------------------------------------------------------------------------------
Timestamp . . . = yyyy-mm-dd-hh.mm.ss.nnnnnn
Authid . . . . . =
Sqlid . . . . . =
Return_Code . . =
Function . . . . =
Object_Type . . =
Object_Qual . . =
Object_Name . . =
Product . . . . =
You may add additional conditions in the WHERE clause below
WHERE- On the Browse Audit Log panel, in the Authid field, type an operator and a value for the authorization ID.
- (Optional) Specify a WHERE clause for the SQL statement.If you include a WHERE clause, observe the following rules:
- Use the column names as they are displayed on the Browse Audit Log panel.
- Use correct SQL case, punctuation, and syntax as required by Db2.
- Use only wildcards that are supported by Db2.
Press Enter.The Audit Log List panel is displayed.
DEMO-R ----------------------- Audit Log List ----------- Row 1 to 35 of 163
Command ===> Scroll ===> PAGE
01
Enter D or S to describe a single audit log row.
WHERE A.AUTHID LIKE 'MVSJXL%' AND A.SEQUENCE = 1
Cmd Date Time Authid ProductRtCode Action
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v---
2022-03-01 15.38 MVSJXL2 ACT131 000 DROP TRIGGER QCT.QCTR0X_DE01S01T01
2022-03-01 15.37 MVSJXL1 ACT131 000 CREATE TRIGGER . QCT.QCTR0X_DE01S0
2022-02-28 12.53 MVSJXL1 ACT131 -20314 ALTER PROCEDURE. ALTLONG.ALT128B_0
2022-02-25 10.35 MVSJXL1 ACT131 000 -START DATABASE()
2022-02-24 23.53 MVSJXL1 ACT131 000 ALTER TABLE . QCT_SCHEMA_128BYTES_
2022-02-24 23.53 MVSJXL1 ACT131 000 COMMENT ON TABLE QCT_SCHEMA_128BYT
2022-02-24 23.53 MVSJXL1 ACT131 000 LABEL ON TABLE QCT_SCHEMA_128BYTES
2022-02-24 23.53 MVSJXL1 ACT131 000 LABEL ON QCT_SCHEMA_128BYTES_LONG_
2022-02-24 23.53 MVSJXL1 ACT131 000 COMMENT ON QCT_SCHEMA_128BYTES_LON
2022-02-24 23.53 MVSJXL1 ACT131 000 ALTER TABLE . QCT_SCHEMA_128BYTES_
2022-02-24 23.53 MVSJXL1 ACT131 000 CREATE UNIQUE . INDEX QCT_SCHEMA_1
2022-02-24 23.53 MVSJXL1 ACT131 000 CREATE TABLE . QCT_SCHEMA_128BYTES
2022-02-24 23.53 MVSJXL1 ACT131 000 CREATE TABLESPACE QCTS0512 IN QCTD
2022-02-24 23.52 MVSJXL1 ACT131 -104 CREATE TABLE . QCT_SCHEMA_128BYTES
2022-02-24 14.13 MVSJXL1 ACT131 000 ALTER TABLE . QCT_SCHEMA_128BYTES_
2022-02-24 14.11 MVSJXL1 ACT131 000 CREATE TABLE . QCT_SCHEMA_128BYTES
2022-02-24 14.11 MVSJXL1 ACT131 000 CREATE UNIQUE . INDEX QCT_SCHEMA_1
2022-02-24 14.11 MVSJXL1 ACT131 000 CREATE . INDEX QCT_SCHEMA_128BYTES
2022-02-24 14.05 MVSJXL2 ACT131 000 CREATE TABLESPACE QCTS04X2 IN QCTD
2022-02-24 14.03 MVSJXL1 ACT131 -204 CREATE TABLE . QCT_SCHEMA_128BYTES
2022-02-24 14.03 MVSJXL2 ACT131 000 DROP TABLE QCT_SCHEMA_128BYTES_LON
2022-02-24 12.39 MVSJXL1 ACT131 000 GRANT .REFERENCES(COLUMN_2_LONG_NA
2022-02-24 12.39 MVSJXL1 ACT131 000 GRANT .UPDATE(COLUMN_1_LONG_NAME_C
2022-02-24 12.39 MVSJXL1 ACT131 000 GRANT .REFERENCES(COLUMN_2_LONG_NA
2022-02-24 12.39 MVSJXL1 ACT131 000 GRANT .UPDATE(COLUMN_3_LONG_NAME_C
2022-02-24 12.39 MVSJXL1 ACT131 000 GRANT .UPDATE(COLUMN_3_LONG_NAME_C
2022-02-24 12.39 MVSJXL1 ACT131 000 GRANT .UPDATE(COLUMN_3_LONG_NAME_C
2022-02-24 12.39 MVSJXL1 ACT131 000 GRANT .UPDATE(COLUMN_15_LONG_NAME_
2022-02-24 12.35 MVSJXL1 ACT131 000 REVOKE UPDATE . ON TABLE . QCT_SCH
2022-02-24 12.34 MVSJXL1 ACT131 000 REVOKE UPDATE . ON TABLE . QCT_SCH
2022-02-24 12.34 MVSJXL1 ACT131 000 REVOKE REFERENCES . ON TABLE . QCT
2022-02-24 12.34 MVSJXL1 ACT131 000 REVOKE UPDATE . ON TABLE . QCT_SCH
2022-02-24 12.34 MVSJXL1 ACT131 000 REVOKE REFERENCES . ON TABLE . QCT
2022-02-24 12.29 MVSJXL1 ACT131 000 REVOKE UPDATE . ON TABLE . QCT_SCH
2022-02-24 12.28 MVSJXL1 ACT131 000 REVOKE REFERENCES . ON TABLE . QCT- In the Cmd column for the appropriate items, type D or S to view detailed information about entries on the list.
Press Enter.The Describe Audit Log Entry panel is displayed, which shows information from the Drop Recovery Log.
DEMO-R ------------------ Describe Audit Log Entry ------- Row 1 to 19 of 19
Command ===> Scroll ===> PAGE
02
-------------------------------------------------------------------------------
FROM ACT131.AUDIT_LOG
------------------------------------------------------------------------------
Logts. . . . . . : 2022-03-01-15.37.21.080212
Sqlid. . . . . . : MVSJXL1
Authid . . . . . : MVSJXL1
Session_seq. . . : 1
Sequence . . . . : 1
Function . . . . : CREATE
Return_code. . . : 0
Object_type. . . : TRIGGER
Object_qual. . . : QCT
Object_name. . . : QCTR0X_DE01S01T01
Product. . . . . : ACT131
Action . . . . . : CREATE TRIGGER . QCT.QCTR0X_DE01S01T01 . NO CASCADE BEFORE
INSERT . ON QCT.QCTT01_DE01S01 . REFERENCING . NEW AS NEW
. FOR EACH ROW . MODE DB2SQL . WHEN (NEW.COLUMN_1 > 2)
BEGIN ATOMIC SIGNAL SQLSTATE '75002' ( . 'FIELD
INSERTED') ; END . .
------------------------------------------------------------------------------
******************************* Bottom of data ********************************- Press END to move to the next Describe Audit Log Entry panel (if you selected multiple items) or back to the Audit Log List panel.
Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*