More tips for using Temporal tables

I first wrote about using Temporal tables when they were introduced as part of IBM i 7.3, back in 2016. I thought they would become widely used, as they provide a way to look at the same data as it was at different times in the past.

Having had three years to "play" with Temporal tables, I have learned many things I can do with them, that I did not know when I first wrote about them. I thought this would be a good opportunity to revisit them, and to share some of the extra tips and tricks I have learned.

One thing to remember, and I always forget, is that all of the tables used must be journaled. For the easiest way to do this refer to my original post about Temporal tables.

For a Temporal table I need a table that will be the "live" table. I have deliberately kept this example very simple, so the data does not get in the way of the important columns needed for a Temporal table. My example table will only contain two "data" columns, the rest are used by the Temporal table process. This is the example table I will be using:

01 CREATE TABLE MYLIB.MY_TEMPORAL_TABLE 02 FOR SYSTEM NAME "TMPRLTAB1" 03 (THING FOR COLUMN VARCHAR(20), 04 VALUE FOR COLUMN VARCHAR(30), 05 BEGINNING_TIME FOR COLUMN "BEGIN_TS" 06 TIMESTAMP(12) NOT NULL 07 GENERATED ALWAYS AS ROW BEGIN 08 IMPLICITLY HIDDEN, 09 ENDING_TIME FOR COLUMN "END_TS" 10 TIMESTAMP(12) NOT NULL 11 GENERATED ALWAYS AS ROW END 12 IMPLICITLY HIDDEN, 13 TRANSACTION_START FOR COLUMN "TIME_ID" 14 TIMESTAMP(12) NOT NULL 15 GENERATED ALWAYS AS TRANSACTION START ID 16 IMPLICITLY HIDDEN, 17 PERIOD SYSTEM_TIME (BEGINNING_TIME,ENDING_TIME), 18 AUDIT_USER VARCHAR(18) 19 GENERATED ALWAYS AS (USER) 20 IMPLICITLY HIDDEN, 21 AUDIT_CHANGE FOR COLUMN "AUDIT_CHG" 22 CHAR(1) 23 GENERATED ALWAYS AS (DATA CHANGE OPERATION) 24 IMPLICITLY HIDDEN, 25 PRIMARY KEY(THING)) ;

Line 1 and 2: I have called this table MY_TEMPORAL_TABLE which is longer than ten characters. I use the SYSTEM NAME to give this table a name that will be used by the system when I look at this file things like PDM, etc. Either name can be used in SQL statements.

Lines 3 and 4: These are the definitions for what I called the "data" columns.

Lines 5 – 17: This code is needed for any Temporal table as it must contain:

• Lines 5 – 8: The timestamp of when the row was created
• Lines 9 – 12: The timestamp of when the row was changed (updated or deleted)
• Lines 13 – 16: Transaction start id, which always contains the same time as the row begin column
• Line 17: PERIOD SYSTEM_TIME tells Db2 for i the columns that contain the start and end timestamp for the row

Lines 18 – 24: These are Audit columns that can be added to any DDL table or DDS file.

• Lines 18 – 20: User profile of the person who inserted, updated, or deleted this row.
• Lines 21 – 24: Which operation (insert, update, delete) was performed to this row.

Lines 8, 12, 16, 20, and 24: IMPLICITY HIDDEN is a useful feature. When you use a SQL Select statement, RUNQRY command, to display all the columns/fields in a table/file these will not be displayed. You have to name all the columns in the column part of the statement to see them.

Now I need to clone the “live” table to create the “history” table. I do so using the following SQL statement:

01 CREATE TABLE MYLIB.MY_TEMPORAL_HISTORY 02 FOR SYSTEM NAME "TMPRLTAB1H" 03 LIKE MY_TEMPORAL_TABLE

The name I used for my "history" table, MY_TEMPORAL_HISTORY, is longer than ten characters, therefore I have use the SYSTEM NAME, line 2, to give this table a decent, meaningful, name ten character name.

The last part is to activate the versioning. For that I use the following ALTER TABLE expression:

01 ALTER TABLE MY_TEMPORAL_TABLE 02 ADD VERSIONING USE HISTORY TABLE 03 MY_TEMPORAL_HISTORY 04 ON DELETE ADD EXTRA ROW

Line 4: This is new, not mentioned in my previous Temporal table post. This tells Db2 for i to write a deleted row to the "history" file when the matching row is deleted from the "live" file. I will give an example of this below.

Now let's add some data to the "live" table. Here I am doing so using a source member and the RUNSQLSTM command to execute the statements within the member.

01 INSERT INTO MYLIB.MY_TEMPORAL_TABLE VALUES('ANIMAL','AARDVARK') ; 02 CL: DLYJOB 120 ; 03 UPDATE MYLIB.MY_TEMPORAL_TABLE SET VALUE = 'BUFFALO' WHERE THING = 'ANIMAL' ; 04 CL: DLYJOB 120 ; 05 UPDATE MYLIB.MY_TEMPORAL_TABLE SET VALUE = 'COUGAR' WHERE THING = 'ANIMAL' ;

Line 1: I insert a row into the "live" table.

Line 2: The CL: allows me to execute a CL command. In this case a Delay Job command, DLYJOB, of two minutes.

Line 3: I update the row in the "live" table, changing the VALUE column.

Line 4: Another delay of two minutes.

Line 5: Change the VALUE column again.

What would I expect to find in my Temporal tables?

1. The "live" table to contain the COUGAR row.
2. The "history" table to contain rows for the times when the row was equal to AARDVARK and BUFFALO.

This is where I can show what the IMPLICITLY HIDDEN does. If I use the following SQL statement:

SELECT * FROM MYLIB.MY_TEMPORAL_TABLE

I only see the columns that are not IMPLICITLY HIDDEN:

THING VALUE ------ -------- ANIMAL COUGAR

To see the values of all the columns I need to give them in the Select statement:

SELECT THING,VALUE,BEGIN_TS,END_TS,TIME_ID,AUDIT_USER,AUDIT_CHANGE FROM MYLIB.MY_TEMPORAL_TABLE THING VALUE BEGIN_TS ------ -------- -------------------------------- ANIMAL COUGAR 2019-08-01 21:35:18.344909000244 END_TS -------------------------------- 9999-12-30 00:00:00.000000000000 TIME_ID AUDIT_USER AUDIT_CHANGE -------------------------------- ---------- ------------ 2019-08-01 21:35:18.344909000244 SIMON U

I have not had to provide any values for the temporal and audit columns, they are provided by Db2 for i.

In the "history" table I can see the previous values for the "live" table's rows:

SELECT THING,VALUE,BEGIN_TS,END_TS,TIME_ID,AUDIT_USER,AUDIT_CHANGE FROM MYLIB.MY_TEMPORAL_HISTORY THING VALUE BEGIN_TS ------ -------- ------------------------------- ANIMAL AARDVARK 2019-08-01 21:31:08.894507000244 ANIMAL BUFFALO 2019-08-01 21:33:15.169035000244 END_TS -------------------------------- 2019-08-01 21:33:15.169035000244 2019-08-01 21:35:18.344909000244 TIME_ID AUDIT_USER AUDIT_CHANGE -------------------------------- ---------- ------------ 2019-08-01 21:31:08.894507000244 SIMON I 2019-08-01 21:33:15.169035000244 SIMON U

I am sure you can determine what the values in the AUDIT_CHANGE columns mean.

 

Oops, someone has deleted the row. Is there a way I can recreate it?

Oh no, someone has deleted a row out of the "live" table! What can be done?

When any change is made to a "live" row, including being deleted, a copy of the pre-change row is inserted into the "history" table. Therefore, I can use a SQL Select statement on the "history" table to find a copy of the row before it was deleted:

SELECT THING,VALUE,BEGIN_TS,END_TS,AUDIT_CHANGE FROM MYLIB.MY_TEMPORAL_HISTORY WHERE THING = 'ANIMAL'

The above statement will return all of the inserted, updated, and the deleted row from the "history" file.

THING VALUE BEGIN_TS ------ -------- -------------------------------- ANIMAL AARDVARK 2019-08-01 21:31:08.894507000244 ANIMAL BUFFALO 2019-08-01 21:33:15.169035000244 ANIMAL COUGAR 2019-08-01 21:35:18.344909000244 ANIMAL COUGAR 2019-08-01 22:13:08.714900000244 END_TS AUDIT_CHANGE -------------------------------- ------------ 2019-08-01 21:33:15.169035000244 I 2019-08-01 21:35:18.344909000244 U 2019-08-01 22:13:08.714900000244 U 2019-08-01 22:13:08.714900000244 D

I am sure you have noticed that there are two rows for COUGAR. When the row is deleted an "update" row is inserted into the "history" table, and as I gave ON DELETE ADD EXTRA ROW when I activated the temporal relationship between the two tables a "delete" row is inserted into the "history" table too. This makes it easier for me to find rows that were deleted from the "live" table as I can just look for the "deleted" rows in the "history" table.

If I wanted to restore a deleted row from the "history" table back into the "live" table I could just use the following statement:

01 INSERT INTO MYLIB.MY_TEMPORAL_TABLE 02 SELECT THING,VALUE 03 FROM MYLIB.MY_TEMPORAL_HISTORY 04 WHERE THING = 'ANIMAL' 05 AND AUDIT_CHANGE = 'D' 05 ORDER BY END_TS DESC 06 LIMIT 1

Line 1: I am inserting a row into MY_TEMPORAL_TABLE.

Line 2: I only need to insert these two columns as the others, temporal and audit columns, are updated automatically by Db2.

Line 3: From the Temporal History file.

Line 4: When the key column value matches the deleted row.

Line 5: I am only want "deleted" rows in my results.

Line 6: I am sorting the rows in the Temporal Historical file by the ending timestamp in descending order, in other words the most recent will be first. I am doing this just in case this row has been deleted more than once before.

Line 7: LIMIT allows me to tell the Select statement how many results I want returned. In this case I only want one.

After running this statement I have "re-created" the row, well... it does have different timestamp and the audit user values, but the rest of the row is restored.

THING VALUE BEGIN_TS END_TS TIME_ID AUDIT_CHANGE ------ -------- ------------- ------------- ------------- ------------ ANIMAL COUGAR 2019-08-03... 9999-12-30... 2019-08-03... I

 

How do I delete a Temporal table if I decide I don't want to use it?

It is now possible to use the DLTF command or the SQL DROP TABLE while a table is being used as a Temporal table. I have to end the "relationship" between the two tables first, by using the ALTER TABLE statement:

ALTER TABLE MYLIB.MY_TEMPORAL_TABLE DROP VERSIONING

After this if any changes are made to MY_TEMPORAL_TABLE the historical row is not written to MY_TEMPORAL_HISTORY.

I can now DROP (delete) the tables:

DROP TABLE MYLIB.MY_TEMPORAL_HISTORY DROP TABLE MYLIB.MY_TEMPORAL_TABLE

 

This article was written for IBM i 7.3, and will work for later releases too.