von Patrick Arnold

Dabei handelt es sich um eine systemseitige Historisierung von geänderten (Update) oder gelöschten (­Delete) Datensätzen. Die Datenbank unterstützt temporale Abfragen durch neue SQL-Statements.

Im Rahmen meiner Bachelorarbeit bei der All for One Steeb AG habe ich mir Temporal Support genauer angeschaut. Anwendungen aus dem Fachbereich DCW-Software setzen auf genannte IBM-Systeme und werden seit Jahrzehnten von Anwendern – unter anderem wegen ihrer Zuverlässigkeit – geschätzt. Warum der Einsatz dieser neuen Technologie auch hier Sinn macht und welche Vorteile die vom RDBMS nativ ­unterstützte temporale Datenhaltung bietet, galt es zu untersuchen.

Die Gesetze der Physik setzt IBM zwar nicht wirklich außer Kraft, dennoch müssen vorab andere Herausforderungen – gerade im Zusammenspiel mit DDS-basierten Anwendungen – erkannt und überwunden werden.

Als Einstieg in diese Artikelserie möchte ich Ihnen zunächst die Basics der temporalen Datenhaltung näherbringen. Ausgerüstet mit diesem grundlegenden Verständnis, ergeben sich die einen oder anderen Motive für Temporal Support beinahe von selbst. Einrichtung und Administration dieses Features sind sowohl über den klassischen Green Screen als auch über moderne Java- oder webbasierte GUIs möglich.

Temporale Datenhaltung bisher

Konventionelle RDBMS bieten keine temporale Datenhaltung an. Zugehörige Datensätze enthalten jeweils nur die aktuell gültigen Werte, die bei Updates oder Deletes unwiederbringlich durch neue Informationen ersetzt oder gelöscht werden. Zeitorientierte Datentypen und Operationen werden zwar durchaus unterstützt, sodass zumindest kontextbezogene Informa­tionen, beispielsweise zu Bewegungs- oder Stammdaten, festgehalten werden können. Dass Werte wie der Geburtstag eines Mitarbeiters oder das Produktionsdatum eines Artikels aber nicht zur Historisierung der Datensätze herangezogen werden können, leuchtet ein. Bei solchen benutzer- oder anwendungsdefinierten Datumswerten kann nicht gewährleistet werden, dass es sich ausschließlich um Gültigkeits- oder Transaktionszeiten bezüglich der Datensätze handeln. Das wäre für eine spätere Auswertung der Historie aber zwingend erforderlich.

Das Sichern früherer Datenbestände zur Rekonstruktion von Änderungen ist natürlich seit jeher ein Kernthema der elektronischen Datenverwaltung. Bei derzeit üblichen relationalen Datenbanken übernehmen in der Regel Zusatzprodukte diese Aufgabe, die journal- oder triggerbasiert arbeiten.

Kommerzielle Produkte, wie z.  B. IBM InfoSphere Change Data Capture mit dem Live-Audit-Modul, sind vergleichsweise teuer und besitzen sowohl Vor- als auch Nachteile gegenüber einer, in das RDBMS integrierten, Lösung. Die Vorteile beziehen sich insbesondere auf eine komfortable Administration per GUI.
Eigene Lösungen bedürfen eines hohen Entwicklungsaufwands und sind deshalb häufig nur für Teilbereiche einer Anwendung umgesetzt. Diese Eigenentwicklungen entsprechen mitunter nicht den hohen Anforderungen in kritischen Produktionsprozessen. Wenn die Anwendung zur Ermöglichung von Audits auch noch befähigt sein muss, sich selbst zu auditieren, um Manipulationen ausschließen zu können, dann wird es hier schon komplizierter.

SQL:2011-Standard

Die Unterstützung einer temporalen Datenhaltung in Tabellen relationaler Datenbanken wurde im Dezember 2011 mit der ISO-Norm ISO/IEC 9075 bezüglich des Standards SQL:2011 eingeführt. Für die Historisier­ung ist es notwendig, den Datensätzen Gültigkeitszeiträume zuweisen zu können. Erreicht wird dies durch die Verwendung zweier Datenfelder mit identischem Datentyp zur Spezifikation des Zeitraums (z. B. TIMESTAMP). Über den Datentyp entscheidet hierbei das temporale RDBMS. Zur Wahrung der Konsistenz sind diese Felder zwingend zu befüllen und daher mit den erforderlichen Constraints (NOT NULL) Bestandteil der Tabellendefinition. Über die neue „PERIOD FOR“-Klausel wird das Intervall zwischen den beiden Datumsfeldern dem System als Zeitraum bekannt ­gemacht.

SQL:2011 definiert folgende drei Typen temporaler Tabellen:

• Application-Time Period Tables
• System-Versioned Tables
• Bitemporal Tables

Bei der ersten Variante ist die Anwendung in der Pflicht, die Datumsfelder (bspw. PERIOD_START und PERIOD_END) mit validen Werten zu füllen und per Plausibilitätsprüfung unter anderem sicherzustellen, dass das Endedatum nicht vor dem Startdatum liegt. Updates auf die Felder sind möglich, was hinsichtlich Manipulierbarkeit problematisch sein kann.

Bei System-Versioned Tables sichert das System den aktuellen Zustand eines Datensatzes automatisch, bevor UPDATE- oder DELETE-Operationen auf diesen ausgeführt werden. Start- und Ende-Felder werden systemseitig gefüllt. Änderungen an historisierten Datensätzen und an zugehörigen Gültigkeitszeiträumen sind anwendungsseitig nicht möglich.

Bitemporale Tabellen nutzen die Felder der beiden zuvor genannten Varianten.

Temporal Support in IBM DB2 for i

IBM setzt bei TS auf die Ausprägung „System-Versioned Tables“. Als integrierte Lösung ist TS kostenneutral und zuverlässig. Die Dualität der Datenbank hinsichtlich DDS-basierter Dateien und DDL-basierter Tabellen wird unterstützt, sodass TS mit beiden Varianten genutzt werden kann.

Das IBM SQL-Referenzhandbuch enthält umfassende Informationen zu TS. Die wichtigsten Eigenschaften und Voraussetzungen werden hier aufgeführt:

• „Temporal Table“ wird die um Felder der Gültigkeits- und Transaktionszeit erweiterte Tabelle genannt; es gilt:
• – eine Temporal Table wird mit einer einzigen History Table assoziiert
• – eine Temporal Table kann auf einer Physical File (DDS) oder auf einer Table (SQL) basieren
• – eine Temporal Table und die zugehörige History Table müssen journalisiert werden
• – History Table muss in der selben Bibliothek liegen
• – Definition der History Table muss exakt der Temporal Table entsprechen
• – History Table muss ebenfalls journalisiert werden; hier gilt:
• – – Einträge in History-Tabellen können nicht entfernt oder geändert werden
• – – unerwünschte Historie kann aber, z. B. zur Auslagerung, entfernt werden:
• – – – per DELETE oder per TRUNCATE
• – – – per ALTER TABLE DROP PARTITION
• – – – per ALTER TABLE DETACH PARTITION

Grundlegende Funktionsweise

Im Gegensatz zu anderen Historisierungslösungen, die auf Basis von Triggern arbeiten, lässt Temporal Support einen direkten Zugriff auf die History-Tabellen nicht zu. Bei Abfragen zu vergangenen oder zukünftigen Zeiten (Prognosen) greift das RDBMS automatisch – und für die Anwendung vollkommen transparent – auf die benötigte History-Tabelle zu.

Die zu überwachende Tabelle wird mit den für die Historisierung notwendigen Spalten erweitert und damit zur Temporal Table. Dieser Tabelle entsprechend wird eine History Table erzeugt und dem System bekannt gemacht. Durch Aktivierung der Journalisierung und Versionierung werden Operationen auf Datensätze der Temporal Table in der History Table protokolliert.

INSERT-Operation auf Temporal Table

• Der eingefügte Datensatz erhält als Startwert des Gültigkeitszeitraums den aktuellen TIMESTAMP(12).
• Der eingefügte Datensatz erhält als Endwert des Zeitraums einen unendlichen Wert (NOT NULL-Constraint); dies wird praktisch realisiert durch den TIMESTAMP(12) mit dem Wert „9999-12-30 00:00:00.000000000000“.

UPDATE-Operation auf Temporal Table

• Der geänderte Datensatz erhält als Startwert des Gültigkeitszeitraums den zum Änderungszeitpunkt aktuellen TIMESTAMP(12).
• Der geänderte Datensatz erhält als Endwert des Zeitraums einen unendlichen Wert; dies wird praktisch realisiert durch den TIMESTAMP(12) mit dem Wert „9999-12-30 00:00:00.000000000000“.
• Der ursprüngliche Datensatz wird mit ursprünglichem Startwert in die History Table eingetragen.
• Als Endwert des in die History Table eingetragenen Datensatzes wird der zum Änderungszeitpunkt aktuelle TIMESTAMP(12) gesetzt.

DELETE-Operation auf Temporal Table

• Der Datensatz wird aus der Temporal Table entfernt.
• Sofern die History Table mit „ON DELETE ADD EXTRA ROW“ definiert wurde, wird der gelöschte Datensatz in diese eingetragen. Dieser Eintrag erhält dann als Endwert den zum Operationszeitpunkt aktuellen TIMESTAMP(12).

Optionale systemgenerierte Datenfelder

Einer Temporal Table – so bezeichnet man die um die TS-Felder erweiterte Basistabelle – können bei Bedarf weitere Spalten hinzugefügt werden, welche systemseitig generierte Werte enthalten. Diese Besonderheit stellt wertvolle Audit-Informationen zur Verfügung und unterscheidet TS von anderen Historisierungslösungen. Wichtig ist, vorab zu planen, welche Felder für den jeweiligen Einsatzzweck benötigt werden. Einerseits muss, entsprechend der Datentypen, mit dem entstehenden Datenvolumen gerechnet werden, andererseits lassen sich nachträglich doch benötigte Informa­tionen ohne vorherige Einrichtung nicht rekonstruieren. Nähere Informationen dazu liefert unter anderem das IBM SQL-Referenzhandbuch.

• Data Change Operation: Das Feld speichert einen Wert (CHAR(1), {I, U, D}) für jede Zeile, die in eine Temporal Table eingefügt (I, INSERT) oder dort verändert (U, UPDATE) wird. Falls ein Datensatz gelöscht wird und die zugehörige History Table mit „ON DELETE ADD EXTRA ROW“ definiert wurde, wird die Löschoperation mit entsprechender Information (D, DELETE) vermerkt.
• Special Registers: Diese Register liefern Informa­tionen zum Client, wie z.  B. den Namen der Workstation oder des Users.
• Built-In Global Variables: Diese Variablen liefern Informationen zur aktuellen Systemumgebung, wie z.  B. der IP-Adresse des Users, der die Änderung durchgeführt hat.

Einrichtung in wenigen Schritten

1) Die zu historisierende Tabelle wird mit den für Temporal Support notwendigen Spalten neu angelegt oder um diese erweitert. Die dadurch entstehende Tabelle nennt sich „Temporal Table“.
(Siehe Code 1)

2) Die Tabelle, die ursprüngliche Datensätze nach einer Operation speichert, nennt sich „History Table“ und enthält dieselben Felder wie die zu überwachende Temporal Table.

CREATE TABLE example_hist LIKE example;

3) Dem System muss nun bekannt gemacht werden, dass die Temporal Table mit Hilfe der History Table überwacht werden soll.

ALTER TABLE example
ADD VERSIONING USE HISTORY TABLE example_hist;

4) Zuletzt muss noch die Journalisierung aktiviert werden; in diesem Fall per CL:

a. Journal Receiver für jede Temporal Table und für jede History Table erstellen:

CRTJRNRCV *JRNRCV

b. Definition des jeweils zugehörigen Journals:

CRTJRN *JRN

c. Starten der Journalisierung:

STRJRNPF *JRN

Motivation

• Regulatorische Vorgaben: gesetzliche und von Geschäftspartnern vertraglich vorgegebene Anforderungen müssen eingehalten werden, sonst drohen ernstzunehmende Konsequenzen.
  Zu nennen sind unter anderem die neue EU-Datenschutzreform, die GoBD, Vorgaben hinsichtlich der FDA-Zertifizierung bzgl. der Pharmabranche, im Finanz-/Wertpapierbereich Basel II, Basel III sowie der Sarbanes-Oxley- und der Dodd-Frank-Act.
• Audits: systemseitige Historisierung der Daten und aller Änderungen sorgt für sorgenfreie Audits ohne langwierige ETL-Prozesse oder sonstige Daten­beschaffungsprobleme.
• Business Intelligence: lückenlose Nachvollziehbarkeit von Änderungen und früheren Zuständen und Nutzung dieser Daten im Rahmen der BI.
• Business Analytics: zusätzliche SQL-Statements ermöglichen komplexe temporale Abfragen zur Beschreitung völlig neuer Ansätze und Wege.

Neue Möglichkeiten temporaler Abfragen

Abfragen sind komfortabel per SQL möglich. Das System prüft dabei im Hintergrund, ob aktuelle Daten aus der Temporal Table geliefert werden können, oder ob auf die History Table zurückgegriffen werden muss.
Abfragen sind für einen bestimmten Zeitpunkt oder einen Zeitraum möglich.

Zeitpunkt

• SET CURRENT TEMPORAL SYSTEM_TIME = ‚2017-01-01‘
• – SQL-Abfragen dieser Session liefern Werte, als wäre es der 01.01.2017.
• SELECT * FROM example FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP -1 YEAR;
• – Diese Abfrage liefert den Datensatz aus der angegebenen Tabelle von vor einem Jahr.

Zeitraum

• FOR SYSTEM_TIME FROM <date from> TO <date to>
• – Werte des angegebenen Zeitraums – inklu­dierend – [date from : date to]
• FOR SYSTEM_TIME BETWEEN <date from> AND <date to>
• – Werte des angegebenen Zeitraums – exklu­dierend – ]date from : date to[

Ausblick auf Teil II

Im folgenden Artikel Teil II werde ich den Einsatz von Temporal Support an einem Beispiel demonstrieren und komplexe Abfragen nutzen. Weiterhin werden die Herausforderungen, die es zu bewältigen gilt, beleuchtet.

Kontaktieren Sie mich gerne für Feedback und ­Anregungen zur Artikelserie.

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