Load n go Artikel der Ausgabe Februar / März 2012 und Teil 2

Es folgt der Download Code für Abonnenten:

• NEWSolutions-2012-FebruarMaerz_code.zip

Sie benötigen das Upload Programm, um diese Utility auf Ihre i5 AS/400 iSeries zu laden, dieses finden Sie im Load´n´go Bereich.

von Lynne Noll

Vor langer Zeit machte ich die Erfahrung, dass es ein Fehler ist, Programme einzeln und nacheinander zu erstellen. Wenn ich denselben Code an verschiedenen Stellen brauche, frage ich mich, ob ich eine Prozedur daraus machen sollte. Wenn der Code zwar nicht genau derselbe, aber doch sehr ähnlich ist, überlege ich, ob man vielleicht ein Utility daraus machen könnte.

Dynamisches SQL ist ein nützliches Werkzeug für Tools, kann aber abschreckend komplex wirken. Wenn man das Format der zurückgegebenen Daten nicht kennt, muss man mit Arrays und Pointern herumspielen und decodieren, wie die Datenformate aussehen. Ich hatte vor einigen Jahren schon einmal ein Tool veröffentlicht, das alle diese Funktionen abdeckt, und habe dieses Tool in der Zwischenzeit immer wieder erweitert. Inzwischen besteht es aus mehreren Teilen und ist ziemlich komplex. Dieses Tool leistet mehr als man für einfache Aufgaben benötigt, wie z.B. die Prüfung einer Kundennummer im Eingabebildschirm eines CL-Programms.

Viele Programmierer verwenden den Qshell-Befehl DB2 zum Ausführen von dynamischem SQL oder zum Ausführen von SQL-Abfragen in CL. Diese Technik ist zwar leistungsfähig und flexibel, aber sie kann nicht mit QTEMP arbeiten, akzeptiert keine Overrides und zwingt den Anwender zu Tricks, um überhaupt Daten zu empfangen.

Ich stelle in diesem Artikel drei einfache, leicht verständliche Utilities zur Verfügung, die mit dynamischem SQL arbeiten. Auch wer noch nie mit eingebettetem SQL gearbeitet hat, kann sie nachvollziehen:

• ITSQL3RI übergibt einen Zähler.

• ITSQL2RI übergibt ein Datenelement.

• ITPRMPRI übergibt eine Auswahl aus einem universellen Suchfenster.

Falls die Tools Ihren Anforderungen nicht ganz entsprechen, sind sie leicht anpassbar. Sie sind komplett native, können mit Daten in QTEMP arbeiten, reagieren auf Overrides wie gewohnt und liefern ihre Ergebnisse über eine einfache Parameter-Schnittstelle. Aber sie können nicht alles. Jedes Utility erledigt eine Aufgabe in einfach anwendbarer Weise. Um Probleme mit unbekannten Datentypen und Pointern zu vermeiden, übergibt jedes Tool ein gut definiertes Datenelement.

Da die drei Tools auch für die Verwendung in CL-Programmen gedacht sind, sind sie als Programme realisiert, um Ihnen das Binden Ihrer CL-Programme zu ersparen. Das bedeutet, dass die Tools sowohl mit OPM RPG, CL und COBOL als auch mit RPG III und ILE RPG funktionieren. Die beiden Daten-Retrieval-Tools sind hauptsächlich für die Verwendung in CL gedacht, während das universelle Suchfenster überall praktisch ist.

Warum kann ich nicht einfach das Satzformat haben?

Häufig werde ich von Programmierern gefragt, wie man in SQL ein Satzformat abruft, das man vorher noch nicht kennt. Sie wollen das komplette Satzformat in einem zusammenhängenden Speicherbereich, um es dort zu verarbeiten.

Üblicherweise codiert man eine dynamische SQL-Abfrage auf eine bestimmte Tabelle mit einer Syntax wie Select * from MyTable und verarbeitet dann mit einer extern beschriebenen Datenstruktur das Ergebnis. Die Sortierfolge und die Where-Klausel können komplett dynamisch aufgebaut werden. Viele Programmierer haben das schon oft für flexible Abfrageprogramme auf bestimmte Datenbanktabellen, wie z.B. eine Auftragskopfdatei praktiziert. Um alle Felder in einem Datensatz abzurufen, kann man angeben:

Exec sql Fetch Cursor1 into :dsCursor1;

Aber obwohl es so aussieht, als ob SQL es ermöglicht, ein komplettes Satzformat abzurufen, wird eigentlich eine separate Variable für jedes zurückgegebene Feld erwartet. Der SQL-RPG-Precompiler löst alle Variablen in den deklarierten Datenstrukturen auf, auch in solchen, die auf die externe Beschreibung einer Datenbanktabelle referenziert sind. Obwohl es also so aussieht, als ob die SQL-Anweisung ein Satzformat abruft, ist das nur eine Illusion. Der Precompiler muss über eine bestimmte Datenstruktur kompilieren, die später verwendet werden soll; er kann nicht selbstständig zur Laufzeit die extern beschriebene Datenstruktur integrieren. Der Programmierer selbst kann das tun, wie ich in einem früher veröffentlichten Tool gezeigt habe, aber das ist mühsam und selten wirklich nötig.

Man braucht etwas, worauf man zählen kann

Da SQL über einen reichen Vorrat an Funktionen zur Typumwandlung und String-Manipulation verfügt, können SQL-Anweisungen so geschrieben werden, dass sie die Ergebnisdaten im erwarteten Format liefern. Daher kann ein Utility das passende Ergebnis liefern, auch wenn noch nicht vorher bekannt ist, welche Tabellen verwendet werden. Bei komplexen Anwendungen macht dieser Ansatz die SQL-Anweisung zu schwierig, aber bei einfachen Standardanwendungen funktioniert er tadellos.

Ein einfacher Anwendungsfall ist das Prüfen von Benutzereingaben. Normalerweise werden Benutzereingaben mit Werten in einer Stammdatei oder einer Prüfdatei abgeglichen. Wenn z.B. ein Benutzer eine Kundennummer eingibt, wird diese Nummer in der Kundenstammdatei gesucht. Andere denkbare Beispiele wären Firmen-, Abteilungs- oder Lagernummern. Eine Art, mit SQL zu prüfen, ob etwas existiert, ist das Abfragen eines Zählers: Ist das Ergebnis größer als Null, existiert das gesuchte Datenelement. Das Ergebnisformat eines Zählers ist bekannt: Eine Ganzzahl, die niemals NULL (im Sinne von „nichts“) ist. Das Tool ITSQL3RI (Abbildung 1) liefert diesen Zähler.

Das Utility liefert eine gepackte anstatt einer binären Ganzzahl, weil diese in CL leichter zu verarbeiten ist. Das CL-Programm stellt die SQL-Anweisung zusammen, übergibt sie an das Utility und erhält den Zähler und den SQL-Code zurück. (Wie immer bei SQL, müssen Sie auch hier SQL-Code oder SQL-State abfragen.) Falls ein Fehler auftritt, wird eine negative Eins (-1) als Zähler zurückgegeben.

Abbildung 2 zeigt ein Beispiel für ein CL-Programm, das mithilfe dieses Tools einen Zähler abfragt. Beachten Sie, dass in der Anweisung zum Zusammenstellen der SQL-Anweisung die einfachen Anführungszeichen verdoppelt werden, um sie als Bestandteil des Strings zu kennzeichnen. Sehen wir uns an, wie ITSQL3RI funktioniert: Im RPG-Code gibt es eine Variable namens ASTMT für die SQL-Anweisung, die dann im SQL-Code C1STMT heißt. Die Declare- und die Prepare-Anweisung beziehen sich auf den SQL-Namen, wobei die Prepare-Anweisung auch die RPG-Variable anspricht. So müssen dynamische, eingebettete SQL-Abfragen aufgebaut werden. Ein dynamisches SQL-Programm, das einen Cursor verarbeitet, muss den Cursor basierend auf dem Namen der Anweisung deklarieren, die Anweisung mithilfe der Programmvariablen vorbereiten, den Cursor öffnen und dann die Daten über den Cursor abrufen. Diese Schritte sind bei dynamischem SQL mit SELECT-Anweisungen immer gleich. Man kann nicht einfach, wie bei statischem SQL, eine Anweisung SELECT INTO benutzen. Da der Cursor mit ENDMOD deklariert wird, geht er verloren, wenn man zum aufrufenden Programm zurückkehrt, auch wenn das aufgerufene Programm nicht LR setzt oder den Cursor schließt.

Eine andere Möglichkeit, dieselbe Aufgabe zu lösen, bietet eine SQL-Anweisung etwa in der folgenden Form:

Select 1 from CustMaster if custnumber=12345;

Wenn kein Kunde mit der Nummer 12345 existiert, findet die SQL-Anweisung nichts und gibt den SQL-Code 100 zurück, was die Routine veranlasst, den Zähler mit dem Wert 0 zurückzugeben. Wenn der Kunde mit der Nummer 12345 existiert, gibt die Routine 1 zurück. Auch in diesem Fall prüft das CL-Programm nur, ob der Zähler 0 oder größer als 0 ist.

Es gibt noch mehr im Leben als Ganzzahlen!

Da man hin und wieder etwas anderes als einen Integer-Wert erwartet, habe ich ein weiteres Utility geschrieben, das ein Feld zurückgibt. ITSQL2RI (Abbildung 3) liefert wahlweise eine 15-stellige, gepackte Ganzzahl, einen 15-stelligen gepackten Wert mit 5 Nachkommastellen oder einen String mit 500 Bytes. Das Utility versucht nicht festzustellen, welches Format verwendet werden soll. Das CL-Programm, das ITSQL2RI aufruft, teilt dem Utility im zweiten Parameter mit, welches Format verwendet werden soll. Die gültigen Werte für diesen Parameter sind „I“ für Integer, „N“ für numerisch und „A“ für alphanumerisch. (Bei falschen Angaben droht ein Fehler im SQL-Code, also immer prüfen!)

Ich verwende fast immer das alphanumerische Format. Wenn ich mehrere Spalten haben will, kann ich sie zusammenhängen und im CL-Programm mit der Substring-Funktion %SST wieder auseinandernehmen. Dieses Tool liefert nur die Daten aus der ersten abgerufenen Zeile und verliert den Cursor sofort. (Mehr benötigt man in einem CL-Programm normalerweise nicht.) Sie können das Utility aber leicht anpassen, um es mehrfach aufzurufen und mehrere Zeilen abzurufen. Aber dann muss die Anweisung mit *ENDACTGRP erstellt und abschließend einmal aufgerufen werden, um den Cursor freizugeben (was bei vielen Anwendungen umständlich ist).

Wie bereits erwähnt, sind die Tools ITSQL3RI und ITSQL2RI vor allem für die Verwendung in CL-Programmen gedacht. Da RPG über eine sehr gute Embedded-SQL-Variante verfügt, gibt es wenig Grund, diese Tools in RPG einzusetzen, außer wenn Sie ein Programm nicht auf SQLRPGLE umstellen möchten, aber ein einzelnes Datenelement mit SQL abfragen wollen.

Aber ITSQL2RI hat auch noch einen weniger offensichtlichen Verwendungszweck: Es kann zum Prüfen von Ausdrücken genutzt werden. Viele meiner CL-Programme verwenden dieses Tool, um den umfassenden Vorrat von Datumsfunktionen in SQL nutzen zu können und z.B. Datumsberechnungen durchzuführen. Man kann eine beliebige Berechnung in SQL definieren und dann mit einer SELECT-Anweisung ausführen, die sich auf SYSIBM/SYSDUMMY1 bezieht. IBM garantiert, dass diese Tabelle genau eine Zeile enthält. Sie ist explizit für solche Anwendungsfälle gedacht. Somit kann man dieses Tool zusammen mit einer SQL-Anweisung dazu verwenden, Berechnungen mit Konstanten und beliebigen SQL-Funktionen (auch mit selbst definierten) durchzuführen. Abbildung 4 zeigt einen Ausschnitt aus einem CL-Programm, in dem ein Datumsbereich berechnet wird.

 

Das letzte Suchfenster, das Sie jemals programmieren

Also gut, vielleicht. ITPRMPRI genügt in den meisten Fällen meinen Anforderungen an ein Such- und Auswahlfenster, aber ich verwende immer noch ein anderes Programm für die Adressdatei mit 6 Millionen Einträgen. Das Suchfenster kann mit CL- und RPG-Programmen, aber auch von der Befehlseingabezeile aus aufgerufen werden – ich habe es auch als eigenständige Anwendung an manche Menüpunkte gebunden.

Eine Anwendung für das Suchfenster wird mit einem Eintrag in die Anwendungsdefinitions-Tabelle erzeugt. Jede Anwendung hat einen 10stelligen Namen, der von dem Programm übergeben werden muss, das die entsprechende Auflistung anzeigen will. Die SQL-Anweisung für die Auswahl der Daten, der Titel des Suchfensters, die Spaltenüberschriften sowie die Startposition und die Länge des zurückgegebenen Schlüsselwerts werden unter dem Anwendungsnamen in der Anwendungstabelle gespeichert.

Wenn man ein Suchfenster anzeigen will, ruft man ITPRMPRI auf und übergibt die Anwendungs-ID und einen oder zwei Empfangsvariablen für den ausgewählten Wert. Der erste Empfangswert enthält nur den ausgewählten Schlüsselwert, während der zweite die gesamte Zeile liefert. Nur die Anwendungs-ID ist obligatorisch, aber zum Abrufen der Benutzerauswahl muss zumindest der Parameter für den Schlüsselwert übergeben werden. ITPRMPRI liest die unter der übergebenen Anwendungs-ID gespeicherte SQL-Anweisung und führt sie aus. Die Anwendung zeigt das Ergebnis der SQL-Abfrage in einem Subfile-Fenster an, in dem der Anwender seine Auswahl treffen kann. Jede Zeile hat eine Zeilennummer. Um eine Zeilennummer auszuwählen, gibt der Benutzer entweder die Zeilennummer ein oder ein „X“ im Auswahlfeld der entsprechenden Zeile. (Auch die Auswahl per Mausklick ist realisierbar.)

Das Tool enthält auch eine Suchfunktion, weil ein herkömmliches „Positionieren auf“-Feld in einem universellen Suchprogramm naturgemäß schwer realisierbar ist. Wenn etwas im Suchfeld eingegeben wird, zeigt das Suchfenster nur noch die Zeilen an, die den eingegebenen Wert enthalten. Dieses Feature wird unterstützt, indem alle Daten komplett in zwei Subfiles geladen werden. Ein Subfile, das immer verborgen bleibt, dient als Speicher für den vollen Datenumfang, solange eine eingeschränkte Auswahl angezeigt wird. Das Programm greift nicht mehr aus die Datenbank zu, wenn eine eingeschränkte Suche angefordert wird. Dieses Suchverfahren bedingt, dass alle Daten auf einmal geladen werden, weshalb ich das Programm nicht für sehr große Dateien empfehle.

Die Anwendung kann nur Schlüsselwerte zurückgeben, die in den angezeigten Daten enthalten sind. Ich hatte überlegt, zwei Felder zu selektieren: eines mit den anzuzeigenden Daten und ein zweites mit Daten die als verborgenes Feld im Subfile mitgeführt werden, aber das hätte die SQL-Anweisung zum Einrichten einer Anwendung noch komplexer gemacht, als sie ohnehin schon ist.

Zum Zusammenstellen der anzuzeigenden Daten muss die SQL-Abfrage alle gewünschten Feldinhalte in Zeichenketten umwandeln und sie , ergänzt um die erforderliche Anzahl von Leerzeichen, miteinander verketten. Ich erstelle die Anweisungen normalerweise im interaktiven SQL und füge sie dann mit DBU in die Anwendungsdefinitions-Tabelle ein. Da der Anwendungsname der einzige erforderliche Parameter für ITPRMPRI ist, und da das Programm auch von der Befehlseingabe aus aufgerufen werden kann, kann die Anwendung bequem während der Einrichtung getestet werden, um die Darstellung der Daten und die Ausrichtung der Überschriften zu überprüfen.

Variable Selektionskriterien

In meiner Firma will ich mit dem Tool Aufgaben erledigen wie z.B. alle Abfahrtstermine für bestimmte Touren herauszufinden, und deshalb unterstützt es Platzhalter und Ersatzvariablen. In der SQL-Anweisung werden die Platzhalter in der folgenden Form codiert:

Select depart from toursched where tourid=’<<TOUR>>’

Beachten Sie, dass die übergebenen Ersatzwerte getrimmt werden und dass nicht berücksichtigt wird, ob der Platzhalter in Anführungszeichen steht. Dieser Ansatz deckt nicht alle denkbaren Fälle ab, aber er ist einfacher anzuwenden als die Methode von QMQRY – man muss Character-Werte nicht in Anführungszeichen einschließen, bevor sie übergeben werden. In CL kann man die Platzhalter als Konstanten übergeben, aber man muss den Ersatzwert als Variable mit 50 Bytes Länge definieren, um keine unerwünschten Zeichen zu erhalten. In RPG kann man sich einfach auf den Prototyp verlassen.Es können bis zu vier Platzhalter definiert werden, aber die Angabe von Platzhaltern ist wahlfrei. Die Platzhalter können sowohl im Titel und in der Spaltenüberschrift als auch in der SQL-Anweisung verwendet werden. Die Verwendung eines Platzhalters im Titel ermöglicht besser beschriebene Fenstertitel (z.B. „Abfahrtstermine für die Rhein-Mosel-Tour“) antelle generische Titel. Die Platzhalter können mehr als einmal angegeben werden und werden immer durch denselben Ersatzwert ersetzt. Beim Übergeben der Ersatzwerte an das Anzeigeprogramm sind die Größer- und Kleiner-Zeichen nicht anzugeben.

Normalerweise teste ich eine Anwendung von der Befehlseingabe aus mit Konstanten anstelle der Ersatzwerte, um den Test zu vereinfachen. Erst danach füge ich die Platzhalter ein und teste über das aufrufende Programm.

Die beiden nachfolgend dargestellten Anweisungen rufen das Suchfenster auf. Die erste Anweisung übergibt die erforderliche Anwendungs-ID und den optionalen Rückgabewert. Die zweite Anweisung übergibt auch den Parameter zum Abrufen der gesamten Zeile, den Platzhalter ‚PGM‘ und die Ersatzvariable (&REP1). &REP1 wird mit 50 Bytes Länge deklariert – auch wenn der Wert eine Konstante sein könnte, sollte er als 5ß-Byte-Variable übergeben werden. Die im zweiten Beispiel aufgerufene Anwendung PGMSUPL verwendet nur einen Platzhalter/Ersatzwert, aber es sind bis zu vier möglich.

CALL ITPRMPRI (‘DSPOBJD’ &KEY)
CALL ITPRMPRI (‘PGMSUPL’ &KEY &RETURN ‘PGM’ &REP1)

Falls es Sie interessiert: ich habe versucht, die Anwendung über eine User Defined Tabke Function laufen zu lassen, und es hat gut funktioniert. Die SELECT-Anweisung muss gültiges SQL enthalten, das ein einzelnes Feld mit bis zu 70 Stellen liefert. (Tatsächlich können auch längere Werte abgefragt werden, sie werden aber abgeschnitten.)

Beispiele für definierte Suchfenster finden Sie in den Abbildungen 5 und 6. Beachten Sie das aktive FIND-Feature in Abbildung 6. Abbildung 7 enthält die Definition der Anwendung in den vorangehenden beiden Abbildungen. In Abbildung 8 sehen Sie eine Anwendungsdefinition mit Platzhaltern.

Versuchen Sie es selbst

Installieren Sie die Tools, und testen Sie sie in Ihrer eigenen Umgebung. Der Programmcode enthält die beiden RPG-Programme für die SQL-Abfrage-Tools ITSQL2RI und ITSQL3RI und für das Suchfenster, sowie die DDS für das Suchfenster und die Anwendungsdefinitions-Tabelle. Ich bin mir sicher, dass Sie den Nutzen dieser Tools schätzen werden. Wenn Sie sich in der Anwendung der Tools sicher fühlen, können Sie daran gehen, Sie für Ihre speziellen Anforderung anzupassen und nahtlos in Ihre Anwendungen zu integrieren.

Lynne Noll (lvwnoll@hotmail.com) arbeitet als Senior Programmer Analyst bei Vantage World Travel. Sie ist seit 1987 auf IBM i und den Vorgängermodellen tätig.

Übersetzt und für den deutschsprachigen Markt überarbeitet von Mathias Spateneder.