Info from Audit Trails in SE -
Below is a very neat way of accessing the contents of an audit trail
file:
--------------------------------CUT HERE--------------------------
PROCEDURE for MANIPULATING an AUDIT TRAIL
-----------------------------------------
1. Make a copy of the CREATE TABLE script for each table you want to work
with. These copies will be used to create database tables to hold audit trail
information.
2. Edit each of the CREATE TABLE statements to include 5 new columns. The
new columns will hold the header data of each audit trail record. Give each
table a new name. Insert the five new columns shown below at the beginning of
the file:
au_type char(2)
au_time integer
au_procid smallint
au_userid smallint
au_recnum integer
3. Run the CREATE TABLE statements to build each of the new .dat files
{Example for customer table of stores database}
CREATE TABLE aud_cust
(
au_type char(2),
au_time integer,
au_procid smallint,
au_userid smallint,
au_recnum integer,
customer_num serial not null,
fname char(15),
lname char(15),
company char(20),
address1 char(20),
address2 char(20),
city char(15),
state char(2),
zipcode char(5),
phone char(18)
);
4. Query the systables catalog to see the 3 digit number assigned to each of
the table names.
5. Copy the audit trail log files to the names of the newly created .dat
files.
Example: cp aud_file stores.dbs/aud_cus109.dat
6. Create an index in each new table - one index per file on any column.
7. From ISQL, repair each of the new tables
Example: repair table aud_cust
Note: This will invoke bcheck with the -y option to rebuild the index
file.
8. You can now work with these new tables as ordinary database tables.
---------------------------- CUT HERE ----------------------------
Thanks to Scott Myers at Informix for the above.
As an additional point, if after carrying out the above up to point 4
you update the entry in systables for the logfile to point at the
audit trail file before carrying out points 6 and 7 you should have
access to the up-to-date audit trail for all of your reports. (I
thought of this bit 8-)).
Hope that this is useful to someone else.
Thanks again Scott.
--------------------------------------------------------------
| Who cares whos opinions these are?! |
| Send me mail and I'll love you forever (Maybe) |
|------------------------------------------------------------|
| Stuart Hemming | shemminga@cix.compulink.co.uk |
| Tudor Labels Ltd | uunet!cix.compulink.co.uk!shemminga |
| Roman Bank Bourne | Tel : (+44) 778 426444 |
| Lincs PE10 9LQ. UK. | Fax : (+44) 778 421862 |
--------------------------------------------------------------
Stuart
|8-)