SELECT FROM Temporal Table panel

The SELECT FROM Temporal Table window displays when you enter Y in the SELECT FROM Temporal Table field. It allows you to construct the SELECT FROM statement to be used for a temporal table (or view starting with Db2 Version 11) to be browsed (System Period and Business Period) or edited (Business Period only).

SELECT FROM Temporal Table

A temporal table records the period of time when a row is valid. Db2 supports two types of periods, the application period (BUSINESS_TIME) and the system period (SYSTEM_TIME).

System Period

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 system period is meaningful because you can define system-period data versioning on a table that has this period. 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 (Table Type=H). You can delete the rows from the history table when those rows are no longer needed, if you have the proper 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 tablespace to hold that table. You define system-period data versioning by issuing the ALTER TABLE ADD VERSIONING statement with the USE HISTORY TABLE clause.

Business (Application) Period

A business (application) period consists of a pair of columns with application-maintained values that indicate the period of time when a row is valid. A table with only a business period is called an application-period temporal table.

Bitemporal Table

A bitemporal table 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.

For each System Time and Business Time, specify the time period to be used to select rows from a temporal table.

Use System Time

Specify Yes if the table includes System Time and you want to select rows from the specified period.

Time period

Select the period method you want to specify for System Time:

1

AS OF creates the AS OF ... period statement to select valid rows as of a certain point in time. This option requires you to also specify a value for Date/Timestamp 1. Example statement:

2

FROM creates the FROM ... TO ... period statement to select valid rows from a certain time to a certain time. FROM is inclusive, TO is exclusive. That means, the specified start time is included in the period but the specified end time is not. This option requires you to also specify a value for Timestamp 1 (start time) and Timestamp 2 (end time). Example statement:

3

BETWEEN creates the BETWEEN ... AND ... period statement to select valid rows from a certain time to a certain time. BETWEEN and AND are inclusive. That means, that both the specified start time and end time are included in the period. This option requires you to also specify a value for Timestamp 1 (start time) and Timestamp 2 (end time). Example statement:

Timestamp 1

specify the corresponding start timestamp for the selected system time period.

Timestamp 2

specify the corresponding end timestamp for the selected system time period. Only required for FROM and BETWEEN periods.

Use Business Time

Specify Yes if the table includes Business Time and you want to select rows from the specified period.

Time period

Select the period method you want to specify for Business Time:

1

AS OF creates the AS OF ... period statement to select valid rows as of a certain point in time. This option requires you to also specify a value for Date/Timestamp 1. Example statement:

2

FROM creates the FROM ... TO ... period statement to select valid rows from a certain time to a certain time. FROM is inclusive, TO is exclusive. That means, the specified start time is included in the period but the specified end time is not. This option requires you to also specify a value for Date/Timestamp 1 (start time) and Date/Timestamp 2 (end time). Example statement:

3

BETWEEN creates the BETWEEN ... AND ... period statement to select valid rows from a certain time to a certain time. BETWEEN and AND are inclusive. That means, that both the specified start time and end time are included in the period. This option requires you to also specify a value for Date/Timestamp 1 (start time) and Date/Timestamp 2 (end time). Example statement:

Date/Timestamp 1

specify the corresponding start date/time for the selected business time period.

Date/Timestamp 2

specify the corresponding end date/time for the selected business time period. Only required for FROM and BETWEEN periods.