Introduction – 

In this article, I want to run though a great new feature of DB2 that was released as part of the Version 7.3 announcements last month.

As the title of this article states ‘It’s about time’!

IBM introduced a new feature within the database called temporal support. So, what is temporal support?

Temporal support is the ability to go back in time in your database to a snapshot, or period, in the past.

You may say, ‘Well, I can do that already with journaling!’ but journaling and temporal are two totally different features.

Journaling does not produce relational data that you can run a query statement over as part of the normal day to day running of the business.

The main purpose of journaling is transaction isolation and recovery, and often used as a support mechanism for transaction replication.

This is a quote seen on Twitter on the day of the April version 7.3 announcement –
“DB2 temporal support in #IBMi 7.3 will allow me to remove 46 redundant libraries & improve historical data archiving/analysis.”

One happy IBMi user!

With temporal tables, if the boss asks ‘What did our orders look like this time last year?’ We can easily go back in time to query our tables using a snapshot of the business at that point in time.

An additional benefit of temporal support is that as a fully auditable trail is recorded when data has been modified, or deleted, and who did it. This provides a budget friendly method of addressing auditing and compliance.

Temporal support is built into the DB2 database and is not an additional feature you have to install. It’s there ready for use, Why not use it!

What are Temporal Tables?

The advantages of a temporal table are that the table maintains historical versions of its rows.

In fact, the term temporal data refers to records that are versioned within the database.

So for any given logical record, there is a current version & zero or more prior versions.

This allows for the calculating of trends over time, and all managed by the database – not by journaling!

This is pictorially shown in the graphic below.

How does this all work?

For each physical table, we have an associated history table, which stores all the time travelling information, but firstly we must add a couple of columns to our table to make it a temporal table. As you can guess, these are time related columns. We have to give the database a bit of help here!

The steps we need to take are:
Defining row begin, row end & transaction start ID columns for the database manager to use for maintaining historical times for each row

Defining a SYSTEM_TIME period to track when a row is current

The SQL statements to add these features is shown in the graphic below:

This is the only change we need to make to our existing table.

Let us now move onto the history side of the equation.

History Tables

The history table must be defined as exactly the same layout, columns and attributes, as its associated temporal table.

A quick an easy SQL statement will create that for us, using the base table for its definition.

Once we have our history table in place, we need to link them together to have our temporal features in operation.

Once again, a quick SQL statement is the answer here.

In this example, our employee table is now a temporal table and we can use all the full features of versioning.

Querying Temporal Tables

So, now we have everything in place, how do we see our versioning?

All the SQL statements to use temporal tables are performed on the main table, we do not query the history table, the database management system, will decide, if and when to pull data from the history table. Less work for us to think about, I’m happy with that!

The SQL statement uses the ‘SYSTEM_TIME’ attribute to query both the main table and its history.

The results produced by the query, show all versions of the employee record

Conclusion

Hopefully, this article has given you an insight into the world of time travelling for DB2 on IBM i.

To learn more, come and see my workshop on ‘Time Travelling with Temporal Support’, (the only one in the world at present), at the International iPower conference in June where you can get hands-on experience of this up-to-date feature.

Full details can be found at https://www.nccomms.com/international_iPower/Workshops.aspx

Andy Youens is an IBM i consultant/instructor at Milton Keynes, UK-based FormaServe Systems.