Wednesday, October 26, 2022

SQL Views to help get information about Temporal Tables

When I ask people about their use of recent additions to the IBM i operating system my question about Temporal Tables are generally met by question "What are those?"

I am not going to explain what they are and how to use them in this post, as I wrote about them when they were introduced as part of IBM i 7.3 . You learn about them in the post I wrote when they introduced as part of 7.3, you can read about them here.

What I am going to explain in this post are two SQL Views that show you information about Temporal Tables:

Before I use the Views I am going to need a Temporal Table. Those of you who have attended my recent presentations about Temporal Tables will recognize it:

01  CREATE TABLE MYLIB.SHARE_PRICE
02           FOR SYSTEM NAME "SHAREPRICE"
03    (SHARE_SYMBOL FOR COLUMN "SYMBOL" VARCHAR(5) NOT NULL,
04     SHARE_PRICE FOR COLUMN "PRICE" DEC(8,2),
05     BEGIN_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
06     END_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
07     TS_ID TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
08     AUDIT_CHANGE FOR COLUMN "AUDITCHG" CHAR(1) GENERATED ALWAYS
                     AS (DATA CHANGE OPERATION),
09     PERIOD SYSTEM_TIME (BEGIN_TS,END_TS),
10     PRIMARY KEY(SHARE_SYMBOL)) ;

11  CREATE TABLE MYLIB.SHARE_PRICE_HISTORY
12  FOR SYSTEM NAME "SHAREPRICH"
13  LIKE MYLIB.SHARE_PRICE ;

14  ALTER TABLE MYLIB.SHARE_PRICE ADD VERSIONING USE
15  HISTORY TABLE MYLIB.SHARE_PRICE_HISTORY
16  ON DELETE ADD EXTRA ROW ;

Lines 1 – 10: The definition for the Table that will become the Temporal Table. Explanation for how the columns are defined is found in my earlier post.

Lines 11 – 13: "Cloning" the Temporal Table to create the Temporal History Table.

Lines 14 - 15: Activate the relationship between the Temporal Table and its History Table.

Now I have those tables I can start explaining what the Views contain.

 

SYSHISTORYTABLES View

The SYSHISTORYTABLES View lists the relationship between the Temporal History Table and its Temporal Table. In this example statement I want to list all the rows for any Temporal History Tables in my library:

01  SELECT HISTORY_TABLE_NAME,
02         VERSIONING_STATUS,
03         PERIOD_NAME,
04         TABLE_NAME,
05         SYSTEM_HISTORY_TABLE_NAME,
06         SYSTEM_TABLE_NAME
07    FROM QSYS2.SYSHISTORYTABLES  
08   WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB'

As the Temporal Table and its History Table have to be in the same library, I have omitted the columns in the results that contain the schema or library name.

                                                           SYSTEM_
                                                           HISTORY_   SYSTEM_
                     VERSIONING  PERIOD                    TABLE_     TABLE_
HISTORY_TABLE_NAME   _STATUS     _NAME        TABLE_NAME   NAME       NAME
-------------------  ----------  -----------  -----------  ---------  --------
STOCK_PRICE_HISTORY  E           SYSTEM_TIME  STOCK_PRICE  STKPRICEH  STKPRICE

The columns that need explanation are:

  • VERSIONING_STATUS:  "E"= Relationship established, "D" = Relationship defined but not established
  • PERIOD_NAME:  Name of the period. All of the Temporal Tables I checked are "SYSTEM_TIME"

This is certainly good information when trying to discover the relationships between Temporal Tables and their History Tables, as not everyone gives name to their Temporal and Temporal History Tables like I do.

 

SYSPERIODS View

The second View, SYSPERIODS, gives more information than the previous View. It does contain the name of the Temporal Table and its History Table, and the columns that are used to define the effective timestamp range of the row.

01  SELECT PERIOD_NAME,
02         TABLE_NAME,
03         BEGIN_COLUMN_NAME,
04         END_COLUMN_NAME,
05         PERIOD_TYPE,
06         HISTORY_TABLE_NAME,
07         ON_DELETE_ADD_EXTRA_ROW,
08         VERSIONING_STATUS,
09         SYSTEM_TABLE_NAME,
10         SYSTEM_HISTORY_TABLE_NAME,
11         SYSTEM_BEGIN_COLUMN_NAME,
12         SYSTEM_END_COLUMN_NAME
13    FROM QSYS2.SYSPERIODS
14   WHERE SYSTEM_TABLE_SCHEMA = 'MYLIB'

Again I have omitted the result columns that are for schema and library names from the results below.

                          BEGIN_    END_
                          COLUMN    COLUMN  PERIOD
PERIOD_NAME  TABLE_NAME   _NAME     _NAME   _TYPE   HISTORY_TABLE_NAME
-----------  -----------  --------  ------  ------  -------------------
SYSTEM_TIME  STOCK_PRICE  BEGIN_TS  END_TS  S       STOCK_PRICE_HISTORY


                                  SYSTEM_    SYSTEM    SYSTEM
ON_DELETE               SYSTEM_   HISTORY_   _BEGIN_   _END_
_ADD_EXTRA  VERSIONING  TABLE_    TABLE_     COLUMN    COLUMN
_ROW        STATUS      NAME      NAME       _NAME     _NAME
----------  ----------  --------  ---------  --------  ------
YES         E           STKPRICE  STKPRICEH  BEGIN_TS  END_TS    

The columns that are not in the SYSHISTORYTABLES View are:

  • BEGIN_COLUMN_NAME:  The long SQL name of the column that is used for the effective timestamp of the temporal row
  • END_COLUMN_NAME:  The SQL long name of the column for the expiration timestamp of the temporal row
  • PERIOD_TYPE:  "S" denotes that it is the system period
  • ON_DELETE_ADD_EXTRA_ROW:  Should a row be inserted into the History Table when a row is deleted from the Temporal Table
  • SYSTEM_BEGIN_COLUMN_NAME:  The short system name for the effective timestamp
  • SYSTEM_END_COLUMN_NAME:  The short system name for the expiration timestamp

 

I have to admit I prefer and use the SYSPERIODS rather than the SYSHISTORYTABLES as it does contain more useful information about the relationship between the two files.

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.5, and should work for some earlier releases too.

2 comments:

  1. According to the IBM docs on creating temporal tables at this link (https://www.ibm.com/docs/en/db2-for-zos/11?topic=tables-creating-temporal), it appears that in addition to system-period, there is also application-period.

    ReplyDelete
    Replies
    1. The link you provided is for Z/OS (mainframe). Db2 for i and Db2 for Z do have many differences.

      Delete

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.