Let's Do the Time Warp: Temporal Data Comes To IBM DB2
November 29, 2010
A couple of months ago, I included DB2's new temporal data capabilities as one of my "Top 10 IBM Data Management Trends for 2010". As DB2 10 for z/OS is now Generally Available, we're going to dig into this popular new feature in a little more detail in this month's column.
Many IT systems need to keep some form of historical information in addition to the current status for a given business object. For example, a financial institution may need to retain the previous addresses of a customer as well as the one they are currently living at, and know what address applied at any given time. Equally, an insurance company may need to know what level of coverage was in place two months ago when a claim was made. Previously, these kinds of requirements would have involved the DBA and application developers spending valuable time creating and testing the code and associated database design to support the historical perspective, while minimizing any performance impact.
The new temporal data support in DB2 10 provides this functionality as part of the core database engine. The DBA indicates which tables/columns require temporal support when they are created, and DB2 will automatically maintain the history whenever an update is made to the data. Elegant SQL support allows the developer to query the database with an "as of" date, which will return the information that was current at the specified time.
DB2 for z/OS is often perceived as having to play catch up with new functionality introduced in DB2 for Linux, UNIX and Windows (aka DB2 LUW) or other vendor's databases. Temporal data support is a rare and very welcome example of a brand new concept being delivered in the z/OS version of the product before any other database, including its distributed sibling. DB2 LUW support for temporal data will appear in a future release, and IBM is also working with both the ISO and ANSI committees to incorporate temporal SQL into the relevant SQL standards.
DB2 10 for z/OS Temporal Support
Preparing a table for temporal support is relatively simple. Start and end timestamp columns are used by DB2 to determine when a given version of a row was valid, so these need to be added to the table to be temporally enabled (this can be done via ALTER TABLE...ADD COLUMN). If required, the columns can be specified as GENERATED ALWAYS (so that the relevant timestamps are automatically populated by DB2) and IMPLICITLY HIDDEN (so that they won't show up on any SELECT * statements submitted by application programs).
Once the columns have been added to the base table, an additional "history table" is created. This has to have an identical structure to the base table (which can be easily accomplished via CREATE TABLE….LIKE). Finally, an ALTER TABLE….ADD VERSIONING statement is used to enable temporal versioning on the base table and identify the associated history table to DB2.
As shown the diagram below, DB2 then automatically maintains the history table for updated rows in the temporal table. This is completely transparent to the developer, who codes INSERT/UPDATE/ DELETE SQL against the base table as usual.
When a row is updated (as shown at time T3 in the diagram), DB2 will store a version of the old row in the history table before updating the current row in the main table. Similarly, when a row is deleted it is first copied to the history table before being removed from the main table. DB2 maintains system timestamps (the SYS_START and SYS_END columns shown) to record the period during which a given version of the row was current.
The new "AS OF" clause in SQL SELECT statements allow the developer to see the data as it was at a given point of time. In the example, the policy information at time T2 is required, which will return the original address (A3) instead of the current address (A4). Note that the developer will be completely unaware that the history table has been accessed in this case, as DB2 automatically determines which version of the temporal table to get the data from.
One DB2 10 beta customer estimated that 80% of their existing applications contain some form of temporal logic. With so many IT systems needing to accommodate a historical perspective and maintain audit logs of changes made to sensitive data, DB2's new temporal support promises to save many hundreds of hours of design, coding and testing that would otherwise be required to build this function manually for each application. While the benefit for existing applications is of course quite limited, this feature promises to deliver major productivity savings for many new developments.