Executive Summary
The temporal features in the IBM® DB2® for Linux®, UNIX®, and Windows® Version 10 product (hereafter referred to as "DB2") provide rich functionality for time-based data management. For example, you can choose to record the complete history of data changes for a database table so that you can "go back in time" and query any past state of your data. You can also indicate the business validity of data by assigning a pair of date or timestamp values to a row to indicate when the information is deemed valid in the real world. Using new and standardized SQL syntax, you can easily insert, update, delete, and query data in the past, present, or future.
The temporal features in the DB2 product enable you to accurately track information and data changes over time and provide an efficient and cost-effective way to address auditing and compliance requirements. This article describes a set of best practices that help ensure smooth operation and high performance for DB2 temporal data management.
Introduction to the temporal capabilities in DB2
The DB2 for Linux, UNIX, and Windows Version 10 software ("DB2") supports time-based data management that allows you to insert, update, delete, and query data in the past, the present, and the future while keeping a complete history of "what you knew" and "when you knew it".
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.
For the remainder of this article, we assume that you are familiar with the basics of system-period temporal tables, application-period temporal tables, and bitemporal tables in DB2. You should know how to create, query, and update such tables, and understand how DB2 might perform row-splits when you update or delete data for a specified portion of business time. The article "
A Matter of Time: Temporal Data Management in DB2" provides a complete introduction to these
topics.
#Db2