Temporal data stores information about values that change over time. Temporal data is structured to reduce the effort that is needed to maintain and manage temporal columns in database tables. Using temporal data enables you to take advantage of simplified query syntax and semantics when working with records that contain data that pertains to specific time periods.
In DB2, there are three types of temporal data: system time, business time, and bitemporal data. System time tracks when changes are made to the state of data in a table. The state of a record in a table is determined by the SYS_START and SYS_END values. In a table that manages system time, the transaction time, or TRANS_START, is also maintained. The TRANS_START time records when a transaction to change a record, such as updating the SYS_END date, took place. SAS generates these three columns automatically when you create a table that stores system time data.
Business time tracks the effective dates for business data, such as promotional start and end dates. The effective start and end dates for business time data are stored in the BUS_START and BUS_END values. SAS generates these columns automatically when you create a table that stores business time data.
Bitemporal data tracks system time data and business time data. A table that contains bitemporal data contains all of the columns that are automatically included for system time data and business time data: SYS_START, SYS_END, TRANS_START, BUS_START, and BUS_END.
Tables that contain
business time data include historical, current, and future data records.
For tables that contain system time data or bitemporal data, SAS automatically
generates history tables. The name of a history table is constructed
as <base-table>_HISTORY.
History tables contain records that are determined to be historical.
That is, when the SYS_END date precedes the current date or datetime,
then the record is a historical record. SAS/ACCESS for DB2 under z/OS
automatically issues an ALTER TABLE ... ADD VERSIONING statement
that links the base table with its history table.
The temporal table and its associated history table must be stored in separate tablespaces. In addition, each table should be the only table in its tablespace. If a tablespace is not included as part of the creation of a temporal table from a SAS DATA step, then DB2 automatically assigns a unique tablespace to the temporal table and to the associated history table. For this reason, you cannot specify a tablespace in the values for the IN= option or the DB2IN= system option, if you are working with temporal tables that contain system time or bitemporal data. For more information, see IN= data set option, IN= LIBNAME option, or SAS System Options, Settings, and Macros for DB2 under z/OS.
The following data set options are used when you work with temporal data:
specifies the type of temporal data that is stored in a table. Possible values are BUSINESS, SYSTEM, or BITEMPORAL. For more information, see TEMPORAL= Data Set Option.
specifies the data type for the BUS_START and BUS_END values. Possible values are DATE or TIMESTAMP(6). The default value is TIMESTAMP(6). For more information, see BUSINESS_DATATYPE= Data Set Option.
specifies a time period to be used when querying or modifying a table that contains temporal data. You provide the beginning and ending values for the time period based on the BUSINESS_DATATYPE value. Specify the time period in the format:
FROM <date-or-datetime> TO <date-or-datetime>For example, use the following code to specify the time period from January 1, 2014 to December 31, 2014 (using date values):
busines_timeframe="from '01JAN2014'd to '31DEC2014'd"For more information, see BUSINESS_TIMEFRAME= Data Set Option.
specifies a time period to be used when querying or modifying a table that contains temporal data. You provide the beginning and ending datetime values in the format:
FROM <datetime> TO <datetime>Provide datetime values that include year, month, day, hours, minutes, seconds, and fractions of a second. For example, use the following code to specify the time period from midnight, November 1, 2012 to midnight, December 1, 2030:
system_timeframe="from '2012-11-01-00.00.00.0' to '2030-12-01-00.00.00.0'"For more information, see SYSTEM_TIMEFRAME= Data Set Option.
specifies columns that should not contain active business time periods that overlap. By default, overlaps are allowed for active business time records. Separate column names with commas. For more information, see OVERLAPS= Data Set Option.