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.

 

 

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: 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.

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.

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

Da hierbei keine bereits vorhandenen Daten geändert oder gelöscht werden, bleibt die History Table unberührt:

SELECT name, price AS kurs, quantity AS anzahl, rowBegin, rowEnd, TransActId, dtaChgOp AS
dco, sessionUsr AS nutzer
FROM portfolio_hist;

UPDATE

Per Update-Operation wird der Kurs des Bitcoins am 12.01.2017 auf den aktuellen Wert gesetzt:

UPDATE portfolio SET price = 774.30 WHERE name = 'Bitcoin';
SELECT name, price AS kurs, quantity AS anzahl, rowBegin, rowEnd, TransActId, dtaChgOp AS
dco, sessionUsr AS nutzer FROM portfolio;

Der ursprüngliche Datensatz wird vor dem Update auf die Temporal Table in der History Table gesichert:

SELECT name, price AS kurs, quantity AS anzahl, rowBegin, rowEnd, TransActId, dtaChgOp AS
dco, sessionUsr AS nutzer
FROM portfolio_hist;

• ROWEND des historisierten Eintrags in der History Table wird auf den ROWBEGIN-Wert des aktualisierten
  Eintrags in der Temporal Table gesetzt, da durch das Update die Gültigkeit des alten Datensatzes endet.

DELETE

Nun wird der Eintrag per Delete-Operation aus der Temporal Table gelöscht; fiktiv könnte das einem vollständigen
Verkauf des Bitcoins entsprechen:

DELETE FROM portfolio WHERE name = 'Bitcoin';
SELECT name, price AS kurs, quantity AS anzahl, rowBegin, rowEnd, TransActId, dtaChgOp AS
dco, sessionUsr AS nutzer FROM portfolio;

Der ursprüngliche Datensatz wird vor dem Delete auf die Temporal Table in der History Table gesichert.

Da ‚ON DELETE ADD EXTRA ROW‘ gesetzt wurde, wird zusätzlich die Löschoperation historisiert. Anhand dieses
Eintrags kann beispielsweise nachvollzogen werden, wer den Datensatz gelöscht hat:

SELECT name, price AS kurs, quantity AS anzahl, rowBegin, rowEnd, TransActId, dtaChgOp AS
dco, sessionUsr AS nutzer
FROM portfolio_hist;

• Die drei Zeitstempel bezüglich Gültigkeitszeitraum und Transaction Start ID sind beim Delete-Eintrag (Data
  Change Operation „D“) identisch.

SQL-Abfragen zu TS-Daten

Informationen im Datenbankkatalog

Ein Datenbankkatalog enthält Metadaten über das DBMS. Der Katalog der DB2 for i besteht aus den Tabellen und Views im QSYS2-Schema. Mit TS wurden zwei neue Views und einige bereits bestehende um zusätzliche Informationen ergänzt.

Neu hinzugekommen sind die Views SYSPERIODS und SYSHISTORYTABLES:

• QSYS2/SYSPERIODS
  • enthält eine Zeile für jede System-Period Temporal Table mit zugehörigen Temporal- und Versionierungsinformationen, insbesondere…
    • HISTORY_TABLE_NAME der verbundenen History Table
    • ob ON_DELETE_ADD_EXTRA_ROW definiert wurde
    • die Namen der obligatorischen TS-Felder
• QSYS2/SYSHISTORYTABLES
  • enthält eine Zeile für jede History Table, insbesondere mit den Informationen…
    • TABLE_NAME der verbundenen Temporal Table
    • VERSIONING_STATUS (E: Established, D: Defined, but not established)

Zu den wesentlichen, um TS-Informationen erweiterten Views, gehören SYSTABLES und SYSCOLUMNS:

• QSYS2/SYSTABLES
  • neue Spalte TEMPORAL_TYPE gibt Auskunft über den Typ der temporalen Tabelle:
    • H: History Table
    • N: weder Temporal Table noch History Table
    • S: System-Period Temporal Table
• QSYS2/SYSCOLUMNS
  • vorhandene Spalte HAS_DEFAULT erweitert um Werte bezüglich des Typs der systemgenerierten Spalte:
    • Q: Spalte mit GENERATED AS ROW BEGIN
    • R: Spalte mit GENERATED AS ROW END
    • X: Spalte mit GENERATED AS TRANSACTION START ID
    • a: Spalte enthält Wert eines Special Registers
    • c: Spalte enthält Wert einer Global Variable
    • d: Spalte mit Wert der DATA CHANGE OPERATION

In Zeile 28 der Abbildung 4 stößt der CL-Befehl auf die Längenbeschränkung des Dateinamens auf system­intern 10 Zeichen. Der per SQL generierte Tabellenname PORTFOLIO_HIST wird intern gekürzt und mit fortlaufender Nummer versehen. – Der genaue Name (PORTF00001) sowie die zuvor genannten TS-Informationen lassen sich unter anderem wie in Abbildung 5 dargestellt ermitteln.

Abfragen am Beispiel

Die Temporal Table PORTFOLIO stellt sich nun folgendermaßen dar:

SELECT name, price AS kurs, quantity AS anzahl, rowBegin, rowEnd, TransActId, dtaChgOp AS dco, sessionUsr AS nutzer FROM portfolio;

Temporale Abfragen sind für einen bestimmten Zeitpunkt oder für einen bestimmten Zeitraum möglich.

Das DBMS prüft dabei im Hintergrund, ob aktuelle Daten aus der Temporal Table geliefert werden können, oder ob auf gesicherte Datensätze in der History Table zurückgegriffen werden muss. Die neu eingeführten SQL-Statements wurden bereits in Teil I der Artikelserie vorgestellt.

Da im Portfolio eingetragene Kurse regelmäßig vom Benutzer ‚Updater‘ aktualisiert werden, kann der ­Bitcoin-Preis zum gewählten Datum abgefragt werden:

SELECT name, price AS kurs, rowBegin AS datum FROM portfolio
FOR SYSTEM TIME AS OF ‚2017-03-02‘;

Folgende Abfrage führt einen Vergleich des Portfoliowertes zwischen März und August 2017 durch – in Abhängigkeit von Kurs und Anzahl:

SELECT (mar.price * mar.quantity) AS wert_maerz, (aug.price * aug.quantity) AS wert_august
FROM portfolio FOR SYSTEM_TIME AS OF '2017-03-02' mar, portfolio FOR SYSTEM_TIME AS OF '2017-08-31' aug
WHERE mar.name = 'Bitcoin' AND aug.name = 'Bitcoin';

Tipp: CCSID-Problematik beheben

Falls SQL-Abfragen nur Zeichensalat liefern, liegt das häufig an einem vom Betreiber nicht korrekt gesetzten CCSID (Coded Character Set Identifier); die Systeme werden standardmäßig mit dem Wert 65535 ausgeliefert. In Run SQL Scripts lässt sich die Übersetzung der ASCII-Zeichen über Connection => JDBC Configurations… => <Konfiguration> => Bearbeiten => Translation => Translate CCSID 65535 aktivieren (siehe Abbildung 6).

 

Herausforderungen des TS-Einsatzes

Die erste Hürde, die es zu überwinden gilt, ist die Aktualisierung des Betriebssystems. Temporal Support ist erst ab DB2 for i 7.3 verfügbar. Die größere ­Herausforderung stellen aber DDS-basierte ­Anwendungen dar, die nach dem Hinzufügen der für Temporal Support notwendigen Felder nicht mehr funktionieren.

Problematisch ist hierbei der Record Format Level Identifier (Formatebenen-ID). Dabei handelt es sich um einen 13 Byte großen String, der beim Kompilieren einer physischen Datei, einer logischen Datei, eines Display Files oder eines Printer Files wie ein Hashwert (Fingerabdruck) dieser Datei generiert wird.

Ein Programm, das diese Datei nutzt, muss die korrekte Formatebenen-ID kennen. Durch Anfügen der TS-Felder ändert sich diese ID allerdings, wodurch das Programm mit einem Level-Check-Fehler abbricht.
Die betroffene Anwendung neu zu kompilieren, sodass sie die veränderte ID kennt, würde zwar helfen, ist aber nur möglich, wenn dafür alle Sourcen vorhanden sind.

Ü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