A step by step guide to creating DB2 Temporal Tables on IBM i
In this article we will cover System Period Temporal Tables; one of many areas where IBM continues to improve the DB2 for IBM i database. This functionality was introduced in IBM i 7.3 and is an extremely useful and powerful function to help you get the most out of your data.
In keeping with IBM’s drive to make IBM i more data-centric and SQL focused we use the terms Table for File; Row for Record and Column for Field.
System Period Temporal Tables give you the ability to store and retrieve historical ‘point in time’ information from your tables.
A simple example will explain the concept – We have a table of customer deposits:
- New record for customer 7372 created in January 2017
- Customer 7372 deposit level changed in March 2017
- Customer 7372 deposit level changed in May 2017
- Customer 7372 deposit level changed in……
- Customer 7372 records deleted in January 2018
As part of your management information processes, you need to know customer 7372’s deposit amount in July 2018. How do you get this?
If you don’t have an in-house created history table holding this information finding the answer could be anything from tricky to impossible, involving restoring information from backups. Even if you do have a history table, read on to see how System Period Temporal Tables are a better solution.
System Period temporal Tables consist of two tables: the current live table and a history table. Using the above example, between them they would hold all the records at each change point. When querying the information there is no need to know whether to access the current table or the history table; you always use the current table and if the time period you’re after is in the history table the system knows this and accesses it automatically.
For an in-house created history function to automatically know where each ‘point in time’ record exists would be more complicated to manage. However, another big benefit of System Period Temporal Tables is that they are ‘Data-Centric’ – any and all applications that write, update or delete to the live table automatically maintain the history data, there is nothing else to worry about. With an in-house solution, you have to know all the programs and utilities that maintain the live table and make sure that the historical data is maintained accordingly. That’s a lot of effort.
System Period Temporal Tables:
- Are supported in SQL Data Definition Language tables and in the traditional Data Description Specifications files.
- Tables have to be journaled.
- Can be implemented on existing files
- History file format must match the current (temporal) file format
- Adding a column to temporal tables automatically adds it to the history table
- You cannot drop a column (field) in a temporal table
To implement System Period Temporal Tables, consider the following example table:
The following SQL alters the existing current table:
The current table has the following added:
- Two TIMESTAMP columns to record when row became current and when it stopped being current.
- A TIMESTAMP transaction ID field.
- The SYSTEM_TIME period column for a system-period temporal table indicates when the version of a row is current.
- Generated by the database manager.
- Can be used in queries
- The following SQL statements create the history table and associate it with the current table
Now you are all set to take advantage of recording ‘point in time’ record changes. So based on the example table above we add in a new row and amend it several times:
- Deposit 111 added
- Deposit 111 becomes 222
- Deposit 222 becomes 333
If we query the current table:
- Deposit amount is the latest amount
- The ROW_START field is a timestamp of the last update
- ROW_STOP is meaningless as the row is still active in the current temporal table
- T_ID field is the timestamp of the transaction (last update)
If we query the history table:
- Deposit amounts are the originals before the change
- The ROW_START field is timestamp of the last update
- ROW_STOP is timestamp when row ceased to be current
- Notice how ROW_STOP value of the second record matches ROW_START of the current record
- T_ID field is the timestamp of the transaction (last update)
Querying the current temporal table or the history table serves only to explain the concept; remember earlier I said that you do not have to be concerned with which file holds which records. New SQL constructs allow you to query the current table and the system will do the rest:
Query by time period:
- Select * from chilli/custdep for system_time as of ‘2018-02-22-15.17.17’
- Data is from the record as of the time selected.
- Data comes from a history table even though you did not specify it.
- Select * from chilli/custdep for system_time from ‘2018-02-22-15.00.00’ to ‘2018-02-22-16.00.00’
- Data may consist of more than one row as in this example.
- Data comes from a history table even though you did not specify it.
- Unless you order the records it may not be apparent which is the most current record
There are a few things to be aware of such as SQL is recommended for data retrieval as methods are built into the language and so the history table is accessed automatically as needed. Using something like RPG requires more effort as the history table access is not automatic and requires separate access. However, RPG data changes do still result in correct history tracking.
Summary of clauses on SELECT statement
- FOR SYSTEM_TIME AS OF
- FOR SYSTEM_TIME FROM TO
- FOR SYSTEM_TIME BETWEEN AND <value)
So, briefly, we have covered the powerful System Period Temporal Table; the ability to let the database record and retrieve historical information from your tables.