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
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
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
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.