Developing Time-Oriented Applications in SQL | Richard T. Snodgrass

### Retaining An Audit Trail

# IBM DB2 Universal Server Implementation Examples

## Contents at a Glance

> Defining the Audit Trail
> Queries and Modifications

> > Extracting a Prior State
> > Other Queries

> Permitting Insertions
> Backlogs
> Using After Images Consistently

#

## Detailed Contents

This document outlines the implementation of an audit trail using the DB2 DBMS. It follows the general outline of the TDB book and is arranged in the following manner.

> Defining the Audit Trail

> > Implementation Details
> > Create the audit trail table
> > Create the triggers

> Queries and Modifications

> > Extracting a Prior State

> > > Execute the transactions
> > > Reconstruction Algorithm 1

> > Other Queries

> > > Reconstruction Algorithm 1 as a View
> > > Convert PROJECTIONS to a transaction state table

> Permitting Insertions

> > Create an INSERT trigger
> > Reconstruction Algorithm 2

> Backlogs

> > Reconstruction Algorithm 3

> Using After Images Consistently

> > Create the after image triggers
> > Reconstruction Algorithm 4
> > Transaction state table using after image
> > Reconstruct current table using after images
> > Define PROJECTIONS as a view on P_Audit

## Defining the Audit Trail (IBM DB2 Universal Server)

### Implementation Details

There are a few items that must be emphasized regarding the implementation of the queries in DB2. First of all, the command line processor for DB2 does not allow hard returns embedded in queries. Therefore, the batch SQL included in this chapter may be difficult to read. In some cases, such as creating tables and triggers, the SQL is provided in an easy to read format along with the code that actually runs. Secondly, triggers in DB2...

An audit trail specifies the sequence of modifications to a single table, the audited table. The audit trail consists of information about each modification, such as the date when it occurred. This allows the audited table to be reconstructed at any given time in the past. We define the audit trail for the PROJECTIONS table as the P_Audit table.

> SQL to create the audit trail table. (P_Audit.SQL)
> Code to create the audit trail table. (P_Audit.bat)

The audit trail table contains all the columns from the PROJECTIONS table as well as the When_Changed column, which indicates the date on which the values in the row were removed or changed. The key of the audit table is PROJECTION_ID and When_Changed. Note that DB2 requires that primary key columns be constrained as NOT NULL. In addition, the TIMESTAMP (instead of DATE) data type was used for the When_Changed column to facilitate testing. It is still assumed that When_Changed will be updated at most once per day. In order to track modifications to the PROJECTIONS table, we establish triggers which work behind the scenes. We define two triggers, DELETE and UPDATE, on the audited table.

> SQL to create the triggers.(Triggers.SQL)
> Code to create the audit trail table. (Triggers.bat)

In both cases, the values stored in the audit trail table are the old values. Since inserted values can be found in the audited table, there is no need for an INSERT trigger.

#

##

## Queries and Modifications (IBM DB2 Universal Server)

###

### Extracting a Prior State

The audit trail allows us to reconstruct the state of the PROJECTIONS table at any day in the past. The following transactions are executed on the PROJECTIONS table. The results of the transactions show only the table's key (PROJECTION_ID) and one additional column (PROJECTION_TYPE). Both the PROJECTIONS and the P_Audit tables are shown.

> Execute the transactions. (trans.bat)
> View the results. (trans.out)

We now wish to reconstruct the table as of April 1, 1996, that is, to see the table as it existed between transactions 7 and 8. The code to construct the table is provided below, along with the reconstructed table.

> Reconstruction Algorithm 1 (reco1.bat)
> Reconstructed Table (reco1.out)

### Other Queries

Current queries on audit trails are trivial: just perform them on the audited table, as before. More extensive queries are best realized by forming a view, which can then be queried. The following code creates such a view. The view is then queried to list the information (PROJECTION_ID and PROJECTION_TYPE) on projection type 12 as of April 1, 1996.

> Reconstruction Algorithm as a View (reco1view.bat)
> Reconstructed View Query (vquery.out)

The easiest way to perform sequenced and nonsequenced queries on an audit trail is to convert it to a transaction state table, with start and stop transaction timestamps. The following code converts PROJECTIONS to a transaction state table. The query below can be used to determine when it was recorded that projection type 12 had a particular spheroid code.

> Transaction State Table (state.bat)
> State Table Query (state.out)

#

##

## Permitting Insertions (IBM DB2 Universal Server)

The previous reconstruction makes the assumption that the table was originally constituted with insertions and any subsequent changes were either updates or deletions. Hence, no insertions are allowed after any updates or deletes. To remove this assumption, we define an INSERT trigger to include insertions in the audit trail.

> SQL to create the INSERT trigger. (InsertTrigger.SQL)
> Code to create the INSERT trigger. (InsertTrigger.bat)

Suppose an insertion of a new projection, number 6, was made on June 29, 1996. The reconstruction code in the previous section would include this projection in the reconstructed table as of April 1, 1996. We now add this transaction to the previous set of transactions and then execute the following reconstruction algorithm. The reconstructed table is also provided below.

> Reconstruction Algorithm 1 (reco2.bat)
> Reconstructed Table (reco2.out)

#

##

## Backlogs (IBM DB2 Universal Server)

Although the trigger in the previous section allows insertions to the PROJECTIONS table other than at the beginning, it does not allow insertions after a deletion. Currently a deletion followed by an insertion of the same projection is indistinguishable from two sequential updates in the audit trail. To differentiate a deletion from an update, we create a backlog, which is an audit trail containing a column that indicates which operation was done. We therefore add the Operation column to P_Audit and update the triggers to reflect the additional column as well. The following code executes a set of transactions (which includes deletions and insertions of the same projection) and then reconstructs the PROJECTIONS table as of April 1, 1996. The reconstructed table is also provided.

> Reconstruction Algorithm 3 (reco3.bat)
> Reconstructed Table (reco3.out)

In this code, a combination of before images (previous values of the row) and after images (new values of the row) appear in P_Audit. The UPDATE and DELETE triggers store before images and the INSERT trigger stores after images.

#

##

## Using After Images Consistently (IBM DB2 Universal Server)

The reconstruction algorithm can be simplified considerably if the DELETE and UPDATE triggers use after images consistently. Specifically, the DELETE trigger need not store any values, because the after image is not defined for deletions. The UPDATE trigger is modified to store the new row values. The INSERT trigger may be retained, as it already records the after image. The following code creates the modified DELETE and UPDATE triggers.

> SQL to create the after image triggers. (AfterTriggers.SQL)
> Code to create the after image triggers. (AfterTriggers.bat)

The following code executes the same set of transactions as before and reconstructs the PROJECTIONS table using after images. The reconstructed table is also provided.

> Reconstruction Algorithm 4 (reco4.bat)
> Reconstructed Table (reco4.out)

Using only after images also changes the transaction-state view. The following code converts PROJECTIONS to a transaction state table using after images.

> Transaction State Table using After Images (afterstate.bat)
> After Image State Table Query (afterstate.out)

P_Audit now contains all of PROJECTIONS, allowing the current version to be computed even more easily. The following code reconstructs the current version of the table.

> Reconstruct the current version. (current.bat)
> Reconstructed current table. (current.out)

We can also define the PROJECTIONS table as a view on P_Audit to achieve space savings, as the current information would not be in both the audited table and the backlog. However, this will require more expensive retrieval of that information. The following code defines PROJECTIONS as a view on P_Audit.

> PROJECTIONS as a view of P_Audit (projview.bat)
> P_Audit view results (projview.out)

#

#### HTML Credits:

Jose Alvin G. Gendrano, Department of Computer Science, University of Arizona (jag@cs.arizona.edu)
Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
April 27, 1999 (Last Update)

#### DB2 Specific Code Credits:

Helen M. Thomas, Department of Management Information Systems, University of Arizona (helen@loochi.bpa.arizona.edu)
December 2, 1997 (Last Update)

#