In diesem ersten Artikel der zweiteiligen Reihe werden die Grundkonzepte sowie die Verwendung von System Time dargestellt. In der nächsten Ausgaben werden dann die Benutzung von Business Time und die Kombination aus System- und Business Time gezeigt. 

- Gastbeitrag von Stefan Hummel, Certified IT Specialist IBM Deutschland -

Einführung und System Time

Durch die Zeit reisen – ein Traum so alt wie die Menschheit. Zumindest auf Datenebene kann dieser verwirklicht werden: Db2 (für Linux, Unix und Windows) verfügt ab Version 10 über eine Zeitmaschine, mit der sich Daten aus der Vergangenheit, Gegenwart und Zukunft betrachten lassen.

Das in der Db2 integrierte temporale Datenmanagement spart Zeit und Geld. Neue Funktionen zur Auswertung historischer Datensätze und zur Verfolgung zeitlicher Änderungen erleichtern Datenbankadministratoren und Anwendungsentwicklern die Arbeit erheblich. 

Statt eigenentwickelter Trigger oder komplexer Funktionen erleichtert Db2 solche Aufgaben mit Hilfe temporaler Tabellen sowie durch Abfragen und Semantiken auf der Grundlage von ANSI/ISO SQL:2011.

Anwendungsszenarien einer temporalen Datenhaltung

Temporale Datenhaltung ist in vielen verschiedenen Anwendungsszenarien nützlich: 

1. Für die interne Revision muss ein Kreditinstitut einen Bericht über die in den vergangenen fünf Jahren vorgenommenen Änderungen an den Unterlagen eines Kunden vorlegen. 

2. Im Rahmen eines anhängigen Rechtsstreits muss ein Krankenhaus seinen Kenntnisstand über den medizinischen Zustand eines Patienten vor der Umstellung auf eine neue Behandlung überprüfen. 

3. Ein Kunde legt Widerspruch gegen die Regulierungsentscheidung einer Versicherung im Zusammenhang mit einem Autounfall ein. Die Versicherung muss nun überprüfen, welche Versicherungsbedingungen zum Zeitpunkt des Unfalls Bestand hatten.

4. Ein Online-Reisebüro möchte, dass das System bestimmte logische Widersprüche bei Reisebuchungen erkennt. Falls also ein Kunde für acht Tage ein Hotel in Rom bucht und für drei dieser Tage einen Mietwagen in New York reserviert, möchte das Reisebüro benachrichtigt werden, um dies prüfen zu können. 

5. Ein Einzelhändler will sicherstellen, dass für jedes Produkt in seinem Sortiment innerhalb eines beliebigen Zeitraums höchstens ein einzelner Rabatt gewährt wird. 

6. Wegen eines Fehlers bei der Dateneingabe im Zusammenhang mit einem dreimonatigen Sonderzinssatz für die Kreditkarte eines Neukunden muss die Bank nun eine Korrektur vornehmen - und einen neuen Saldo berechnen, falls erforderlich. 

Bei jedem dieser Szenarien ist Zeit ein kritischer Faktor. Die Funktionen für temporale Datenhaltung in Db2 unterstützen mit relativ geringem Aufwand die Entwicklung zeitbezogener Anwendungen und Abfragen. Vor der Vertiefung dieser Fähigkeiten werden zunächst zeitbezogene Konzepte vorgestellt und dabei auch die Unterschiede zwischen System Time (Bearbeitungszeit) und Business Time (Gültigkeitszeit bzw. Anwendungszeit) erläutert.

Basiskonzepte

System Time ermöglicht das Nachverfolgen von Änderungen in einer Tabelle, etwa wenn eine Versicherungspolice geändert oder ein Kredit vergeben wird (Historisierung). Business Time ermöglicht die zeitlich korrekte Darstellung von Geschäftsvorfällen, beispielsweise die jeweils aktuellen Bedingungen einer Versicherungspolice oder der jeweilige Zinssatz eines Kredits. Bisweilen wird Business Time auch als Gültigkeits- oder Anwendungszeit bezeichnet. Sollen sowohl System Time wie auch Business Time dargestellt werden, müssen beide Arten temporaler Daten in einer Tabelle abgebildet sein. Solche Tabellen nennt man dann bitemporal.

Wie lassen sich diese Konzepte nun auf die oben beschriebenen Szenarien anwenden? Für die Szenarien 1 und 2 werden Informationen über die Bearbeitungszeit benötigt, um den historischen Zustand einer oder mehrerer Tabellen darstellen zu können. Für die Szenarien 3 bis 6 werden Informationen über die Gültigkeitszeit benötigt, um die Gültigkeitszeiträume verschiedener Geschäftsvorfälle darzustellen. Darüber hinaus wird für Szenario 6 möglicherweise auch die Bearbeitungszeit benötigt - das heißt bitemporale Daten - sofern die Bank den Dateneingabefehler rückwirkend korrigieren und gleichzeitig festhalten möchte, wann der Fehler korrigiert wurde.

Periodenkonzepte sind sowohl für die Gültigkeitszeit wie auch für die Bearbeitungszeit relevant. Eine Periode gibt den Startzeitpunkt und den Endzeitpunkt eines Zeitraumes an. Bei Db2 lassen sich die Start- und Endzeiten einer Periode mit zwei Spalten einer Tabelle festlegen. Die Unterstützung temporaler Daten für neue oder vorhandene Tabellen erfolgt mit Hilfe von Syntaxerweiterungen der Anweisungen CREATE TABLE und ALTER TABLE.

Vorteile temporaler Datenhaltung

Die in Db2 integrierten Funktionen für temporale Datenhaltung ermöglichen eine einfachere Anwendungsentwicklung und gewährleisten die konsistente Verarbeitung zeitbezogener Ereignisse für alle Anwendungen, die auf die Datenbank zugreifen – einschließlich kommerzieller Anwendungen. Mit Hilfe einfacher deklarativer Anweisungen kann man in Db2 eine automatische Historie der Änderungen an einer Datenbank anlegen oder die Termine bestimmter Geschäftsvorfälle verfolgen, ohne dass dafür eigene Trigger, Prozeduren oder Anwendungen entwickelt werden müssen. Das erleichtert den Unternehmen auch die Umstellung auf neue Compliance-Regeln. Ein konsistentes Konzept für temporale Datenhaltung ermöglicht darüber hinaus die Arbeit mit deutlich weniger komplexen Abfragen und eine effektivere Analyse zeitabhängiger Vorfälle.

Die temporale Unterstützung in Db2 reduzierte den Programmieraufwand gegenüber den beiden eigenentwickelten Implementierungen um über 90 Prozent. Allein die Darstellung grundlegender temporaler Funktionen mit Hilfe von Prozeduren in SQL oder Java erforderte 16 bzw. 45 Mal so viele Programmzeilen wie die einfachen SQL-Anweisungen, die auf die temporalen Funktionen von Db2 zurückgreifen (siehe Abbildung 1). Die Entwicklung und Prüfung dieser wenigen Db2/SQL-Anweisungen dauerte weniger als eine Stunde.

Die Entwicklung und Prüfung der eigenentwickelten Lösungen hingegen nahm vier bis fünf Wochen in Anspruch, und beide Alternativen boten dann nur einen Teil der temporalen Unterstützung, die Db2 zur Verfügung stellt. So fehlten bei den Eigenlösungen temporale Konzepte wie System Time, bitemporale Daten, Views und viele andere Db2-Funktionen. Die Eigenentwicklung eines vergleichbaren Umfangs an temporalen Funktionen würde also wahrscheinlich Monate in Anspruch nehmen. Das steht in keinem Verhältnis zu dem geringen Aufwand, der für die Formulierung entsprechender Db2/SQL-Anweisungen nötig ist!

Beispielszenario

Ein einfaches Beispiel mit Musterdaten zeigt, welche Möglichkeiten die Funktionen zur temporalen Datenhaltung in Db2 bieten. Im folgenden Beispiel geht es um Policen für Kfz-Versicherungen, die der Einfachheit halber in einer einzelnen Tabelle dargestellt sind. Die Tabelle enthält verschiedene Informationen, die in solchen Policen in der Regel erfasst sind: Versicherungsnummer (id), Fahrgestellnummer (vin), voraussichtliche jährliche Kilometerleistung (annual_mileage), Kostenübernahme für Leihwagen bei schadensbedingter Fahrzeugreparatur (rental_car) und die Deckungssumme (coverage_ amt), einschl. Sachschäden, Heilkosten usw. Die Abbildung 2 zeigt den Aufbau der Tabelle POLICY ohne temporale Unterstützung. Wie können die temporalen Funktionen von Db2 die Verwaltung solcher Versicherungspolicen erleichtern?

Datenverwaltung mit System Time

Das Konzept System Time in Db2 bietet die Möglichkeit, die zeitliche Entwicklung von Daten zu verfolgen und darzustellen. Wird eine Tabelle mit einem Bearbeitungszeitintervall (System Time Period) erzeugt, erfasst Db2 sämtliche Änderungen an dieser Tabelle automatisch und speichert „alte" Sätze in einer separaten History-Tabelle mit identischer Struktur. Werden zeitbezogene Abfragen​ an die Tabelle gerichtet, greift Db2 bei Bedarf transparent auf die automatisch gepflegte History-Tabelle zu. Diese Funktion ermöglicht den einfachen Umgang mit historischen Daten und macht komplexe Abfragen mit WHERE-Klauseln, diversen Zeitstempeln und JOIN-Bedingungen überflüssig.​

Definition einer Tabelle mit System Time

Das Definieren einer Tabelle mit System Time erfolgt in drei einfachen Schritten:

1. Basistabelle für aktuelle Daten definieren

Zusätzlich zu den eigentlichen Spalten einer Tabelle werden drei TIMESTAMP(12)-Spalten angelegt, zwei für die Start-/Endzeitpunkte der Bearbeitungszeit und eine für den Zeitpunkt des Transaktionsstarts. Db2 nutzt diese Spalte um festzustellen, wann die Transaktion erstmals eine Anweisung zur Änderung der Daten in der Tabelle ausführte. Die drei TIMESTAMP-Spalten können mit GENERATED ALWAYS definiert werden, damit Db2 die Werte bei jedem INSERT, UPDATE und DELETE automatisch erzeugt. Damit ist sichergestellt, dass die Werte stets in die Datenbank eingetragen werden und dass die Zeitstempel korrekt sind. Definiert man diese Spalten zusätzlich noch als IMPLICITLY HIDDEN, werden sie bei SELECT*-Anweisungen nicht angezeigt. 

2. History-Tabelle definieren

Erzeugen einer Tabelle mit identischer Struktur, wie sie die Tabelle mit den aktuellen Daten aufweist. Dies lässt sich einfach mit der Anweisung CREATE TABLE . . . LIKE bewerkstelligen. 

3. Versionierung für die Basis-Tabelle aktivieren

An einem Beispiel wird deutlich, wie einfach es mit Db2 ist, mit System Time automatisch verschiedene Versionen von Daten zu pflegen.

1. Schritt: Erzeugen einer Tabelle mit einer SYSTEM_TIMEPeriode - Definition der Tabelle POLICY

2. Schritt: Erzeugen einer zugehörigen History-Tabelle 

CREATE TABLE policy_history LIKE policy; 

3. Schritt: Versionierung aktivieren 

ALTER TABLE policy ADD VERSIONING USE 

HISTORY TABLE policy_history;

Die Abbildungen 3 und 4 zeigen das mit o.g. Anweisungen erzielte Ergebnis. 

Bereits vorhandene Tabellen können mit der Anweisung ALTER TABLE für die Verwaltung historischer Daten genutzt werden. Hierzu müssen die oben erwähnten Spalten sys_start und sys_end eingefügt und PERIOD SYSTEM_TIME definiert werden. 

Db2 unterstützt automatische Schemaänderungen für zugehörige History-Tabellen, sofern für die Basistabellen wie in Schritte 1-3 beschrieben die Versionierung aktiviert ist. Wird also eine Spalte in die Tabelle POLICY eingefügt, erweitert Db2 die Tabelle POLICY_HISTORY ebenfalls um die gleiche Spalte.

Dateneingabe in eine Tabelle mit System Time

Das Einfügen von Daten in eine Tabelle mit System Time unterscheidet sich nicht von der Dateneingabe in herkömmliche Tabellen. Beispielsweise müssen am 15. November 2015 zwei neue Datensätze für Kfz-Versicherungen in die Tabelle POLICY eingegeben werden. Das geschieht mit folgenden Anweisungen:​

INSERT INTO policy(id, vin, annual_mileage, rental_car, coverage_amt)
         VALUES(1111, 'A1111', 10000, 'Y', 500000);
INSERT INTO policy(id, vin, annual_mileage, rental_car, coverage_amt)
        VALUES(1414, 'B7777', 14000, 'N', 750000);​

Beim Einfügen der neuen Zeilen in die aktuelle Tabelle erzeugt Db2 die Zeitstempel für die Spalten sys_start und trans_start. Diese Zeitwerte waren in den INSERT-Anweisungen nicht angegeben. Db2 hat sie automatisch eingefügt. Die Abbildungen 5 und 6 zeigen, wie die Tabellen POLICY und POLICY_HISTORY nach den Einfügungen aussehen. Zur Vereinfachung zeigen die beiden Abbildungen nur die Datumswerte der Spalten sys_start und sys_end an. Die Daten werden im Format YYYY-MM-DD angezeigt. Da die Spalte mit der Transaktionsstartzeit als verborgen definiert wurde, wird sie hier in den Abbildungen 5 und 6 nicht angezeigt.​
Die Werte in der Spalte sys_start in der Tabelle POLICY geben an, wann die Sätze eingefügt wurden, im Beispiel am 15. November 2015. Die Werte für sys_end wurden auf den 30. Dezember 9999 gesetzt, um anzuzeigen, dass die Daten dieser Sätze noch aktuell sind.​

Aktualisieren von Daten in eine Tabelle mit System Time

Werden Daten in der Tabelle aktualisiert, sichert Db2 automatisch eine Vorversion der Daten in der zugehörigen History-Tabelle. Dieser Vorgang läuft transparent ab und erfordert weder Programmierung noch Benutzereingriff. Angenommen, die folgende Anweisung wird am 31. Januar 2016 ausgeführt, um die Deckungssumme für Police 1111 in 750000 zu ändern:

UPDATE policy 

        SET coverage_amt = 750000 

        WHERE id = 1111;

Wie in Abbildungen 7 und 8 gezeigt, aktualisiert Db2 den Wert in der Zeile der aktuellen Tabelle. Dann verschiebt Db2 eine Kopie des alten Satzes in die History-Tabelle. In beiden Tabellen speichert Db2 darüber hinaus die Startund Endzeiten für die Werte in diesen Zeilen. So wird der Wert für diese Zeile in der Spalte sys_end in der History-Tabelle auf den Transaktionszeitpunkt der UPDATE-Anweisung gesetzt. All dies geschieht automatisch und transparent für den Anwender. Db2 speichert auch die Transaktionsstartzeit in den Abbildungen 7 und 8, auch wenn das hier nicht angezeigt wird.

Alle weiteren Aktualisierungen werden natürlich auf ähnliche Weise abgewickelt. Angenommen, dass Police 1111 am 31. Januar 2017 in einigen weiteren Punkten wie der jährlichen Kilometerleistung, der Kostenübernahme für Leihwagen während schadensbedingter Fahrzeugreparatur und der Deckungssumme aktualisiert wird. Hier ist die entsprechende UPDATE-Anweisung:

UPDATE policy

SET annual_mileage = 5000, rental_car='N', coverage_amt = 250000

WHERE id = 1111;

Bei der Ausführung dieser Anweisung modifiziert Db2 automatisch die Tabellen POLICY und POLICY_HISTORY, wie in Abbildungen 9 und 10 gezeigt.

Löschen von Daten aus einer Tabelle mit System Time

Bei einem Löschvorgang entfernt Db2 die jeweiligen Daten aus der aktuellen Tabelle und überträgt eine Kopie der gelöschten Daten in die zugehörige History-Tabelle. Db2 setzt den Endzeitpunkt der gelöschten Daten in der History-Tabelle auf den Transaktionsstartpunkt der DELETE-Anweisung. Dieser Vorgang läuft transparent ab und erfordert weder Programmierung noch Benutzereingriff.

Angenommen die Zeile mit den Daten für Police 1414 wird am 31. März 2017 durch folgende Anweisung gelöscht: 

DELETE FROM policy WHERE id = 1414; 

Db2 löscht den Satz in der Tabelle POLICY und fügt diesen in die History-Tabelle ein (siehe Abbildungen 11 und 12). Die Spalte sys_end wird automatisch auf das aktuelle Datum, also auf den Wert 31. März 2017 gesetzt.

Abfragen an eine Tabelle mit System Time

Abfragen an eine Tabelle mit System Time lassen sich ganz einfach formulieren. Syntax und Semantik herkömmlicher SELECT-Anweisungen bleiben unverändert. So gelten diese Anweisungen ohne Zeitraumangaben stets für aktuelle Daten in der Basis-Tabelle. Vorhandene Anwendungen, Prozeduren und Datenbankberichte können also auch nach einer Erweiterung alter Tabellen durch das temporale Konzept weiter genutzt werden. In die SELECT-Anweisung lassen sich nun zusätzlich für den transparenten Zugriff auf historische Daten (oder eine Kombination aus aktuellen und historischen Daten) drei Zeitraumangaben aufnehmen.

Einige Beispiele zeigen, wie einfach sich zeitbezogene Abfragen formulieren lassen.

Die Informationen über Kfz-Versicherungen in der aktuellen und der History-Tabelle entsprechen den in Abbildungen 11 und 12. Nun wird folgende Abfrage gestellt:

SELECT coverage_amt 

        FROM policy 

        WHERE id = 1111;

Wie zu erwarten, gibt Db2 einen Satz mit einer Deckungssumme von 250.000 zurück.

Für Abfragen nach älteren Daten muss eine von drei unterstützten Zeitraumangaben in die FROM-Klausel aufgenommen werden:

• FOR SYSTEM_TIME AS OF ...
  gibt Daten ab einem bestimmten Zeitpunkt zurück.
  
  SELECT coverage_amt
       FROM policy FOR SYSTEM_TIME AS OF '2015- 12-01'  
       WHERE id = 1111; 
• FOR SYSTEM_TIME FROM ... TO ... gibt Daten für einen bestimmten Zeitraum zurück. Db2 nutzt hier ein inclusive-exclusive-Konzept für diese Zeitraumangabe. Das bedeutet: Der angegebene Startzeitpunkt liegt innerhalb des Zeitraums, der Endzeitpunkt nicht.
  
  SELECT count(*)
     FROM policy FOR SYSTEM_TIME FROM '2016-11-30' TO '9999-12-30'
     WHERE vin = 'A1111';
• FOR SYSTEM_TIME BETWEEN ... AND ...
  gibt Daten zurück, die zwischen bestimmten Startund Endzeiten liegen. Db2 nutzt hier ein inclusiveinclusive- Konzept für diese Zeitraumangabe. Das bedeutet: Der angegebene Startzeitpunkt und der Endzeitpunkt liegen innerhalb des Zeitraums.
  

Fazit

Die temporalen Funktionen von Db2 ermöglichen auf einfache Weise die differenzierte Auswertung historischer Datensätze. Auf Grundlage der temporalen Erweiterungen des SQL:2011-Standards bietet Db2 eine innovative Umgebung für effektive temporale Datenhaltung, die im Vergleich mit selbst entwickelten Lösungen zur Abbildung temporaler Konzepte in Form von Triggern, Prozeduren oder selbst entwickelten Anwendungen ganz erhebliche Zeitund Kostenvorteile bietet.​

Quellen

[Q1] Cynthia M. Saracco, Matthias Nicola, Lenisha Gandhi: „A matter of time: Temporal data management in DB2" 

[Q2] Db2 auf IBM developerWorks (Technische Artikel, Software, Forum) http://www.ibm.com/developerworks/data/products/db2

Über den Autor

Stefan Hummel, Certified IT Specialist IBM Deutschland