Historisierung und Versionierung mit Oracle Workspace

KONFERENZ
Donnerstag, 11. November 2004
15h00, Variohalle 5
Historisierung und Versionierung mit
Oracle Workspace Manager im Einsatz bei
der Deutschen Bahn AG
Claus Holzknecht
its-people
Schlüsselworte:
Database, 10g, Workspace Manager, Spatial
Einleitung
In vielen Projekten steht man vor der Herausforderung aus Gründen der Nachvollziehbarkeit die Vergangenheit – also eine (Bearbeitungs) Historie der Daten
– abzubilden. Auch existiert häufig die Anforderung, dass Datensätze nicht
sofort bei Eingabe wirksam werden sollen, sondern für sie ein gewisser Gültigkeitszeitraum definiert wird. Manchmal sollen z.B. für das Durchspielen verschiedener Planvarianten auch noch verschiedene Versionen ein und des selben Datensatzes abgelegt werden. Die Umsetzung dieser Anforderungen stellt
sich als enorm komplex dar.
Seit Oracle 9i hat man über das Feature „Flashback Query“ die Möglichkeit
einen Zustand aus der Vergangenheit für einzelne Datensätze wiederherzustellen. Dies ist jedoch, da es mit Hilfe der Standard Redo Funktionalität der Datenbank abgebildet wird, nur für relativ kurze Zeiträume möglich. Man benötigt
alle Redo Daten der gesamten Datenbank bis zu dem gewünschten Zeitpunkt
zurück. Plattenplatz ist heutzutage zwar billig, aber wer hat schon einen Undo
Tablespace, der z.B. die Änderung des letzten Jahres vorhalten kann.
Die Aufgabenstellung die Historien und Versionen pro Tabelle abzubilden
bewältigt der Oracle Workspace Manager. Mit dem Oracle Workspace Manager
steht seit Version 8i ein Feature als Bestandteil der Datenbank zur Verfügung
das in den Versionen 9i R2 und 10g noch um wesentliche Funktionalität
erweitert wurde.
Dieses Feature ist im Umfang jeder Datenbanklizenz enthalten, so dass sich ein
Einsatz auch kostengünstiger auswirkt als diese Funktionalität in Eigenentwicklung nachzubilden. Es besteht zudem auch die Möglichkeit den Workspace
Manager für schon bestehende Anwendungen im nachhinein einzusetzen und
somit z.B. eine Anwendung um eine Historisierung zu erweitern.
Begriffsklärung Historisierung – Versionierung
Im folgenden werden die Begriffe Historisierung und Versionierung, wie sie in
diesem Vortrag verwendet werden, definiert:
Historisierung:
Die Historisierung dient der Abbildung der Änderungshistorie eines
Datensatzes.
Ein historischer Datensatz wird jedes mal dann erzeugt, wenn eine
Anpassung an einem Datensatz vorgenommen wird und der alte
Zustand nicht mehr betrachtet werden soll.
Versionierung:
Versionierung ist die Speicherung von Daten des selben Objektes, die zu
verschiedenen äußeren Randbedingungen betrachtet werden z.B. zu verschiedenen Zeiten oder in verschiedenen Planvarianten (Szenarien).
Die verschiedenen Versionen der Daten werden durch weitere Merkmale
die den Kontext beschreiben, unterschieden. z.B. Gültigkeitszeitraum
oder Planvariante 1, Planvariante 2.
Eine neue Version entsteht immer dann, wenn Daten mit einem neuen
Unterscheidungsmerkmal (z.B. neuer Gültigkeitszeitraum oder Planvariante 3) parallel zu schon bestehen Daten erzeugt werden. Werden Daten
einfach nur geändert, bleibt die Version gleich, der alte Zustand wird
lediglich historisiert.
Die im weiteren näher betrachtete zeitliche Versionierung unterscheidet
die Versionen durch den Gültigkeitszeitraum der einzelnen Datensätze.
Hierbei kann zu einem Zeitpunkt immer nur eine Version eines Datensatzes gültig sein.
Versionierung und Historisierung ermöglichen, dass der Zeitpunkt der logischen Gültigkeit eines Datensatzes (Versionierung) vom Zeitpunkt der Erfassung/Bearbeitung (Historisierung) getrennt wird.
Der Oracle Workspace Manager ist in der Lage alle obigen Fälle und auch alle
Kombinationen (z.B. Verwaltung von verschiedenen Planvarianten verschiedener Benutzer für Daten mit Gültigkeitszeiträumen und zusätzlicher Bearbeitungshistorie) abzubilden.
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Historisierung
Wer vor der Aufgabe steht die Historie einer Tabelle abzubilden wird dies wahrscheinlich über die Einführung zweier Spalten z.B. Gueltig_Von und Gueltig_Bis bewerkstelligen. Diese geben den Zeitraum an in dem dieser Zustand des
Datensatzes aktiv war, also z.B. vom Datum des Anlegens bis zum Datum der
ersten Änderung. Die aktuellen Datensätze sind durch einen leeren Gueltig_Bis
Eintrag gekennzeichnet.
Empno
7788
7839
7839
Ename
SCOTT
BOSS
KING
DeptNo
20
10
10
Gueltig_Von
23-JAN-2000
23-JAN-2000
12-JAN-2004
Gueltig_Bis
12-JAN-2004
Dieses Beispiel zeigt folgende Konstellation: der erste und dritte Datensatz wurden am 23.01.2000 angelegt. Für den zweiten Datensatz wurde am 12.01.2004
der Name von ‚BOSS’ auf ‚KING’ geändert.
Führt man ein Update der Daten durch wird nunmehr nicht mehr ein Update
Statement mit den geänderten Daten gegen die Tabelle abgesetzt sondern ein
Update der oben beschriebenen Spalte Gueltig_Bis, deren Wert auf sysdate
gesetzt wird. Die geänderten Daten werden dann über ein Insert Statement mit
den Werten Gueltig_Von = sysdate1 als neue Zeile eingefügt. Ein Delete der
Daten führt anderseits zu einem Update der Gueltig_Bis Spalte. Ein wirkliches
Delete wird in der Tabelle nie durchgeführt.
An diesem Vorgehen erkennt man schon den wesentlichen Nachteil der
Methode. Die Applikation muss nun nicht mehr wie gewohnt Insert, Update
und Delete Statements absetzten, sondern, wie oben beschriebenen nur Insert
Statements und gleichzeitig ein Update des Zeitraumes. Hat man somit eine
bestehende Applikation oder verwendet eine Standardappliaktion erscheint
eine nachträgliche Erweiterung dieser um eine Historisierung enorm aufwändig wenn nicht gar unmöglich.
Es gibt jedoch noch eine elegantere Lösung des Problems. Basierend auf der
oben genannten Tabelle kann man durch die Erstellung einer View, die genau
die Spalten der nicht historisierten Ausgangstabelle enthält, der Anwendung
eine unveränderte Datenbasis vorgaukeln. Diese View hat die Aufgabe die
jeweils aktuellen Datensätze anzuzeigen. Durch einen Instead-of-Trigger an dieser View für Insert, Update und Delete könnte man zudem die oben beschrieben Umsetzungen der DML Statements erreichen.
Betrachtet man nun den Workspace Manager (dessen API über das Package
dbms_wm aufgerufen werden kann), so lässt sich durch den Befehl
execute dbms_wm.EnableVersioning('EMP','VIEW_WO_OVERWRITE');
für eine Tabelle nachträglich eine Historisierung anlegen2. Durch den zweiten
Parameter, der per Default nicht gesetzt ist, gibt man an, dass die historisierten
Daten nicht überschrieben werden sollen (without overwrite)3.
Der Workspace Manager erzeugt im Hintergrund genau die oben beschriebenen
Objekte. Es wird:
• Die Tabelle umbenannt (hier in EMP_LT)
• Dieser neuen Tabelle neue Spalten angehängt (unter anderem Gueltig_Von,
Gueltig_Bis)
• Eine View mit dem der ursprünglichen Struktur (identische Spalten) und dem
ursprünglichen Namen der Tabelle erzeugt
• Für diese View Instead-Of-Trigger für Insert, Update, Delete erzeugt (falls für
die Ausgangstabelle Unique-, Check- oder Foreign-Key-Constraints definiert
waren wir in diesem Trigger auch Code erzeugt um die Funktionalität dieser
Constraints nachzubilden)
• Weitere View – wie z.B. EMP_HIST, EMP_LOCK – werden angelegt.
–––––––––
1 Um
zu vermeiden, dass zu einem Zeitpunkt zwei gueltige Datensätze existieren, wird angenommen, dass
das Intervall Gueltig_Von, Gueltig_Bis an einer Seite offene Intervallgrenzen hat, d.h. dass entweder der
Anfangs- oder der Endwert nichr mehr zum Gültigkeitsintervall dazuzählen. Alternativ kann man auch
gschlossene Intervalle und Grenzen Sysdate + 1 Sekunde bzw. unter Verwendung des Timestamp Datentyps + 1 ms wählen. Aus Gründen der Übersichtlichkeit wurde die Darstellung hier auf Tage beschränkt.
2 Voraussetzung hierfür ist, dass die Tabelle einen Primary Key besitzt der für einen Datensatz im folgenden
auch nicht mehr geändert werden kann.
3 Durch Setzten des zweiten Parameters auf VIEW_W_OVERWRITE besteht auch die Möglichkeit, nicht die
komplette Historie der Daten zu speichern, sondern immer nur den letzten historisierten Wert eines Datensatzes.
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Abb.2: Aktivierung des Workspace Managers für eine Tabelle
Der einzige Unterschied, den der Anwender bzw. die Anwendung bemerkt, ist,
dass EMP nun eine View ist und nicht mehr eine Tabelle.
Ein einfaches Select Statement gegen EMP liefert weiterhin die aktuellen Datensätze zurück.
Möchte man den Zustand der Daten am 01.01.2003 selektierten, kann man
durch folgenden Befehl den Betrachtungszeitpunkt der aktuellen Session setzten
execute dbms_wm.GotoDate('01-JAN-2003');
Für alle in dieser Session folgenden Statements werden die Daten selektiert, die
am 01.01.2003 aktuell waren d.h. in unserem Falle würde nun das selbe Select
Statement gegen EMP für die Empno 7839 nicht den aktuellen Ename ‚KING’,
sondern den am 01-Jan-2003 gültigen Wert ‚BOSS’ zurückliefern.
Weiterhin kann man, wie bisher, Insert, Update und Delete Statements gegen
EMP absetzen, die dann durch den erzeugten Instead-of-Trigger weiterverarbeitet werden.
Prinzipiell bleiben bei Verwendung des Workspace Managers alle DML Statements für die „Tabelle“ gleich. Der Zeitraum der Betrachtung wird stets durch
vorheriges Setzten einer Variablen definiert.
Durch die Aktivierung des Workspace Managers werden noch zusätzliche Views
erzeugt, wie z.B. EMP_HIST (generell <Tabellenname>_HIST) über die sich die
komplette Historie der Datensätze anzeigen lässt.
Diese durchaus erstaunliche Funktionalität ist aber nicht die eigentliche Aufgabe des Workspace Manager, sondern nur ein gern gesehenes Nebenprodukt
um den eigentlichen Zweck, nämlich die Unterstützung von lang laufenden
Arbeitsschritten zu ermöglichen.
D.h. die Eingaben eines Nutzers wird nicht schon nach dem Commit, sondern
erst nach einer gezielten Veröffentlichung (Merge) für andere Benutzer sichtbar
gemacht. Dies erfolgt nach einer ähnlichen Funktionsweise, wie die eines Versionierungstools.
• Der Benutzer arbeitet so lange autark (auch über Commits hinweg) bis er
seine Änderungen in den Bestand (bzw. in einen anderen Worksspace) einspielt (merge)
• Der Benutzer kann als Basis seiner Arbeit einen Zustand der Daten zu einem
definierten Zeitpunkt wählen oder immer mit den gerade aktuelles Daten
arbeiten.
• Beim Einspielen der Änderungen können Aufgrund von Änderungen der
Bestanddaten Konflikte beim Einspielen der Daten entstehen, die vor dem
Einspielen aufgelöst werden müssen.
Hierfür bietet der Workspace Manager alle benötigten Funktionen zur Verwaltung von Workspaces, deren Zugriffe und dem Lock- und Konflikt-Management. Außerdem bietet er eine effiziente Speicherung der Daten und eine Verwaltungsmöglichkeit über den Oracle Enterprise Manager.
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Abb.2: Workspace Manager Komponenten
Versionierung
Als neuen Feature in Oracle 10g (und auch schon ab Patch 9.2.4.0 für Oracle 9i)
wurde der Valid Time Support des Workspace Managers eingeführt, um Gültigkeitszeiträume von Daten abzubilden z.B. ab 01-JAN-2005 wird BLAKE bis auf
weiteres in der Abteilung 100 tätig sein.
Empno
7698
7698
Ename
BLAKE
BLAKE
DeptNo
20
Vers_Gueltig_Von
01-JAN-2000
100
Vers_Gueltig_Bis
01-JAN-2005
01-JAN-2005
Bei den beiden Datumsspalten handelt es sich nun nicht mehr um die obigen Spalten zur Speicherung der Bearbeitungshistorie, sondern um den
Gültigkeitszeitraum bzw. den Aktivierungszeitpunkt eines Datensatzes.
Die oben betrachteten Datumsspalten zur Verwaltung der Bearbeitungshistorie würden noch zusätzlich benötigt. Auf diese wird aus Gründen der
Übersichtlichkeit ab nun verzichtet.
Mit dem Workspace Manager kann man durch eine weitere Option beim oben
erwähnten Befehl die Unterstützung von Gültigkeitszeiträumen (Valid Time
Support) für eine Tabelle aktivieren:
execute dbms_wm.EnableVersioning('EMP', 'VIEW_WO_OVERWRITE',
FALSE, TRUE);
Der dritte Parameter gibt an, ob es sich bei der Tabelle um eine Oracle Spatial
Topology handelt. Dies ist für unsere Betrachtung unerheblich. Durch Setzten
des vierten Parameters wird der Valid Time Support aktiviert.
Hierdurch erhält die Tabelle eine zusätzliche Spalte WM_VALID zur Speicherung des Gültigkeitszeitraums jedes einzelnen Datensatzes:
SQL> desc emp
Name
Null?
----------------- -------EMPNO
ENAME
DEPTNO
WM_VALID
Type
------------------NUMBER(4)
VARCHAR2(10)
NUMBER(2)
WMSYS.WM_PERIOD
Der Datentyp dieser Spalte4 ist zusammengesetzt aus zwei Feldern, die den Gültigkeitszeitraum als Timestamp mit Zeitzone und somit mit einer Genauigkeit
bis zu einer Millisekunde abspeichern.
SQL> desc wmsys.wm_period
Name
Null?
Type
---------- -------- -------------------------------VALIDFROM
TIMESTAMP(6) WITH TIME ZONE
VALIDTILL
TIMESTAMP(6) WITH TIME ZONE
Ein Select der Tabelle liefert:
EMPNO ENAME DEPTNO WM_VALID(VALIDFROM, VALIDTILL)
----- ------ ------ --------------------------------------7698 BLAKE
20
WM_PERIOD(
'01-JAN-2000 12.00.00.000000 AM +02:00',
'01-JAN-2005 12.00.00.000000 AM +02:00'
)
7698 BLAKE
100
WM_PERIOD(
'01-JAN-2005 12.00.00.000000 AM +02:00',
NULL )
–––––––––
4 WMSYS
ist der Metadatenuser zur Verwaltung aller Workspace Manager Informationen und Objekte
Wert NULL in der ValidTill Spalte gibt an, dass der Wert bis auf weiteres gültig ist, bis er von einem
zukünftigen Wert überschrieben wird
5 Der
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Beim Einfügen von Datensätzen muss man nun den jeweiligen Gültigkeitszeitraum berücksichtigen. Ein einfaches Insert wird damit zu:
INSERT INTO EMP
VALUES (7839, 'KING', 10,
WM_PERIOD(to_date('01-JAN-2010','DD-MON-YYYY'),
to_date('01-JAN-2020','DD-MON-YYYY')
)
);
Das Insert ist nur dann erfolgreich, wenn in der Tabelle selbst und auch in
anderen Tabellen, die über Foreign-Key Beziehungen in Abhängigkeit stehen,
kein Konflikt der Gültigkeitszeiträume entsteht. Der Workspace Manager stellt
also sicher, dass es nicht zwei Versionen der selben EMPNO zu einem Zeitpunkt
gibt und auch, dass z.B. in eine Mastertabelle DEPT die entsprechenden Einträge für DEPTNO zu dem Gültigkeitszeitraum existieren.
Durch den Befehl:
execute dbms_wm.SetValidTime(
TO_DATE('01-JAN-2005', 'DD-MON-YYYY')
);
kann man nun den gewünschten Zeitraum auswählen und sich so z.B. die
Daten auswählen die ab 01.01.2005 gültig sind. Wie schon oben im Falle der
Historisierung, würde ein einfaches Select auf die Tabelle nun nur die dann
gültigen Datensätze zurückliefern.
Man kann die im ersten Teil besprochene Historisierung und die Versionierung
kombinieren und somit auch Fragen der Art beantworten: Wie war der Planungszustand für X am letzten Freitag.
Workspaces
Um nun verschiedene Planungsvarianten (Szenarien) parallel abzulegen und
diese autark zu bearbeiten kann man diese in unterschiedlichen Workspaces
verwalten z.B. Planvariante A in Workspace A und Planvariante B in Workspace
B. Man muss zur Bearbeitung der einzelnen Varianten nun vorher den entsprechenden Workspace auswählen:
execute dbms_wm.gotoWorkspace('A');
Man kann jedoch auch hier über eine spezielle View die Daten aus anderen
Workspaces einsehen und somit einen Überblick über alle Versionen eines
Datensatzes erhalten.
Empno
7698
7698
7698
Ename
BLAKE
BLAKE
BLAKE
DeptNo
20
100
200
Vers_Gueltig_Von
01-JAN-2000
01-JAN-2005
01-JAN-2005
Vers_Gueltig_Bis
01-JAN-2005
NULL
NULL
Workspace
A
B
Es existiert ein besonderer Workspace (‚LIVE’) der den aktuell zusammengeführten, konfliktfreien Zustand der Daten darstellt. Im obigen Beispiel müsste
man sich irgendwann entscheiden welcher der Planvarianten, A oder B, zum
Zuge kommt und entsprechend die Inhalte dieses Workspaces in den Workspace LIVE überführen (Merge). Falls hierbei Konflikte auftreten, z.B. dass
schon ein Eintrag für die EMPNO zum selben Zeitpunkt vorhanden ist, muss
dies beim überführen vom Anwender aufgelöst werden.
STREDA.X
Im geographischen Auskunftssystem STREDA.X der DB Netz AG wird die heutige Ausstattung des Streckennetzes der Deutschen Bahn mit Elektronischen
Stellwerken abgebildet. Zusätzlich werden dort die Planungszustände d.h. die
mittelfristige und langfristige strategische Abdeckung des Streckennetzes mit
Elektronischen Stellwerken definiert.
Der Workspace Manager mit der Funktionalität Valid Time Support wird
sowohl bei der Verwaltung des Streckennetzes, als auch bei der Definition der
Wirkbereiche der Stellwerke verwendet. Zum Beispiel erscheint eine Neubaustrecke im System erst ab dem Zeitpunkt zu dem sie in Betrieb geht. Im umgekehrten Fall wird eine in Zukunft rückgebaute Strecke für zukünftige Planungen nicht mehr angezeigt.
Des weiteren wird die Erstellung der Stellwerke und die Ausdehnung ihrer
Wirkbereiche in der Anwendung zeitbezogen erfasst. Somit können in
STREDA.X auch zeitbezogene Auswertungen vorgenommen werden, wie z.B.
eine geographische Auswertung zum Stand des Ausbaus im Jahre 2008.
Randbedingungen
Natürlich gibt es einige Randbedingungen, die durch den Einsatz von Views
mit Instead-of-Trigger einhergehen. Die Verwendung mancher Befehle wie z.B.
truncate table emp;
führt natürlich bei der Verwendung einer View emp zu einem Fehler.
Es gibt einige weitere Befehle und Features, die bei genauerer Betrachtung nicht
mit Views, bzw. nicht mit nicht updateable Views funktionieren:
• Returning Clause für DML Operationen
• Select der Rowid
• Merge Statement
Wer in seiner Anwendung solche Befehle verwendet, muss mit Anpassungen
der Anwendungen rechnen.
Database
17. Deutsche ORACLE-Anwenderkonferenz
KONFERENZ
Fazit
Der Workspace Manager enthält alle benötigten Funktionalitäten, um die Historisierung und die Versionierung von Daten abzubilden. Er ist als PL/SQL
Packages und Triggern tief im Datenbankkern verankert und ist im Umfang der
Datenbanklizenz enthalten und steht somit jedermann zur Verfügung.
Da sich eine Historisierung mit Hilfe des Oracle Workspace Managers sehr einfach auch nachträglich aktivieren und deaktivieren lässt und keinerlei Änderung der SQL Statements erfordert, kann jeder durch einen Aktivierungsbefehl
(Enable_Versioning) testen, ob der Einsatz für die konkrete Anwendung in
Frage kommt.
Da sich, wie oben beschrieben, auch Konstellationen ergeben, die die Verwendung des Workspace Managers erschweren, sollte der Einsatz trotzdem so früh
wie möglich und am besten schon im Design mitberücksichtigt werden.
Die Versionierung mittels des Valid Time Supports des Workspace Managers
lässt sich zwar genauso problemlos nachträglich einführen, wird aber, da hierfür Eingaben der Benutzer nötig sind, nicht ohne weitere Änderungen auf
Anwendungsseite vonstatten gehen können.
Desweiteren hat die Einführung von getrennten Workspaces noch komplexere
Auswirkungen auf die Anwendung, so dass eine Erweiterung um eine Versionierung und um isolierte Arbeitsbereiche und besonders um die Konfliktauflösung beim Zusammenführen der Arbeitsbereiche nicht einfach nachträglich
möglich ist, sondern zunächst ein Redesign der Anwendung erfordert.
Referenzen
• Oracle Database Documentation,
Application Developer’s Guide – Workspace Manager 10g Release 1 (10.1)
Part No. B10824-01
• DOAG 2002 Vortrag , Versionierung von Datensätzen mittels Oracle Workspace Manager, Hans Viehmann, ORACLE Deutschland GmbH
• http://www.oracle.com/technology/products/workspace_mgr/index.html
Kontaktadresse:
Claus Holzknecht
its-people Hochtaunus GmbH
Nassauer Str. 60
D-61440 Oberursel
Telefon:
E-Mail:
Internet:
+49(0) 172-6117492
[email protected]
www.its-people.de