Creation of temporal tables

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

You can create application-period temporal tables and system-period temporal tables. An application-period temporal table includes an application period, which is a period in which you maintain the beginning and ending values for a row. A system-period temporal table has a system period, and you can define system-period data versioning on the table to manage historical and current table data. A bitemporal table is a table that is both a system-period temporal table and an application-period temporal table.

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. 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. You must specify a name for the history table and create a table space to hold that table.

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 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.

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.