15. Performing Backup and Recovery : Journals : Audit Trails with Journals : How to Load an Audit Trail as a Table
 
Share this page                  
How to Load an Audit Trail as a Table
To make querying the audit data easier, you can create an audit trail as a file in your current directory and load the file into a table in your database.
To do this, follow these steps:
1. When you create the audit trail, use the -file flag to create an audit trail file in the current directory.
Note:  You must have first specified at least one table.
In the following example, auditdb extracts a record of the changes to the employee table from the journal for the demodb database. It places the changes in the current directory in a file named empaudit.trl.
auditdb -table=employee -file=empaudit.trl demodb
2. To copy the file into a database table, create a table to hold the audit trail data.
When creating the table, include the audit trail and employee table columns shown below. Enter the audit trail columns before the table’s columns, in the order shown. If you do not, the copy operation can fail when you try to copy the audit trail data into the table.
Column Name
Data Type
Description
date
date not null with default
Date and time of the beginning of the multi-query transaction that contained the operation
username
char(32) not null with default
User name of the user who performed the operation
operation
char(8) not null with default
Insert, update, or delete operation
tranid1
integer not null with default
Transaction identification number. Concatenated with tranid2.
tranid2
integer not null with default
Transaction identification number. Concatenated with tranid1.
table_id1
integer not null with default
Table identification number. Corresponds to value in table_reltid column of iitables system catalog for specified table.
table_id2
integer not null with default
Table identification number. Corresponds to value in table_reltidx column of iitables system catalog for specified table.
name
varchar(20)
Employee name
age
integer
Employee age
salary
money
Employee salary
dname
varchar(10)
Department name
manager
varchar(20)
Employee manager
In the following example, a table named empaudit is created to hold the data from the empaudit.trl file:
create table empaudit
(date date not null with default,
username char(32) not null with default,
operation char(8) not null with default,
tranid1 integer not null with default,
tranid2 integer not null with default,
table_id1 integer not null with default,
table_id2 integer not null with default,
name varchar(20),
age integer,
salary money,
dname varchar(10),
manager varchar(20));
The last five columns are from the employee table.
3. Use the COPY statement to load the new table with the data from the file from Step 2.
In the following example, the data in the empaudit.trl file is copied to the empaudit table:
Windows:
copy empaudit() from 'C:\users\joe\empaudit.trl';
UNIX:
copy empaudit() from '/usr/joe/empaudit.trl';
VMS:
copy empaudit() from '[usr.joe]empaudit.trl';
The table created from the audit trail (in this example, the empaudit table) contains:
• A row for each row added to the employee table
• A row for each row removed
• Two rows for each update: one showing the row before the update and the other showing the row after the update