In den vorhergehenden Artikeln haben wir uns intensiv mit den Db2 und IBM Services zur Analyse von ILE (Integrated Language Environment)-Objekten befasst. Im nächsten Schritt wollen wir einen tieferen Blick in die (Service-)Programme werfen und uns dort vorrangig um embedded SQL-Befehle kümmern. Je mehr (embedded) SQL verwendet wird, desto wichtiger wird es, auch diese (Service-)Programme, SQL-Routinen bzw. die enthaltenen SQL-Statements zu analysieren. Zu diesem Zweck wurden zwei (Catalog-)Views und User Defined Table Function (UDTF) bereitgestellt. Um welche Views und Tabellen-Funktionen es sich handelt und wie diese eingesetzt werden können, wird in diesem Artikel gezeigt.
(Embedded) SQL-Statements werden im Source-Code hinterlegt und zum Kompilierungs-Zeitpunkt vom SQL-Pre-Compiler durch API-Aufrufe in ausführbare SQL-Statements konvertiert. Die Informationen über die statischen SQL-Statements sind im Modul, das dann in Programme oder Service-Programme gebunden wird, enthalten. Bei der Erstellung von Modulen/Programmen mit embedded SQL können zusätzliche Kompilierungs-Optionen angegeben werden. Alternativ können diese Kompilierungs-Optionen auch mit Hilfe eines SET OPTION-Statement in dem Quell-Code hinterlegt werden. Alle diese Informationen können mit Hilfe der Views ausgelesen und analysiert werden.
Wird jedoch dynamisches SQL verwendet, ist eine Analyse der SQL-Statements nicht möglich. Dynamische SQL-Statements werden erst zur Ausführungszeit generiert und können somit nicht im Modul/(Service-)Programm-Objekt gespeichert werden. Die Zugriffspläne (Access Plans) für dynamisches SQL werden im SQE Plan Cache gespeichert. Bei der Optimierung der dynamischen SQL-Statements können diese Zugriffspläne zur Validierung herangezogen werden. Einen Bezug zu einem Objekt gibt es jedoch nicht. Der gleiche Zugriffsplan kann von (gleichen, mehrfach generierten) SQL-Abfragen in unterschiedlichen (Service-)Programmen und Aktivierungsgruppen während der Optimierungsphase herangezogen, validiert und upgedatet werden.
SQL ist auch eine einfache prozedurale Programmiersprache, mit der SQL-Routinen (Stored Procedures, Triggers und User Defined (Table) Functions) erstellt werden können. Bei der Kompilierung wird der SQL-Code dann in C-Quellcode mit embedded SQL konvertiert und im Anschluss wird das Objekt durch den C-Compiler erstellt.
Da die Vorgehensweise bei embedded SQL und bei der Programmiersprache SQL identisch ist, kann auch beides mit den gleichen Views und Funktionen analysiert werden.
Für die Analyse von (embedded) SQL (Service-)Programmen und SQL-Routinen sowie statischen SQL-Statements werden die folgenden Views und Tabellen-Funktionen bereitgestellt:
- View SYSPROGRAMSTAT – (Service-)Programm-Objekt Informationen
Die View SYSPROGRAMSTAT liefert für jedes Modul, Programm- oder Service-Programm-Objekt, in dem (embedded) SQL verwendet wird (bzw. das mit dem Befehl CRTSQLXXXI kompiliert wird), genau eine Zeile.
Die View liefert u. a. Objekt-Informationen (z. B. Objekt-Typ/SQL-Typ), Kompilierungs-Informationen, die entweder über den Compile-Befehl (CRTSQLXXXI) oder mit Hilfe des SQL-Befehls SET OPTION in der Quellen-Datei, gesetzt wurden. Außerdem werden die Quellen-Informationen (Teildatei/IFS-Datei) ausgegeben. - View SYSPROGRAMSTMTSTAT – (Service-)Programm-SQL-Statement-Informationen
Die View SYSPROGRAMSTMTSTAT enthält eine Zeile für jedes (mit EXEC SQL) ausgeführte SQL-Statement das in einem Modul/Programm/Service-Programm hinterlegt wurde.
Neben dem auszuführenden SQL-Statement liefert diese View auch noch weitere detaillierte Informationen, z. B. wieviele Host-Variablen verwendet werden, mit welchem Commitment-Level das SQL-Statement ausgeführt wird oder ob sich um ein READ ONLY-Statement handelt. - Tabellen-Funktion PARSE_STATEMENT – Analyse/Prüfung SQL-Statement
Die Tabellen-Funktion PARSE_STATEMENT analysiert ein übergebenes SQL-Statement und liefert eine Liste von Tabellen/Views und Spalten, die in der Abfrage oder in dem übergebenen INSERT-/UPDATE- oder DELETE-Statement verwendet werden.
Die Tabellen-Funktion PARSE_STATEMENT kann in Verbindung mit den Catalog-Views SYSPROGRAMSTAT und SYSPROGRAMSTMTSTAT eingesetzt werden.
Es ist allerdings auch möglich die Tabellen-Funktion PARSE_STATEMENT zur Analyse und Prüfung eines beliebigen SQL-Statements zu verwenden.
VIEW SYSPROGRAMSTAT
Die View SYSPROGRAMSTAT liefert pro Modul/Programm/Service-Programm eine Zeile zurück, in der u. a. auch die Anzahl der verwendeten SQL-Statements in dem Objekt aufgelistet werden.
Manchmal kommt es vor, dass eine Quelle kopiert und dann für das nächste Modul/Programm modifiziert wird. Vielfach wird dabei nicht auf das Quellen-Attribut geachtet. So kann es vorkommen, dass eine Quelle mit dem Quellen-Attribut SQLRPGLE kopiert wurde, die endgültige überarbeitete Quelle später jedoch kein SQL-Statement beinhaltet. Und meist hat man an dieser Stelle vergessen das Quellen-Attribut auf RPGLE zurückzuändern.
Das ist zunächst nicht tragisch, jedoch zumindest unsauber.
Beim Erstellen des Objekts muss das Ganze jedoch unnötigerweise zunächst durch den SQL-Pre-Compiler bearbeitet werden, bevor dann das Objekt mit Hilfe des Language-Compilers (CRTBNDxxx oder CRTxxxMOD) erstellt werden kann.
In dem folgenden Beispiel werden alle (Service-)Programme in der Bibliothek BXOBJ dahingehend geprüft, ob sie tatsächlich SQL-Statements enthalten. Es werden nur (Service-)Programme selektiert, die keine SQL-Statements beinhalten.
Quelle: HauserIn dem vorherigen Beispiel wurden alle (Service-)Programme ermittelt, die mit dem SQL-Pre-Compiler erstellt wurden, jedoch keine SQL-Statements enthalten. Um die Unsauberkeit zu korrigieren, bzw. die Objekt-Attribute zu ändern, müssen zunächst die entsprechenden Quellen-Teildateien ermittelt werden. Solange aus einer Teildatei genau ein (Service-)Programm mit dem gleichen Namen erstellt wird, können die Quellen noch relativ einfach ermittelt werden. Da jedoch mehrere Module (mit unterschiedlichen Namen) zu einem (Service-)Programm-Objekt mit einem anderen Namen gebunden werden können, kann die Quellen-Suche aufwändiger werden.
Die Quellen-Informationen können normalerweise aus der Spalte ORIGINAL_SOURCE_FILE in der View SYSPROGRAMSTAT entnommen werden. Wenn das Modul/(Service-)Programm jedoch keine SQL-Statements beinhaltet, werden keine die Quellen-Informationen ausgegeben, d. h. in der Spalte ORIGINAL_SOURCE_FILE steht ein NULL-Wert.
Die Quellen-Informationen, aus denen die Module generiert werden, die in die (Service-)Programme gebunden werden, können auch über den Service BOUND_MODULE_INFO ermittelt werden.
Wenn man dann die View SYSPROGRAMSTAT mit der View BOUND_MODULE_INFO über die Programm-Bibliothek, den (Service-)Programm-Namen, den Programm-Typen und das Modul verknüpft, können die zu den (Service-)Programmen/Module ohne SQL-Statements gehörenden Quellen-Informationen direkt ermittelt werden.
In dem nächsten Beispiel wird das vorherige Beispiel dahingehend erweitert, dass die Quellen-Informationen aus der View BOUND_MODULE_INFO ermittelt und ausgegeben werden.
Quelle: HauserMit Hilfe der View SYSPROGRAMSTAT können auch die (SQL-)Kompilierungs-Optionen ermittelt bzw. geprüft werden, die entweder direkt im Compile-Befehl angegeben oder über ein SET OPTION Statement gesetzt wurden.
In dem folgenden Beispiel werden wiederum die beiden Views SYSPROGRAMSTAT und BOUND_MODULE_INFO miteinander verknüpft, um sofort auch zugehörigen die Quellen-Informationen ausgeben zu können.
Des Weiteren werden die Inhalte der Optionen ISOLATION = Commitment Steuerung, CLOSE_SQL_CURSOR (SQL-Cursor schließen), DECIMAL_POINT (Dezimal-Trennzeichen), DATE_FORMAT (Datums-Format), TIME_FORMAT (Zeit-Format) und SORT_SEQUENCE (Sortierreihenfolge) ausgegeben.
Über die WHERE-Bedingungen werden alle (Service-)Programme und Module ausgewählt, die von dem firmeninternen „Standard“-Compile abweichen. In diesem Fall werden alle Sätze ausgewählt, die ein von NC (ohne Commit) abweichendes Commitment-Level haben. Ebenso werden alle Objekte ausgewählt, die nicht mit der Kompilierungs-Option CLOSQLCSR = *ENDACTGRP erstellt wurden. Weiterhin werden das Dezimal-Trennzeichen (Standard = *COMMA – Komma), das Datums-Format (Standard = *ISO‘ – JJJJMMTT), das Zeit-Format (Standard HMS oder ISO, d. h. HHMMSS), sowie die Sortierreihenfolge (Standard = BY HEX VALUE) geprüft. Sofern eine dieser Optionen vom Standard abweicht, wird das entsprechende Objekt angelistet.
Quelle: HauserView SYSPROGRAMSTMTSTAT
In den vorherigen Beispielen wurden die (Service-)Programm-Objekte, die embedded SQL-Statements beinhalten analysiert. Im nächsten Schritt wollen wir uns etwas näher mit den SQL-Statements beschäftigen.
In der View SYSPROGRAMSTMTSTAT werden alle (SQL-)Statements aufgelistet, die mit EXEC SQL ausgeführt werden bzw. die in einer SQL-Routine codiert wurden. Diese Statements können wiederum mit SQL durchsucht und gefiltert werden.
Anmerkung:
Das SET OPTION Statement enthält nur Compile und Laufzeit-Informationen und wird zur Laufzeit nicht ausgeführt.
Aus diesem Grund enthält die SYSPROGRAMSTMTSTAT-View auch keine SET OPTION-Statements.
In dem folgenden Beispiel wird in allen (embedded) SQL-(Service-)Programmen und allen SQL-Routinen in der Bibliothek BXOBJ bzw. in allen SQL-Statements in diesen Objekten nach CSRDLTIFS gesucht.
Es wurden zwei Cursor gefunden, die mit CSRDLTIFS beginnen (CRTDLTIFS und CRTDLTIFSALL). Für beide Cursor gibt es in dem Service-Programm BXCHKIFS in der Bibliothek BXOBJ, ein DECLARE CURSOR Statement, ein OPEN, ein FETCH und zwei CLOSE Statements.
Über die Statement-Nr. wird ersichtlich, dass der erste CLOSE jeweils vor dem OPEN liegt.
Das wurde bewusst so programmiert, um sicherzugehen, dass der Cursor vor dem Öffnen auch wirklich geschlossen ist. Ein bereits geöffneter Cursor kann nicht erneut geöffnet werden … und wenn in dem bereits geöffneten seriellen Cursor alle Zeilen verarbeitet wurden, werden keine Daten zurückgeliefert.
Quelle: HauserAnmerkung:
Auch wenn innerhalb einer Quelle mehrere Cursor an unterschiedlichen Positionen in unterschiedlichen Prozeduren sind, werden die Cursor-Definitionen (DECLARE CURSOR-Statements) vom SQL-Pre-Compiler intern vor alle anderen SQL-Statements geschoben.
In SQL-Routinen müssen Cursor in der DECLARE Section, also vor der eigentlichen Verarbeitung definiert werden.
Deshalb haben die DECLARE CURSOR-Statements die niedersten Statement-Nr.
Und noch ein weiteres Verwendungsbeispiel:
In einer Firma wurden für INSERT, UPDATE und DELETE für jede einzelne Tabelle individuelle Prozeduren angelegt. In diesen Prozeduren erfolgen zusätzliche Prüfungen, über die Default-Werte gesetzt werden, Spalten-Inhalte abgeglichen werden oder über die auch geprüft wird, ob ein Datensatz gelöscht werden darf.
Diese Prozeduren sollen zukünftig anstelle der Insert-/Update-/Delete-Statements (unabhängig ob diese mit native I/O oder mit embedded SQL codiert sind) aufgerufen werden.
Leider gibt es immer noch einige Programme in denen anstatt des Prozedur-Aufrufs die Datensätze entweder über native I/O oder mit einem SQL-Manipulations-Befehl (INSERT, UPDATE oder DELETE) fortgeschrieben werden.
Mit dem folgenden Beispiel werden alle direkt ausgeführten SQL -INSERT-, -UPDATE- und -DELETE-Statements in allen (Service-)Programmen und SQL-Routinen (Stored Procedures, Triggers, User Defined Functions) in der Bibliothek BXOBJ ermittelt. Um ggf. gleich eine Korrektur vornehmen zu können, werden zusätzlich die Quellen-Informationen ausgegeben.
Quelle: HauserDies waren einige Anwendungsbeispiele für die Views SYSPROGRAMSTAT und SYSPROGRAMSTMTSTAT über die SQL-Befehle in embedded SQL aber auch in SQL-Routinen ermittelt und geprüft werden können.
In dem nächsten Artikel werden wir uns die Tabellen-Funktion PARSE_STATEMENT genauer ansehen.
Bis dahin schon einmal viel Spaß beim Analysieren Ihrer (embedded) SQL-Programme und SQL-Routinen!
Birgitta Hauser ist IBM Champion und Spezialistin für SQL- sowie RPG-Programmierung.
Frau Hauser gibt regelmäßig Workshops im Rahmen der MIDRANGE ACADEMY.
Sie schreibt regelmäßig für MIDRANGE und den TechKnowLetter. Hier erhalten Sie brandneue, tiefe Informationen zu SQL, RPG und vielem mehr.
Der TechKnowLetter erscheint monatlich. Sechs Ausgaben erhalten Sie für 88 Euro hier.