What If Your Data Had a Time Machine?

Imagine working on a shared Google Sheet. Someone updates a salary, deletes a row, or corrects a typo. But what if you need to know what was there before? Easy - just check Version History.

Now imagine your IBM i Db2 tables having the same power - being able to see what your data looked like yesterday, last week, or even six months ago.

That’s exactly what Temporal Tables offer.

What Are Temporal Tables?

A temporal table is a special type of table that automatically keeps track of changes over time. It doesn’t just store current data - it stores past versions as well, so you can:

• View old records
• Compare data across time
• Recover deleted or changed values
• Audit what changed and when

Think of it as:

“Version History” for your database table.

How It Works (With Analogy)

Let’s imagine a CCTV camera installed in your store’s billing system. Every time someone updates the price of an item, the CCTV records it.

A Simple Example

Step 1: Create a Temporal Table

CREATE TABLE employee (

  emp_id INT,

  name VARCHAR(50),

  salary DECIMAL(10,2),

  start_time TIMESTAMP GENERATED ALWAYS AS ROW BEGIN,

  end_time TIMESTAMP GENERATED ALWAYS AS ROW END,

  PERIOD FOR SYSTEM_TIME (start_time, end_time)

)

WITH SYSTEM VERSIONING;

What This Does:

• Adds system-maintained timestamps (start_time, end_time)
• Enables automatic versioning of every row change

Step 2: Make Some Changes

UPDATE employee SET salary = 6000 WHERE emp_id = 101;

DELETE FROM employee WHERE emp_id = 102;

With system versioning ON, the old rows aren’t lost - they go into the history table automatically!

Step 3: Query the Past

See how the table looked on Jan 1, 2025

SELECT * FROM employee

FOR SYSTEM_TIME AS OF '2025-01-01';

Tip : This is like checking a recorded CCTV clip from a specific time.

Why Should Beginners or QA Care?

Even if you’re not a DBA, this feature helps you:

• Find out who changed what and when
• Compare before and after values in testing scenarios
• Recover data without full restore
• Investigate production issues easily

Without Temporal Tables

Let’s say a salary was accidentally changed. Without journaling or temporal tables:

• The original value is gone
• You need backups or logs (if available)
• You can’t trace what went wrong

Temporal Tables vs Journaling: What’s the Difference?

Both are powerful and can be used together.

Things to Remember

• Old versions consume disk space - clean or archive when needed
• Use it where data changes frequently or audit trails are important
• Temporal tables are supported in Db2 for i 7.3+

Final Words

Whether you're in QA, development, or support - temporal tables are a modern, beginner-friendly feature that gives you peace of mind, auditability, and power to track data over time.

So next time someone says, “Hey, what was this value yesterday?” - you’ll know exactly where to look.