DB2 10 introduces temporal table support in accordance with the SQL 2011 ANSI/ISO standard and is the first commercial database to implement temporal table support as specified by the standard. Other commercial databases have their own version of temporal table implementation; this limits application transparency and portability. The three types of temporal tables are:
• System-period temporal tables
• Application-period temporal tables
• Bitemporal tables.
Here we will discuss in detail the first two and provide an overview of bitemporal table support.
Time Travel Query
IBM uses the term Time Travel Query to describe temporal table support in DB2 10. The requirement for companies to track data over time drives the need for temporal tables. For financial service firms, this could be the need to track the value of a certain portfolio for a given period of time, track the period when certain products were offered and valid, or track the value of stocks or bonds at a specific time. The need to track can be based on business requirements, internal policies, regulatory and audit requirements, Generally Accepted Accounting Principles (GAAP) or International Financial Reporting Standards (IFRS). The use of temporal tables drastically reduces the amount of application logic previously required to implement user-defined historical tables.
Temporal tables help answer questions such as:
• Does your company have full traceability of all data changes and retroactive data corrections?
• Can you delineate the price of an item during any specific current or historical period of time?
• What were the prices of stock used in a valuation or initial public offering three years ago?
• When the company merged with another company, what were the financials at the time of the merger?
• How long was Policy B offered by the company at a certain rate?
• How much was the deductible for customer X during a time span relative to a filed claim?
System-Period Temporal Tables
System-period temporal tables manage data according to system time. Each row and its corresponding history are automatically assigned a pair of system timestamps. DB2 keeps a history of rows that have been updated or deleted over time. In accordance with the SQL 2011 ANSI/ISO standard, the DB2 10 implementation lets you go “back in time” and run queries for any chosen point in the past.
Using system-period temporal tables reduces the impact on applications that only access current data since historical data is contained in the associated history table. Inserts of new rows are faster than if the history was contained in the same table. Utilities, such as LOAD and REORGANIZATION on the current table, aren’t slowed down by the presence of historical data.Moreover, you can also:
• Have different indexes on the current table vs. history table
• Recover current data and history data independently
• Have different constraints on current table vs. history table
• Use different storage options for base and history table such as compression, range partitioning, clustering and storage group and physical location on disk.
Time periods apply to system time and business time. A period indicates the starting and ending points of a time interval. DB2 uses an inclusive/exclusive approach for implementing time periods.
DB2 10’s support for system time provides for multiple versions of your data. When you define a system-period temporal table, DB2 will automatically capture changes to the table and save the old rows to a user-created history table. The history table is a separate table with the same structure as your current table. This capability alone saves developers and DBAs countless hours of application development time. In the past, developers or DBAs had to write custom application logic, stored procedures or triggers to keep track of historical data changes in separate tables. Now, DB2 10 does it for you!