Der Überblick zu DDS mit SQL, Teil 1 : Create Or Replace …

In vielen IBM i-Anwendungen ist es auch heute noch so, dass zumindest ein großer Teil, wenn gar nicht alle verwendeten Tabellen/physische Dateien und Indices/geschlüsselte logische Dateien mit DDS (Data Definition Screen) erzeugt wurden und auch weiterhin mit DDS gewartet werden. DDS hat uns über Jahre hinweg treue Dienste geleistet, ist jedoch veraltet und bereits seit Release V5R3M0 stabilisiert. Alle Neuerungen und Erweiterungen, Tabellen und Indices betreffend, werden nur noch in der SQL Data Definition Language (DDL) implementiert. Beharrt man weiterhin auf DDS können viele dieser Neuerungen nicht eingesetzt werden. Als Hauptgrund, weswegen keine Umstellung erfolgen kann, wird meist der Datenzugriff über native I/O angeführt. Native I/O stellt jedoch für die Konvertierung von DDS nach SQL kein Hindernis dar. In diesem Artikel wird gezeigt, welche Schritte erforderlich sind, um DDS-beschriebene Dateien in SQL-definierte Datenbanken-Objekte zu konvertieren.

Viele Anwendungen, die heute auf der IBM i ausgeführt werden, haben ihre Wurzeln in den 80er oder 90er Jahren des letzten Jahrhunderts. Auch wenn es (leider noch viele) Firmen gibt, in denen auf einem Programmierstil aus dem letzten Jahrtausend beharrt wird, existieren genügend andere, die den Wert ihrer selbstgeschriebenen Anwendungen und das darin integrierte Know-how zu schätzen wissen, und die in die Modernisierung ihrer Anwendungen vorantreiben. Green-Screen-Oberflächen werden durch graphische Oberflächen ersetzt. Alte Programme mit fixem RPG-Code werden in Free-Format-RPG konvertiert. Native I/O wird nach und nach durch Embedded SQL konvertiert.

Eine Konstante wird oft bei all diesen Modernisierungs-Konzepten nicht berücksichtigt: die Datenbank. Basis der alten Anwendungen sind DDS-beschriebene physische und logische Dateien, die seit Entstehung der Anwendung (im letzten Jahrtausend!) nahezu unverändert sind. Die vorhandenen Dateien wurden lediglich um neue Felder/Spalten erweitert und zusätzliche logische Dateien angelegt.

Was den Zugriff auf die Daten mit native I/O oder mit SQL angeht, so macht es keinen Unterschied ob die Datenbanken-Objekte mit DDS beschrieben oder SQL definiert sind. Solange die Dateien jedoch weiterhin mit DDS beschrieben sind, können viele Neuerungen, die mit den letzten Releases und Technology Refreshes in die SQL DDL (Data Definition Language) integriert wurden, nicht genutzt werden.

Der erste Schritt, der in Richtung Modernisierung der Datenbank geht, ist daher die Konvertierung von DDS-beschriebenen Dateien in SQL-definierte Tabellen und Indices. Viele Firmen zögern allerdings ein derartige Konvertierung vorzunehmen. Zum einen sehen sie keinen Vorteil in einer Umstellung, da die Dateien ja zumindest vorerst auf die gleiche Art und Weise eingesetzt werden. Zum anderen wird befürchtet, dass eine Konvertierung zeit- und arbeitsintensiv, sowie fehleranfällig ist. Wodurch der reibungslose Ablauf der vorhandenen Anwendung nicht mehr gewährleistet sein könnte.

Die erste Frage, die sich in diesem Zusammenhang ergibt ist: Welche Vorteile bringt eine Konvertierung von DDS auf SQL-DDL?

• DDS ist bereits seit Release V5R3M0 stabilisiert, d.h. es werden keinerlei Änderungen oder Erweiterungen mehr in DDS eingefügt. Bereits seit Release V5R1M0 gibt es Features, die nur in SQL DDL und nicht jedoch in DDS integriert wurden: Zum einen können Identity Columns nur in SQL-Tabellen integriert werden. In Identity Columns wird beim Einfügen des Datensatzes automatisch und unabhängig vom verwendeten Interface (SQL, Native I/O, ODBC, UPDDTA) ein eindeutiger fortlaufender Zähler eingefügt. Zum anderen können Large-Object-Datentypen (CLOB, DBCLOB, BLOB, XML) nur in SQL-Tabellen definiert werden. Alle Arten von Audio-, Video-, Text- und XML-Dateien können in Large Object-Spalten in SQL-Tabellen gespeichert werden.
• Weitere Erweiterungen wie ROW CHANGE TIMESTAMP Spalten können ebenfalls nur in SQL-Tabellen definiert werden. In Zeitmarken-Felder, die mit ROW CHANGE TIMESTAMP definiert wurden, wird automatisch und unabhängig vom verwendeten Interface die Zeitmarke der letzten Änderung gespeichert wird. Seit Release 7.3 können mit der Erweiterung GENERATED ALWAYS und in Verbindung mit Special Registers oder System-Globalen-Variablen, weitere Spalten definiert werden, die automatisch aktualisiert werden. Zu diesen Informationen gehören u.a. der Session-User, Job-Name, Workstation, Host, Routine/Programm.
• Selbst wenn man die zuvor genannten Erweiterungen nicht verwenden möchte, gibt es einen weiteren, wichtigen Grund, warum man von DDS auf SQL umsteigen sollte: DDS-beschriebene physische Dateien und SQL-Tabellen können zwar in der gleichen Art und Weise verwendet werden, dennoch unterscheidet sich die interne Architektur von physischen Dateien und SQL-Tabellen. Beim Schreiben in DDS-beschriebene Tabellen, erfolgt innerhalb des Datenbanken-Objekts keinerlei Prüfung dahingehend, ob die Daten gültig sind. Aus diesem Grund ist es möglich z.B. mit dem CL-Befehl CPYF (Datei kopieren) und der Option Satzformat-Feldübereinstimmung FMTOPT=*NOCHK (ohne Prüfung) ungültige Daten in eine DDS-beschriebene Datei zu übertragen.
  Eine Gültigkeitsprüfung erfolgt erst beim Lesen aus der Datensätze. Ungültige Daten werden dann ggf. konvertiert. Bei SQL-definierten Tabellen ist es genau umgekehrt. Beim Schreiben in die Tabelle werden die Daten auf Gültigkeit geprüft, während beim Lesen keine Prüfung mehr erfolgt. Bei dem Versuch ungültige Daten in eine SQL-Tabelle zu übernehmen, erfolgt ein Abbruch mit entsprechender Fehler-Meldung. Führt man also den CL-Befehl CPYF mit Option FMTOPT=*NOCHK mit ungültigen Daten für eine SQL-Tabelle aus wird der Befehl abgebrochen.
  Der Abbruch erfolgt bei dem ersten Fehler. Folgende, gültige Daten werden nicht mehr übernommen. Vergleicht man die Anzahl Lese-Operationen mit der Anzahl Insert/Update-Operationen, wird man feststellen, dass im Verhältnis wesentlich mehr Lese-Operationen als Insert/Update-Operationen erfolgen. Da beim Lesen aus DDS-Dateien die Daten auf Gültigkeit geprüft werden, während bei SQL-Tabellen keine Prüfung erfolgt, bedeutet dies, dass die Daten aus SQL-definierten Tabellen schneller gelesen werden. Auch wenn der Unterschied an dieser Stelle nur Nano-Sekunden beträgt, die Anzahl der Zugriffe macht den Unterschied.

Nachdem die Entscheidung für eine Umstellung gefällt wurde, stellt sich die nächste Frage: Welche Schritte für die Konvertierung sind erforderlich. Dazu sind die folgenden Phasen zu nennen.

Schritt 1: Reverse Engineering

Um eine physische Datei in eine SQL-Tabelle zu konvertieren, benötigt man zunächst das CREATE TABLE-Statement, um die physische Datei mit einer SQLbeschriebenen Tabelle ersetten zu können.
Man kann das CREATE TABLE Statement natürlich in liebevoller Kleinarbeit manuell erfassen, was jedoch zeitaufwändig und fehleranfällig ist. Zum Glück bietet IBM aktuell zwei Möglichkeiten, um das CREATE TABLE-Statement direkt aus der DDS-beschriebenen Dateien zu generieren.

• Reverse Engineering über IBM i Access Client Solutions (ACS).
• Aufruf der Stored Procedure GENERATE_SQL() in Bibliothek QSYS2.

Mit Reverse Engineering über ACS oder dem Aufruf der Stored Procedure GENERATE_SQL() kann der SQL-Source-Code von allen Datenbanken-Objekten, also auch physischen und logischen Dateien, SQL-beschriebenen Tabellen, Views, Stored Procedures, Trigger etc. , generiert werden.

Der Source-Code kann wahlweise als SQL-Script erstellt und direkt angezeigt werden oder aber auch als PC-, IFS-Datei oder als klassische Quellen-Teildatei ausgegeben werden.

Schritt 2: Prüfen des generierten SQL-Skripts

Es gibt einige wenige Schlüssel-Worte und Optionen, die in DDS definiert werden können, in SQL jedoch nicht unterstützt werden, z.B. DATFMT oder EDTCDE. Sollte das DDS-Datenbanken-Objekt solche Features beinhalten, wird dies im SQL-Skript durch einen entsprechenden Kommentar gekennzeichnet.

Des Weiteren unterstützt SQL keine Schlüssel in Tabellen. Es können lediglich Primary und/oder Unique Key Constraints angelegt werden. Enthält die physische Datei einen eindeutigen Schlüssel, so wird dieser automatisch im SQL Skript in eine Primary Key Constraint konvertiert. Nicht eindeutige Schlüssel werden ignoriert!

Tabellen mit einem primary Key können mit native I/O genauso verwendet werden, wie geschlüsselte physische Dateien. Da für eine Tabelle jedoch nur ein einziger primary Key definiert werden kann, und außerdem weitere Modernisierungs-Schritte und vielleicht sogar ein Redesign geplant ist, sollte der Primary Key nicht verschwendet werden. Stattdessen sollte man eine neue logische Datei (oder besser einen Index) mit dem entsprechenden Schlüssel anlegen. Für physische Dateien mit einem nicht eindeutigen Schlüssel ist ein zusätzlicher Zugriffsweg sowieso erforderlich. Die Programme, die mit native I/O geschlüsselt zugreifen, müssen dann vor der Konvertierung entsprechend angepasst werden.

Schritt 3: Ausführen des SQL Skripts mit CREATE OR REPLACE TABLE

Mit Technology Refresh in Release 7.3 wurde der CREATE TABLE-Befehl um OR REPLACE erweitert. Ein CREATE OR REPLACE TABLE Statement kann mehrfach für die gleiche Tabelle aufgerufen werden, ohne dass die Daten in der Tabelle zerstört werden. Abhängige Objekte wie Views oder Triggers bleiben ebenfalls erhalten. Sofern bei Ausführung des CREATE OR REPLACE TABLE-Statements die Tabelle noch nicht existiert, wird sie erstellt.

Sofern die Tabelle bei Ausführung des CREATE OR REPLACE TABLE-Statements bereits existiert, werden lediglich die Abweichung im SQL-Skript gegenüber der aktuellen Tabellen-Beschreibung (z.B. neue Spalten, lange Spalten-Namen, größere Spalten-Länge, anderer Datentyp) in die bestehende Tabelle übernommen. War die Basis eine DDS beschriebene physische Datei, wird diese in eine SQL-definierte Tabelle konvertiert. Die Daten, sowie die abhängigen Objekte (z.B. Indices, Views, logische Dateien, Constraints, Trigger) bleiben erhalten und werden ggf. angepasst.

Nachdem das Ergebnis aus dem Reverse Engineering überarbeitet wurde, kann es aus einer SQL-Umgebung oder mit dem CL-Befehl RUNSQLSTM (SQL-Anweisungen ausführen) ausgeführt werden. Dabei ist unbedingt zu beachten, dass das Erstellungs-Statement OR REPLACE enthalten muss.

Das erneute Erstellen von RPG und Cobol-Programmen ist nach der Konvertierung von DDS nach SQL nicht erforderlich, da die Format-Definition unverändert ist. Die Format-Definition verändert sich auch dann nicht, wenn man gleich in diesem Schritt zusätzlich zu den vorhandenen (kryptischen) kurzen DDS Feld-Namen, lange beschreibende SQL-Namen festlegt.

Nach der Konvertierung der DDS-beschriebenen physische Datei in eine SQL-Tabelle können die Programme sofort, unverändert und ohne erneute Kompilierung ausgeführt werden.

Schritt 4: Konvertierung von geschlüsselten logischen Dateien in SQL-Indices

Der Zugriff auf die Datenbanken-Daten mit Native I/O erfolgt in der Regel über geschlüsselte logische Dateien. Das Gros dieser logischen Dateien enthält lediglich Schlüssel-Informationen. Ge-Jointe logische Dateien oder logische Dateien mit SELECT-Omit-Anweisungen werden meist nur in Ausnahmefällen verwendet. SQL kennt zwei Arten von „logischen“ Dateien, Views und Indices:

• Views sind immer ungeschlüsselt und daher für native I/O nur begrenzt einsetzbar.
• Indices enthalten alle Schlüssel-Informationen. Indices dürfen in SQL-Statements nicht angegeben werden, können jedoch in Verbindung mit native I/O wie geschlüsselte logische Dateien verwendet werden. Mit Release 6.1 wurden für SQL-Indices eine Reihe von Erweiterungen eingeführt, für die es keine Entsprechung in DDS gibt. So können z.B. Schlüssel-Felder mit Hilfe von skalaren Funktionen z.B. UPPER() definiert werden.
  Ebenso können seit Release 6.1 WHERE-Bedingungen zu SQL Indices hinzugefügt werden, die wesentlich mächtiger als SELECT/OMIT-Anweisungen sind und ebenfalls wesentlich einfacher und verständlicher zu codieren sind. Diese Erweiterungen wurden in Release 6.1 in erster Linie für den Zugriff über Native I/O integriert. Ein weiterer Grund, der für die Definition über SQL statt DDS spricht.

Danach sind zunächst die vorhandenen logischen Dateien in SQL Indices zu verwandeln. Für die Konvertierung von DDS beschriebenen logischen Dateien in SQL Indices sind die gleichen Schritte, wie bei der Konvertierung von physischen Dateien erforderlich.

• Über Reverse Engineering zunächst den Source Code für den (neuen) SQL Index ermitteln. Dabei ist die Auswahl der Option „Generate Index instead of view“ wichtig, da nur dann der Erstellungs-Code für einen Index generiert wird. Wird diese Option nicht angegeben, wird eine ungeschlüsselte View generiert, die für native I/O nur eingeschränkt nutzbar ist, erstellt.
• SQL-Skript prüfen
• Da für den Befehl CREATE INDEX die Erweiterung OR REPLACE nicht erlaubt ist, und da logische Dateien von SQL wie Views behandelt werden, müssen die zu ersetzenden logischen Dateien zunächst gelöscht werden. Wird die Auswahl DROP gesetzt, wird automatisch ein DROP (Lösch-)Statement in das SQL Skript eingefügt.
• Im Anschluss das SQL-Skript zur Erstellung der Indices ausführen.

Nach der Erstellung der Indices können die Programme mit Native I/O sofort, unverändert und ohne erneute Kompilierung ausgeführt werden.

Soweit die trockene Theorie zu diesem Thema. In den nächsten Artikeln dieser Reihe werden wir anhand eines Beispiels die Konvertierung von DDS beschriebenen Dateien zu SQL-definierten Tabellen und Views Schritt für Schritt durchgehen. Bis dahin schon mal viel Spaß beim Planen und evtl. auch schon beim Ausprobieren.

Birgitta Hauser