Creating a table for audit data

About this task

To load data files into a database with dbload, a table to receive the data must already exist.

Procedure

Create a table to hold audit data with the CREATE TABLE statement.

The order and data types of the columns is important.

Use the order shown in the example in the following example. The sample schema reflects the format of the dbload data file that onshowaudit created.

The sample CREATE TABLE statement in the following example creates an audit table with the name frag_logs. For information about the contents of each column, see Interpreting data extracted from audit records.

The sample CREATE TABLE statement in the following example does not include the WITH CRCOLS option, which is for conflict resolution during database replication. To replicate the audit database, use WITH CRCOLS in the CREATE TABLE statement.

CREATE TABLE frag_logs
(
    adttag CHAR(4) NOT NULL,
    date_time DATETIME YEAR TO FRACTION(3) NOT NULL,
    hostname VARCHAR(128) NOT NULL,
    pid INTEGER NOT NULL,
    server VARCHAR(128) NOT NULL,
    username VARCHAR(32) NOT NULL,
    errno INTEGER NOT NULL,
    code CHAR(4) NOT NULL,
    dbname VARCHAR(128),
    tabid INTEGER,
    objname VARCHAR(128),
    extra_1 INTEGER,
    partno INTEGER,
    row_num INTEGER,
    login VARCHAR(32),
    flags INTEGER,
    extra_2 VARCHAR(160)
);

The table that the statement in the preceding example creates does not have any indexes. To improve audit-analysis performance, you can place indexes on columns within the table, depending on the type of analysis that you perform. For guidance on indexing columns, see your HCL OneDB™ Performance Guide.