13 Minuten Lesezeit (2677 Worte)

Die Datenverwaltung mit Business Time

News

Donnerstag, 26. Juli 2018

Mit Business Time lässt sich nachverfolgen, wann bestimmte Geschäftsvorfälle gültig waren, gültig sind oder gültig sein werden. Beispiel: Ein Produkt kostet normalerweise 45 Euro, jedoch während einem Aktionszeitraum von ein Monat nur 39 Euro. Oder auf einen Kredit sind in einem Jahr sechs Prozent und im nächsten Jahr acht Prozent Zinsen zahlbar. Für solche Problemstellungen ist das Konzept Business Time vorgesehen, das die einfache Darstellung von Gültigkeitszeiten ermöglicht.

Wie bei der Bearbeitungszeit ist auch für die Gültigkeitszeit (Business Time) die Angabe einer Periode erforderlich (die Start- und Endzeiten der Gültigkeit). Anders als das Konzept System Time wird für Business Time keine separate History-Tabelle benötigt. Frühere, aktuelle und künftige Gültigkeitszeiten und die zugehörigen Geschäftsdaten werden alle in einer einzigen Tabelle verwaltet. Darüber hinaus ist es der Benutzer, der die Start-/Endwerte für die Business-Time-Spalten bei der Dateneingabe festlegt. Weiterhin wird auch für die Transaktionsstartzeit keine Spalte benötigt.

Erzeugen einer Tabelle mit Business Time

Um eine Tabelle mit Business Time zu erzeugen, sind Spalten für die Start-/Endzeitpunkte des Gültigkeitszeitraums sowie eine PERIOD BUSINESS_TIME-Klausel erforderlich. Die Spalten für die Start-/Endzeitpunkte des Gültigkeitszeitraums können vom Typ DATE oder TIMESTAMP sein.

Im folgenden Beispiel wird eine Tabelle für Kfz-Versicherungspolicen erzeugt, in der auch die Gültigkeitszeiten der jeweiligen Daten hinterlegt sind. Im vorliegenden Beispiel sind die Spalten bus_start und bus_end vom Typ DATE. Mit der Klausel PERIOD BUSINESS_TIME wird Db2 angewiesen, diese Spalten zur Nachverfolgung der Start- und Endzeiten für die Gültigkeit von Geschäftsvorfällen in jedem Satz zu nutzen. Um die temporale Integrität der Daten zu gewährleisten, legt Db2 automatisch eine Bedingung (Constraint) fest, die dafür sorgt, dass die Werte in bus_start vor denen in bus_end liegen.

CREATE TABLE policy (
    id              INT NOT NULL,
    vin             VARCHAR(10),
    annual_mileage  INT,
    rental_car      CHAR(1),
    coverage_amt    INT,
    bus_start       DATE NOT NULL,
    bus_end         DATE NOT NULL,
    PERIOD BUSINESS_TIME(bus_start, bus_end),
    PRIMARY KEY(id, BUSINESS_TIME WITHOUT OVERLAPS)
);

Die in der CREATE TABLE-Anweisung definierte Bedingung für den Primärschlüssel nutzt die optionale Angabe BUSINESS_TIME WITHOUT OVERLAPS. Damit wird Db2 angewiesen, nur eindeutige Primärschlüsselwerte für jeden Zeitpunkt in Gültigkeitszeiträumen anzulegen. Im vorliegendem Beispiel sorgt BUSINESS_TIME WITHOUT OVERLAPS also dafür, dass keine zwei Versionen oder Zustände derselben Police gleichzeitig bestehen können.

Mit der Anweisung ALTER TABLE lassen sich vorhandene Tabellen für die Darstellung von Gültigkeitszeiten nutzbar machen. Hierzu müssen die oben erwähnten DATE- oder TIMESTAMP-Spalten eingefügt und PERIOD BUSINESS_TIME definiert werden.

Einfügen von Daten in eine Tabelle mit Business Time

Das Einfügen einer Zeile in eine Tabelle mit Business Time ist einfach: Es müssen lediglich gültige Werte für alle Spalten mit der Bedingung NOT NULL angegeben werden – auch für die Start- und Endzeitpunkte des Gültigkeitszeitraums. Mit folgenden Anweisungen lassen sich einige Zeilen in der Tabelle POLICY mit Business Time einfügen (siehe Abbildung 1). Die Abbildung 2 zeigt das Ergebnis der Operationen.

Die Daten zeigen, dass für Police 1111 im Zeitraum vom 1. Januar 2015 bis zum 1. Januar 2016 eine Deckungssumme von 500.000 € vereinbart war. Ab dem 1. Januar 2016 gilt eine Deckungssumme von 750.000 €. Die Daten zeigen weiterhin, dass vom 1. Mai 2013 bis zum 1. März 2015 für Police 1414 eine Deckungssumme von 750.000 € für ein Fahrzeug mit einer voraussichtlichen Jahreskilometerleistung von 14.000 km vereinbart war. Vom 1. März 2015 bis zum 1. Januar 2016 galten für diese Police eine Deckungssumme von 600.000 € und eine voraussichtliche Jahreskilometerleistung von 12.000 km . Wie wird die für diese Tabelle (mit der Klausel BUSINESS_TIME WITHOUT OVERLAPS) vorgeschriebene zeitliche Eindeutigkeit in der Praxis gewährleistet? Dazu wird folgende INSERT-Anweisung eingegeben:

INSERT INTO policy
    VALUES(1111,'A1111',10000,
'Y',900000,'2015-06-01','2016-09-01');

Db2 wird diese Anweisung nicht ausführen und stattdessen Fehlermeldung ausgeben, weil versucht wurde, eine Zeile für Police 1111 einzufügen, die für einen Zeitraum gilt, für den bereits eine oder mehrere andere Zeilen für diese Police als gültig definiert sind. Es liegt also eine Verletzung der zeitlichen Eindeutigkeit vor. Soll die Deckungssumme für Police 1111 vom 1. Juni 2015 bis 1. September 2016 angepasst werden, ist dafür eine UPDATE-Anweisung erforderlich.

Aktualisieren von Daten in einer Tabelle mit Business Time

Auch Tabellen, die Gültigkeitszeiträume vorsehen, können mit herkömmlichen UPDATE-Anweisungen aktualisiert werden. Zusätzlich verfügbar ist die Klausel FOR PORTION OF BUSINESS_TIME zur Beschränkung der Aktualisierung auf einen bestimmten Gültigkeitszeitraum. Sind von der Aktualisierung auch Sätze betroffen, die nicht vollständig innerhalb des angegebenen Zeitraums liegt, aktualisiert Db2 die innerhalb der Zeitraumbedingung liegenden Daten und fügt zusätzliche Sätze ein zur Protokollierung der alten Werte für Sätze, die nicht innerhalb des angegebenen Zeitraums liegen.

Beispiel: Die Deckungssumme für Police 1111 soll für den Zeitraum vom 1. Juni 2015 bis 1. September 2016 geändert werden. Die entsprechende UPDATE-Anweisung könnte so aussehen:

UPDATE policy
    FOR PORTION OF BUSINESS_TIME FROM '2015-
06-01' TO '2016-09-01'
    SET coverage_amt = 900000
    WHERE id = 1111;

Hierbei ist zu beachten, dass die zeitliche Bedingung für die Abfrage (FOR PORTION OF BUSINESS_TIME FROM ... TO ...) hinter dem Tabellennamen steht und nicht Teil der WHERE-Klausel ist.

Wie in Abbildung 2 gezeigt, gab es ursprünglich zwei Zeilen für Police 1111. Beide sind von der UPDATE-Anweisung betroffen, da der zu aktualisierende Gültigkeitszeitraum teilweise innerhalb der in beiden Zeilen gespeicherten Zeiträume liegt. Diese Überlappung ist im oberen Teil von Abbildung 2 dargestellt. Wenn Db2 die Aktualisierung vornimmt, wird jede der ursprünglichen Zeilen in zwei Zeilen geteilt, wie im unteren Teil von Abbildung 2 dargestellt. Db2 passt die Gültigkeitszeiten der Sätze automatisch an.

Abb. 1: Einfügen von Daten in eine Tabelle mit Business Time

Abb. 2: Ergebnis der INSERT-Befehle aus Abbildung 1

Abb. 3: Die Tabelle POLICY nach dem UPDATE von Police 1111

Abb. 4: Die Tabelle POLICY nach dem Löschen von Daten

Abb. 5: Abfrageergebnis

Abbildung 3 zeigt die Tabelle POLICY nach der Aktualisierung. Sowohl der erste als auch der zweite Satz aus Abbildung 2 wird in jeweils zwei neue Sätze geteilt.

Löschen von Daten aus einer Tabelle mit Business Time

Das Löschen von Daten aus Tabellen mit Gültigkeitszeiträumen kann mit der Klausel FOR PORTION OF BUSINESS_TIME auf spezifische Zeiträume beschränkt werden. Enthält eine zum Löschen vorgesehene Zeile Daten, die nicht vollständig innerhalb des angegebenen Zeitraums liegen, sorgt Db2 dafür, dass diese Daten erhalten bleiben.

DELETE FROM policy
    FOR PORTION OF BUSINESS_TIME FROM
'2015-06-01' TO '2016-01-01'
    WHERE id = 1414;

Die Abbildung 4 zeigt das Ergebnis nach dem Löschen der Daten aus dem oben genannten Befehl an. Beachten Sie den letzten Satz (id = 1414), bei dem Db2 die Business Time korrigiert hat.

Abfragen an eine Tabelle mit Business Time

Abfragen an eine Tabelle mit Gültigkeitszeiträumen lassen sich ganz einfach formulieren. Für zeitbezogene Abfragen zur Ermittlung vergangener, aktueller und künftiger Geschäftsvorfälle stehen drei optionale Klauseln zur Verfügung. Natürlich lassen sich Tabellen mit Gültigkeitszeiträumen auch mit herkömmlichen, d.h. nicht-temporalen, SELECT-Anweisungen abfragen. Db2 führt solche Abfragen in der gewohnten Weise aus. Ein Beispiel zeigt, wie einfach sich zeitbezogene Abfragen mit dem Konzept Business Time formulieren lassen.

Die Tabelle POLICY enthält nun dieselben Daten, die auch Abbildung 2 zeigt, unmittelbar nachdem wir die Tabelle POLICY erzeugt und vier Sätze eingefügt hatten.

Abb. 6: Erzeugen einer bitemporalen Tabelle

Für Abfragen nach temporalen Daten muss auch hier analog zu Abfragen mit System Time eine von drei unterstützten Zeitraumangaben in die FROM-Klausel aufgenommen werden. Die Zeitangaben können sowohl in der Vergangenheit als auch in der Zukunft liegen:

FOR SYSTEM_TIME AS OF ...

FOR SYSTEM_TIME FROM ... TO ...

FOR SYSTEM_TIME BETWEEN ... AND ...

Mit folgender SQL-Anweisung lassen sich die für Police 1414 vom 1. Januar 2014 bis zum 1. Januar 2016 geltenden Versicherungsbedingungen abfragen. Das Ergebnis ist in Abbildung 5 dargestellt.

SELECT *
    FROM policy
    FOR BUSINESS_TIME FROM '2014-01-01' TO
'2016-01-01'
    WHERE id = 1414;

Temporale Abfragen an Tabellen mit Gültigkeitszeiträumen werden intern umgesetzt in WHERE-Klauseln für die DATE- oder TIMESTAMP-Spalten, in denen die Start- und Endzeitpunkte der Gültigkeitsdauer abgelegt sind.

Zusätzliche temporale Funktionalitäten

Wie die Beispiele oben zeigen, sind die von Db2 unterstützten temporalen Konzepte System Time und Business Time einfach zu implementieren. Wie bereits erwähnt, bietet Db2 darüber hinaus noch weitere Möglichkeiten zur temporalen Datenhaltung – etwa in so genannten bitemporalen Tabellen. Db2 unterstützt ebenfalls temporale Views und eine Registereinstellung, mit der die jeweilige Datenbank ähnlich wie eine Zeitmaschine arbeitet. Da eine ausführliche Darstellung dieser und anderer erweiterten temporale Funktionen den Rahmen dieses Artikels sprengen würde, sollen sie an dieser Stelle nur kurz vorgestellt werden.

Bitemporale Tabellen

Bitemporale Tabellen ermöglichen die Datenhaltung mit Bearbeitungs- und Gültigkeitszeit und die Ausnutzung der Vorzüge beider Konzepte. So lässt sich die Gültigkeitszeit für die Abbildung logischer Aspekte eines Datenbestands nutzen, wie beispielsweise die Laufzeiten von Versicherungspolicen, und die Bearbeitungszeit zur Nachverfolgung der Änderungen, die an diesen Policen vorgenommen wurden.

Das Erzeugen von Tabellen mit Bearbeitungs- und Gültigkeitszeit ist ebenso einfach wie das Ändern vorhandener Tabellen zur Aufnahme der beiden temporalen Konzepte. Die folgende CREATE TABLE-Anweisung definiert eine bitemporale Tabelle mit einer Periode für BUSINESS_TIME in den Spalten bus_start und bus_ end sowie mit einer Periode für SYSTEM_TIME in den Spalten sys_start und sys_end (siehe Abbildung 6).

Nach dem Erzeugen der bitemporalen Tabelle muss eine identische History-Tabelle erzeugt und die Versionierung aktiviert werden.

Die bitemporale Beispieltabelle POLICY und die zugehörige History-Tabelle sind in den Abbildungen 7 und 8 dargestellt. (Die Spalte trans_start wird zur Vereinfachung hier nicht dargestellt.)

Im folgenden Beispiel hat der Kundenservice folgende Handlungen vorgenommen:

Am 15. November 2016 wird die Police 1111 für das Fahrzeug A1111 angelegt. Als Beginn der Laufzeit der Police mit einer Deckungssumme von 500.000 € wurde der 1. Januar 2017 festgelegt.

INSERT INTO policy(id, vin, annual_
mileage, rental_car, coverage_amt, bus_
start, bus_end)
VALUES(1111,'A1111',10000,
'Y',500000,'2017-01-01','9999-12-30');

Am 1. März 2017 wurden die Bedingungen der Police 1111 mit Wirkung ab dem 1. Juni 2017 geändert: Die Deckungssumme wurde reduziert und die Kostenübernahme für Leihwagen entfernt. Das geschah mit folgender UPDATE-Anweisung:

UPDATE policy
    FOR PORTION OF BUSINESS_TIME FROM
'2017-06-01' TO '9999-12-30'
    SET coverage_amt = 250000, rental_car='N'
    WHERE id = 1111;

Die Abbildungen 9 und 10 zeigen die Inhalte der Tabellen POLICY und POLICY_HISTORY nach diesen Operationen. (Der Einfachheit halber sind in den Spalten sys_start und sys_end nur die Datums-werte gezeigt und nicht den exakte Zeitpunkt)

Darüber hinaus wird unter der Police 1111 wegen eines Unfalls am 20. Juni 2017 ein Schadenersatzanspruch geltend gemacht. Der zuständige Sachbearbeiter kann die Rechtmäßigkeit des Anspruchs mit folgender Ab- frage prüfen:

SELECT vin, rental_car, coverage_amt
    FROM policy FOR BUSINESS_TIME AS OF
'2017-06-20'
    WHERE id = 1111;

Db2 gibt die in Abbildung 11 gezeigte Information zurück, aus der hervorgeht, dass eine Versicherungsdeckung für das Fahrzeug besteht, der Kunde aber keinen Anspruch auf kostenlose Nutzung eines Mietwagens hat.

Am 10. Juli 2017 beschwert sich der Kunde und verlangt eine Aufstellung der in den vergangenen zwei Jahren an seiner Police vorgenommenen Änderungen. Diesem Wunsch kann der Sachbearbeiter mit folgender Abfrage nachkommen:

SELECT id, vin, annual_mileage, rental_car,
coverage_amt, bus_start, bus_end, sys_
start, sys_end
    FROM policy FOR SYSTEM_TIME FROM
'2015-07-10' TO '2017-07-11'
    WHERE id = 1111;

Db2 gibt die in Abbildung 12 gezeigten Ergebnisse zurück. Der Sachbearbeiter kann dem Kunden nun mitteilen, wann die Änderungen an der Police vorgenommen wurden und ab wann sie wirksam wurden (oder wirksam werden sollten).

Abb. 7: Die bitemporale Tabelle POLICY

Abb. 8: Die bitemporale Tabelle POLICY_HISTORY

Abb. 9: Die bitemporale Tabelle POLICY nach dem UPDATE

Abb. 10: Die bitemporale Tabelle POLICY_HISTORY nach dem UPDATE

Abb. 11: Abfrageergebnis

Abb. 12: Abfrageergebnis (die letzte Zeile stammt aus der HISTORY-Tabelle)

Views

Db2 stellt für temporale Tabellen zwei Arten von Views zur Verfügung. Solche Views ermöglichen ein flexibles Anwendungsdesign, um bezogen auf eine Datenbanksitzung die Daten für unterschiedliche Zeitpunkte oder Perioden abzubilden.

Views können für eine temporale Tabelle mithilfe einer FOR SYSTEM_TIME- oder FOR BUSINESS_TIME-Klausel definiert werden, um die Sicht auf einen bestimmten Zeitpunkt oder Zeitraum zu beschränken. Anschließend können diese Views mit herkömmlichen SQL-Anweisungen abgefragt werden. Abfragen an solche Views dürfen keine FOR SYSTEM_TIME- oder FOR BUSINESS_TIME-Klauseln enthalten, weil die zeitliche Bedingung in der Abfrage mit der zeitlichen Bedingung für die Views im Widerspruch stehen oder zu zweideutigen Ergebnissen führen könnte.
Die Definition einer View für eine temporale Tabelle ohne eine FOR SYSTEM_TIME- oder FOR BUSINESS_TIME-Bedingung. Solche Views stellen Daten für alle Zeitpunkte dar und lassen sich mit Anweisungen abfragen, die FOR SYSTEM_TIME- oder FOR BUSINESS_ TIME-Klauseln enthalten. Solche Klauseln werden dann automatisch auf alle Tabellen in der View- Definition angewendet, die Perioden für System Time oder Business Time enthalten.

Db2 bietet damit enorme Flexibilität bei der Arbeit mit Views und temporalen Daten. Temporale Bedingungen lassen sich also in der View-Definition oder in Abfragen an Views von temporalen Tabellen nutzen.

Globale Einstellungen über Register-Variable

Db2 Registereinstellungen bieten die Möglichkeit, Datenbestände mit vorhandenen Anwendungen von einem bestimmten Zeitpunkt aus zu analysieren, ohne dass die Anwendung selbst geändert werden muss. Im Folgenden geht es um eine Anwendung, die zahlreiche SQL-Abfragen oder bestimmte Berichtsabfragen enthält, die von Zeit zu Zeit ausgeführt werden müssen. Mit den temporalen Funktionen in Db2 wird man solche Abfragen auch für Bearbeitungszeitpunkte ausführen können, die in der Vergangenheit liegen, oder für vergangene oder künftige Gültigkeitszeiten. Müsste man nun jedoch alle vorhandenen SQL-Anweisungen um eine FOR SYSTEM_TIME- oder FOR BUSINESS_TIME-Klausel erweitern, wäre das möglicherweise sehr zeitaufwändig. Gleiches gilt für das Erzeugen von Views für alle betroffenen temporalen Tabellen. Deshalb stellt Db2 spezielle Register zur Verfügung, mit denen eine Datenbanksitzung auf einen spezifischen Zeitpunkt eingestellt werden kann.

Beispiel: Mit folgendem SET-Befehl wird die Systemzeit der eigenen Sitzung auf den 1. Januar 2013, 10.00 Uhr, eingestellt:

SET CURRENT TEMPORAL SYSTEM_TIME = '2013-01-01 10:00:00';

Nun werden alle in dieser Datenbanksitzung an temporale (oder bitemporale) Tabellen gerichteten Abfragen für die Bearbeitungszeit 1. Januar 2013, 10.00 Uhr, ausgeführt. Oder anders ausgedrückt: Alle Abfragen in dieser Datenbanksitzung werden automatisch um die Klausel FOR SYSTEM_TIME AS OF '2013-01-01 10:00:00' erweitert. Db2 führt dies automatisch aus. Der Benutzer muss weder an der Anwendung noch an den SQL-Anweisungen irgendetwas ändern. Mit folgender Registereinstellung lassen sich die Daten für einen Zeitpunkt auswerten, der einen Monat vor der aktuellen Bearbeitungszeit liegt:

SET CURRENT TEMPORAL SYSTEM_TIME = current timestamp – 1 MONTH;

Steht im Register CURRENT TEMPORAL SYSTEM_TIME ein anderer Wert als NULL, sind Datenänderungen wie ändern, einfügen oder löschen an temporalen Tabellen unzulässig. Für die Business Time lässt sich die Registervariable CURRENT TEMPORAL BUSINESS_TIME setzen, um Abfragen, Änderungen, Löschungen usw. an zurückliegenden oder künftigen Gültigkeitszeitpunkten vorzunehmen.

Beispiel:

SET CURRENT TEMPORAL BUSINESS_TIME = '2017-06-01';

Nun werden alle Abfragen und Befehle zur Datenmanipulation, die an Tabellen mit Business Time (oder bitemporale Tabellen) gerichtet sind, um die Klausel FOR BUSINESS_ TIME AS OF '2017-06-01' erweitert. Auch hier nimmt Db2 diese Änderung automatisch vor.

Fazit

Die temporalen Funktionen von Db2 ermöglichen auf einfache Weise sowohl die differenzierte Auswertung historischer Datensätze sowie die Verfolgung zeitlicher Änderungen. Auf der Grundlage der temporalen Erweiterungen des SQL:2011-Standards bietet Db2 eine 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 Zeit- und Kostenvorteile bietet.

Autor:

Stefan Hummel, Certified IT Specialist

IBM Deutschland