Sep 15 ’08

Going Back in Time: How to Leverage Data Imaging for DB2 Tables

by Editor in z/Journal

You may wish you could go back and experience life at a particular point in time. While there isn’t a time machine just yet, there is a way to create one for your data.

You can create a way to view “as of ” data using DB2 tables to image your data before and after changes occurred and then be able to see your data at a point in time, or see how your data has changed over time. You can do this while also being able to back out corrupted data changes without a major outage. It may take a bit of design work on both the database and application, but ultimately, you can provide instant access to “as of ” data.

Auditing Table Changes

To produce the “as of ” data images there must be a place in the database where data that has changed will be stored. There are many ways to store changes to data; establishing audit tables is one of the most common. The audit table’s major purpose is to record images of rows of data as they existed before or after a change to the row. An audit table has the same design as its corresponding base table except for an additional key field—typically a timestamp field to be able to store multiple versions of a row. Figure 1 shows that the AUDIT_TABLE contains the same data as the MAIN_TABLE, but the AUDIT_TABLE contains a start timestamp and end timestamp vs. the MAIN_TABLE update timestamp.

These timestamps represent times in which the data stored in the MAIN_TABLE was active. Whenever a row is updated or deleted in the MAIN_TABLE, a row is written to the AUDIT_TABLE to record the period of time in which the image of the row was active before the change was made.

So, in Figure 2, the MAIN_TABLE reflects the current condition of the data, and the AUDIT_TABLE holds the data that was active for the range, using the before image of the update timestamp as the start timestamp and the after image as the end timestamp. Deletes are typically similarly recorded; inserts aren’t typically recorded, but can be.


Automated Data Replication

Population of audit tables can be a programmatic or automated process. Typically, an automated process is preferred because there’s little or no application programming involved. An automated audit process also lets you turn the process on or off without application programming changes.

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:

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.

Point-in-Time Images

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.

Figure 4 shows a base account table and its corresponding audit table. The base table holds current data with an update timestamp reflecting when the last update was performed, and the audit table holds the data as it looked before it was updated. It contains the update timestamp (UPDATE_TSP), which is the time the last update to the data occurred, and the entry timestamp (ENTRY_TSP), which is the time the current update or delete occurred and the row was placed in the audit table. There’s also a delete flag to indicate that the row was deleted from the base table and now the before image resides in the audit table.


For a point-in-time extract, let’s say you want to see your account balance on 12/24/02. You could use the query in Figure 5, which will look for the account information on or before that day. This query would return the following data from your audit table:


1234 350.00 Market 1111 01/01/02-00:00:00

Range images: There’s also the possibility of using these same tables to do range images. Let’s say you want to see how the account looked at a particular point in time, plus any changes since then. You could use the query in Figure 6, which would return the result from your account audit table (see Figure 7). This capability lets you view data as it changes over time.



Application Data Corruption Recovery

Table auditing and “as of ” imaging can be used to remove data that’s been corrupted by errant application programs. This is referred to as logical recovery, and it lets readers of the data see an image of data before it had been corrupted. This allows for instantaneous recovery from data corruption without incurring an outage. Sound like magic? Read on.

The logical recovery process starts with an audit table design similar to the designs previously described. The key to this logical recovery process is the ability to control application recovery activation. This typically can be enabled via a DB2 table called the Logical Recovery Table (LRT), which will almost always be empty and will always be read by any application program reading the main tables. If the application ever reads the LRT and it returns data, then the application will switch into logical recovery mode and read the “as of ” images. The LRT contains nothing more than a timestamp column, and will have no rows unless a recovery is needed. At that time, one row will be inserted with a timestamp representing the desired recovery time. Figure 8 shows the application activation process.


Once logical recovery has been activated in the application, it reads a set of views rather than the main tables. Other than reading views, virtually all other application code remains the same. The views then hold the key to the recovery process, and the purpose of the views is to return the “as of” image of the data depending on the value of the timestamp in the LRT. These views look very similar to the previous “as of” examples in this article with the exception that they’re also reading the LRT. Figure 9 shows an example of a “logical recovery view” that returns the “as of” image represented by the logical recovery timestamp.


With the application in logical recovery mode, a background process can use the value of the logical recovery timestamp to clean up any changes made to data after that point in time. If your applications are reading WITH UR and aren’t updating, then these data corrections can be made while all readers continue reading the “as of ” data images. Once the clean-up process completes, the timestamp can be deleted from the LRT, and normal processing can begin.

IBM DB2 Futures

You may see an “as of” capability built into an upcoming release of DB2. Consideration is being given to providing a snapshot query capability to see what data looked like at a particular time. While this sounds like an exciting feature, it’s a ways out in the future. If you currently have requirements to provide that capability, for now it must come from creative table and code design.


While time travel is still impossible, it isn’t for your data. You have the capability to see your data at a point in time, see ranges of data as it changes over time, and go back in time to see your data as it was before it became corrupted while backing out the corruption. Those are three attractive ways to take a trip to the past using a few DB2 tables and some imaginative code.