Nachdem im Blogartikel „Tabellenänderungen nachvollziehen mit Db2 Systemtemporalen Tabellen“ die Grundlagen der Historisierung gelegt worden sind, soll es in diesem Beitrag über die sogenannte Business Perioden gehen. Db2 stellt dazu „Temporale Tabellen für Anwendungszeitraum“ zur Verfügung. Da sich das Deutsch wieder sehr sperrig anhört, verwenden wir hier einfach den englischen Begriff „Application-period temporal tables“ oder Tabellen mit Business Time.
Time Travel SQL ist eine Möglichkeit Db2 Temporale Tabellen abzufragen und mittels zeitlichem UPDATE oder DELETE Operationen zu ändern. Dazu später mehr.
Application-Period Temporal Tables erlauben es, dass die Anwendung – oder der User – individuelle Start- und Endzeiten angibt. Diese werden also nicht – wie bei Systemtemporalen Tabellen – automatisch von Db2 erzeugt. Damit können auch beliebige Zeiten in der Vergangenheit oder der Zukunft angegeben werden. Das ist nützlich, wenn zum Beispiel Ereignisse vorab erfasst werden sollen, wie z.B. eine Konfigurationsanpassung zum 01.01. des nächsten Jahres. Solche Historisierungen werden oft auch bei Anwendungen benötigt, um Versicherungszustände, Fälligkeiten oder ähnliches abzubilden.
Den kompletten Artikel inklusive aller kopierbaren SQL-Statements finden Sie hier zum kostenfreien Download.
Starten wir wieder mit einem Beispiel:
Auch hier werden zwei NOT NULL Spalten, die beliebig benannt werden können, in einer PERIOD Klausel als BUSINESS_TIME definiert. Diese Spalten können im Gegensatz zur SYSTEM_TIME eine beliebige Timestamp-Präzision haben oder sogar von Datentyp Date sein. Eine History-Tabelle ist bei reinen Business Time Temporalen Tabellen ebenfalls nicht nötig. Dies bedeutet, alle Historiensätze – inklusive des aktuellen – befinden sich in der Basistabelle.
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 „A“ steht dabei für Application Temporal Table. Alternativ stehen weitere Details dazu in der SYSCAT.PERIODS.
Wir starten also mit einer Application Temporalen Tabelle und folgendem Inhalt:
Wie man mit dem Satz des Mitarbeiters ‘030000’ sieht konnte ein zukünftiger Satz eingetragen werden.
Hinweis:
Db2 prüft in dieser Technik selbstständig, dass der End-Zeitpunkt größer ist als der Beginn-Zeitpunkt, es können hier aber zeitliche Lücken und Überlappungen der Perioden entstehen – was bei Systemtemporalen Objekten nicht möglich war.
Tipp:
Eine zeitliche Überlappung von Perioden kann in Db2 verhindert werden, indem man einen Primärschlüssel (oder UNIQUE Constraint) mit der Option BUSINESS_TIME WITHOUT OVERLAPS spezifiziert. Das obige CREATE würde also um folgende Zeile erweitert werden:
Damit kann man nun auch testen, dass Db2 auf zeitliche Überlappung prüft:
Dies resultiert, wie zu erwarten, in folgendem – sprechenden – Fehler:
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by “1” constrains
table “DB2ADMIN.EMPL_BUSINESS” from having duplicate values for the index key.
SQLSTATE=23505
Beim temporalen SQL – dem TIME TRAVEL SQL – ,der Teil des SQL Standards SQL_2011 ist, wird es nun richtig spannend.
Temporale Abfragen
Die Tabelle kann man natürlich mit jeglichem SELECT Statement ganz herkömmlich abfragen, aber eben auch per temporalem SQL. Das Ganze gleicht sehr den SELECTs, die auch für die systemtemporalen Tabellen verwendet werden können.
Zeiträume lassen sich folgendermaßen abfragen:
So werden alle Sätze zurückgegeben, die eine Gültigkeit zum angegebenen Zeitpunkt oder im angegebenen Zeitraum haben. Es qualifizieren sich also auch Datensätze, die vorher begonnen und später aufgehört haben – wie man sehen kann. Es sind dazu auch keine separaten WHERE-Bedingungen nötig, um die BUSINESS_TIME einzugrenzen.
Auch Änderungen können in temporaler Art und Weise gemacht werden.
Der temporale Update
Der Mitarbeiter 020000 soll im 2. Quartal 2023 and das Department E11 ausgeliehen werden und so dem WORKDEPT = E11 zugeordnet werden. Dazu wird neben den Komponenten eines normalen (nicht-temporalen) Updates die Klausel FOR PORTION OF BUSINESS_TIME benötigt – wie im folgenden Statement gezeigt:
Was ist Ihre Erwartungshaltung bezogen auf die Daten?
Der Mitarbeiter 020000 sollte ab 1.1.2023 existieren und der Abteilung D11 zugeordnet sein. Ab 1.4.2023 sollte er für drei Monate in der Abteilung E11 sein. Was gilt danach? Danach ist er bis zum Jahre 9999 (bzw. bis auf Weiteres) wieder dem Workdept = D11 zugeordnet.
Somit sollten wir, nach dem Update, statt einem drei Sätze von diesem Mitarbeiter haben – und so ist es auch:
Diese korrekte Verschneidung und Vervielfältigung der Daten ist die „Macht“ temporalen SQLs. Die Inserts etc. wurden implizit durch Db2 durchgeführt.
Klassische Updates funktionieren selbstverständlich weiterhin.
Hinweis:
Ein temporaler Update wird eine vorhandene Zeitperiode nie verlängern oder verkürzen – er ändert sie nur. Im konkreten Beispiel bedeutet das, dass der Zeitpunkt 01.01.2023 von keinem temporalen Update verändert werden kann.
Schematisch lässt sich dies folgendermaßen skizzieren:
Dabei stellt der graue Pfeil die Zeit dar und der grüne Balken die gültige Periode eines vorhandenen Datensatzes. Der rote Peil zeigt die Periode an, über die der temporale Update erfolgt. Der untere Pfeil zeigt dann das Ergebnis mit dem geänderten dunkelblauen Bereich.
Hinweis:
Die drei Zeilen – in Abbildung 8 Im Kasten unten links – entstehen durch den Update des ursprünglichen Datensatzes und zwei Inserts (I) wie im Bild gezeigt.
Das temporale Delete
Ist der temporale Update verstanden, hat man es beim temporalen Delete nicht mehr schwer, denn er funktioniert konzeptionell identisch.
In Abbildung 4 bedeutet der weiße Bereich die gelöschte Periode. Auch hier gilt wieder, dass nur Zeiträume gelöscht werden können, die vorhanden sind – eigentlich klar, aber manchmal muss man sich dies doch noch mal verdeutlichen, wenn man sich in komplexen Konstellationen zurechtfinden will.
Der zeitliche Delete sieht dann beispielsweise so aus:
Die Klausel FOR PORTION OF BUSINESS_TIME entspricht der des Updates und wird direkt nach dem Tabellennamen spezifiziert, so dass ein Correlation-Name für die Tabelle ebenfalls hinter dieser Klausel stehen muss.
Hinweis:
Will man mehrfach dieselbe Business_Time referenzieren, kann man auch ein Special-Register verwenden.
Diese kann man zum Deaktivieren auch auf NULL setzen.
Der Partner an Ihrer Seite
Das sollte als erster Einstieg in die Welt von Application Period Temproal Tables ausreichen, um erste eigene Tests zu machen. Db2 Funktionalitäten zu nutzen, statt sich selbst in einer Anwendung um die Historisierung kümmern zu müssen, sollte verlockend sein, da einem viel Komplexität abgenommen wird und man zusätzlich Time Travel SQL Funktionalitäten bekommt.
Wir setzen diese Technologie seit einigen Jahren ein – auch in Data Warehouse Umgebungen – und helfen Ihnen gerne, auch Ihr Projekt umzusetzen – fragen sie uns. Schicken Sie mir dazu bitte einfach eine Mail an kontakt@mip.de und ich komme zeitnah auf Sie zu. Ich freue mich auf Ihre Anfrage.
Ihr Michael Tiefenbacher
Principal Consultant
