A common business problem is the need to be able to identify what data looked like at a particular point in time in the past. For example, an insurance company may need to know what level of coverage was in place for a particular policy two months ago, when a claim was made. Traditionally, in order to support a requirement such as this, databases would need to be specifically designed with history tables containing “effective date” and “end date” columns, and complex logic would need to be coded and maintained in application programs to both correctly populate the tables (ensuring no overlap or gaps in dates), and retrieve the data as of a specified point-in-time.
The introduction of temporal tables addresses and alleviates these issues. There are two “flavors” of temporal tables: system time and business time. In a system time temporal implementation, DB2 keeps track of history based on when data was actually changed in the database. In a business time temporal implementation, DB2 keeps track of history based on information provided by the application, such as effective dates of when data is to be considered “active”. It is possible to keep track of both kinds of temporal data (system and business) in a single table. Such a table would be considered a bitemporal table.
In a system time implementation, DB2 will automatically maintain the history of data by archiving the old versions of updated or deleted data into a history table that is associated with the base table. In a system time implementation, the base table will contain the current data, while the history table contains the archived data. Start and end timestamp columns are automatically maintained by DB2 to keep track of when data was archived.
In a business time implementation, the application provides the start and end timestamp information for data that is stored in a business time temporal table. This provides the capability to not only store past and current data in a business time temporal table, but future data as well.
No changes to SQL are required for retrieving current data from a temporal table. The SELECT statement would return the most current data from a system time temporal table, and would return the data for the currently effective rows (based on timestamp) from a business time temporal table.
For retrieving non-current, or “point-in-time” data from a temporal table, an “AS OF” timestamp clause can be added to the SELECT statement to specify the time period that you are interested in.
Example of SELECTing from a system time temporal table:
SELECT BALANCE
FROM CHECKING_ACCOUNT FOR SYSTEM_TIME
AS OF ‘2012-06-23-12.33.27.000000’
WHERE ACCOUNT_NUMBER = ‘5081237459’;
In this example, we are looking to retrieve the balance of checking account “5081237459” as of a very specific point in time in the past. Even though we are naming the CHECKING_ACCOUNT table as the target table from which we wish to retrieve the data, DB2 is aware that CHECKING_ACCOUNT is a temporal table with an associated history table, and will actually query and retrieve the requested data from the history table.
Example of SELECTing from a business time temporal table:
SELECT UNIT_PRICE
FROM PARTS FOR BUSINESS_TIME AS OF ‘2013-09-15’
WHERE PART_NUMBER = ‘A3473-33129’;
In this second example, let’s assume that pricing changes are scheduled to go into effect later this year. The new prices for each part have already been stored in the PARTS table with effective dates for when the new prices will take effect. For part number “A3473-33129”, we want to determine how much it will cost on September 15 of this year.