Often it would be beneficial to be able to track changes of table contents. For configuration tables for example it could be helpful which value got valid at what time and what was configured before.
In Db2 a perfect solution exists: system-period temporal tables – not to be mixed up with temporary tables like DGTT or CGTTs. Db2 will automatically do the whole historization – without a need to change the application – an enormous advantage.
Technically system-period temporal tables consist of two tables – one containing current data and the other one with historical data. The plus is that these tables look like a single object to the “outside” and can be queried with temporal – so called time travel SQL. This SQL extension is – against expectation of many – part of the SQL standard (SQL:2011).
An example will help and clarify this:
CREATE TABLE EMPL (
empno char(6) not null,
workdept char(3),
edlevel int,
system_start timestamp(12) generated always as row begin not null,
system_end timestamp(12) generated always as row end not null,
trans_start timestamp(12) generated always as transaction start id
not null,
PERIOD SYSTEM_TIME (system_start, system_end)
);
Three additional time related columns are needed in the table to implement historization. These columns have a data type of timestamp(12) and need to be defined as “generated always” so they get populated by Db2 automatically.
system_start: Begin of the period
system_end: End of the period
trans_start: Start of the transaction – is only needed to solve certain problem scenarios
The additional PERIOD clause defines which columns are used to implement historization because the column names can be chosen arbitrarily.
To store the historic rows a history table is needed with the same structure as the base table and these can be created like this:
CREATE TABLE empl_hist LIKE empl
This statement creates a structural clone of the base table. This table will hold all finished periods of the data.
Tip:
Because it is – physically – a separate object specific settings can be used for it and different security settings can be used. Users may have insert, update and delete rights but only need a select privilege on the EMPL_HIST to avoid manipulations of the history. A DBA also could use the “append on” option in the CREATE statement to optimize for the inserts as only insert operations are executed on this table avoiding unnecessary free space searches.
The last step is combining both objects to a logical entity via:
ALTER TABLE empl ADD VERSIONING USE HISTORY TABLE empl_hist
Tip:
If you want to check if such kind of tables already exist in your database one could query the TEMPORALTYPE column in SCSCAT.TABLES where “S” is the abbreviation for system-period temporal table type. Alternatively further details could also be found in SYSCAT.PERIODS.
With this the DDL is complete and use cases can be tested.
Some sample INSERTs:
INSERT INTO empl (empno, workdept, edlevel)
VALUES ('001000', 'D11', 13)
, ('001100', 'D11', 14)
, ('001200', 'E11', 10)
This will insert three rows on the EMPL table.
EMPL
Picture 1: After Insert
The interpretation is: Three employees have been created on 2022-12-01 with the corresponding EDLEVEL which is still valid. The associated EMP_HIST table is empty at this point in time as there has not been any change (UPDATE or DELETE) yet.
This will change once we execute following Update (on 23.12.2022 at 11:00 o’clock):
UPDATE empl
SET edlevel = edlevel + 1
WHERE empno = '001000'
EMPL
EMPL_HIST
Picture 2: After Update
This can be understood as follows:
Employee '001000' has a current EDLEVEL of 14 – more precisely since 2022-12-23 at 11.01 o’clock and is valid to the end of time.
The EMPL_HIST table shows that exactly this employee had an EDLEVEL of 13 from 2022-12-01 11:01 to 2022-12-23. Please note that the SYSTEM_START of the current row equals the SYSTEM_END of the previous period. Important: This is no problem at all as all the start times are inclusive but the end times are exclusive in Db2 which will ensure uniqueness.
The EMPL_HIST was not referenced in the update statement ad Db2 has managed that in the background – another great advantage.
Let us check now what will happen at a regular delete opertaion:
DELETE FROM empl
WHERE empno = '001200'
EMPL
EMPL_HIST
Picture 3: After Delete
As expected the row of employee 001200 got deleted from the EMPL table (picture 3).
The employee appears in the EMPL_HIST with a valid period from 2022-12-01 08:00 to 2022-12-23 11:15 because it existed within that period with a EDLEVEL = 10.
These tables can be also queried with so called time travel SQL. The specialty is that there is no need to query the tables independently. Only the EMPL table can be queried and everything else will be handled by Db2 under the covers – a further advantage.
Let’s examine following question as an example:
Which EDLEVEL was associated with the members of department D11 at 2022-12-15?
SELECT *
FROM EMPL FOR SYSTEM_TIME AS OF '2022-12-15-00.00.00.000000'
WHERE WORKDEPT = 'D11'
Picture 4: Result of the AS OF SELECTs
The state of a certain timestamp can be selected without any reference to the SYSTEM_START or SYSTEM_END columns. In this example the state of employee 001000 with EDLEVEL = 13 was automatically selected from the EMPL_HIST table as shown in Picture 4.
Similarly, a whole period can be selected if i.e. December needs to be examined:
SELECT *
FROM EMPL FOR SYSTEM_TIME FROM '2022-12-01' TO '2022-12-31'
Picture 5: Result of the period selection
Db2 offers much more in this area for example there are application-period temporal tables which could also be combined with system-period temporal tables as bi-temporal tables.
So far so good – much fun trying this great functionality 😊
Summary:
System-period temporal tables are a very useful functionality to track changes of tables including the timing and without changes to the application. It is not considered monitoring as there are other methods for that.
Hint:
If someone is querying the table with “SELECT *” type SQLs the extra columns could be hidden by using the “implicitly hidden” clause in the CREATE TABLE statement. These will be returned if they get selected explicitly but will not get returned in a “SELECT *” kind of query.
We have used this technology also in data warehouse environments as historization is always a point there.