Make Table Changes trackable by using Db2 System-Period Temporal Tables
This article is also available in English on the IBM Data Management Community blog. Please click here
Oft wäre es hilfreich, nachvollziehen zu können, wann sich ein Tabelleneintrag wie geändert hat. So will man häufig bei Konfigurationstabellen sehen, seit wann welcher Wert gültig ist und was vorher galt.
In Db2 gibt es dafür die perfekte Lösung: System Temporal Tables (was mit „Temporale Tabellen für Systemzeitraum“ in Deutsch übersetzt wird) – nicht zu verwechseln mit Temporären Tabellen (wie DGTT oder CGTTs). Dabei übernimmt Db2 automatisch die ganze Historisierung, ohne dass die Anwendung angepasst werden muss – einer der gewaltigen Vorteile.
Technisch bestehen die System Temporal Tables aus zwei Tabellen – eine mit den aktuellen Daten und eine Tabelle mit den historischen Daten. Das Gute ist, dass sie nach außen, also zum SQL, wie ein Objekt auftreten und mit temporalen SQL – sogenanntem Time Travel SQL – selektiert werden können, als wäre es ein Objekt. Diese SQL-Erweiterung ist – was viele nicht vermuten – Teil des SQL-Standards (SQL:2011).
Am Beispiel lässt sich das einfacher verdeutlichen – gehen wir also in die Praxis:
Drei zusätzliche Spalten sind in der Tabelle erfordert, die Zeitinformationen enthalten, um die Historisierung abzubilden. Da diese Timestamp (12) Spalten „generated always“ zu definieren sind, werden sie von Db2 automatisch befüllt.
system_start: Beginn des Gültigkeitszeitraums
system_end: Ende des Gültigkeitszeitraums
trans_start: Startzeitpunkt der Transaction – wird nur für die Bereinigung von Sonderfällen benötigt
Die zusätzliche PERIOD Klausel definiert, welche Spalten für die Systemhistorisierung herangezogen werden sollen, da die Spaltennamen frei gewählt werden können.
Um die historischen Sätze verwalten zu können, wird eine Historien-Tabelle benötigt, die prinzipiell dieselbe Struktur hat, wie die Ausgangstabelle und z.B. so angelegt werden kann:
Erzeugt einen strukturellen Klon der Ausgangstabelle. Diese dient dazu alle abgeschlossenen Zeitintervalle – also die Historie – aufzunehmen.
Tipp:
Da es sich um ein separates Objekt handelt, können hier auch spezifische Einstellungen und Berechtigungen gesetzt werden. So kann man User auf die Basistabelle für Insert, Update und Delete berechtigen, aber nur das SELECT-Recht auf die EMPL_HIST geben, um eine Manipulation der Historie zu verhindern. Der DB-Admin kann auch die Option „append on“ im CREATE mitgeben, da in diese Tabelle nur eingefügt wird, um diese Einfüge-Operation zu optimieren, denn eine Freiplatzsuche wird hier nicht benötigt.
Der letzte Schritt ist die Verbindung der beiden Objekte zu einem logischen Objekt via:
Tipp:
Wer prüfen will, ob solche Tabellen bereits in der Datenbank vorhanden sind, kann dies über die Spalte TEMPORALTYPE in SCSCAT.TABLES abfragen, ein „S“ steht dabei für System Temporal. Alternativ stehen weitere Details dazu in der SYSCAT.PERIODS.
Damit ist das DDL fertig und die Nutzung kann ausprobiert werden.
Einige beispielhafte INSERTs:
Das ergibt also drei Zeilen in der Tabelle EMPL.
Interpretiert wird dies nun folgendermaßen: Drei Mitarbeiter wurden am 01.12.2022 angelegt mit ihren jeweiligen – aktuell noch gültigen – EDLEVEL. Die zugehörige EMPL_HIST ist zu diesem Zeitpunkt noch leer, denn es hat ja keine Änderung (UPDATE oder DELETE) stattgefunden. Das ändert sich, wenn wir folgendes Update (zeitlich am 23.12.2022 um 11:00 Uhr) durchführen:
EMPL
EMPL_HIST
Das ist nun folgendermaßen zu verstehen:
Der Mitarbeiter ‘001000’ hat aktuell den EDLEVEL = 14 – genauer gesagt seit 23.12.2022 um 11.01 Uhr und dieser ist aktuell bis ans Ende aller Zeit gültig.
Die EMPL_HIST Tabelle zeigt, dass eben dieser Mitarbeiter vom 01.12.2022 bis 11:01 Uhr am 23.12.2022 den EDLEVEL 13 hatte. Auffällig ist, dass der SYSTEM_START Zeitpunkt des aktuellen Satzes und der SYSTEM_END des vorhergehenden Zustandes den exakt selben Zeitstempel haben.
Wichtig: Dies ist problemlos, denn in Db2 sind die Startzeitpunkte inklusiv zu sehen, während die Endzeitpunkte exklusiv sind. Die Eindeutigkeit ist somit gewährleistet.
Die EMPL_HIST wurde nicht im Update erfasst, sondern Db2 hat dies im Hintergrund selbstständig verarbeitet – ein weiterer großer Vorteil.
Jetzt wollen wir noch prüfen, was bei einem regulären Delete vor sich geht:
EMPL
EMPL_HIST
Wie zu erwarten, wurde der Datensatz des Mitarbeiters 001200 aus der Tabelle EMPL gelöscht (Abbildung 3).
In der EMPL_HIST taucht der Mitarbeiter hingegen neu auf, mit einer Gültigkeit vom 01.12.2022 08:00 Uhr bis 23.12.2022 11:15 Uhr, denn in diesem Zeitraum gab es diesen Mitarbeiter mit dem EDLEVEL = 10.
Diese Daten kann man nun auch mit sogenanntem Time Travel SQL abfragen. Das Besondere hierbei ist, dass man die einzelnen Tabellen nicht separat abfragen muss, sondern nur die EMPL Tabelle angeben muss – alles andere wiederum erledigt Db2 automatisch – noch einer der Vorteile.
Als Beispiel nehmen wir die folgende Fragestellung:
Welchen EDLEVEL hatten die Mitarbeiter des Departments D11 am 15.12.2022?
Der Zustand eines Zeitpunktes kann also ganz ohne Bedingungen auf SYSTEM_START und SYSTEM_END erreicht werden. Bei diesem Ergebnis wurde der Zustand mit EDLEVEL = 13 des Mitarbeiters 001000 automatisch aus der EMPL_HIST verwendet, wie Abbildung 4 zeigt.
Ebenso kann man auch einen Zeitraum abfragen – wenn z.B. der gesamte Dezember betrachtet werden soll:
Db2 bietet noch wesentlich mehr in diesem Bereich, so gibt es neben den System-Temporalen Tabellen auch Business-Temporal Tables und die Kombination von beiden Techniken, was dann als Bi-Temporale Tabelle bezeichnet wird. Das soll aber in separaten Blogbeiträgen behandelt werden.
Vorerst viel Spaß beim Testen 😊
Mein Fazit:
Systemtemporale Tabellen sind ein sehr nützliches Hilfsmittel, um Veränderungen in Tabellen zeitlich korrekt zu erfassen und das, OHNE Anpassungen in der Applikation vornehmen zu müssen. Es handelt sich aber nicht um Monitoring – dafür gibt es andere Möglichkeiten.
Noch ein Tipp: Falls jemand per „SELECT *“ auf die obigen Tabellen zugreift, kann man im CREATE TABLE die drei extra Spalten auch per „implicitly hidden“ Klausel verstecken. So können sie zwar explizit abgefragt werden, aber werden im „SELECT *“ nicht ausgegeben.
Den ganzen Artikel inklusive aller kopierbaren SQL-Statements finden Sie hier zum Download.
Wir haben diese Technologie auch in Data Warehouse Umgebungen im Einsatz, denn auch dort spielt die Historisierung eine zentrale Rolle. Dafür sind allerdings noch einige weitere Punkte zu bedenken.
Ich berate Sie gerne, falls auch Sie die Historisierung in Db2 Hände legen wollen. Bitte schicken Sie mir dazu einfach eine kurze Mail an kontakt@mip.de. Ich freue mich auf Ihre Anfrage.
Ihr Michael Tiefenbacher
Principal Consultant
