DB2 Version 10.1 for Linux, UNIX, and Windows

Creating tables to hold the DB2 audit data

Before you can work with audit data in database tables, you need to create the tables to hold the data. You should consider creating these tables in a separate schema to isolate the data in the tables from unauthorized users.

Before you begin

See the CREATE SCHEMA statement for the authorities and privileges that you require to create a schema.
See the CREATE TABLE statement for the authorities and privileges that you require to create a table.
Decide which table space you want to use to hold the tables. (This topic does not describe how to create table spaces.)

Note: The format of the tables you need to create to hold the audit data might change from release to release. New columns might be added or the size of an existing column might change. The script, db2audit.ddl, creates tables of the correct format to contain the audit records.

About this task

The examples that follow show how to create the tables to hold the records from the delimited files. If you want, you can create a separate schema to contain these tables.

If you do not want to use all of the data that is contained in the files, you can omit columns from the table definitions, or bypass creating certain tables, as required. If you omit columns from the table definitions, you must modify the commands that you use to load data into these tables.

Procedure

Issue the db2 command to open a DB2® command window.
Optional: Create a schema to hold the tables. For this example, the schema is called AUDIT:
```
   CREATE SCHEMA AUDIT
```
Optional: If you created the AUDIT schema, switch to the schema before creating any tables:
```
  SET CURRENT SCHEMA = 'AUDIT'
```
Run the script, db2audit.ddl, to create the tables that will contain the audit records.
The script db2audit.ddl is located in the sqllib/misc directory (sqllib\misc on Windows). The script assumes that a connection to the database exists and that an 8K table space is available. The command to run the script is: db2 +o -tf sqllib/misc/db2audit.ddl The tables that the script creates are: AUDIT, CHECKING, OBJMAINT, SECMAINT, SYSADMIN, VALIDATE, CONTEXT, and EXECUTE.
After you have created the tables, the security administrator can use the SYSPROC.AUDIT_DELIM_EXTRACT stored procedure, or the system administrator can use the db2audit extract command, to extract the audit records from the archived audit log files into delimited files. You can load the audit data from the delimited files into the database tables you just created.