Temporal Tables in DB2

DB2 is not just for DBAs anymore. DB2—long known for outstanding core DBMS capabilities—now also lends its features to developers. One example of this is the ability to enable time-based information directly within the schema. This is facilitated by new temporal tables in DB2 10 for Linux, UNIX, and Windows.

With temporal tables, you can easily track and analyze changes in your business and accurately compare data from two points in time. This capability allows you to effectively perform and trace data corrections and make data changes in the past, i.e. “effective” as a specific past point in time and also record when the change was made. Temporal tables facilitate auditing and compliance by providing the ability to show past data for any point in time, as well as show which information was changed in the same transaction and when.

What are temporal tables?

Temporal tables allow you to associate time-based state information to your data that is managed by DB2 and independent of application logic. Temporal table capabilities within DB2 allow for the tracking of data changes (versioning) and support custom business data storage and manipulation—as well as a combination of the two.

There are two types of temporal tables: system and application. DBAs can utilize the capabilities of both types using bi-temporal tables.

Temporal tables help to increase business insight by enabling easy reporting on data at a given point in time in the past, present, or future. This capability helps lower costs by increasing developer productivity (processing time management within DB2 means less coding, and simpler code means reduced maintenance costs). Temporal tables also allow for reduced compliance efforts and better tracking of data changes. Temporal tables offer standards-based technology, which provides consistency and data quality across the enterprise. Temporal tables are deeply integrated with all database features including partitioning, compression, and views.

System temporal tables

System tables enable time-based capability based on the operational time—i.e., the operating system time. System temporary tables utilize a history table that stores historical versions of the data.

How do you implement them? First, create or alter a base table with the SYSTEM_TIME attribute and include the following three specific generated columns:

• Row-begin column: the time at which the row data became current
• Row-end column: the time at which the row data is no longer current
• Transaction start-ID column: the start time of execution for the transaction affecting the row.

Next, create an identical table that will serve as the history table. This table must use an identical layout to the base table. However, the history table can be configured to suit your needs—for partitioning, compression, or storage location, for example.

The third step is to associate the history table and add versioning to the base table with an Alter statement, i.e. ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info.

For example:

Function of system tables

DB2 automatically migrates the rows from base table to the history table as changes occur and updates the three time columns as required. All management of the base and history table is automatic and transparent; no coding is necessary. Queries are written to only reference the base table, and DB2 will access the history table as needed based on the date ranges.

System temporal tables offer several benefits:

• For applications that only access current data, the impact is reduced because DB2 only accesses the base table
• Online load, reorg, index creation, and other maintenance operations for current data are not slowed down by the history table
• The physical storage options can be chosen individually for the base and history tables (for example, the location, compression, partitioning, clustering, etc.)
• The base table and the history table can have different indexes and constraints
• Recovery for the base table data and history data can be performed independently

DML for system temporal tables

Once the system temporal table is defined, populating and managing the data via performing DML (Insert, Update, Delete, Select) on temporal tables is similarly straightforward as with other tables. Below are the specifics for DML operations.

Insert: DB2 10 will insert the operating system time in the timestamp fields—they do not need to be provided.

Update: In addition to updating the values of specified columns in rows of the system temporal table, the UPDATE statement inserts a copy of the existing row into the associated history table. The history row is generated as part of the same transaction that updates the row. If a single transaction makes multiple updates to the same row, only one history row is generated and that row reflects the state of the record before any changes were made by the transaction.

Delete: In addition to deleting the specified rows of the system temporal table, the DELETE FROM statement moves a copy of the existing row into the associated history table before the row is deleted from the system temporal table.

Select: When querying a system temporal table, you can include FOR SYSTEM_TIME in the FROM clause. Using FOR SYSTEM_TIME specifications, you can query the current and past state of your data. Time periods are specified as follows:

• AS OF value1: Includes all the rows where the begin value for the period is less than or equal to value1 and the end value for the period is greater than value1. This enables you to query your data as of a certain point in time.
• FROM value1 TO value2: Includes all the rows where the begin value for the period is equal to or greater than value1 and the end value for the period is less than value2. This means that the begin time is included in the period, but the end time is not.
• BETWEEN value1 AND value2: Includes all the rows where any time period overlaps any point in time between value1 and value2. A row is returned if the begin value for the period is less than or equal to value2 and the end value for the period is greater than value1.

Time travel management: Setting a special register

When you have an application that you want to run against a system temporal table to query the state of your business for a number of different dates, you can set the date in a special register. If you need to query your data as of today, as of the end of the last quarter, and as of the same date from last year, it might not be possible to change the application and add AS OF specifications to each SQL statement. This restriction is likely the case when you are using packaged applications. To address such scenarios, you can use the CURRENT TEMPORAL SYSTEM_TIME special register to set the date or timestamp at the session level.

Setting the CURRENT TEMPORAL SYSTEM_TIME special register does not affect regular tables. Only queries on temporal tables with versioning enabled (system temporal tables and bi-temporal tables) use the time set in the special register. There is also no affect on DDL statements.

Application temporal tables

Application temporal tables allow for storing business time information (the application’s logical view of time).

For example:

• Pricing policy valid date ranges from April 1 to June 30
• Interest rate is in effect from start effective date to end effective date.
• Future – on a specific date the interest rate will increase to a new interest rate.

In these tables, DB2 adds, splits, or deletes rows transparently as needed. Application tables can be used to represent data in the future as well as the past and present. Constraints can be automatically enforced to disallow overlapping validity periods.

How do you implement them? First, create or alter a base table with the BUSINESS_ TIME attribute and include a pair of timestamp or date columns, stored by the application:

• Begin column: represents the time at which row data begins to be valid
• End column: represents the time at which row data is no longer valid

Time-sensitive columns are controlled by the application. Unlike system temporal tables, no separate history table is required.

Next, create a table with a BUSINESS_TIME period. For example:

Optional: Create a unique index that prevents overlapping periods of BUSINESS_TIME for the same
policy_id

. For example:

DML for application temporal tables

Once the application temporal table is defined, populating and managing the data via performing DML (Insert, Update, Delete, Select) on this type temporal tables is similarly straightforward as with other tables. Below are the specifics for DML operations.

Insert: When inserting data into an application temporal table, the only special consideration is the need to include the row-begin and row-end columns that capture when the row is valid from the perspective of the associated business applications. This valid period is called the BUSINESS_TIME period. The database manager automatically generates an implicit check constraint that ensures that the begin column of the BUSINESS_TIME period is less than its end column. If a unique constraint or index with BUSINESS_TIME WITHOUT OVERLAPS was created for the table, you must ensure that no BUSINESS_TIME periods overlap.

Update: In addition to the regular UPDATE statement, application temporal tables also support time range updates where the UPDATE statement includes the FOR PORTION OF BUSINESS_TIME clause. A row is a candidate for updating if its period-begin column, period-end column, or both fall within the range specified in the FOR PORTION OF BUSINESS_TIME clause. Updating an application temporal table by using the FOR PORTION OF BUSINESS_TIME clause prevents period overlap problems.

Delete: In addition to the regular DELETE statement, application temporal tables also support time range deletes where the DELETE statement includes the FOR PORTION OF BUSINESS_TIME clause. A row is a candidate for deletion if its period-begin column, period-end column, or both fall within the range specified in the FOR PORTION OF BUSINESS_TIME clause. Deleting data from an application temporal table removes rows from the table and can potentially result in new rows that are inserted into the application temporal table itself.

Select: When querying an application temporal table, you can include FOR BUSINESS_TIME in the FROM clause. Using FOR BUSINESS_TIME specifications, you can query the current, past, and future state of your data. Time periods are specified as follows:

• AS OF value1: Includes all the rows where the begin value for the period is less than or equal to value1 and the end value for the period is greater than value1.
• FROM value1 TO value2: Includes all the rows where the begin value for the period is greater than or equal to value1 and the end value for the period is less than value2. This means that the begin time is included in the period, but the end time is not.
• BETWEEN value1 AND value2: Includes all the rows where any time period overlaps any point in time between value1 and value2. A row is returned if the begin value for the period is less than or equal to value2 and the end value for the period is greater than value1.

“Time travel” management with special registers

Using the DB2 Time Travel Query feature, you can set the clock back or forward to a specific time for a given session and no changes are required to an application. When you have an application that you want to run against an application temporal table to query the state of your business for a number of different dates, you can set the date in a special register. If you need to query your data as of today, the end of the last quarter, or if you are simulating future events as of some future date, it might not be possible to change the application and add AS OF specifications to each SQL statement. This situation is often the case when a packaged application is used. In these scenarios, you can use the CURRENT TEMPORAL BUSINESS_TIME special register to set the date at the session level.

Setting the CURRENT TEMPORAL BUSINESS_TIME special register does not affect regular tables. Only queries on temporal tables with a BUSINESS_TIME period enabled (application temporal tables and bi-temporal tables) use the time set in the special register.

Figure 1. Comparison of time-period for system time versus application time

Bi-temporal tables

A bi-temporal table is a table that combines the historical tracking of a system temporal table with the time-specific data storage capabilities of an application temporal table. You can use bi-temporal tables to keep user-based period information as well as system-based historical information. All the restrictions that apply to system temporal tables and application temporal tables also apply to bi-temporal tables.

Consider this example: An organization wants to know which products were offered on June 20, 2011, as recorded in the database on May 10, 2011. A bi-temporal table combines the capabilities of application (ATT) and system (STT) tables. Every row has a pair of:

• Timestamps set by DB2 (SYSTEM_TIME period)
• Business timestamps or date columns set by the application (BUSINESS_TIME period)

For example:

Optionally, you can create a unique index that includes the BUSINESS_TIME period. For example:

DML for bi-temporal tables

Once the bi-temporal table is defined, populating and managing the data via performing DML (Insert, Update, Delete, Select) on bi-temporal tables is similarly straightforward as with other tables. Below are the specifics for DML operations.

• Insert: Inserting data into a bi-temporal table is similar to inserting data into an application temporal table.
• Update: Updating data in a bi-temporal table results in rows that are added to its associated history table and can potentially result in rows that are added to the bi-temporal table itself.
• Delete: Deleting data from a bi-temporal table results in rows that are deleted from the table, rows that are added to its associated history table and can potentially result in new rows that are inserted into the bi-temporal table itself.
• Select: Querying a bi-temporal table can return results for a specified time period. Those results can include current values, previous historic values, and future values.

Many IT organizations have existing tables that they would like to turn into system-temporal and/or application-temporal tables. These tables can be made into temporal tables and will gain the following benefits:

• Lower cost of operation, since logic is performed inside the DB2 engine
• Reduced application logic and shorter application development time
• Fewer hand-coded triggers and stored procedures
• Simpler queries
• Transparent deployment
• Simpler, faster compliance

Views on temporal tables

Views may be defined on system temporal tables (base and history), application temporal tables, or bi-temporal tables. All syntax (e.g. FOR PORTION OF, AS OF, FROM… TO, etc.) is also supported for views. Two types of views may be defined for temporal tables: FOR BUSINESS_TIME or FOR SYSTEM_TIME.

Conclusion

Temporal tables provide a database-oriented method of managing time-based data. This ensures that all applications are using the same method for time-stored data. It leads toward a common “time travel” module and once implemented can greatly simplify managing time-oriented data in your applications.