Modeling temporal data

You can use the workbench to model data that is based on time. Use the temporal features of the workbench to create and modify temporal objects.

A temporal table is a table that records the period of time when a row is valid with respect to system time (or transaction time, when the transaction is recorded), business time (or valid time, when the data is valid with respect to information about the real world), or both.

A period is an interval of time that is defined by two date or time 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. 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 is included in the row. Whereas, if the end column has a value of 03/21/1995, that date is not within the period of the row.

The workbench supports three types of temporal tables (DB2® for z/OS® version 10 New-Function Mode or DB2 for Linux, UNIX, and Windows version 10 only):
System period
A system period table maintains historical information that is based off of system time (or transaction time, when the transaction is recorded).

The system period consists of a pair of columns with system-maintained values that indicate the period of time when a transaction occurred. The begin column contains the date or timestamp value for when a row is created, either by an insert operation or an update operation on an existing row. The end column contains the timestamp value for when the row is no longer valid. The value is entered here when a row is updated or deleted.

The system period is meaningful because of system-period data versioning. System-period data versioning specifies that updated or deleted 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. If you have the correct authorization, you can delete the rows from the history table when those rows are no longer needed.

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 then you can create a table space to hold that table.

Application period
An application period table maintains historical information that is based off of business time (or valid time, when the data is valid with respect to information about the real world).

The application period consists of a pair of columns with application-maintained values that indicate the period of time when a row is valid with respect to information about the real world. The begin column contains the date or timestamp value about when a real world event or state begins. 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 primary key and specify that the values in that key must be unique within a period.

Bitemporal
A bitemporal table maintains historical information that is based off of both system time and business time. 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.

Feedback