Temporal Support in DB2 for i – Teil II: Einrichtung und Nutzung

26. September 2017 | Von | Kategorie: Strategische Berichte

Besonders die englischsprachige Literatur über moderne Datenbank-Management-Systeme (DBMS), die der Zeit nun endlich den gefürchteten Zahn ziehen, bedient sich oftmals des Begriffs „Temporal Support“ (TS). Hin und wieder trifft man auch auf die Abwandlung „Temporal Table Support“. Diese meint zwar dasselbe Feature und beschreibt es eigentlich sogar genauer, sorgt hierzulande aber häufig für temporäre Verwirrung …

Die Zeitreise beginnt…

von Patrick Arnold

Bei Temporal Tables handelt es sich nicht um Temporary Tables! – Der Name lässt es ja eigentlich vermuten: die letztgenannte temporäre Tabelle existiert nur in der QTEMP eines Jobs und ist damit recht volatil. Eine temporale Tabelle hingegen ist permanent in ihrem Schema vorhanden und enthält aktuelle Daten sowie TS-spezifische Eigenschaften. Was genau diesen Tabellentyp ausmacht, wie temporale Datenhaltung prinzipiell funktioniert, welche Motive den Hersteller dazu bewegen, das – bereits 2011 in den SQL-Standard übernommene – Feature endlich zu implementieren und welche Besonderheiten die DB2 for i hierbei bietet, haben wir uns im ersten Teil der Artikelserie (Gestern, heute, morgen… : Temporal Support in DB2 for i – Teil I: Grundlagen) angeschaut.

Teil II demonstriert nun sowohl die Einrichtung des Temporal Supports anhand eines eingängigen Beispiels als auch die Nutzung neuer SQL-Statements für zeitbasierte Abfragen. Als systemseitige Versionierung betroffener Datensätze vor Update- oder Delete-Operationen bietet TS Vorteile und vielfältige Einsatzmöglichkeiten, schafft aber auch eine Reihe neuer Herausforderungen, die es vor dem Einsatz im Produktivbetrieb zu meistern gilt. Welche das sind, verrät ­Ihnen der Artikel am Schluss.

Einrichtung der temporalen Datenhaltung

Temporal Support lässt sich natürlich ganz klassisch per CL und SQL im Green Screen einrichten. IBM forciert aber die Nutzung moderner Werkzeuge und stellt mit IBM i Access Client Solutions (ACS) eine mächtige Toolbox zur Verfügung, mit deren Hilfe sich die notwendigen Schritte sehr komfortabel gestalten.

Exkurs: IBM i Access Client Solutions

ACS basiert auf Java und ist damit weitestgehend plattformunabhängig; dem Einsatz unter Linux, Mac und Windows steht somit nichts im Wege. Als Nachfolger des erfolgreichen IBM i Access for Windows („Client Access“) löst es dieses sukzessive ab. Gerade wenn der betagte Windows  7-Rechner gegen ein aktuelles Modell mit Windows 10 ausgetauscht wird, stößt man auf das Problem, dass Client Access zwar irgendwie funktioniert, IBM aber keinen Support für den Einsatz mit dem neuen Betriebssystem gewährt. Fehlfunktionen sind möglich, lassen sich aber nur schwer auf eine eventuelle Inkompatibilität zurückführen. Insbesondere hinsichtlich der Verbindung zur Datenbank über Schnittstellen wie ODBC, die einer Treiberinstallation bedürfen, sollte man besser auf das, speziell für die neueste OS-Version entwickelte, optionale ACS Windows Application Package vertrauen.

 

Abb. 1: IBM i Access Client Solutions

 

Abb. 2: ACS – Run SQL Scripts unterstützt auch CL-Befehle

Das IBM Knowledge Center liefert dazu hilfreiche Informationen unter:

https://www.ibm.com/support/knowledgecenter/de/ssw_ibm_i_73/rzahg/rzahgiciaw.htm

Die Software stellt IBM hier zur Verfügung:

https://www-03.ibm.com/systems/power/software/i/access/solutions.html

Abbildung 1 zeigt die Willkommensseite der Toolbox mit den ausführbaren Werkzeugen in übersichtlicher Darstellung. Der 5250-Emulator unterstützt mehrere parallele Sessions, die Datenübertragung – zusätzlich zum bekannten Feature des Client Access – weitere Dateiformate, wie *.ods und *.xlsx, sowie eine Integration in OpenOffice und Microsoft Excel. Über „Integrated File System“ lassen sich Objekte im IFS, wie von gängigen Dateimanagern gewohnt, finden und verwalten. Das „SQL Performance Center“ ermöglicht die komfortable Analyse und Optimierung der, in Anwendungen verwendeten, SQL-Statements.

Temporal Support am Beispiel

Die exemplarische Einrichtung des Temporal Supports findet im Folgenden anhand des Run SQL Scripts statt, das zusätzlich Control Language (CL)-Befehle ausführen kann (siehe Abbildung 2).

Das Tool hilft dem Benutzer mit einer Reihe von Codebeispielen, die aus verschiedenen Kategorien ausgewählt oder – wie in Abbildung 3 dargestellt – per Eingabe ins Suchfeld gefunden werden können. Die aufgelisteten Beispiele sind über die markierte Schaltfläche in der Toolbar oder über das Menü Bearbeiten => Insert from Examples… erreichbar.
Das in Abbildung 3 angebotene SQL-Codebeispiel fügt einer bereits vorhandenen Tabelle die für die Historisierung ­notwendigen Spalten per ALTER TABLE-Statement hinzu. Weiterhin erzeugt es die History Table und macht sie dem DBMS als solche bekannt. Es fällt auch auf, dass nach jedem SQL-Statement ein Semikolon gesetzt wird. Interaktive SQL-Sitzungen (STRSQL) per Terminal­emulator sehen zwar über das Fehlen des Strichpunkts hinweg, jedoch sollte dieser gesetzt werden, da nur dadurch einzelne SQL-Statements voneinander getrennt werden. Die für Tests nützliche Funktion in Run SQL Scripts, nur markierte Abschnitte auszuführen (Menü Run => Selected), stoppt beispielsweise erst beim nächsten Semikolon.

Abb. 3: Codebeispiele in Run SQL Scripts:

Abb. 3: Codebeispiele in Run SQL Scripts: Wie bereits aus Teil I der Artikelserie bekannt, basiert die systemseitige Historisierung per TS darauf, dass eine Tabelle zur Temporal Table wird, indem ihr weitere Spalten angefügt werden. Diese beinhalten Start- und Ende-Werte bezüglich des Gültigkeitszeitraums des jeweiligen Datensatzes sowie ein Feld mit der Transaktionszeit. Sobald eine Änderung durch eine Update- oder Delete-Operation erfolgt, verliert der betroffene Satz seine Gültigkeit und wird mit den bisherigen Werten in der – identisch mit der Temporal Table aufgebauten – History Table gesichert. Lediglich das bis dahin offene Gültigkeitsende-Feld wird mit dem Timestamp des Änderungszeitpunkts versehen.

Einrichtung in vier Schritten

Die Einrichtung des TS erfolgt in folgenden vier Schritten:

  1. Erstellen der Temporal Table
  2. Erstellen der zugehörigen History Table
  3. Systemseitigen Historisierung durch Verlinken der History Table aktivieren
  4. Journalisieren der Temporal Table und der History Table

1. Erstellen der Temporal Table

Um eine Tabelle mit TS-spezifischen Feldern neu zu erstellen oder zu erweitern, bietet sich insbesondere das CREATE OR REPLACE TABLE-Statement an. Dabei wird eine möglicherweise bereits existierende Tabellendefinition gegen die neue ersetzt, vorhandene Daten bleiben aber standardmäßig erhalten. Die in Abbildung 4 erzeugte Beispieltabelle PORTFOLIO soll verschiedene Vermögenswerte enthalten können und beschränkt sich hier auf die Felder NAME (Bezeichnung), PRICE (Kurs) und QUANTITY (Anzahl). Die für TS benötigten Felder werden ab Zeile 5 definiert, weitere optionale GENERATED ALWAYS AS-Felder ab Zeile 9 sowie in Zeile 13 der Gültigkeitszeitraum als Zeitspanne zwischen ‚rowBegin‘ und ‚rowEnd‘. Weitere Informationen zu diesen automatisch generierten Werten liefern Tabelle 1 und das SQL-Referenzhandbuch. Aufgrund des gesetzten IMPLICITLY HIDDEN-Attributs werden die TS-Felder nur ausgegeben, falls diese explizit im SELECT-Statement aufgeführt werden (Zeile 30 und Ausgabe darunter); ein unqualifiziertes SELECT *-Statement liefert dagegen nur die ersten drei Spalten, die in Zeile 3 definiert wurden.

Abb. 4: Vier Schritte der TS-Einrichtung

2. Erstellen der History Table

In Zeile 16 der Abbildung 4 wird die zur Temporal Table PORTFOLIO zugehörige History Table PORTFOLIO_HIST erzeugt. Da in der History Table alte Datensätze der Temporal Table abgelegt werden, müssen die Tabellendefinitionen absolut identisch sein. Erreicht wird dies durch die LIKE-Klausel – mit einer wichtigen Ausnahme:
Für TS erforderliche und optionale Spalten der Temporal Table, die systemgenerierte Werte enthalten („GENERATED ALWAYS AS“), werden zwar mit ihrem Datentyp übernommen, gelten in der History Table aber nicht mehr als generierte Spalten. Andernfalls würde das temporale Datenbank-Management-System nicht die ursprünglichen Zeitstempel, sondern die zum Zeitpunkt der Historisierung aktuellen Werte, eintragen. Die HAS_DEFAULT-Felder in Abbildung 5 veranschaulichen diese Eigenschaft zusätzlich.

3. Versionierung aktivieren

Im dritten Schritt wird die systemseitige Versionierung der Temporal Table durch ein ALTER TABLE-Statement aktiviert, das die zu verwendende History Table benennt (Abbildung 4, Zeile 19). Damit auch gelöschte Datensätze gesichert werden, muss die Versionierung mit „ON DELETE ADD EXTRA ROW“ definiert werden.

4. Journalisierung starten

Temporal Support basiert auf der Journalisierung, die sowohl für die Temporal Table als auch für die History Table aktiviert werden muss. Dies erfolgt im Beispiel per CL, das von Run SQL Scripts unterstützt wird. Zum Ausführen muss lediglich „CL:“ vorangestellt und der Befehl mit einem Semikolon abgeschlossen werden (Abbildung 4, Zeile 23 ff).

Besonderheit: Transaction Start ID

Das Feld „Transaction Start ID“ enthält den Zeitstempel der ersten Datenänderung innerhalb einer Transaktion; werden nacheinander mehrere Datensätze geändert, tragen alle dieselbe Transaction Start ID. Der Timestamp wird allerdings nur dann eingetragen, wenn die Spalte als NOT NULL definiert wurde, oder wenn der Wert vom Gültigkeitsbeginn-Feld abweicht. Letzteres kann sich durch konkurrierende Transaktionen ergeben. Im ungünstigsten Fall liegt der historisierte Wert des Gültigkeitsende-Datums vor dem Wert des Gültigkeitsbeginns. Das DBMS korrigiert diese Werte bei entsprechender Konfiguration, der Gültigkeitsbeginn entspricht dann aber nicht mehr der Transaction Start ID. Weitere Informationen dazu liefert die IBM SQL-Referenz unter dem Begriff „System-period temporal table timestamp value conflicts“.

Weitere systemgenerierte Spalten

Zusätzlich zu den drei obligatorischen Feldern bezüglich der Gültigkeit und des Transaktionszeitpunktes können weitere systemgenerierte Spalten genutzt werden, um beispielsweise besser nachvollziehen zu können, von welchem Benutzer Änderungen durchgeführt wurden und welcher Art diese waren. Im Beispiel werden der Session User (SESSION_USER) und die zugehörige IP-Adresse (SYSIBM.CLIENT_IPADDR) protokolliert. Die DB2 for i unterstützt für diese Audit-Zwecke 11 globale Variablen und 8 Spezialregister sowie ein Feld für den Typ der Änderungsoperation (DATA CHANGE OPERATION – I: Insert, U: Update oder D: Delete). Da die bei der Historisierung anfallende Datenmenge auch vom Datentyp der jeweiligen Felder abhängt, sollte vorab überlegt werden, welche Informationen benötigt werden.

Tab. 1: Weitere systemgenerierte Felder

Verhalten bei Inserts, Updates und Deletes

INSERT

Im Beispiel wird der Temporal Table PORTFOLIO per Insert-Operation ein erster Datensatz eingefügt; fiktiv wird
am 05.01.2017 ein Bitcoin gekauft und dem Portfolio hinzugefügt:

INSERT INTO portfolio(name, price, quantity)
VALUES('Bitcoin', 1037.18, 1.0000);
SELECT name, price AS kurs, quantity AS anzahl, rowBegin, rowEnd, TransActId, dtaChgOp AS
dco, sessionUsr AS nutzer FROM portfolio;

  • ROWBEGIN als Startwert des Gültigkeitszeitraums des Datensatzes enthält den TIMESTAMP(12) des Insert-
    Zeitpunkts
  • ROWEND als Endwert des Gültigkeitszeitraums ist derzeit noch unbekannt und wird deshalb auf den maximal
    möglichen Wert gesetzt
  • TRANSACTID entspricht ROWBEGIN
  • DCO (Data Change Operation) – I: Insert
  • Nutzer: der Session_User, der den Datensatz eingefügt hat

Über den Autor

Patrick Arnold hat in Darmstadt Informatik studiert und als IT-/Netzwerktechniker gearbeitet. Die dabei erlernten Fähigkeiten setzt er nun bei der All for One Steeb AG im Fachbereich DCW ein, um neue Technologien, wie Node.js und Temporal Support, und deren Nutzen für die Stakeholder zu erkunden. Dabei legt er Wert auf Verständlichkeit; allzu trockene Inhalte lösen bestenfalls Schlafprobleme, die bei seiner Affinität zu gutem Kaffee doch aber gewollt sind. Als Kind der 80er kennt er zwar die besten Zeitreisefilme, hatte im allgemeinen Informatikstudium und als Android-Nutzer aber kaum Berührungspunkte zur „i-Welt“. Dank Vorlesung und Bachelorarbeit bei Manfred Sielhorst hat sich das aber geändert. Über Feedback und Fragen freut sich der Autor immer unter: patrick.arnold (äät) all-for-one.com.

Ausblick auf Teil III

Teil III der Artikelserie befasst sich mit Lösungsideen zu den genannten Herausforderungen und beleuchtet, welche Fallstricke dabei zu beachten sind. Ein weiteres interessantes und recht neues Feature der DB2 for i ist „Row and Column Access Control“ (RCAC, ab Version 7.2). Ob und wie sich das zusammen mit TS für die Umsetzung einiger Anforderungen der europäischen Datenschutzgrundverordnung (EU-DS-GVO) und des neuen Bundesdatenschutzgesetzes (BDSG-neu) nutzen lässt, wird ebenfalls behandelt.

Quellen

Forstie, Scott: Time travel with DB2 for i – Temporal tables on IBM i 7.3. COMMON Europe Congress Stockholm, Juni 2016.
Hauser, Birgitta: Temporal Tables – Zeitreise mit SQL. POW3R Köln, 28.06.2017.
IBM: IBM i 7.3 Database : DB2 for i SQL reference. https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/db2/rbafzpdf.pdf.
t

Schlagworte: , , , , , , , , , , , , , , ,

Schreibe einen Kommentar

Sie müssen eingeloggt sein, um einen Kommentar schreiben.