Preparing Audit Analysis Records for SQL Access on Extended Parallel Server

Prepare audit analysis records in the following steps:

1. If this is the first time you extract and load audit records, create the database and table into which you load the external table data. Then create an external table definition that has the same column format as the database table.

   For later operations, you can truncate the existing table and reuse it for new data. For other possible ways to reuse or expand the analysis tables, refer to your IBM Informix: Administrator's Reference and the IBM Informix: Extended Parallel Server Performance Guide.

2. With onshowaudit, extract the audit records you want to analyze into the external table.
3. Enter the SQL statement that loads data from the external table into the database table.

Creating the Database and Tables

Before you can load audit records into a table, you must create a database and a table to hold the records. By default, the CREATE DATABASE statement creates the database with privileges that allow access only to the owner, which is the appropriate security measure. You might create an ANSI-standard database for additional security, as mentioned in Revoking and Granting Privileges to Protect Audit Data.

The following SQL statement creates a database called auditlogs2000:

CREATE DATABASE auditlogs2000

After you create the database, create an OPERATIONAL table to contain the data for SQL access. To create a table named audit_logs with appropriate column names and data types for extracted audit data, use the statement that appears in Figure 10 and add location and fragmentation information as appropriate.

CREATE OPERATIONAL TABLE audit_logs (
    adttag CHAR(4),
    cosvr_id INT,
    date_time DATETIME YEAR TO FRACTION(3),
    hostname CHAR(18),
    pid INT,
    server CHAR(18),
    username CHAR(8),
    errno INT,
    code CHAR(4),
    dbname CHAR(18),
    tabid INT,
    objname CHAR(18),
    extra_1 INT,
    partno INT,
    row_num INT,
    login CHAR(8),
    flags INT,
    extra_2 VARCHAR(160,1))
...;

You create an OPERATIONAL table so that you can use the Express mode when you load the data from the external table. For information about the characteristics of OPERATIONAL tables, refer to the IBM Informix: Extended Parallel Server Performance Guide.

After you create the database and table, use the definition of the database table, audit_logs, to create an external table definition that is used as a formatting and conversion template when you load the extracted audit data. The external table definition also specifies a file to contain records that are rejected during the load process. Because onshowaudit uses a pipe character as the delimiter, you do not need to specify the delimiter character. The statement that defines the external table appears in Figure 11.

CREATE EXTERNAL TABLE load_audit
SAMEAS audit_logs
(FORMAT "DELIMITED",
   DATAFILES       ("disk:1:/data/data2load.tbl.1"
   REJECTFILE "/tmp/load_reject");;

After you create the database and internal table and define the external table, you do not need to repeat these steps.

Extract Audit Records to Load into the External Table

Use onshowaudit -l to extract selected audit records into an output file. The following example shows how to extract audit records for the user pat from all database server-managed audit files and to redirect the records to the data2load output file:

onshowaudit -I -u pat -l > data2load

For information about the command-line syntax to extract information with onshowaudit, see The onshowaudit Utility.

Loading Data from the External Table to the Database Table

To use the external table definition to load data from the data2load data file into the audit_logs database table, execute the SQL statement shown in Figure 12.

INSERT INTO audit_logs 
   SELECT * FROM load_audit;

For more information about using external tables to load data, refer to your IBM Informix: Administrator's Reference.