Mit der Datenbank durch die Zeit reisen (Teil I): Temporales Datenmanagement in IBM Db2 macht`s möglich

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

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

CREATE TABLE policy (
id INT primary key not null,
vin VARCHAR(10),
annual_mileage INT,
rental_car CHAR(1),
coverage_amt INT,
sys_start TIMESTAMP(12) GENERATED
ALWAYS AS ROW BEGIN NOT NULL,
sys_end TIMESTAMP(12) GENERATED
ALWAYS AS ROW END NOT NULL,
trans_start TIMESTAMP(12) GENERATED
ALWAYS AS TRANSACTION START ID
IMPLICITLY HIDDEN,
PERIOD SYSTEM_TIME (sys_start, sys_end)
); 

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

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

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