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.

Important

For information about purging the DDL Audit Log, see Purging-the-Session-Log.

To browse the DDL Audit Log

  1. On the Primary Menu panel or any list panel, on the Command line, type MAINTAIN ( MAINT ).
  2. 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                                                                          
  3. On the Browse Audit Log panel, in the Authid field, type an operator and a value for the authorization ID.
  4. (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.
  5. 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 
  6. In the Cmd column for the appropriate items, type D or S to view detailed information about entries on the list.
  7. 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 ********************************
  8. 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*