Temporal tables and data versioning

A temporal table is a table that records the period of time when a row is valid.

A period is an interval of time that is defined by two datetime columns in a temporal table. A period contains a begin column and an end column. The begin column indicates the beginning of the period, and the end column indicates the end of the period.

Db2 supports two types of periods, which are the system period (SYSTEM_TIME) and the application period (BUSINESS_TIME).

System-period data versioning

The system period consists of a pair of columns with system-maintained values that indicate the period of time when a row is valid. The begin column contains the timestamp value for when a row is created. The end column contains the timestamp value for when a row is updated or deleted. The beginning value of the period is inclusive, but the ending value of a period is exclusive. For example, if the begin column has a value of '01/01/1995', that date belongs in the row. Whereas, if the end column has a value of '03/21/1995', that date is not part of the row.

The system period is meaningful because of system-period data versioning. System-period data versioning specifies that old rows are archived into another table. The table that contains the current active rows of a table is called the system-period temporal table. The table that contains the archived rows is called the history table. You can delete the rows from the history table when those rows are no longer needed, if you have the correct authorization. When you define a base table to use system-period data versioning, or when you define system-period data versioning on an existing table, you must create a history table, specify a name for the history table, and create a table space to hold that table. You define versioning by issuing the ALTER TABLE ADD VERSIONING statement with the USE HISTORY TABLE clause.

When you update or delete a row in a system-period temporal table, Db2 inserts the previous version of the row into the history table. The historical versions of rows are written to the history table to record committed versions of the data in the associated system-period temporal table. Intermediate or uncommitted versions of rows in a system-period temporal table are not normally recorded in the history table. If a row in a system-period temporal table is updated multiple times within a single unit of work, and then a commit occurs, only one new historical version of that row is recorded in the history table. If a row is inserted into a system-period temporal table, and the insert is rolled back, nothing is recorded in the history table for the insert that was never committed. You can query a system-period temporal table with timestamp criteria to retrieve previous data values. You can specify the timestamp criteria in the query or by using special registers.

You can use system-period data versioning instead of developing your own programs for maintaining multiple versions of data within a database. With Db2, system-period data versioning is a more efficient method for maintaining versioned data.

You can use system-period temporal tables to track auditing information about when data changes occurred. If you want to track more information, such as who changed the data and the SQL operation that changed the data, you can include non-deterministic generated expression columns in the system-period temporal table.

For a list of restrictions that apply to system-period temporal tables, see Restrictions for system-period data versioning.

Temporal logical transactions

When you use system-period data versioning , timestamp values are normally recorded at the start and end of a business function, but the start and end times of the work units within a business function are not recorded. However you can specify more granular records of start and end timestamps by using temporal logical transactions. Temporal logical transactions allow you to specify when a transaction starts, so that its time is recorded. For example, you can use temporal logical transactions to record of the start and end timestamps for each individual transaction in a batch job.

The TEMPORAL_LOGICAL_TRANSACTIONS built-in global variable whether multiple temporal logical transactions are allowed within a single unit of work.

While the SYSIBM.TEMPORAL_LOGICAL_TRANSACTION_TIME built-in global variable is set to a non-null value, the application controls the scope of temporal logical transactions, and Db2 does not ensure the uniqueness across transactions for generated values for row-begin columns in system-period temporal tables or end columns in history tables.

For more information, see TEMPORAL_LOGICAL_TRANSACTION_TIME built-in global variable.

Application-period data versioning

The application period consists of a pair of columns with application-maintained values that indicate the period of time when a row is valid. The begin column contains the value from which a row is valid from. The end column contains the value for when a row stops being valid. A table with only an application period is called an application-period temporal table. For application-period temporal tables, the beginning value of the period is always inclusive. By default, the ending value of a period is exclusive. However, you can specify that the ending value of a period be inclusive by using the INCLUSIVE keyword.

When you use the application period, determine the need for Db2 to enforce uniqueness across time. You can create a UNIQUE index that is unique over a period of time.

Bitemporal tables

A bitemporal table is a table that is both a system-period temporal table and an application-period temporal table. You can use a bitemporal table to keep application period information and system-based historical information. Therefore, you have a lot of flexibility in how you query data, based on periods of time.