The RESTRICT ON DROP clause on the history table prevents the history table from being implicitly dropped if the system-period temporal table is dropped. For our example, this is the ITEM table, which might be similar to one a retailer uses to track the sales price of items over time. The historical data can be the basis for reports the retailer could use for planning and reviewing sales trends based on sale price or for developing financial reports based on the item’s sale price for a period. The examples given show only the year, month and day portion of the TIMESTAMP columns.
Inserts and Updates With System-Period Temporal Tables
Inserts work as before, with DB2 generating the defaults for the period start and end columns (see Figure 3). Continuing with our example in Figure 3, SKU 18934 goes on sale; the price is updated as shown in Figure 4.
With updates to system-period temporal tables, the before image of the row is copied to the history table before the update, providing a complete before and after record of data changes without a need to use applications, programs, stored procedures or triggers.
Querying System-Period Temporal Tables
When you query a system-period temporal table, the query runs against the current data (table) only by default (see Figure 5). For DB2 to automatically and transparently access the history table, the query must specify the FOR SYSTEM_TIME clause in the query.
A query with the FOR SYSTEM TIME clause might retrieve rows from the base table, history table or both. The FOR SYSTEM_TIME clause can also specify a time range using the keywords FROM and TO, or BETWEEN and AND.
System Time Special Register
Instead of specifying the FOR BUSINESS TIME clause in the query, you can set the SYSTEM TIME SPECIAL REGISTER and existing queries need not change to access the associated history table. For example, to see what the price was on 11/14/2012, you can set this new special register to the period of interest with this statement:
“SET CURRENT TEMPORAL SYSTEM_TIME ‘11/14/2012’;
After setting the special register, the query executes automatically and transparently against the history table; the result shows the price in effect for 11/14/2012. This was 194.18 per the query in example B in Figure 5.
No Write Operations in the Past
It’s important to note, however, that when the current TEMPORAL SYSTEM_TIME special register is set to a non-null value, this blocks Insert, Update and Delete operations and LOAD utilities on system-period temporal tables. Also, you can only specify the desired system time either in the query or with the special register, but not both. Doing so will result in an error being returned.