temporal tables

Mit DB2 V10 hat IBM mit der Einführung der "temporal tables" eine (rudimentäre) Möglichkeit geschaffen, Daten automatisiert zu versionieren (bzw. zu historisieren).
Dazu wird die bestehende Basistabelle um mehrere Spalten erweitert, die die Gültigkeitsdauer der Zeile verwalten. Zusätzlich wird eine vom Aufbau her identische Historientabelle angelegt, in denen die Datenveränderungen gespeichert werden.

Der SQL-SELECT-Befehl wurde um die Möglichkeit erweitert, den Datenbestand so zu selectieren, wie er zu einem bestimmten Zeitpunkt existiert hat ( oder existieren wird … ).

Wie funktioniert das jetzt genau ?

Für die Verwaltung der Gültigkeit benötigt die User-Tabelle drei zusätzliche Spalten, deren Namen frei wählbar sind. Sie definieren Beginn und Ende des Gültigkeitszeitraums sowie eine Transaktions-ID zur internen Synchronisierung.
Diese zusätzlichen Spalten können mit ALTER an eine bestehende Tabelle angefügt werden oder gleich beim CREATE mit vorgegeben werden.
Des weiteren muss DB2 noch mitgeteilt werden, dass diese Spalten für die Datenversionierung verwendet werden sollen:

create table pssst.gehaltsdaten
(userid integer not null,
gehalt decimal(9,0) not null,
sys_start timestamp(12) not null generated always as row begin,
sys_end timestamp(12) not null generated always as row end,
trans_id timestamp(12) generated always as transaction start id,
period system_time(sys_start,sys_end))
in dbperso.tsgehalt ;

besteht die Tabelle bereits, so muss sie um die Verwaltungsinformation erweitert werden:

alter table pssst.gehaltsdaten add sys_start timestamp(12) not null generated always as row begin;
alter table pssst.gehaltsdaten add sys_end timestamp(12) not null generated always as row end;
alter table pssst.gehaltsdaten add trans_id timestamp(12) generated always as transaction start id;
alter table pssst.gehaltsdaten add period system_time(sys_start,sys_end) ;

nachdem die Tabelle auf diese Weise vorbereitet ist, wird noch eine Historientabelle mit gleichem Aufbau benötigt. Diese Tabelle will natürlich auch einen Tablespace für sich alleine.

Create table pssst.gehaltsdaten_hist
LIKE pssst.gehaltsdaten
IN dbperso.higehalt ;

Als drittes müssen nun die Basis- und die Historientabelle miteinander verknüpft werden.

alter table pssst.gehaltsdaten add versioning use history table pssst.gehaltsdaten_hist;

Nun sind die Tabellen bereit für den Einsatz und es kann mit dem Befüllen begonnen werden.
(aus Datenschutzgründen werde ich nicht verraten, wer sich hinter den einzelnen UserIDs verbirgt und auch nicht, in welcher Währung das Gehalt bezahlt wird).

Der aktuelle timestamp sei 2013-10-09-10.11.00.000000000000 (plus/minus ein paar Millisekunden)

insert into pssst.gehaltsdaten ( userid,gehalt ) values (1 , 75000. ) ;
insert into pssst.gehaltsdaten ( userid,gehalt ) values (2 , 72000. ) ;
commit;
insert into pssst.gehaltsdaten ( userid,gehalt ) values (3 ,125000. ) ;
insert into pssst.gehaltsdaten ( userid,gehalt ) values (4 , 85000. ) ;

Diese Daten werden ganz normal in die Tabelle eingefügt. Die Historientabelle bleibt von den INSERTs unberührt:

SELECT * FROM pssst.gehaltsdaten ;
USERID	GEHALT	SYS_START	SYS_END	TRANS_ID
1	75000.	2013-10-09-10.11.42.621277189000	9999-12-30-00.00.00.000000000000	<null>
2	72000.	2013-10-09-10.11.42.621277189000	9999-12-30-00.00.00.000000000000	<null>
3	125000.	2013-10-09-10.11.42.626137159000	9999-12-30-00.00.00.000000000000	<null>
4	85000.	2013-10-09-10.11.42.626137159000	9999-12-30-00.00.00.000000000000	<null>

Unter SYS_START wird der Beginn-TIMESTAMP der jeweiligen UOW eingetragen, SYS_END ist immer der 30.12.9999. Die Spalte TRANS_ID ist immer NULL ( sofern die Spalte NULL-fähig ist )
Da zwischen dem zweiten und dritten INSERT ein COMMIT steht und damit eine neue UOW begann unterscheiden sich SYS_START hier um wenige Millisekunden.
Wären die Spalten mittels ALTER an die Tabelle angehängt worden, so wäre der Wert von SYS_START für bestehende Zeilen der Timestamp 0001-01-01-00.00.000000000000.

Nach genau einem Jahr (am 9.10.2014) bekommt User 1 eine Gehaltserhöhung. Die DB2-Tabelle wird entsprechend abgeändert:

update pssst.gehaltsdaten
set gehalt = gehalt + 2000
where userid = 1 ;

Die Gehaltstabelle sieht nun so aus:

SELECT * FROM pssst.gehaltsdaten ;
USERID	GEHALT	SYS_START	SYS_END	TRANS_ID
1	77000.	2014-10-09-10.21.38.370295502000	9999-12-30-00.00.00.000000000000	<null>
2	72000.	2013-10-09-10.11.42.621277189000	9999-12-30-00.00.00.000000000000	<null>
3	125000.	2013-10-09-10.11.42.626137159000	9999-12-30-00.00.00.000000000000	<null>
4	85000.	2013-10-09-10.11.42.626137159000	9999-12-30-00.00.00.000000000000	<null>

Und in der Historie findet sich ein erster Eintrag:

SELECT * FROM pssst.gehaltsdaten_hist ;
USERID	GEHALT	SYS_START	SYS_END	TRANS_ID
1	75000.	2013-10-09-10.11.42.621277189000	2014-10-09-10.21.38.370295502000	<null>

Ca. 2 Monate später verlässt User 4 das Unternehmen. Nach einem :

delete from pssst.gehaltsdaten
where userid = 4 ;

sehen die beiden Tabellen jetzt so aus:

SELECT * FROM pssst.gehaltsdaten ;
USERID	GEHALT	SYS_START	SYS_END	TRANS_ID
1	77000.	2014-10-09-10.21.38.370295502000	9999-12-30-00.00.00.000000000000	<null>
2	72000.	2013-10-09-10.11.42.621277189000	9999-12-30-00.00.00.000000000000	<null>
3	125000.	2013-10-09-10.11.42.626137159000	9999-12-30-00.00.00.000000000000	<null>

SELECT * FROM pssst.gehaltsdaten_hist ;
USERID	GEHALT	SYS_START	SYS_END	TRANS_ID
1	75000.	2013-10-09-10.11.42.621277189000	2014-10-09-10.21.38.370295502000	<null>
4	85000.	2013-10-09-10.11.42.626137159000	2014-12-09-13.35.45.986872876000	<null>

Das mag an Änderungen zur Anschauung erstmal genügen.

Neben dem automatischen Verwalten einer Historie liegt der Vorteil dieses Systems auch darin, mittels SELECT den Datenbestand zu einem beliebigen Zeitpunkt zu betrachten. DB2 erzeugt dann aus Basis- und Historientabelle mittels eines Abart von UNION ALL den Datenbestand zum vorgegebenen Zeitpunkt.
Um dies zu ermöglichen, wurde der SELECT-Befehl um die Syntax

FOR SYSTEM_TIME AS OF <timestamp>

erweitert.
<timestamp> muss dabei ein gültiger Timestampwert sein, egal ob eine Konstante, Spezialregister, Hostvariable, Parameter Marker, arithmetische Operation oder das Ergebnis einer skalaren Funktion. Nested SELECTs sind nicht erlaubt.

Will der Chef überprüfen, wie die Mitarbeitergehälter zu Beginn des Jahres 2014 waren, so kann er das mit dem folgenden SELECT ermitteln:

select *
from pssst.gehaltsdaten
FOR SYSTEM_TIME AS OF timestamp('01.01.2014') ;

Damit wird der Zustand ausgegeben, in dem sich die Tabellendaten zum Zeitpunkt 2014-01-01-00.00.00.000000 befunden haben

select * from pssst.gehaltsdaten FOR SYSTEM_TIME AS OF timestamp('01.01.2014') ;
USERID	GEHALT	SYS_START	SYS_END	TRANS_ID
2	72000.	2013-10-09-10.11.42.621277189000	9999-12-30-00.00.00.000000000000	<null>
3	125000.	2013-10-09-10.11.42.626137159000	9999-12-30-00.00.00.000000000000	<null>
1	75000.	2013-10-09-10.11.42.621277189000	2014-10-09-10.21.38.370295502000	<null>
4	85000.	2013-10-09-10.11.42.626137159000	2014-12-09-13.35.45.986872876000	<null>

Man beachte, dass diemal die UserIds 2 und 3 zuerst in der Tabelle erscheinen und danach erst die UserIDs 1 und 4.
Dies liegt daran, dass die ersten aus der laufenden Tabelle selectiert wurden, die beiden letzteren aus der Historientabelle.
Selbstverständlich kann mittels ORDER BY die Sortierreihenfolge wie gewohnt vorgegeben werden.

Innerhalb eines einzelnen SELECTs sind sogar mehrere Sichten (also zu unterschiedlichen Zeitpunkten) auf die gleiche Tabelle möglich.

Der Betriebsrat möchte z.B. die Gehaltsentwicklung im letzten Jahr überprüfen. Dann kann er folgenden SELECT absetzen:

with daten2013 as (
select userid,gehalt
from pssst.gehaltsdaten
FOR SYSTEM_TIME AS OF '2013-12-31-00.00.00.000000000000'
) ,
daten2014 as (
select userid,gehalt
from pssst.gehaltsdaten
FOR SYSTEM_TIME AS OF TIMESTAMP('31.12.2014')
)
select ifnull (daten2013.userid,daten2014.userid) as userid,
daten2013.gehalt as "Gehalt 2013" ,
daten2014.gehalt as "Gehalt 2014"
from daten2013 full join daten2014
on daten2013.userid = daten2014.userid ;

Als Ergebnis bekommt er dann diese Tabelle

USERID	Gehalt 2013	Gehalt 2014
1	75000.	77000.
2	72000.	72000.
3	125000.	125000.
4	85000.	<null>

Restiktionen

Um die Datenintegrität zwischen Basis- und Historientabelle einigermaßen zu gewährleisten, existieren für die Basistabelle einige Restriktionen:

Es sind keine Utilities erlaubt, die Daten aus der Basistabelle löschen. Dies betrifft vor allem LOAD REPLACE , REORG DISCARD und CHECK DATA DELETE YES.
Ebenfalls nicht erlaubt ist CHECK DATA mit einer INVALIDATE-Option (LOBERROR INVALIDATE, AUXERROR INVALIDATE, oder XMLERROR INVALIDATE)
Im Fall eines Point-in-time Recovery müssen Basis- und Historientablespace als SET recovered werden (oder die Option VERIFYSET NO muss gesetzt werden)
Es ist kein ALTER COLUMN und kein RENAME COLUMN erlaubt (sehr wohl aber ein ADD COLUMN. In diesem Fall wird die Spalte automatisch auch der Historientabelle zugefügt)
Das Statement TRUNCATE TABLE ist nicht erlaubt
Die Spalten SYS_START, SYS_END und TRANS_ID sind schreibgeschützt. Sie können weder per UPDATE verändert werden, noch bei einem INSERT vorgegeben werden.
Basistabelle und Historie müssen jeweils die einzige Tabelle im Tablespace sein.
Es ist kein CLONE für Basis- oder Historientabelle erlaubt.
Die Basistabelle darf keine column mask, keine row permission und keine security label Spalte enthalten
Die Basistabelle darf keine MQT sein
Weder die Basistabelle noch die Historientabelle können geDROPed werden. Ebenso nicht die beiden Tablespaces. (Zuerst muss ein alter table drop versioning erfolgen)
Sofern field procedures verwendet werden, müssen diese für Basis- und Historientabelle identisch sein.
Die Syntax FOR SYSTEM_TIME AS OF kann nicht innerhalb eines CREATE VIEW-Statements verwendet werden und nicht bei einem SELECT FROM view. Selbst wenn es sich dabei um einen 1:1-View auf die Tabelle handelt (Update: seit Version 11 ist die Verwendung von Views möglich)

Für die Historientabelle existieren nur wenige Einschränkungen:

Hat die Basistabelle eine ROWID GENERATED ALWAYS or ROWID GENERATED BY DEFAULT Spalte, dann muss dies Spalte in der Historientabelle das Attribut ROWID GENERATED ALWAYS haben. Sonstige GENERATED Spalten darf die Hiostorientabelle nicht haben.

Im Gegensatz zur Basistabelle können die Spalten SYS_START und SYS_END der Hístorientabelle per UPDATE verändert werden und auch INSERT und DELETE unterliegen keinen Einschränkungen.
Damit können zeitliche Lücken in der Historisierung erzeugt werden oder auch überlappende Werte.
Im Gegensatz zur BUSINESS TIME kann für die SYSTEM TIME kein Index über den Geltungsbereich WITHOUT OVERLAPS gelegt werden.

Notizen

Die automatische Befüllung der Historientabelle entspricht einem Standard-Insert. Es ist möglich, einen INSERT-Trigger auf die Historientabelle zu legen, der dann feuert.
Mit Hilfe eines derartigen Triggers könnte dann z.B. die User-ID abgespeichert werden, die die Änderung veranlasst hat. ( Eine entsprechende Spalte, hier aenderID VAR CHAR(124) vorausgesetzt ).

create trigger triggername
no cascade before insert on schema.tabelle_hist
referencing new as n
for each row mode db2sql
begin atomic
set n.aenderId = current sqlid ;
end

Interessanterweise kann das IBM-Admin Tool für Historientabellen keine Trigger anzeigen ("falscher Tabellentyp"). Trigger auf Historientabellen sind offensichtlich von IBM nicht vorgesehen.

Im DB2-Katalog wird die Verbindung zwischen Basis- und Historientabelle durch die beiden Spalten VERSIONING_SCHEMA und VERSIONING_TABLE hinterlegt. Sie beinhalten beide mal die korrespondierende Tabelle. Die Historientabelle hat dabei den TYPE = 'H', während die Basistabelle den Standardtyp 'T' hat.

Viel Spass bei der Reise durch die Zeit

Zurück zu DB2

Home

Impressum / Datenschutz