Free SQL Help for Devs, DBAs, and the Curious

In the world of database management, tracking historical data can be a complex task. Changes in data over time are frequently needed in various industries like banking, insurance, retail, and healthcare. One of the most efficient ways to handle this is through the use of temporal tables. Today, we will be focusing on DB2 Temporal tables and how they can be used for historical data tracking.

Temporal tables, also known as system-versioned tables, were introduced in DB2 11.5. They allow you to keep a history of data changes directly in the database, without the need for a separate history table. When a row in a temporal table is modified, DB2 automatically creates a copy of the old data and stores it in the associated history table. This approach can make querying historical data much easier and more efficient.

Let's dive into an example. Assume we have a 'Customers' table, and we want to track changes in the 'Address' column. Here's how you can create a temporal table in DB2:

CREATE TABLE Customers
(
  CustomerID INT NOT NULL PRIMARY KEY,
  Name VARCHAR(100) NOT NULL,
  Address VARCHAR(100) NOT NULL,
  SysStartTime TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL,
  SysEndTime TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL,
  PERIOD SYSTEM_TIME (SysStartTime, SysEndTime)
)
WITH SYSTEM VERSIONING ON HISTORY TABLE Customers_History

With the above code, a 'Customers' table is created along with a history table 'Customers_History'. The SysStartTime and SysEndTime columns are used to track the lifespan of each row. Any changes to the 'Address' column in the 'Customers' table will result in the old data being stored in the 'Customers_History' table.

To query the historical data, we can simply use a SELECT statement. For instance, if we want to retrieve the address of a customer at a specific point in time, we can use the following query:

SELECT Name, Address
FROM Customers
FOR SYSTEM_TIME AS OF '2020-12-31'
WHERE CustomerID = 1

This would return the address of the customer with ID 1 as of December 31, 2020. It's worth mentioning that the time travel query feature is not exclusive to DB2. Other relational database management systems like SQL Server 2019 and MySQL 8.0 also support temporal tables and offer similar functionality.

Furthermore, for those who operate in the cloud, Azure SQL Database and Azure Synapse Analytics also support system-versioned temporal tables. This means you can leverage the power of temporal tables in your cloud-based solutions regardless of whether you're using on-premises DB2, SQL Server, or MySQL, or cloud-based Azure SQL or Azure Synapse.

In conclusion, temporal tables in DB2 and other relational database management systems can be an effective tool for historical data tracking. They allow you to keep a record of data changes directly in the database, making querying for historical data simpler and more efficient. Whether you're operating on-premises or in the cloud, temporal tables can be a valuable addition to your data management toolkit.