Most IT systems need to keep historical as well as current information. 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.
DB2 10 provides this functionality as part of the database engine, making DBAs and developers more productive. All the DBA needs to do is indicate 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.
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.