Reading Informix-SE audit trail log tables.

Question

INFORMIX-SQL 7.32 (SE):

I've created an audit trail "a_trx" for my transaction table to know who/when has added or updated rows in this table, with a snapshot of the rows contents. According to documentation, an audit table is created with the same schema of the table being audited, plus the following audit info header columns pre-fixed:

table a_trx

a_type        char(2)    {record type: aa = added, dd =deleted,
                          rr = before update image, ww = after update image.}
a_time        integer    {internal time value.}
a_process_id  smallint   {Process ID that changed record.}
a_usr_id      smallint   {User ID that changed record.}
a_rowid       integer    {Original rowid.}
[...]                    {Same columns as table being audited.}

So then I proceeded to generate a default perform screen for a_trx, but could not locate a_trx for my table selection. I aborted and ls'd the .dbs directory and did not see a_trx.dat or a_trx.idx, but found a_trx, which appears to be in .dat format, according to my disk editor utility. Is there any other method for accessing this .dat clone or do I have to trick the engine by renaming it to a_trx.dat, create an .idx companion for it, tweak SYSTABLES, SYSCOLUMNS, etc. to be able to access this audit table like any other table?.. and what is the internal time value of a_time, number of seconds since 12/31/1899?

Answer 1

The audit logs are not C-ISAM files; they are plain log files. IIRC, they are created with '.aud' as a suffix. If you get to choose the suffix, then you would create it with a '.dat' suffix, making sure the name does not conflict with any table name.

You should be able to access them as if they were a table, but you would have to create a table (data file) and the index file to match the augmented schema, and then arrange for the '.aud' file to refer to the same location as the '.dat' file - presumably via a link or possibly a symbolic link. You can specify where the table is stored in the CREATE TABLE statement in SE.

The time is a Unix time stamp - the number of seconds since 1970-01-01T00:00:00Z.