von Willy Günther
Bisher konnte DB2 für i nur die aktuellen Werte in der Datenbank abspeichern. Das bedeutet, die Datenbank enthält den aktuellen Lagerbestand für einen bestimmten Artikel oder die zur Zeit offenen Forderungen für einen bestimmten Kunden. Wie hoch der Lagerbestand dieses Artikels vor einem Jahr war oder wie sich die offenen Forderungen gegenüber einem Kunden im Jahresverlauf entwickelt haben, das lässt sich nicht feststellen. Die Datenbank hat keine zeitliche Dimension – eine Betrachtung für eine bestimmte Periode ist nicht möglich.
Wenn bisher Werte vorhergehender Perioden benötigt wurden, dann konnte man diese nur durch die Auswertung von Journal Einträgen ermitteln. Diese Vorgehensweise ist extrem aufwändig und daher nur für selten auftretende Anforderungen einsetzbar. Für eine regelmäßige Auswertung von Daten ist diese Methode völlig ungeeignet.
Wenn ich Daten aus der Vergangenheit in einer Tabelle abspeichern will, damit diese einfacher verarbeitet werden können, dann muss die Anwendung die komplette Logik zur Verwaltung der Daten implementieren. Das heißt, beim Update einer Spalte muss der alten Wert in eine History Tabelle übertragen werden, zum Beispiel durch einen Datenbank Trigger und auch die benötigten Zeitstempel muss die Anwendung verwalten. Die Anwendung benötigt auch Logik, um gezielt auf die History Tabelle zuzugreifen wenn Werte aus der Vergangenheit benötigt werden.
Um diese Beschränkungen zu vermeiden, benötigt die Datenbank die Möglichkeit temporale Tabellen zu implementieren. Damit können nicht nur die aktuellen, sondern auch historische Daten mit ihrer zeitlichen Dimension abgespeichert werden. Die Temporal Unterstützung in der Datenbank verknüpft die Basis Tabelle, die die aktuellen Daten enthält, mit der dazugehörenden History Tabelle, in der die Daten aus der Vergangenheit gespeichert werden. Damit ist der Datenbank die Verknüpfung bekannt und sie kann bei Updates der Basis Tabelle die bisherigen Daten automatisch in die History Tabelle übertragen.
Bei der Abfrage von Daten erkennt die Datenbank, dass ich Daten für einen bestimmten Zeitraum suche und schaut dann auch in der History Tabelle nach, ob dort entsprechende Daten vorhanden sind.
Die Zugriffe auf die Daten erfolgt immer über die Basis Tabelle – die Verwaltung der History Tabelle wird automatisch von der Datenbank durchgeführt. Auch das einfügen, ändern oder löschen von Daten erfolgt über die Basis Tabelle. Die Verwaltung der History Tabelle ist für die Anwendung transparent und erfolgt automatisch durch die Datenbank.
Durch diese Technologie lässt sich jetzt durch eine Abfrage sehr einfach feststellen, wie hoch ein Wert vor einem Jahr war oder wie er sich über die letzten 3 Monate verändert hat. Das bedeutet, die zeitliche Dimension wird jetzt mit abgespeichert und ermöglicht damit auch die Analyse zu einem vorgegebenen Zeitpunkt oder über einen Zeitraum.
Erstellen einer temporalen Tabelle
Das verwendete Beispiel stammt aus dem IBM Knowledge Center für IBM i 7.3. Eine temporale Tabelle wird wie jede andere Tabelle per SQL mit CREATE TABLE angelegt. Es ist aber auch möglich eine bestehende Tabelle in eine temporale Tabelle umzuwandeln, dann wird statt CREATE TABLE der ALTER TABLE Befehl verwendet – siehe Abb. 1
Eine temporale Tabelle benötigt 3 zusätzliche Spalten, um die Zeitstempel die für die zeitliche Dimension benötigt werden abzuspeichern.
Die Definition PERIOD SYSTEM_TIME legt fest dass wir eine temporale Tabelle verwenden, die als Zeitstempel die Systemzeit verwendet zu der die Datenbank Transaktion durchgeführt wird. Gleichzeitig wird definiert, in welchen Spalten (in unserem Beispiel sys_start und sys_end) der Start bzw. das Ende einer Periode abgespeichert werden.
In den Spalten sys_start und sys_end wird Anfang und Ende einer Periode festgehalten, in der die Werte der Zeile gültig sind. Die Werte von sys_start und von sys_end werden automatisch vom System gesetzt wenn eine Änderung an den Daten (insert, update oder delete) durchgeführt wird.
Der Wert von ts_id wird auch automatisch vom System generiert. Hier wird der Zeitpunkt der Transaktion festgehalten, dieser Wert ist meistens identisch mit dem sys_start Wert. Es kann aber vorkommen, dass durch unterschiedliche Transaktionen Updates auf den gleichen Satz durchgeführt werden. Dabei kann es durch unterschiedliche Commit Zeitpunkte zu „ungültigen“ Transaktionszeitpunkten kommen.
Die Datenbank kann diesen Konflikt automatisch durch eine Anpassung der sys_start Zeit auflösen. Da der Wert der ts_id in so einem Fall nicht verändert wird, ist er danach nicht mehr mit dem sys_start Wert identisch. Mehr Informationen zu diesem Szenario finden Sie hier:
Die passende History Tabelle wird mit den gleichen Attributen erstellt wie die Basis Tabelle.
CREATE TABLE hist_policy_info LIKE policy_info;
Die Verknüpfung der Basis Tabelle mit der entsprechenden History Tabelle erfolgt durch eine Alter Table Anweisung.
ALTER TABLE policy_info ADD VERSIONING USE HISTORY TABLE hist_policy_info;
Danach ist die Tabelle policy_info mit der History Tabelle hist_policy_info verknüpft.
Einfügen von Daten in die Tabelle policy_info
Wenn wir neue Daten in die Tabelle policy_info einfügen, dann verwendet der Insert Befehl wie bei einer herkömmlichen Tabelle nur die benötigten Anwendungsdaten. Die Zeitstempel die wir für die temporalen Tabellen benötigen werden vom System erstellt – siehe Abb. 2 und Abb. 3
Daten in der Basis Tabelle policy_info nach dem ausführen der INSERT Befehle siehe Abbildung 3.
Wir sehen, dass die Spalten sys_start und ts_id den aktuellen Zeitstempel zum Zeitpunkt der Insert Transaktion bekommen haben, und dass die Spalte sys_end auf einen Default Wert gesetzt wurde.
Der Default Wert in sys_end zeigt an, dass es sich um aktuelle Werte handelt. Dass Einfügen dieser Daten hat keine Einträge in der History Tabelle hist_policy_info angelegt.
Änderung von Daten in der Tabelle policy_info
Wenn die Anwendung durch einen Update Befehl eine Änderung an der Basis Tabelle policy_info durchführt, dann verschiebt die Datenbank automatisch die bisherigen Daten aus der policy_info Tabelle in die hist_policy_info Tabelle.
Außerdem aktualisiert die Datenbank den Zeitstempel vom Zeitpunkt des Update in der Spalte sys_start und ts_id in der Tabelle policy_info und trägt diesen Wert für sys_end in der hist_policy_info Tabelle ein.
In unserem Beispiel wird der Wert für die Versicherungspolice C567 auf 25.000 erhöht.
UPDATE policy_info SET coverage = 25000 WHERE policy_id = 'C567';
Nach diesem Update enthält die Tabelle policy_info die folgenden Daten – siehe Abb. 4 und Abb. 5
Löschen von Daten in der Tabelle policy_info
Beim löschen einer Zeile wird diese komplett aus der Tabelle policy_info entfernt. Die Datenbank verschiebt die bisherigen Daten automatisch in die hist_policy_info Tabelle und schreibt in sys_end den Zeitstempel an dem die Zeile gelöscht wurde.
Wenn die Policy Informationen für die ID B345 aus der Tabelle policy_info gelöscht werden, dann ergibt sich danach folgendes Bild.
DELETE FROM policy_info WHERE policy_id = 'B345';
siehe Abb. 6 und Abb. 7
Wenn Daten mehrfach geändert werden, dann enthält die History Tabelle alle vorhergehenden Werte und sys_start bzw. sys_end geben den Zeitraum an, in dem der einzelne Wert aktuell war. Das bedeutet, bei häufigen Änderungen werden in der History Tabelle sehr viele Daten abgespeichert. Es kann deshalb sinnvoll sein, die History Tabelle zu partitionieren. Auf jeden Fall sollte eine Archivierungsstrategie existieren. Mehr Informationen zu diesen Themen finden Sie unter dem folgenden Link:
Abfrage von Daten aus einer Basis Tabelle am Beispiel policy_info
Eine Abfrage wird immer gegen die Basis Tabelle gestellt. Die Datenbank durchsucht automatisch die History Tabelle wenn eine Zeitangabe bei der Abfrage mitgegeben wurde.
Die Zeitangabe wird mit der FOR SYSTEM_TIME Angabe in der FROM clause hinterlegt. Es besteht aber auch die Möglichkeit über einen VIEW oder über das Register CURRENT_TEMPORAL_SYSTEM_TIME die Zeit anzugeben.
Ausführliche Informationen zu diesen Möglichkeiten finden Sie hier:
Bei einer Abfrage ohne die FOR SYSTEM_TIME Angabe wird nur die Basis Tabelle abgefragt – siehe Abb. 8 und Abb. 9
Bei der nächsten Abfrage wird FOR SYSTEM_TIME verwendet und die Datenbank durchsucht automatisch auch die History Tabelle – siehe Abb. 10 und Abb. 11
Die erste Zeile enthält die aktuellen Daten und stammt aus der Basis Tabelle policy_info und die zweite Zeile wird von der History Tabelle geliefert.
Weitere Query Beispiele finden Sie im IBM Knowledge Center:
Hiding columns
Die für die Temporal Tables erforderlichen zusätzlichen Spalten in unserem Beispiel sys_start, sys_end und ts_id haben für die Anwendung und für den Anwender meistens keine direkte Bedeutung.
Um diese Spalten nicht automatisch bei jedem SELECT * anzuzeigen, können diese Spalten als IMPLICITLY HIDDEN beim Erstellen der Tabelle definiert werden.
Wenn die Spalten benötigt werden, können Sie jederzeit über Ihren Namen in einer SELECT Anweisung benutzt werden.
Native I/O für temporale Tabellen?
DB2 für i erlaubt SQL und nativ I/O (read, write) Zugriffe auf die gleichen Tabellen – dies gilt generell auch für temporale Tabellen. Es gibt Einschränkungen beim Zugriff über nativ I/O’s. Diese ergeben sich aber aus der anderen Arbeitsweise von nativ I/O’s. Mehr Informationen dazu unter:
Weiterführende Informationen
Umfangreiche Informationen zum Thema temporale Datenhaltung finden Sie im IBM Knowledge Center:
Informationen zu den verfügbaren DB2 for i Technology Updates gib es hier:
IBM i 7.3 Informationen im IBM Knowledge Center:
Eine aktuelle IBM i Roadmap und weitere Informationen finden Sie hier:
Der Autor Willy Günther ist Senior IT Spezialist
bei der IBM Deutschland GmbH
wiguenth(ätt)de.ibm.com
Ähnliche Artikel:
IBM i 7.3 Software Ankündigungsüberblick Im Rahmen der IBM Ankündigungen vom 12.04.2016 wurde auch IBM i Release 7.3 angekündigt. In dem folgenden Artikel möchte ich einen Überblick über die wichtigsten IBM i Software Ankündigungen geben.…
SQL: dynamisches Select ohne Cursor In einem System iNetwork Forum tauchte die Frage auf, ob in SQL eine dynamische Select-Anweisung ausgeführt werden kann, ohne zuvor einen Cursor zu definieren. Birgitta Hauser hatte dazu eine Lösung…
Highlights von IBM i 7.3 Als wichtigste Themenbereiche dieses neuen Releases bezeichnet Steve Will, IBM Chief Architect: 1. Vereinfachung der Informations-Gewinnung, 2. intelligente Sicherheit und 3. Nutzen durch Integration. Hier kurz vor dem Drucktermin ein…