Audit record layout for EXECUTE events

The following table describes all of the fields that are audited as part of the EXECUTE category.

Sample audit record:
Note: Unlike other audit categories, the EXECUTE category, when the audit log is viewed in a table format, can show multiple rows describing one event. The first record describes the main event, and its event column contains the key word STATEMENT. The remaining rows describe the parameter markers or host variables, one row per parameter, and their event column contains the key word DATA. When the audit log is viewed in report format, there is one record, but it has multiple entries for the Statement Value. The DATA key word is only be present in table format.
timestamp=2006-04-10-13.20.51.029203;
  category=EXECUTE;
  audit event=STATEMENT;
  event correlator=1;
  event status=0;
  database=SAMPLE;
  userid=smith;
  authid=SMITH;
  session authid=SMITH;
  application id=*LOCAL.prodrig.060410172044;
  application name=myapp;
  package schema=NULLID;
  package name=SQLC2F0A;
  package section=201;
  uow id=2;
  activity id=3;
  statement invocation id=0;
  statement nesting level=0;
  statement text=SELECT * FROM DEPARTMENT WHERE DEPTNO = ? AND DEPTNAME = ?;
  statement isolation level=CS;
  compilation environment=
    isolation level=CS
    query optimization=5
    degree=1
    sqlrules=DB2
    refresh age=+00000000000000.000000
    schema=SMITH
    maintained table type=SYSTEM
    resolution timestamp=2006-04-10-13.20.51.000000
    federated asynchrony=0;
  value index=0;
  value type=CHAR;
  value data=C01;
  value index=1;
  value type=VARCHAR;
  value extended indicator=-1;
  value index=INFORMATION CENTER;
  local_start_time=2006-04-10-13.20.51.021507
Table 1. Audit Record Layout for EXECUTE Events
NAME FORMAT DESCRIPTION
Timestamp CHAR(26) Date and time of the audit event
Category CHAR(8) Category of audit event. Possible values are: EXECUTE
Audit Event VARCHAR(32) Specific Audit Event.

For a list of possible values, refer to the section for the EXECUTE category in Audit events.

Event Correlator INTEGER Correlation identifier for the operation being audited. Can be used to identify what audit records are associated with a single event.
Event Status INTEGER Status of audit event, represented by an SQLCODE where Successful event > = 0 Failed event < 0
Database Name CHAR(8) Name of the database for which the event was generated. Blank if this was an instance level audit event
User ID VARCHAR(1024) User ID at time of audit event.
Authorization ID VARCHAR(128) The Statement Authorization ID at time of audit event.
Session Authorization ID VARCHAR(128) The Session Authorization ID at the time of the audit event.
Origin Node Number SMALLINT Member number at which the audit event occurred
Coordinator Node Number SMALLINT Member number of the coordinator member
Application ID VARCHAR(255) Application ID in use at the time the audit event occurred.
Application Name VARCHAR(1024) Application name in use at the time the audit event occurred.
Client User ID VARCHAR(255) The value of the CURRENT CLIENT USERID special register at the time the audit event occurred
Client Accounting String VARCHAR(255) The value of the CURRENT CLIENT_ACCTNG special register at the time the audit event occurred
Client Workstation Name VARCHAR(255) The value of the CURRENT CLIENT_WRKSTNNAME special register at the time the audit event occurred
Client Application Name VARCHAR(255) The value of the CURRENT CLIENT_APPLNAME special register at the time the audit event occurred
Trusted Context Name VARCHAR(255) The name of the trusted context associated with the trusted connection.
Connection Trust type CHAR(1)
Possible values are:

'' - NONE
'1' - IMPLICIT_TRUSTED_CONNECTION
'2' - EXPLICIT_TRUSTED_CONNECTION
Role Inherited VARCHAR(128) The role inherited through a trusted connection.
Package Schema VARCHAR(128) Schema of the package in use at the time of the audit event.
Package Name VARCHAR(128) Name of package in use at the time the audit event occurred.
Package Section SMALLINT Section number in package being used at the time the audit event occurred.
Package Version VARCHAR(164) Version of the package in use at the time the audit event occurred.
Local Transaction ID VARCHAR(10) FOR BIT DATA The local transaction ID in use at the time the audit event occurred. This is the SQLU_TID structure that is part of the transaction logs.
Global Transaction ID VARCHAR(30) FOR BIT DATA The global transaction ID in use at the time the audit event occurred. This is the data field in the SQLP_GXID structure that is part of the transaction logs
UOW ID BIGINT The unit of work identifier in which an activity originates. This value is unique within an application ID for each unit of work.
Activity ID BIGINT The unique activity ID within the unit of work.
Statement Invocation ID BIGINT An identifier that distinguishes one invocation of a routine from others at the same nesting level within a unit of work. It is unique within a unit of work for a specific nesting level.
Statement Nesting Level BIGINT The level of nesting or recursion in effect when the statement was being run; each level of nesting corresponds to nested or recursive invocation of a stored procedure or user-defined function (UDF).
Activity Type VARCHAR(32) The type of activity.
Possible values are:
  • READ_DML
  • WRITE_DML
  • DDL
  • CALL
  • OTHER
Statement Text CLOB(8M) Text of the SQL or XQuery statement, if applicable.
Statement Isolation Level CHAR(8) The isolation value in effect for the statement while it was being run.
Possible values are:
  • NONE (no isolation specified)
  • UR (uncommitted read)
  • CS (cursor stability)
  • RS (read stability)
  • RR (repeatable read)
Compilation Environment Description BLOB(8K) The compilation environment used when compiling the SQL statement. You can provide this element as input to the COMPILATION_ENV table function, or to the SET COMPILATION ENVIRONMENT SQL statement
Rows Modified INTEGER
Contains the total number of rows deleted, inserted, or updated as a result of both:
  • The enforcement of constraints after a successful delete operation
  • The processing of triggered SQL statements from activated inlined triggers
If compound SQL is invoked, contains an accumulation of the number of such rows for all sub-statements. In some cases, when an error is encountered, this field contains a negative value that is an internal error pointer. This value is equivalent to the sqlerrd(5) field of the SQLCA.
Rows Returned BIGINT Contains the total number of rows returned by the statement.
Savepoint ID BIGINT The Savepoint ID in effect for the statement while it is being run. If the Audit Event is SAVEPOINT, RELEASE_SAVEPOINT or ROLLBACK_SAVEPOINT, then the Savepoint ID is the save point that is being set, released, or rolled back to.
Statement Value Index INTEGER The position of the input parameter marker or host variable used in the SQL statement.
Statement Value Type CHAR(16) A string representation of the type of a data value associated with the SQL statement. INTEGER or CHAR are examples of possible values.
Statement Value Data CLOB(128K) A string representation of a data value to the SQL statement. LOB, LONG, XML, and structured type parameters are not present. Date, time, and timestamp fields are recorded in ISO format.
Statement Value Extended Indicator INTEGER The value of the extended indicator specified for this statement value. The possible values are:
  • 0 if the statement value was specified as assigned by the indicator value,
  • -1 if NULL was specified by the indicator value,
  • -5 if DEFAULT was specified by the indicator value,
  • -7 if UNASSIGNED was specified by the indicator value.
Local Start Time CHAR(26) The time that this activity began working on the partition. This field can be an empty string when the activity does not require a package, that is, for CONNECT, CONNECT RESET, COMMIT, and ROLLBACK, as an example. The value is logged in local time.
Original User ID VARCHAR(1024) The value of the CLIENT_ORIGUSERID global variable at the time the audit event occurred.