Audit archive and extract stored procedures

The security administrator can use the SYSPROC.AUDIT_ARCHIVE stored procedure and table function, the SYSPROC.AUDIT_DELIM_EXTRACT stored procedure, and the SYSPROC.AUDIT_LIST_LOGS table function to archive audit logs and extract data to delimited files.

The security administrator can delegate use of these routines to another user by granting the user EXECUTE privilege on these routines. Only the security administrator can grant EXECUTE privilege on these routines. EXECUTE privilege WITH GRANT OPTION cannot be granted for these routines (SQLSTATE 42501).

You must be connected to a database in order to use these stored procedures and table functions to archive or list that database's audit logs.

If you copy the archived files to another database system, and you want to use the stored procedures and table functions to access them, ensure that the database name is the same, or rename the files to include the same database name.

These stored procedures and table functions do not archive or list the instance level audit log. The system administrator must use the db2audit command to archive and extract the instance level audit log.

You can use these stored procedures and table functions to perform the following operations:
Table 1. Audit system stored procedures and table functions
Stored procedure and table function Operation Comments
AUDIT_ARCHIVE Archives the current audit log. Takes the archive path as input. If the archive path is not supplied, this stored procedure takes the archive path from the audit configuration file.

The archive is run on each member, and a synchronized timestamp is appended to the name of the audit log file.

AUDIT_LIST_LOGS Returns a list of the archived audit logs at the specified path, for the current database.  
Extracts data from the binary archived logs and loads it into delimited files. The extracted audit records are placed in a delimited format suitable for loading into Db2® database tables. The output is placed in separate files, one for each category. In addition, the file auditlobs is created to hold any large objects that are included in the audit data. The file names are:
  • audit.del
  • checking.del
  • objmaint.del
  • secmaint.del
  • sysadmin.del
  • validate.del
  • context.del
  • execute.del
  • auditlobs
If the files already exist, the output is appended to them. The auditlobs file is created if the CONTEXT or EXECUTE categories are extracted. Only archived audit logs for the current database can be extracted. Only files that are visible to the coordinator member are extracted.

Only the instance owner can delete archived audit logs.