It’s only when a history table is associated with the base table that things become more interesting. Inserts into the base table won’t generate any history table activity. However, any update or delete will result in “before” image rows being propagated automatically to the history table. Also, the end timestamp value for the propagated row will be updated to reflect the end period for the data values of the row. For example, if you change the value of the LASTNAME column of employee “000010” as shown in Figure 5, a row will be inserted into the history table to reflect the condition of the data before the update. Likewise, a delete to the DEPT table for DEPTNO “J22” will result in the row for that department being removed from the base table, and a row reflecting the image of the row up to the point of the delete being inserted into the DEPT_HIST table.
Writing Time Travel Queries
Temporal tables can be accessed via normal SQL statements, and don’t require a time specification when accessing them. Queries against temporal tables can also contain a clause called a period-specification. This can be for a specific time or for a range of time values. However, it’s important to know how the queries will behave both with and without the time specification.
Consider a BUSINESS_TIME specification for application-period temporal tables. It’s important to note that the business start and end times for a row in an application-period temporal table are inclusive for the start time and exclusive for the end time. So, if a row has a range of “2012-06-01” to “2012-12-31,” as with our ACT table example, then the data is active inclusively for June 1st through December 30th. Consider the ACT table data in section B of Figure 4. You can assume a query with a period-specification of “2012-12-31” will return no rows while one that specifies “2012-12-30” will return the single row that’s active for that date. Consider Figure 6 for the query and result. The period-specification is transformed by the query transformation component of DB2 into a range predicate.
Besides using the “AS OF” period-specification, you can query based upon a range of time. There are two choices for specifying range values: the “FROM” and “BETWEEN” clauses. They differ slightly. FROM is strictly locating data that was active in the time that intersects the specification, and BETWEEN is locating data that was active in the time that overlaps the specification. FROM will return any rows where a start value for a row is less than the second value specified and the end value for a row is greater than the first value specified. BETWEEN will return any rows where a start value for a row is less than or equal to the second value specified and the end value for the row is greater than the first value specified. For BETWEEN, the end value still must be greater than the first value specified because the end time for a row is exclusive. Figure 6 shows time range period-specification examples for our ACT table data. Make sure developers are trained to accurately code period-specifications. If necessary, you can always code the predicates against the time value columns yourself! This could let you break the rules and treat the end time value as inclusive in your queries.
Query Table Access With System-Period Temporal Tables
System-period temporal base tables and history tables can each be accessed directly via normal SQL. From a performance perspective, this is the preferred way to access the tables. If you need current data, then you read from the base table directly. If you want historical data, then you can read from the history table directly, or from both the base and history tables. You can also include a period-specification in your SQL statement against the base table, in a manner similar to an application-period temporal table. The same rules apply for the various period-specification clauses as with the application-period temporal tables discussed previously. The big difference here is that any time travel query against a system-period temporal table will generate access against both the base and history table.
The query in Figure 7 will return data from our employee history table when the AS OF period-specification includes a time prior to our update. In the transformed query, what was written to appear as single table access was rewritten by DB2 as a union of accesses to both the base and history table. This will happen even for queries in which the AS OF period-specification includes the CURRENT TIMESTAMP value. Your applications that deal with system-period time travel queries should perhaps use them only for truly historical requirements. For current period requirements, only the base table access without a system-period specification will deliver optimal performance. There may be a change in a future release of DB2 that will allow current time queries to prune access to the history table. For now, you will have to code for it yourself.
DB2 query transformation of a query with a system-period specification into a union of the base and history tables can become more complicated when you consider the joining of two tables. No one would think much about a join between the DEPT and EMP tables, and can assume two tables are accessed in the query, but if you include a SYSTEM_TIME period specification for the tables in the query, then the number of tables accessed increases dramatically. The number of tables accessed in a time travel join of system-period temporal tables, if each table reference contains a SYSTEM_TIME period specification, is equal to (2n) x n, where n is the number of tables in the join. The join of DEPT to EMP, each with their own period-specification, results in eight table accesses. A similar three-table join will result in 24 table accesses, and a similar four-table join will result in 64 table accesses. Clearly, joining system-period temporal tables using SYSTEM_TIME period-specification should be done carefully or perhaps avoided when performance is the primary concern. Figure 7 shows the join of two system-period temporal tables that include a period-specification and the transformed query. (Note: Figures 2 and 7 have been edited for length in the print edition of this article but appear unabridged in the digital version.)
Establishing and maintaining application-period, system-period, or bi-temporal tables is straightforward. With DB2 managing the integrity of the temporal data, the application is free from the responsibility of properly updating and reading temporal data. However, it’s important to learn exactly how a period-specification is interpreted by query transformation to be certain these specifications are coded correctly in your application. In addition, performance considerations are critical when using queries containing a period-specification against system-period temporal tables, especially for joins. Using database-enforced referential integrity with temporal tables can be a little more involved than with a normal database design. It is critical for both DBAs and application developers to understand how the tables work.