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 row-begin column and a row-end column. The row-begin column indicates the beginning of the period, and the row-end column indicates the end of the period. The beginning value of a 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.
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 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.
For a list of restrictions that apply to system-period temporal tables, see Restrictions for system-period data versioning.
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. 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.
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.
