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:

• SYSHISTORYTABLES View
• SYSPERIODS View

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:

• SYSHISTORYTABLE View
• SYSPERIODS View

 

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