Die meisten Benutzer der AS/400 Datenbank kennen SQL als Alternative zu Query/400, mit der man auch Daten ändern kann. Doch hierbei handelt es sich nur um einen Ausschnitt der standardisierten Datenbankschnittstelle SQL, die so genannte Daten-Manipulations-Sprache SQL (DML). Einige Systemverwalter kennen SQL auch als Alternative zu den DDS- (Data Definition Specifications-) Quelldateien zur Erstellung von Dateien; hierbei handelt es sich dann um die Daten-Definitions-Sprache SQL (DDL). Immer mehr Programmierer kennen mittlerweile Embedded SQL – die Möglichkeit, mit SQL aus RPG, COBOL oder anderen Programmiersprachen auf die Datenbank zuzugreifen. Die Java-Programmierer unter uns kennen den Datenbankzugriff per SQL über JDBC auf DB2/400. In der Welt von Oracle und DB2 UDB auf anderen Plattformen ist SQL auch als Programmiersprache zur Programmierung der Datenbank-Zugriffsschicht weit verbreitet; hier wird mit SQL Stored Procedures und Trigger geschrieben. Nur wenige kennen auf der AS/400 die Möglichkeit, Programme komplett in SQL zu schreiben, obwohl dies schon unter Version 4 eingeführt wurde. Zunächst gab es da lediglich die Stored Procedures; das sind Programme, die mit der SQL-Anweisung „Call“ aufgerufen werden. Diese können seit Version V4R2 auch in SQL programmiert werden. Dann kamen die so genannten User Defined Functions hinzu, die den SQL-Funktionen ähneln: unter Version 4 zunächst als externe Programme, ab Version 5 auch als komplett in SQL geschriebene, per SQL aufrufbare Programme. Diese Möglichkeiten wurden auch deshalb kaum genutzt, da hierfür ein C-Compiler erforderlich ist, der extra zu bezahlen war.
Mit Version 5 kam dann noch die Erweiterung hinzu, Trigger direkt in SQL zu schreiben. Trigger sind Programme, die von der Datenbank automatisch bei der Veränderung von Daten einer bestimmten Datei aufgerufen werden. Mit der Änderung der Lizenzierung der Entwicklungsumgebungen wird auch das Hindernis C-Compiler stark abgesenkt, da nun jeder, der eine Lizenz für die Entwicklungswerkzeuge kauft, alle Compiler installieren darf.
Die Sache mit dem C-Compiler
Zur Verwendung von SQL als Programmiersprache erstellt man zunächst eine Quelldatei, wie für jedes andere Programm im Allgemeinen auch. SQL-Programme werden auf der AS/400 in eine Teildatei einer physischen Quelldatei gespeichert; als Art der Teildatei kann man TXT auswählen, da ohnehin keine Syntaxunterstützung angeboten wird. Diese Quelle muss dann von einem SQL-Interpreter auf der AS/400 abgearbeitet werden; hierzu ist der OS/400 Befehl RUNSQLSTM verwendbar. Bei der Ausführung der Skripte mit dem Batch-Interpreter des OS/400 sollte man darauf achten, dass die Quelldatei nur 80 Stellen verwendet, da sonst unangenehme Seiteneffekte auftreten.
Bei der Ausführung des SQL-Scriptes wird dann, je nach Anforderung in der SQL-Quelle, die SQL-Anweisung Create Procedure, Create Function oder Create Trigger ausgeführt (siehe Abbildung 1).
Beim Ausführen dieser Create-Anweisung durch den SQL-Interpreter wird dann aus der SQL-Quelle im ersten Schritt die Quelle eines C-Programms mit Embedded SQL erstellt. Aus dieser Quelle wird dann eine C-Quelle generiert, in der die eingebetteten SQL-Anweisungen in Aufrufe der SQL Runtime umgewandelt wurden. Abschließend wird dann bei einer Function ein Serviceprogramm, ansonsten ein Programm erstellt und das nun erstellte SQL-Programm in das Repository der Datenbank eingetragen. Wenn dann alles ohne Fehler funktioniert, ist von diesem Ablauf – außer einer gewissen Verzögerung – wenig zu merken.
Für den ersten Schritt muss das SQL Development Kit installiert sein, für die weiteren Schritte wird der C-Compiler und beim Binden die Bibliothek QSYSINC benötigt, die mit Option 13 des Betriebssystems (System Openess Includes) installiert wurde. Für einige Installationen kann es erforderlich sein, entsprechend nachzuinstallieren; zusätzliche Lizenzkosten fallen dabei unter Version 5 für die meisten nicht an.
Am einfachsten ist etwas Neues immer an einem Beispiel zu verstehen – sehen wir uns also ein paar kleine Beispiele an.
SQL-Programme
Als SQL-Programm wollen wir nun ein kleines Programm schreiben, das einen Satz in die Datei „Kunde“ schreibt; die Feldinhalte werden als Parameter übergeben. Das Programm kümmert sich darum, den nächsten Schlüssel aus einer entsprechenden Verwaltungsdatei AUTOINC zu holen und selbige entsprechend zu aktualisieren. Die Erstellungsskripte der beiden Dateien KUNDE und AUTOINC verdeutlichen den Aufbau dieser beiden Dateien (siehe Abbildung 2).
Eine SQL Procedure wird mit der SQL-Anweisung Create Procedure erstellt. In der Deklaration werden der Name der Prozedur, die Parameterschnittstelle und weitere Optionen angegeben (siehe Abbildung 3).
Die Implementierung der Procedure wird mit „begin“ und „end“ geklammert und kann innerhalb dieses Blocks beliebig viele SQL-Anweisungen enthalten. Die Angabe von SQL unter „language“ erstellt nun eine SQL Procedure; mittels anderer Angaben können auch andere Programme als so genannte externe Procedure eingetragen werden. Die Klausel MODIFIES SQL DATA ist immer dann erforderlich, wenn innerhalb der Procedure Schreiboperationen mit SQL verwendet werden.
Die Beschreibung der Parameter-Schnittstelle zu Beginn der Procedure legt fest, dass alle Felder der Datei KUNDE als Eingabe-Parameter übergeben werden. Bei der Angabe von INOUT kommen Werte auch verändert zurück. Die Parameter sind mit der Angabe in der Schnittstellen-Beschreibung als Variablen bereits deklariert. Dateifelder brauchen ebenfalls nicht explizit deklariert werden (siehe Abbildung 4).
In der Implementierung der Procedure, die sich zwischen „begin“ und „end“ befindet, werden alle SQL-Statements mit einem Semikolon abgeschlossen. Vom Layout her sind selbstverständlich keine Grenzen gesetzt, schließlich handelt es sich nicht um RPG.
In der ersten SELECT-Anweisung wird der zuletzt vergebene Schlüsselwert für KUNDE_ID aus der Schlüsseldatei AUTOINC gelesen und um 1 erhöht. Mit der folgenden UPDATE-Anweisung wird der neue Wert in die Schlüsselverwaltung zurückgeschrieben. Die Commit-Steuerung des aufrufenden Programms sorgt später dafür, dass keine Konflikte auftreten können. Abschließend erfolgt noch das Einfügen des Satzes mit dem ermittelten Schlüsselwert in die Datei „Kunde“.
Dieses SQL Statement in der Quelle wird dann ausgeführt: aus dem interaktiven SQL, aus Operations Navigator oder am Besten mit RUNSQLSTM. Beim darauf folgenden Umwandeln wird dann ein Name für das C-Programm generiert. Wenn die Umwandlung fehlschlägt, sollte man die Druckausgaben prüfen, um festzustellen, was nicht funktioniert hat (siehe Abbildung 5).
In diesem Beispiel sieht man, wie der Aufruf aus SQL heraus erfolgt. Die SQL-Anweisungen zum Aufruf der Prozedur und der Transaktionssteuerung könnten natürlich auch zum Beispiel aus einem RPG Programm mit Embedded SQL oder aus einem Java Programm kommen.
SQL User Defined Functions
Die Datenbank-Abfrage-Sprache SQL verfügt im Standard bereits über mächtige Funktionen. Zusätzlich bieten die meisten Implementierungen gerade bei den Funktionen zahlreiche Erweiterungen an. Neben Spaltenfunktionen (wer kennt nicht SELECT COUNT(*) FROM MyTable) gibt es da noch so genannte Skalare-Funktionen, die Typumwandlungen vornehmen und abgeleitete Werte erzeugen. Ganz allgemein ausgedrückt, ist eine Skalare-Funktion ein Programm, das Aufrufparameter und einen Rückgabewert hat.
Neben den mit der Datenbank mitgelieferten Funktionen kann man weitere selber schreiben: auf der AS/400 in einer beliebigen ILE-Sprache oder eben wieder in SQL. Bei der Erstellung von SQL-Funktionen wird wieder eine C-Quelle generiert und dann tritt erneut der C-Compiler in Aktion, der im Unterschied zu Stored Procedures ein Service-Programm mit der Objektart *SRVPGM erstellt (siehe Abbildung 6).
Der Aufbau der Quelle einer SQL-Function ähnelt dem Aufbau einer Quelle einer Procedure. Die Function wird mit der SQL-Anweisung CREATE FUNCTION erstellt. In dieser SQL-Anweisung wird zuerst der Name der Function und die Parameterschnittstelle deklariert, die zusammen die so genannte Aufruf-Signatur bilden. Es kann mehrere Functions mit gleichem Namen und unterschiedlicher Struktur der Parameterschnittstelle geben; zur Laufzeit wird dann diejenige ausgewählt, die exakt zur übergebenen Parameterliste passt. Man spricht hier auch vom überladen einer Function. Den Java-Programmierern unter den Lesern kommt das sehr bekannt vor, den RPG-Programmierern eher fremd – bei der fehlenden Typgebundenheit ist dies auch für RPG-Programme schwer vorstellbar.
Zusätzlich zu den Übergabeparametern, in denen keine Information zurückfließt, muss noch der Rückgabetyp deklariert werden, der nicht Bestandteil der Signatur ist. Die Angabe von SQL als Sprache unterscheidet SQL Functions von so genannten externen Functions, die in einer anderen Programmiersprache geschrieben sein können. Die implementierenden SQL-Anweisungen werden wieder mit einer BEGIN-END-Klammer zu einer einzigen Anweisung zusammengefasst.
In unserem kleinen Beispiel soll wieder die Generierung von Schlüsselwerten programmiert werden. Die Function bekommt den Namen GET_KEY, als Übergabeparameter werden der Name der Datei und das Schlüsselfeld übergeben und die Funktion gibt dann den Schlüsselwert als Integer zurück. Die Übergabeparameter sind als VARCHAR mit maximaler Länge 18 deklariert; es empfiehlt sich generell Zeichenketten in Parametern von SQL-Funktionen als VARCHAR zu deklarieren, damit keine unerwünschten Überladungseffekte auftreten können. Diese machen sich meist mit der Fehlermeldung „Function *N nicht gefunden“ bemerkbar (siehe Abbildung 7).
Zu Beginn der Quellbestimmungen befinden sich Variablen-Deklarationen, die mit der SQL-Anweisung DECLARE vorgenommen werden. Es können alle SQL-Datentypen verwendet werden, einschließlich der eigenen User Defined Types. Dateifelder brauchen nicht deklariert werden, die Variable „sqlcode“ kann nur referenziert werden, wenn sie explizit deklariert wird. Ist sie deklariert, wird sie automatisch vom Datenbanksystem zur Ausführungszeit gefüllt.
In unserem Beispiel dient diese Variable dazu, abzufangen, dass noch kein Satz in der Schlüsselverwaltung für die entsprechende Tabelle existiert; dieser wird dann automatisch angelegt und die Schlüsselwerte beginnen mit der Zahl 1.
Zuweisungen werden in SQL-Programmen mit der SQL-Anweisung SET vorgenommen. Hierbei ist darauf zu achten, dass SQL eine Sprache mit strenger Typprüfung ist. Die SET-Anweisung prüft immer, ob der zugewiesene Wert eines Ausdrucks mit dem Typ der Speichervariable verträglich ist; gegebenenfalls muss mit einer der immer vorhandenen CAST-Funktionen eine explizite Typumwandlung vorgenommen werden.
Die Beendigung der Function erfolgt mit der RETURN-Anweisung, mit der auch der Rückgabewert benannt wird, der selbstverständlich wieder mit der Deklaration des Rückgabetyps der Funktion verträglich sein muss. Bei der Ausführung des Create Function Statements wird jetzt ein Serviceprogramm erstellt und in das Repository der Datenbank eingetragen.
Sobald die Function erstellt wurde, kann sie auf den unterschiedlichsten Wegen ausgeführt werden. SQL-Funktionen, egal ob mit der Datenbank gelieferte oder selbst erstellte, werden in Ausdrücke eingebaut und innerhalb von SQL-Anweisungen ausgeführt. Dies kann in Programmen mit Embedded SQL, in SQL-Programmen, aus Java über JDBC, über SQL Call Level Interface-Aufrufe, in Query Manager Queries und sogar über SQL Views erfolgen (siehe Abbildung 8).
Die Function GET_KEY kann zum Beispiel bei einem Einfügen von Sätzen aus einem Subselect genutzt werden, um für jeden einzufügenden Satz einen eindeutigen Schlüsselwert automatisch zu generieren. Selbstverständlich könnte man auch die Stored Procedure aus dem ersten Beispiel unter Verwendung der Funktion GET_KEY vereinfachen.
SQL Trigger-Programme
Einige alte Hasen der RPG-Programmierung kennen in RPG Trigger-Programme, die mit der OS/400-Anweisung ADDPFTRG an eine Datei angehängt werden und dann bei vorher definierten Satzänderungs-Ereignissen vom Datenbanksystem automatisch innerhalb der Datenbanktransaktion ausgeführt werden können. Diese Trigger-Programme bekommen dann einen auf den ersten Blick kompliziert aussehenden Parameter-Bandwurm übergeben, aus dem man sich den Inhalt des Datei-Satzes vor und nach der Änderung auslesen kann. So kompliziert ist dieser Parameter-Mechanismus nun auch wieder nicht, aber kompliziert genug, dass einige beim Update auf Version 5 heftige Probleme mit Trigger-Programmen bekamen, die auf Nachlässigkeiten bei der Verarbeitung der Übergabeparameter zurück zu führen sind.
Mit V5R1 ist nun die Möglichkeit hinzugekommen, Trigger-Programme mit Hilfe von SQL zu erstellen. Bei dieser Erweiterung der SQL-Schnittstelle der Datenbank um Programmier-Elemente ist von vornherein auf die Möglichkeit, externe Programme ins Repository einzutragen, verzichtet worden. Die CREATE TRIGGER-Anweisung zielt ausschließlich auf SQL-Programme ab. RPG oder COBOL-Trigger-Programme werden weiterhin mit ADDPFMTRG eingetragen, mit den entsprechenden Limitierungen auf maximal 6 Trigger-Programme auf Satzebene.
Mit SQL kann man auch Trigger auf Feldebene registrieren und man ist nicht auf ein Programm pro Ereignis beschränkt, allerdings gelten hier andere Restriktionen. Before Trigger dürfen keine Updates vornehmen und die Programme müssen in SQL geschrieben werden. Die Quellen werden dann, wie bei allen SQL Programmen, auf dem bereits besprochenen Umweg über C erstellt.
Für unser kleines Beispiel, Generierung von Schlüsselwerten durch SQL-Programme, führt die Restriktion für die Before Trigger bereits dazu, dass die SQL-Variante schwieriger als ein RPG-Trigger-Programm wird. Grundsätzlich kann man ja die Schlüsselvergabe in ein Trigger-Programm verlagern; beim Einfügen eines Satzes gibt man im anfordernden Programm einen Dummy-Wert für den Schlüssel mit (zum Beispiel -1) und ein Trigger-Programm ersetzt diesen Wert durch einen noch nicht vergebenen Schlüsselwert, der wieder einer Verwaltungsdatei entstammt.
Das Trigger-Programm muss auf jeden Insert reagieren; Update- und Delete-Operationen sind für diese Aufgabe nicht relevant und brauchen nicht beachtet werden. Der Schlüsselwert muss vor dem eigentlichen Insert modifiziert werden; also benötigt man einen Before Trigger. Dieser darf aber dann keinen Update auf die Schlüsseldatei vornehmen – einziger Ausweg: man braucht einen Before und einen After Trigger. Der Before Trigger modifiziert den angeforderten Update, der After Trigger aktualisiert die Schlüsseldatei (siehe Abbildung 9).
Die erhöhte Komplexität mit den zwei Trigger-Programmen wird bei weitem wettgemacht mit der Einfachheit der Programmierung. Das komplexe Geschäft mit dem umständlichen Parameter-Handling entfällt. Die simple Klausel REFERENCING NEW
In unserem After Insert Trigger wird nun die Fortschreibung des Schlüsselpools vorgenommen. Die Referenz auf das After Image liefert nun den Wert aus der Datei „Kunde“, der in dem Before Trigger modifiziert wurde. Damit dieses Verfahren sicher funktioniert, darf niemand dazwischen funken; am einfachsten ist dies über Satzsperren zu gewährleisten: Das anfordernde Programm muss unter Commit-Steuerung mit der Sperrstufe *ALL laufen, dann bleibt der Satz in der Schlüsseldatei vom Lesen bis zum abschließenden Commit in dem aufrufenden Programm gesperrt.
Verwaltung der SQL-Programme
Alle SQL-Programme auf der AS/400 erzeugen letztlich ILE C-Programme mit Embedded SQL und Einträge in das Repository der Datenbank. Dieses Repository ist sozusagen die Datenbank über die Datenbank. Zuweilen nennt man dies auch Metadaten. Wenn man nun hergeht und Programmobjekte von der OS/400-Seite her entfernt, können Inkonsistenzen zwischen dem Repository und der Realität entstehen, die Laufzeitprobleme der schwersten Art hinter sich herziehen können. Das kann soweit gehen, dass Dateien nicht mehr verwendbar sind. Im schlimmsten Fall wird ein Neuaufbau der Repository-Daten mit dem OS/400-Befehl RCLSTG *DBXREF erforderlich.
Damit es gar nicht erst soweit kommt, sollte man strikt vermeiden, an der Datenbank vorbei Änderungen an Datenbank-Objekten vorzunehmen. Solange man alle administrativen Tätigkeiten an den SQL-Programmen über SQL vornimmt, sollte nichts passieren dürfen – ein fehlerfreies Betriebssystem vorausgesetzt. Um Letzteres zu garantieren, gibt es extra Servicepacks für die Datenbank (PTF-Nummer SF99501 für V5R1).
Den Inhalt der Repository-Dateien kann man abfragen; dies geschieht am besten mit SQL, wie es übrigens schon die Codd’schen Regeln für relationale Datenbanken fordern.
Das systemweite Repository befindet sich in der Bibliothek QSYS2 und die passenden Views hierfür heißen SYSPROCS für die Stored Procedures, SYSFUNCS für die User Defined Functions und SYSTRIGGER für die Trigger-Programme. Zum Eingrenzen der angezeigten Programme auf eine bestimmte Bibliothek muss man bei SYSTRIGGER statt SPECIFIC_SCHEMA in der Where-Klausel TRIGGER_SCHEMA verwenden.
Zum Löschen der SQL-Programme dienen spezielle SQL-Befehle. DROP PROCEDURE löscht den Repostitory-Eintrag für die SQL Procedure und das zugehörige Programm. DROP TRIGGER löscht das angegebenen Trigger-Programm und den darauf verweisenden Eintrag.
Bei Drop Function ist noch zu beachten, dass bei überladenen Funktionen die Art der Parameter-Schnittstelle mit spezifiziert werden muss. In unserem Beispiel wären also zum Löschen der Function GET_KEY zwei Löschvarianten möglich. DROP FUNCTION GET_KEY ist nur ausführbar, wenn es nur eine Function GET_KEY gibt. Ist GET_KEY mehrfach mit unterschiedlichen Parameter-Schnittstellen vorhanden, muss DROP FUNCTION GET_KEY (VARCHAR(18), VARCHAR(18)) verwendet werden.
Den Autor Dieter Bender erreichen Sie unter dieter.bender@midrangemagazin.de