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.