EIT for DB2 Licensing / licensing, DBA, database migration consultancy. Buy DB2 with confidence from a UK IBM partner. Phone us now to discuss your Oracle Licence / License requirements. We guarantee best discount Oracle price - we will provide a better pricing against any genuine UK Oracle quotation.  This applies to Oracle Licence,  support and training. Discount Dell Server, discount IBM Server

DB2 Time Travel (Temporal Data)

Many applications have a requirement to manage data at different points in time, and to establish how data has been added, modified and deleted over time. This is especially important when there are specific data governance or auditing requirements.

It is possible, but expensive and technically complex for businesses to develop their own infrastructure for temporal data management, such as additional tables, triggers, and application logic; DB2 Time Travel provides a simple and supported method to enable this to be acheived easily out of the box.

Time Travel Query makes your database time-aware and keeps a history of your data changes by using temporal tables. You can travel to the past and query your data as it appeared at different points in time without having to build, maintain, and administer a complex temporal infrastructure.

Time Travel Query helps you:

  • Easily make your existing DB2 tables time-aware
  • Provide a cost-effective and supported means to resolve auditing and compliance issues
  • Achieve full traceability of backdated corrections through the use of bitemporal tables
  • Lower cost with efficient SQL coding of complex time-focused operations to implement and maintain time-aware applications
  • Shorten application development time by allowing DBAs to use an existing SQL application and run it across different time periods
  • Reduce your time to deployment through an inexpensive and easy to maintain time-focused data support infrastructure
  • Create a time-based warehouse at low cost without additional application logic

For example, a database can store the history of a table (deleted rows or the original values of rows that have been updated) so you can query the past state of your data. You can also assign a date range to a row of data to indicate when it is deemed to be valid by your application or business rules.

For many businesses there are important reasons to preserve the history of data changes. Without this capability in the database, it is expensive and complex for businesses to maintain audit trails for regulatory compliance.

Implementation Detail

DB2 supports three types of temporal tables:

  • System-period temporal tables (STTs). For STTs, DB2 transparently keeps a history of updated and deleted rows over time. With new constructs in the SQL:2011 standard, you can "go back in time" and query the database as of any chosen point in the past. This is based on system timestamps that DB2 assigns internally to manage system time, also known as transaction time.
  • Application-period temporal tables (ATTs). Applications supply dates or timestamps to describe the business validity of their data in ATTs. New SQL constructs allow users to insert, query, update, and delete data in the past, present, or future. DB2 automatically applies temporal constraints and "row-splits" to correctly maintain the application-supplied business time, also known as valid time.
  • Bitemporal tables (BTTs). BTTs manage both system time and business time, and combine all the capabilities of system-period and application-period temporal tables. This combination enables applications to manage the business validity of their data while DB2 keeps a full history of any updates and deletes. Every BTT is also an STT and an ATT.

Example

One example doesn't do justice to the power of DB2's Time Travel implementation, but it helps to illustrate the power of the technology.

Suppose we want to record policy details for an insurance company:

 

This dreates the tables policy and policy_history.

A new policy is purchased with a coverage period from January 1 to July 1, 2012:

INSERT INTO policy
VALUES (1, 25000, 'N', 1000000, 474.56, '2012-01-01', '2012-07-01');

Later the policy is updated to include coverage for rental car usage at no additional
charge from June 1 onwards:

UPDATE policy
FOR PORTION OF BUSINESS_TIME FROM '2012-06-01' TO '2012-07-01'
SET rental_car = 'Y'
WHERE id = 1;

A simple select gives this data:

 

To get data for a particular time

SELECT * FROM policy FOR SYSTEM_TIME AS OF '2010-06-16';

 

A common requirement is to find the previous version of a row, which is the latest version before
the current version. This previous version is the most recent version of the row in the history
table. The following query retrieves the previous version of the policy with ID = 1414. There are different ways of doing this, for example:

SELECT *
FROM policy_history
WHERE id = 1414
AND sys_end = (SELECT MAX(sys_end)
FROM policy_history

Another option is to retrieve both the current row and the previous version:


SELECT *
FROM policy FOR SYSTEM_TIME FROM '0001-01-01' TO '9999-12-31'
WHERE id = 1414
ORDER BY sys_start DESC
FETCH FIRST 2 ROWS ONLY;