DB2 10 is the first database product in the industry to provide temporal capabilities based on the ANSI/ISO SQL:2011 standard. The temporal data management capabilities in DB2 are seamlessly integrated with all other database features.
Time Travel Query is available in most DB2 editions, starting with DB2 Express.
The Time Travel Query function enables you to query data as it was at any point in the past, or as it will be at some point in the future. This is accomplished by using temporal tables to store a history of your data changes (deleted rows or the original values of rows that have been updated) so that you can query the past state of your data. You can associate date and time ranges to specific rows of data,thereby defining business validity periods for the data in your table. Time Travel Query helps you make your existing DB2 tables time aware and compliant with audit requirements. It also helps you to avoid the cost of developing, implementing, and maintaining complex time-aware applications.
You can easily enable the Time Travel Query function for existing tables by using the ALTER TABLE statement.
DB2 supports the following three types of temporal tables:
- System-period temporal tables manage data according to system time. DB2 keeps a running history of rows that have been updated or deleted. Each row and its corresponding history are assigned a pair of system timestamps. When a row has been deleted, it is automatically moved to a history table that is transparent to users and applications.
- Application-period temporal tables manage business time. Applications provide dates or timestamps to describe when the data in a given row was or will be valid in a business context.
- Bitemporal tables manage both system time and business time, enabling applications to manage the effective dates of their business data while DB2keeps a full history of data changes.