Create Or Replace … DDS mit SQL, Teil 3: Reverse Engineering

Nachdem im vorherigen Artikel sowohl die DDS beschriebenen physischen und logischen Dateien, als auch ein kleines Testprogramm beschrieben wurden, geht es in diesem Artikel um das Generieren des SQL-Codes für die DDS beschriebenen Tabellen. Dabei werden die verschiedenen Möglichkeiten aufgezeigt, wie der SQL-Quellcode über IBM i Access for Windows (vormals Clieng Access), IBM i Access Client Solutions (ACS), sowie die Stored Procedure GENERATE_SQL generiert werden kann.

Bevor die Konvertierung der DDS-beschriebenen Dateien in die SQL-Äquivalente erfolgen kann, müssen zunächst die zugehörigen CREATE-SQL-Statements generiert werden. Dies kann natürlich von Hand geschehen, was jedoch zeitaufwändig und fehleranfällig ist. Einfacher und sicherer ist es jedoch eine der von IBM bereitgestellten Methoden zu verwenden.

Der SQL-Quellcode für ein beliebiges Datenbanken-Objekt (z.B. Tabelle, Views, Trigger, Sequenz) kann, mit einer der folgenden Methoden generiert werden. Dabei spielt es keine Rolle, ob das Datenbanken-Objekt ursprünglich mit SQL oder mit DDS definiert wurde.

• IBM i Client Access for Windows (auch Client Access) – IBM i Navigator – Database
• IBM i Access Client Solutions (ACS) – Schemas, Nachfolge-Produkt von IBM i Client Access for Windows
• Aufruf der Stored Procedure GENERATE_SQL in Bibliothek QSYS2

Reverse Engineering mit IBM i Access Client Solutions (ACS)

IBM i Access Client Solutions ist das Nachfolge-Produkt von IBM i Access For Windows (auch Client Access). Aktuell noch nicht alle Features aus dem Bereich der Datenbank, die im IBM i Navigator integriert sind, in ACS übernommen. Aus diesem Grund werden bis zur endgültigen Fertigstellung von ACS alle Neuerungen und Erweiterungen für die Datenbank in beiden Software-Paketen implementiert.

 

Um den SQL-Code zur Erstellung eines Datenbanken-Objekts mit Hilfe von ACS zu generieren sind die folgenden Schritte erforderlich:

• IBM i Access Client Solutions öffnen und das gewünschte System auswählen.
• Schemas öffnen (Bild 1 zeigt die ACS-Eingangsmaske).
• Schema/Bibliothek öffnen: Sofern das gewünschte Schema/Bibliothek nicht in der Liste erscheint, kann es über Rechtsklick auf Schemas und der Kontext-Menü-Auswahl „Include (Ctrl+Shift+I)“ ausgewählt und in die Liste eingefügt werden.
• Innerhalb des Schemas können die Datenbanken-Objekte abh. vom Objekt-Typ aufgelistet werden.
• Auf das gewünschte Objekt positionieren, Rechtsklick und über das Kontext-Menü Generate SQL auswählen.

Im Bild 2 wurde auf die (DDS-beschriebene) Tabelle BASEDDS positioniert. Über das Kontext-Menü wird Generate SQL (SQL generieren) ausgewählt.

Nach Auswahl Generate SQL kann zunächst festlegt werden, ob das Ergebnis als SQL-Skript erstellt und im Anschluss geöffnet werden soll, oder ob das SQL-Script in einer Quellen-Teildatei gespeichert werden soll.

 

Bild 3 zeigt die Output-Informationen für die Auswahl Generate SQL.

Des Weiteren können eine Reihe von Erstellungs-Optionen ausgewählt werden. Die meisten Default-Werte, können so wie eingestellt verwendet werden. Dennoch gibt es einige Optionen, die beim Reverse-Engineering von DDS-beschriebenen Dateien berücksichtigt werden sollten.

Die wichtigsten sind in der folgenden Liste beschrieben:

• OR REPLACE clause/Klausel: Die Angabe von OR REPLACE ist bei Tabellen/physischen Dateien immens wichtig. Führt man ein CREATE OR REPLACE TABLE-Statement aus, wird sofern die Tabelle/physische Datei ist nicht vorhanden ist, eine neue Tabelle angelegt. Sollte die Tabelle/physische Datei jedoch bereits angelegt sein, werden nur die Abweichungen aus dem SQL-Skript in die Tabelle übernommen. Die Daten innerhalb der Tabelle bleiben dabei ebenso erhalten wie die abhängigen Objekte (z.B. logische Dateien, Indices, Views, Constraints, Trigger)
• Generate Index instead of view (for keyed logical files): Logische Dateien werden von SQL per Default als Views behandelt. Da Views immer ungeschlüsselt sind, die Mehrzahl der logischen Dateien, jedoch über Schlüssel mit native I/O angesprochen wird, ist die Konvertierung von logischen Datei in Views wenig sinnvoll. Wird die Option „Generate Index instead of view“ gesetzt, wird für eine geschlüsselte logische Datei ein CREATE INDEX-Statement generiert. Dabei ist allerdings folgendes zu beachten: SQL Indices können in Verbindung mit native I/O wie beliebige geschlüsselte logische Dateien verwendet werden. In SQL-Statements darf ein SQL Index im Gegensatz dazu nicht angegeben werden.
• Generate Additional Indexes (for keyed physical and logical files): DDS-beschriebene physische Dateien können neben den Spalten-Definitionen noch Schlüssel-Informationen beinhalten. In SQL-Tabellen können dagegen Schlüssel-Informationen nicht direkt integriert werden, sondern lediglich über einen Primary Key Constraint hinzugefügt werden. Setzt man bei der Konvertierung die Option „Generate Additional Index“ nicht, gehen die Schlüssel-Informationen verloren. Es wird lediglich eine Warnung ausgegeben. Wird die Option „Generate Additional Index“ jedoch gesetzt, wird der Source-Code für einen zusätzlichen Index erstellt. Logische Dateien können sowohl Schlüssel als auch SELECT/OMIT Informationen enthalten. Außerdem können in einer logischen Datei, mehrere physische Dateien/Tabellen miteinander verknüpft werden (Joined locial files). Wird die Option „Generate Additional Index“ bei der Konvertierung von logischen Dateien gesetzt, wird für die Schlüssel-Informationen ein zusätzliches CREATE INDEX-Statement generiert. Die Spalten-Auswahl, sowie die Join und Select/Omit-Anweisungen werden in ein CREATE VIEW-Statement mit entsprechenden JOIN-Anweisungen und WHERE-Bedingungen hinterlegt.

Reverse Engineering mit IBM i Client Access for Windows – IBM i Navigator

Sofern die neueste ACS-Version noch nicht installiert und somit die Auswahl Schemas noch nicht vorhanden ist, kann der SQL-Code für Datenbanken-Objekte auch mit Hilfe des IBM i Navigators erstellt werden. Die notwendigen Schritte und die Auswahlmöglichkeiten sind in ACS und IBM i Navigator annähernd identisch. Lediglich der Einstieg ist etwas anders.

Beim IBM i Navigator, wird zunächst mit dem gewünschten System verbunden. Im Anschluss daran wird über die Auswahl Datenbases/Datenbanken ? Schemas das gewünschte Datenbanken-Objekt ausgewählt. Über die Kontext-Menü-Auswahl „Generate SQL“ kann der SQL-Code über die gleichen Auswahl-Optionen wie in ACS erstellt werden.

 

In Bild 4 wurde im IBM i Navigator auf die (DDS-beschriebene) Tabelle BASEDDS positioniert und über die Kontext-Menü-Auswahl Generate SQL (SQL generieren) wird das SQL-Skript generiert.

SQL Stored Procedure GENERATE_SQL

Die Stored Procedure GENERATE_SQL in Bibliothek QSYS2 wurde erst mit einem der letzten Technology Refreshes eingeführt. Mit Hilfe der Stored Procedure GENERATE_SQL kann der SQL Quell-Code für beliebige Datenbanken-Objekten erstellt und in einer Quellen-Teildatei gespeichert werden.

Beim Aufruf der Stored Procedure GENERATE_SQL können bis zu 34 Parameter angegeben werden. Die Parameter und zulässige Parameter-Werte entsprechen den Auswahl-Optionen, die beim Reverse Engineering in ACS bzw. IBM i Navigator gesetzt werden können. Bild 5 zeigt die Parameter-Definition für die Stored Procedure GENERATE_SQL.

Für den Aufruf der Stored Procedure sind lediglich die ersten drei Parameter erforderlich:

• DATABASE_OBJECT_NAME, Name des Datenbanken-Objekts für das der SQL-Code generiert werden soll,
• DATABASE_OBJECT_NAME_LIBRARY, Schema/Bibliothek in dem sich das Datenbanken-Objekt befindet und
• DATABASE_OBJECT_TYPE, Objekt-Typ des Datenbanken Objekts.

Für die restlichen Parameter sind Default-Werte definiert, die immer dann verwendet werden, wenn der entsprechende Parameter nicht übergeben wird.

Trotz vorhandener Default-Werte sollten zumindest die nächsten drei Parameter ebenfalls übergeben werden:

• DATABASE_SOURCE_FILE_NAME, Quellen-Datei in die das SQL-Skript ausgegeben werden soll; Default Quellen-Datei ist Q_GENSQL,
• DATABASE_SOURCE_FILE_LIBRARY_NAME, Bibliothek/Schema in der sich die Quellen-Datei befindet; Default-Bibliothek für die Quellen-Datei ist QTEMP
• DATABASE_SOURCE_FILE_MEMBER., Teildatei in der das SQL-Skript gespeichert werden soll; Default-Quellen-Teildatei ist Q_GENSQL.

Des Weiteren sollten gerade beim Generieren von SQL-Code für DDS-beschriebene Datenbanken-Objekte die folgenden Parameter beachtet und ggf. entsprechend geändert übergeben werden:

• CREATE OR REPLACE OPTION, fügt bei allen Erstellungs-Befehlen für die OR REPLACE zulässig ist, OR REPLACE ein. Für Erstellungs-Befehle für die aktuell kein OR REPLACE angegeben werden kann (z.B. CREATE INDEX) wird ein CREATE-Befehl (ohne REPLACE) generiert.
• ADDITIONAL_INDEX_OPTION, generiert bei logischen Dateien mit SELECT/OMIT-Anweisungen oder gejointen logischen Dateien ein CREATE INDEX-Statement mit den Schlüssel-Informationen sowie ein CREATE VIEW-Statement in dem die JOIN-Anweisungen und WHERE-Bedingungen hinterlegt sind.
• INDEX_INSTEAD_OF_VIEW_OPTION, generiert bei geschlüsselten logischen Dateien ein CREATE INDEX-Statement, anstatt eines CREATE VIEW-Statements.

Detailliertere Informationen über die Stored Procedure GENERATE_SQL bzw. eine genaue Parameter-Beschreibung findet man im IBM Knowledgecenter.

Sofern nicht alle Parameter übergeben werden sollen, müssen, da ab dem vierten Parameter alle Parameter mit Default-Wert definiert wurden, die folgenden Parameter nicht zwangsläufig übergeben werden.

Alternativ können die benötigten Parameter benannt übergeben werden. Dabei wird der Parameter-Name so wie in der Prozedur-Definition hinterlegt angegeben, gefolgt von einem Gleichheits-Zeichen und einem Größer-Zeichen (=>). Im Anschluss daran wir der dem Parameter zuzuweisende Parameter-Wert angegeben. Sobald ein Parameter benannt angegeben wurde, müssen auch alle folgenden Parameter benannt angegeben werden. Die Reihenfolge in der die benannten Parameter aufgelistet werden spielt keine Rolle. Die Wert-Zuweisung erfolgt aufgrund des Parameter-Namens. Für nicht übergebene Parameter wird der Default-Wert verwendet.

Das folgende Beispiel (Bild 6)  zeigt den Aufruf der Stored Procedure GENERATE_SQL mit benannten Parametern.

Bei diesem Beispiel wird der SQL-Source-Code für die Tabelle SALESP in der Bibliothek HSCOMMON10 in die Teildatei SALESP in der Datei QSQLDDLTBL in Bibliothek HSCOMMON10 ausgegeben. Das SQL-Statement wird mit System-Naming-Konventionen generiert. Als Dezimal-Trennzeichen innerhalb des SQL-Statements wird das Komma verwendet. Es wird ein CREATE OR REPLACE TABLE-Statement generiert. Für die in der physischen Datei enthaltenen Schlüssel-Informationen wird ein zusätzliches CREATE INDEX-Statement generiert. Für die übrigen Parameter werden die Default-Werte verwendet.

Ergebnis des Reverse-Engineering für die DDS beschriebene Datei SALESP

Mit Hilfe des Reverse Engineering wird für die physische Datei SALESP das folgende SQL-Skript generiert. Die Warnung SQL150B kann ignoriert werden, da die Reihenfolge der Datensätze bei dieser Tabelle bzw. den Programmen, in denen diese Tabelle verwendet wird, keine Rolle spielt, d.h. der Zugriff erfolgt immer über geschlüsselte logische Dateien.

Die Warnung SQL1506 kann ignoriert werden. Da ein zusätzlicher Index SALESP_QSQGNDDL_00001 generiert wird, gehen die Schlüssel-Informationen nicht verloren. Bevor die Tabelle jedoch konvertiert wird, sollte sichergestellt werden, dass keine RPG oder Cobol-Programme die physische Datei geschlüsselt verarbeiten. Am besten ist es, wenn man den vorgeschlagenen Index vorab generiert und die Zugriffe auf diesen Index umstellt.

Soweit zum Reverse Engineering von DDS-beschriebenen Dateien, aber auch anderen Datenbanken-Objekte. Und nun viel Spaß beim Erzeugen, Überarbeiten und Sichern von SQL-Source-Code zum (erneuten) Erstellen von Datenbanken-Objekten.

Birgitta Hauser