Synchronous or asynchronous replication are the two primary ways of moving data from the main table to the audit table:
Asynchronous replication: The most common way to capture audit information is via a log analysis product. Several available products will read log records and create either SQL statements to insert data into the audit table or load-ready records in a flat file that can be loaded via a LOAD utility. Use of these tools usually requires that DATA CAPTURE CHANGES be set on for all the tables to be audited. This feature lets DB2 capture full row images for replication in the DB2 log, as opposed to partial images if log records are written for backout and recovery only.
Having an asynchronous replication process can be beneficial because the applications that update the data don’t incur the overhead involved with writing directly to the audit tables, and there’s no additional dependency of the audit tables on the applications that perform updates. The asynchronous process does have a drawback, however, in that replicated data isn’t always immediately available.
Synchronous replication to the audit table typically involves use of DB2 triggers. A trigger is a piece of application code, written in SQL, which can be attached to a table and activated by an insert, update, or delete against the table. Triggers are a completely synchronous process that adds a code path to the statements issued against the table on which the trigger is defined. So, in replicating changes to an audit table, the trigger code would insert a before image of the data to the audit table. This would, of course, increase the cost of the application making the data changes.
Figure 3 shows two triggers, defined on the previous MAIN_TABLE, that replicate before images to the corresponding AUDIT_TABLE.
It’s easy to set up a synchronous replication process, and doing so means the audit data is immediately available. However, having the triggers means increased application costs and dependency for the main application on the audit tables’ availability. This introduces some additional risk to your main applications.
You can use audit tables to produce point-in-time images of your data. Maybe for legal reasons, you need to see how an account looked at a particular time. The following example, using audit tables, can provide this ability.