Auxiliary audit record tables

This topic describes the structure of the auxiliary audit record tables that contribute to the formation of the AUDIT_VIEW table.

To find information about how to access the tables that store data gathered by the audit system, see the Accessing audit records topic. Under the AUDIT schema, you can find the following auxiliary audit record tables.

  • The AUDIT_RECORDS is the basic table that provides information for the AUDIT_VIEW table. It has the following content:
    Table 1. AUDIT_RECORDS
    Column name Description
    ID Record ID
    EVENT TIME Indicates the time of the event.
    USER IP Indicates the IP address of the system on which the action was performed.
    USER LOGIN Indicates the name of the user who connects to the repository indicated in the REPOSITORY NAME column.
    REPOSITORY STORAGE Indicates the URL of the repository storage where the specified event occurred.
    REPOSITORY NAME Indicates the name of the repository in the indicated repository storage where the specified event occurred.
    OBJECT NAME Indicates the name of the event object.
    OBJECT PATH Indicates the path to the event object.
    OBJECT TYPE Indicates the type of the event object.
    DATABASE Indicates the data source that is used by the event object.
    EXECUTION CONTEXT Indicates the path to the object that runs the event object. For example, a path to a procedure or visual dashboard.
    DURATION Indicates the time to process the specified event.
    CATEGORY Indicates the ID of the subsystem where the specified event occurred according to the EVENT_CATEGORY table.
    EVENT TYPE Indicates the event type ID according to the EVENT_TYPES table.
    ERROR CODE Indicates the code of the error that occurred during the specified event.

    The complete list of error messages is provided in Appendix D. Messages in the Installing and managing Db2 QMF for Workstation and Db2 QMF for WebSphere publication.
    ROWS FETCHED Indicates the number of rows retrieved by an SQL query against the database.
    ROWS UPDATED Indicates the number of rows affected by an UPDATE or INSERT SQL query against the database.
    DATABASE LOGIN Indicates the login of the user who connects to the data source indicated in the DATABASE column.
    SQLTEXTID Indicates the ID of the row with the text of the executed SQL query according to the AUDIT_RECORDS_SQL table.
    QUERY TYPE Indicates the ID of the executed query type according to the QUERY_TYPE table.
    RESULT Indicates the ID of the specified event result according to the EVENT_RESULTS tables.
  • The EVENT_TYPES table that provides data for the EVENT TYPE column of the AUDIT_RECORDS table and contains the following content:
    Table 2. EVENT_TYPES
    ID NAME
    1 READ
    2 LOGOUT
    3 DELETE
    4 ENUMERATE
    5 CREATE
    6 UPDATE
    7 RUN
    8 LOGIN
  • The EVENT_CATEGORIES table that provides data for the CATEGORY column of the AUDIT_RECORDS table contains the following content:
    Table 3. EVENT_CATEGORIES
    ID NAME
    1 REPOSITORY
    2 SECURITY
  • The EVENT_RESULTS table that provides data for the RESULT column of the AUDIT_RECORDS table and contains the following content:
    Table 4. EVENT_RESULTS
    ID NAME
    1 SUCCESS
    2 FAILED
    3 ACCESS DENIED
    4 CANCELED
  • The EVENT_OBJECT_TYPES table provides data for the OBJECT TYPE column of the AUDIT_RECORDS table and has the following structure:
    Table 5. EVENT_OBJECT_TYPES table structure
    Column name Description
    ID Object type ID
    NAME Object type name
  • The QUERY_TYPES table provides data for the QUERY TYPE column of the AUDIT_RECORDS table and contains the following content:
    Table 6. QUERY TYPES
    ID NAME
    1 RENAME
    2 ALTER
    3 CALL
    4 SET
    5 CREATE
    6 REVOKE
    7 REFRESH
    8 LOCK
    9 SELECT
    10 COMMENT
    11 DROP
    12 ACQUIRE
    13 DELETE
    14 GRANT
    15 EXPLAIN
    16 LABEL
    17 INSERT
    18 UPDATE
    19 UNKNOWN
  • The AUDIT_RECORDS_SQL table provides data for the SQLTEXTID column of the AUDIT_RECORDS table and has the following structure.
    Table 7. AUDIT_RECORDS_SQL table structure
    Column name Description
    ID SQL statement ID
    SQLTEXT SQL statement text presented as LOB