A new feature included in DB2 10.1 is the time-travel or temporal data tables. Temporal tables allow you track the changes in your data so that you can back in time and query the data at a specific point in time. You can also control the business validity of your data. Temporal data can be used mainly in two flavors: system-period and application-period. You can combine the two to create a third: the so-called bi-temporal tables. Using new and standardized SQL syntax, you can easily insert, update, delete, and query data in the past, present, or future.
The temporal data management capabilities in DB2 are based on three types of temporal tables. The type of temporal table you are going to use is controlled by the nature of the time fields it's going to manage. Is it a system time? Business time? Or both? System time is maintained by DB2 system and is provided by the system clock on DB2 server when a transaction occurs. Business time is date or time stamp provided by the application to control the business validity of records. It's completely unrelated to the system time on DB2 server.
System-period temporal tables (STT) are based on the system time. DB2 keeps a history of rows that have been updated or deleted over time according to system time. Each row and its corresponding history are automatically assigned a pair of system time (transaction time).
Unlike STT, application-period temporal tables (ATT) require that the application is aware of the ATT to provide a valid business time. Applications control the business validity of their data by supplying a date or timestamp. The provided dates or timestamps describe when the data in a given row was or will be valid in the real world. Again, new SQL constructs allow users to insert, query, update, and delete data in the past, present, or future. DB2 automatically applies temporal constraints and row-splits to correctly maintain the application-supplied business time.
Bi-temporal tables manage system and business time, and combine all the capabilities of both. This enables applications to manage the business validity while DB2 keeps a full history of data changes. Every bi-temporal table is both an STT and an ATT.
One key difference between STT and ATT is that an STT has an associated separate history table to maintain previous versions of the data. An ATT has no separate history table; all rows are maintained within the same table. When rows from ATT are affected by delete operations, they are permanently deleted from the table.
Table 4 gives a quick overview of the characteristics of system time and business time. If your requirements match the characteristics of system time, you can use STT. If they match the characteristics of business time, go for ATT. If your application needs both characteristics, you can leverage it to the bi-temporal table.
Table 4. Characteristics of system time and business time
|System time||Business time|
|Captures the time when changes happen to data inside a DB2 database||Captures the time when changes happen to business objects in the real world|
|Maintains a history of updated and deleted rows, generated by DB2||Maintains application-driven changes to the time dimension of business objects|
|History based on DB2 system timestamps||Dates or timestamps are provided by the application|
|DB2's physical view of time||Your application's logical view of time|
|Spans from the past to the present time||Spans past, present, and future time|
|System validity (transaction time)||Business validity (valid time)|
|Supports queries such as "Which policies were stored in the database on June 30?"||Supports queries such as "Which policies were valid on June 30?"|
Consider the following guidelines when you choose temporal tables:
- Use system time and system-period temporal tables to track and maintain a history of when information was physically inserted, updated, or deleted inside your DB2 database.
- Use business time and application-period temporal tables, if you need to describe when information is valid in the real world, outside of DB2.
- Use bi-temporal tables, if you need to track both dimensions of time. With a bi-temporal table you can manage business time with full traceability of data changes and corrections.
To create a system-period temporal table that leverages system time and versioning, you need to create a base table with three specific columns, create an identical history table for historical data, and finally associate the history table with the base table. Let's look at these three steps in more details:
- Create a base table with three specific columns
The three specific columns are row-begin column, row-end column, and transaction start-ID column. The names of three columns can be any valid DB2 column name (e.g., start_time, end_time, and trans_id). Each column must be defined as a TIMESTAMP. The row-begin and row-end column names must be specified in the PERIOD SYSTEM_TIME (start_time, end_time) clause.
Listing 14. Sample DDL table with three specific columns
CREATE TABLE travel( trip_name CHAR(30) NOT NULL PRIMARY KEY, destination CHAR(12) NOT NULL, departure_date DATE NOT NULL, price DECIMAL (8,2) NOT NULL, sys_start TIMESTAMP(12) NOT NULL generated always as row begin implicitly hidden, sys_end TIMESTAMP(12) NOT NULL generated always as row end implicitly hidden, tx_start TIMESTAMP(12) generated always as transaction start id implicitly hidden, PERIOD SYSTEM_TIME (sys_start, sys_end) );
- Create an identical history table for historical data
The history table is logically identical to the base table. However, it can be physically different from the base table. You can put it in a different table space, or even you can have different partitioning schemes for each table. You may use the like clause in the create table statement to create a history table with the same names and descriptions as the columns of the base table as follows:
CREATE TABLE travel_history LIKE travel IN hist_space WITH RESTRICT ON DROP;.
- Associate the history table with the base table
The last step in setting up your system-period temporal table is to link the history table with the base table. This can be easily done with the
ALTER TABLE travel ADD VERSIONING USE HISTORY TABLE travel_history;.
You can hide the special columns by specifying the
HIDDEN clause in the column definition. In that case, if you don't
use the like clause when creating the history table, you must mark all hidden
columns in the base as hidden in the history table as well.
You can also change your existing table to leverage the temporal table using the
same steps. In that case, the base table already exists without the three
columns. Alter the table adding these columns and adding the
PERIOD SYSTEM_TIME(sys_start, sys_end).
ATT allows you to store business time. By "business time," we mean the application logical notion of time. That is, it reflects when information was, is, or will be effective (valid) in the real world. Business time by itself does not involve a history table, and the effective dates must be provided by the application. We are talking about the validity of the data not the history of the data.
Let's take an example to make it clear by an example. Consider the following two very different questions:
- What was the effective price for Manu Wilderness trip on 06/15/2012? (Business time)
- What was the stored price for Manu Wilderness trip on 06/15/2012? (System time)
For example, if a trip will be offered at a discounted price during June, the price change might get entered into the database today or at anytime (system time) with an effective date from 1 Jun 2012 to 1 Jul 2012 (business time). Similarly, a new insurance policy might get created and inserted into the database in May, but backdated with an effective start date of 15 Apr. In short, the business validity for a record is independent of when that record was or was not physically present in the database.
Similar to STT, you will need a pair of DATE or TIMESTAMP columns that describe
the start and end points of the business validity of each record. Although these
columns can have arbitrary names, they must be declared using the
period clause in the
CREATE TABLE statement. This period declaration enables DB2
to enforce temporal constraints and to perform temporal
DELETE operations for business
time. DB2 will transparently add, split, or delete rows as needed.
Let's create a sample, run the following DDL.
Listing 15. Sample DDL to create application-period temporal table
CREATE TABLE travel_att ( trip_name CHAR(25) NOT NULL, destination CHAR(8) NOT NULL, price DECIMAL(8,2) NOT NULL, bus_start DATE NOT NULL, bus_end DATE NOT NULL, PERIOD BUSINESS_TIME (bus_start, bus_end), PRIMARY KEY (trip_name, BUSINESS_TIME WITHOUT OVERLAPS) );
The optional clause
OVERLAPS in the primary key constraint is new in DB2 10.1 and enforces temporal uniqueness. Temporal
uniqueness means, rows with duplicate trip_name values are allowed if the
business time periods of these rows do not overlap. In other words,
BUSINESS_TIME WITHOUT OVERLAPS means that
for any given point in time, there is at most one row that is effective (valid) for a given trip_name.
Inserting new record into an ATT is no different from the regular SQL insert statement. You simply need to supply appropriate values for all required columns, including the columns representing business time start and end values. For example, to insert a two rows into our sample travel table with business time, we could issue the following statement:
INSERT INTO travel_att VALUES ('Manu Wilderness', 'Peru', 1500.00,'05/01/2012', '01/01/2013'), ('The Great Wall Tour', 'China', 2200.00, '07/01/2012', '12/30/9999');
At this point, the data in the travel_att table should look like below:
TRIP_NAME DESTINATION PRICE BUS_START BUS_END -------------------- ----------- -------- ----------- ----------- Manu Wilderness Peru 1500.00 2012-05-01 2015-10-01 The Great Wall Tour China 2200.00 2012-07-01 9999-12-30
As you can see, the TRAVEL_ATT table doesn't contain any information about when the rows were inserted, updated, or deleted. It only tracks validity (business time) of the trips.
The company discovered that the demand on Manu Wilderness trip is very low in July. They made an offer for July reducing its price to $1,000 instead of $1,500. This offer is only valid for July. The price remains the same for the remaining period until the trip becomes invalid:
UPDATE travel_att FOR PORTION OF BUSINESS_TIME FROM '06/01/2012' TO '07/01/2012' SET price = 1000.00 WHERE trip_name = 'Manu Wilderness';
The command completed successfully and the period from 5 May 2012 to 1 Oct 2015 is split into three periods. DB2 inserted two new rows and updated the existing row as shown below:
Listing 16. DB2 inserts two new rows and updates existing row
TRIP_NAME DESTINATION PRICE BUS_START BUS_END -------------------- ----------- ------- ---------- ----------- Manu Wilderness Peru 1000.00 2012-06-01 2012-07-01 The Great Wall Tour China 2200.00 2012-07-01 9999-12-30 Manu Wilderness Peru 1500.00 2012-05-01 2012-06-01 Manu Wilderness Peru 1500.00 2012-07-01 2015-10-01
Now if we try to insert a record with a period overlapping with the existing period, DB2 will restrict you and the command will fail. For example, if you try to insert a new record with trip name Manu Wilderness and an overlapping period, DB2 will fail with SQL error -0803. In that case, you may change the name or update the existing record based on your business needs.
Deleting records from an application-period temporal table removes rows from the
table and can potentially result in new rows inserted into the
application-period temporal table itself. A row is a candidate for deletion if
its period-begin column, period-end column, or both fall within the range
specified in the
FOR PORTION OF BUSINESS_TIME clause. If a specified period
falls in the middle of an existing period, DB2 will transparently split the
exiting period into three periods and delete the correct one. Let's clarify it
more with the following example:
DELETE FROM travel_att FOR PORTION OF BUSINESS_TIME FROM '09/15/2012' TO '09/20/2012' WHERE trip_name LIKE 'Manu Wilderness';
When you delete for a portion of business time, you are not necessarily removing records from the database. You are marking this period as invalid for the application. You are making it logically unavailable from the business perspective. However, this may not be implemented by a physical delete. In our example, the Manu Wilderness is available in the period from 1 Jul 2012 to 1 Oct 2015. Marking this trip is not available (delete for portion of business time) in the period from 15 Sep 2012 to 20 Sep 2012 means that there will be a gap. To keep the records before and after this gap, the current record will be split into two rows. In other words, the existing record will be updated and a new record will be inserted:
Listing 17. Existing record updated and new record inserted
TRIP_NAME DESTINATION PRICE BUS_START BUS_END ------------------- ----------- ---------- ----------- ----------- Manu Wilderness Peru 1000.00 2012-06-01 2012-07-01 The Great Wall Tour China 2200.00 2012-07-01 9999-12-30 Manu Wilderness Peru 1500.00 2012-05-01 2012-06-01 Manu Wilderness Peru 1500.00 2012-07-01 2012-09-15 Manu Wilderness Peru 1500.00 2012-09-20 2015-10-01
DB2 manages all system time and business time periods as inclusive-exclusive periods, or closed-open periods. Therefore, it is recommended to also use inclusive-exclusive periods at the application level and to avoid mapping between inclusive-inclusive and inclusive-exclusive periods.
Bi-temporal tables combine all the capabilities and restrictions that apply on system-period and application-period temporal tables. You need two columns for the systems time period and another two for the business time period, as well as the transaction start-ID column.
Administrators can easily create or alter a table to include system and
business time. For example, the following
TABLE statement defines a
bi-temporal table with a BUSINESS_TIME period on the BUS_START and BUS_END
columns, as well as a SYSTEM_TIME period on the SYS_START and SYS_END columns.
Listing 18. Sample DDL to create bi-temporal tables
CREATE TABLE policy ( id INT NOT NULL, vin VARCHAR(10), annual_mileage INT, rental_car CHAR(1), coverage_amt INT, bus_start DATE NOT NULL, bus_end DATE NOT NULL, sys_start TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL, sys_end TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL, trans_start TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN, PERIOD SYSTEM_TIME (sys_start, sys_end), PERIOD BUSINESS_TIME(bus_start, bus_end), PRIMARY KEY(id, BUSINESS_TIME WITHOUT OVERLAPS) );
The new SQL constructs allow you to query the system-period temporal table or views on an STT to return results for a specified period or point in time. Querying temporal tables can be summarized in five basic examples:
- Query without time specification — This leverages the temporal tables special registers if they are set to a not null value. Otherwise, it retrieves the current data.
- Query with
AS OFvalue clause — Includes all the rows where the begin value for the period is less than or equal to value1 and the end value for the period is greater than value1. This enables you to query your data as of a certain point in time.
- Query with
FROM value1 TO value2clause — Includes all the rows where the begin value for the period is equal to or greater than value1 and the end value for the period is less than value2. This means that the begin time is included in the period, but the end time is not.
- Query with
BETWEEN value1 AND value2clause — Includes all the rows where any time period overlaps any point in time between value1 and value2. A row is returned if the begin value for the period is less than or equal to value2 and the end value for the period is greater than value1.
- Query views defined on a temporal table — You can either set the time specification at the query or in the view DDL. However, you can't have time specified on both.
Any temporal table can also be a range-partitioned table. A history table that belongs to a system period temporal table or a bi-temporal table can also be range-partitioned. The partitioning scheme of a history table may differ from the partitioning of its base table. For example, you can partition the current data by month and the history data by year. However, ensure that the ranges you defined for the history table partitions can absorb any rows moved from the base table to the history table. Consider the following example.
Listing 19. Example for table partitioning
CREATE TABLE policy ( id INTEGER PRIMARY KEY NOT NULL, annual_mileage INTEGER, rental_car CHAR(1), coverage_amt INTEGER, sys_start TIMESTAMP(12) GENERATED AS ROW BEGIN NOT NULL, sys_end TIMESTAMP(12) GENERATED AS ROW END NOT NULL, trans_start TIMESTAMP(12) GENERATED AS TRANSACTION START ID, PERIOD SYSTEM_TIME (sys_start, sys_end) ) PARTITION BY RANGE(sys_start) (STARTING('2012-01-01') ENDING ('2014-12-31') EVERY 1 MONTH ); CREATE TABLE policy_history ( id INTEGER NOT NULL, annual_mileage INTEGER, rental_car CHAR(1), coverage_amt INTEGER, sys_start TIMESTAMP(12) NOT NULL, sys_end TIMESTAMP(12) NOT NULL, trans_start TIMESTAMP(12)) PARTITION BY RANGE(sys_start) (STARTING('2012-01-01') ENDING ('2013-12-31') EVERY 3 MONTHS ); ALTER TABLE policy ADD VERSIONING USE HISTORY TABLE policy_history;
The example shows different partitioning schemes for the base and history table. The policy table is partitioned by month; the policy_history table is partitioned by quarter. This difference in granularity is not a problem. However, the policy table has partitions for sys_start values up to 2014-12-31 while the policy_history table has partitions only up to 2013-12-31. If a row in the policy table with a sys_start value of 2014-01-01 or greater is deleted, the insert into the history table fails because it has no partition to accept this sys_start value. Hence, the entire delete transaction fails. To avoid this, define the history table such that the total range of all it's partitions is always equal to or greater than the total range of partitions in the base table.
After versioning has been enabled with the
TABLE...ADD VERSIONING statement, you can still detach partitions from the history table for pruning
and archiving purposes. However, to detach a partition from the base
table, you must first stop versioning with the
ALTER TABLE...DROP VERSIONING statement.
When you stop versioning and detach a partition from the base table, this
partition becomes an independent table. It retains all three timestamp columns
(row begin, row end, transaction start ID) but not the
PERIOD SYSTEM_TIME declaration. The rows in the detached partition are not automatically moved into
the history table. If you choose to move these rows into the history table
yourself, change the sys_end value of every row from
9999-12-30-00.00.00.000000000000 to the current timestamp. This change is
necessary to reflect the point in time when the rows changed from being current
to being history. If you do not make this change, temporal queries might return
You can attach a table to a partitioned base or history table while versioning is
enabled. The table you attach is not required to have a
PERIOD SYSTEM_TIME declaration, but it must have all three timestamp columns defined as in the base
table. While versioning is enabled, you cannot use the
SET INTEGRITY statement with the
EXCEPTION clause. The reason is that moving any exception rows into
an exception table cannot be recorded in the history table, which jeopardizes
the ability to audit of the base table and its history. However, you can
temporarily disable versioning, perform
INTEGRITY with the
FOR EXCEPTION clause, then enable versioning again.
There are some limitations when using the
ADMIN_MOVE_TABLE stored procedure to
move data in an active system-period temporal table into a new table with the
same name. The following actions are blocked:
- Alter table operations that change the definition of the system-period temporal table or the associated history table are blocked during online move operations.
- The KEEP option of
ADMIN_MOVE_TABLEis unavailable for system-period temporal tables.
Additionally, the online-table-move operation is not supported for history tables.