Wednesday, October 23, 2019

More tips for using Temporal tables

db2 for i temporal tables audit user, recovering deleted row, how to delete 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.

2 comments:

  1. Very interesting post.
    I've been waiting for "temporal tables" for years. And now, in my company, finally we run version 7.3.
    So it's time to play!!!

    let me correct the sintax of the first sentence, the CREATE TABLE, because it has some errors (The 2 first "FOR COLUMN" without any alias).

    CREATE TABLE MY_TEMPORAL_TABLE FOR SYSTEM NAME TMPRLTAB1 (
    THING VARCHAR(20),
    VALUE VARCHAR(30),
    BEGINNING_TIME FOR COLUMN BEGIN_TS TIMESTAMP(12) NOT NULL
    GENERATED ALWAYS AS ROW BEGIN
    IMPLICITLY HIDDEN,
    ENDING_TIME FOR COLUMN END_TS TIMESTAMP(12) NOT NULL
    GENERATED ALWAYS AS ROW END
    IMPLICITLY HIDDEN,
    TRANSACTION_START FOR COLUMN TIME_ID TIMESTAMP(12) NOT NULL
    GENERATED ALWAYS AS TRANSACTION START ID
    IMPLICITLY HIDDEN,
    PERIOD SYSTEM_TIME (BEGINNING_TIME, ENDING_TIME),
    AUDIT_USER VARCHAR(18)
    GENERATED ALWAYS AS (USER)
    IMPLICITLY HIDDEN,
    AUDIT_CHANGE FOR COLUMN AUDIT_CHG CHAR(1)
    GENERATED ALWAYS AS (DATA CHANGE OPERATION)
    IMPLICITLY HIDDEN,
    PRIMARY KEY(THING)) ;



    Thanks (again) for sharing.

    ReplyDelete
  2. not sure if this has been discussed but I am running into an issue where I am making changes (adding and removing columns) to the LIVE table but my changes don't promote because of the versioning so you have to DROP versioning before altering your table columns.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.