Protokollierung von Fehlern und Warnungen in SQL-Routinen – Teil 1: Fehler gehören unbedingt abgefangen

Wenn in einem embedded SQL-Statement oder in einer SQL-Routine (Trigger, Stored Procedure oder User Defined Function) ein Fehler auftritt, bricht das Programm (im Gegensatz zu reinen RPG- oder CL-Programmen) nicht ab. Stattdessen wird ein negativer SQLCODE und ein SQLSTATE, der weder mit 00, noch 01, noch 02 beginnt, ausgegeben.

Werden weder der SQLCODE noch der SQLSTATE geprüft, läuft das Programm unbehelligt weiter, was zu unerwarteten Ergebnissen führen kann. Deshalb ist es wichtig, Fehler abzufangen und zu behandeln.

Da jedoch nicht alle Fehlersituationen vorhersehbar sind, sollten Fehler und Warnungen zusätzlich protokolliert werden, so dass jederzeit nachvollzogen werden kann, was tatsächlich passiert ist. Bevor auch nur ein Fehler protokolliert werden kann, sollte man wissen, wie SQL-Fehler und Warnungen abgefangen und behandelt werden können.

Continue, Exit und UnDo Handler

In SQL-Routinen können Fehler in Condition Handlern behandelt werden. Ein Condition Handler wird aktiviert, sobald ein bestimmter SQLSTATE ausgegeben wird. Eine Prüfung auf den SQLCODE ist in Condition Handlern nicht möglich. Mehrere Fehlersituationen können in der gleichen Routine in diversen Handlern behandelt werden. Für unerwartete bzw. zuvor nicht behandelte Fehler können im Anschluss weitere Handler definiert werden.

Insgesamt gibt es drei unterschiedliche Handler Typen:

• Continue Handler: Nach der Aktivierung werden die im Handler hinterlegten Statements abgearbeitet. Im Anschluss wird die eigentliche Routine nach dem Statement, das den Fehler verursacht hat, fortgesetzt. Continue Handler werden z.B. gerne eingesetzt, wenn ein nicht existierendes Objekt gelöscht werden könnte oder wenn ein eventuell nicht geöffneter Cursor geschlossen werden soll.
• Exit Handler: Bei einem Exit-Handler werden nach der Aktivierung ebenfalls alle im Handler hinterlegten Statements ausgeführt. Nach Beendigung des Exit Handlers wird die Routine direkt verlassen. Auch bei aktiver Commitment-Steuerung wird an dieser Stelle weder COMMIT noch ROLLBACK ausgeführt. Exit Handler werden verwendet, wenn z.B. ein temporäres Objekt grundsätzlich vor Programm-Ende gelöscht werden soll oder wenn ein Cursor auf alle Fälle vor Programm-Ende geschlossen werden soll.
• UnDo Handler: Auch bei einem UnDo-Handler werden zunächst die im Handler hinterlegten SQL-Statements ausgeführt. Die Routine wird im Anschluss verlassen. Im Gegensatz zu einem Exit Handler werden jedoch, sofern man unter Commitment-Steuerung arbeitet, alle Änderungen durch Ausführung des ROLLBACK-Statements zurückgesetzt. Wird ohne Commitment-Steuerung gearbeitet, verhalten sich Exit- und Undo-Handler identisch.

Condition Handler werden am Ende der DECLARE-Section des Routine-Bodies codiert. In einem Handler kann entweder kein, ein einziges oder mehrere auszuführende Statements hinterlegt werden. Sofern mehrere Statements ausgeführt werden sollen, müssen diese in ein Compound-Statement eingebunden werden.

Compound-Statements beginnen immer mit BEGIN und enden mit END. Zwischen BEGIN und END kann zunächst eine DECLARE-Section, in der u.a. lokale Variablen und Cursor definiert werden können codiert werden. Die auszuführenden Statements folgen nach der DECLARE-Section vor dem END (Ende) des Compound-Statements.

Die Syntax sieht für alle Condition-Handler-Typen aus, wie es in Bild 1 gezeigt ist. Innerhalb der DECLARE-Section können mehrere Condition Handler für unterschiedliche SQLSTATEs hinterlegt werden.

Nicht immer können die Fehlersituationen vorhergesehen werden. Gerade diese unvorhergesehenen Fehler oder auch Warnungen sollten abgefangen und sofern eine Behandlung nicht möglich ist zumindest protokolliert werden.

Sonder-Werte: SQLEXCEPTION, SQLWARNING und NOT FOUND

Da SQLSTATEs für unerwartete Fehlersituationen nicht bekannt sind, werden die folgenden Sonder-Werte bereitgestellt, die in den Handlern anstelle der SQLSTATEs angegeben werden können.

• SQLEXCEPTION: Der Sonder-Wert SQLEXCEPTION kann stellvertretend für alle SQLSTATEs, die weder mit 00 (= ordnungsgemäße Ausführung), 01 (=Warnung) noch 02 (=nicht gefunden) beginnen und die zuvor noch nicht behandelt wurden, verwendet werden.
• SQLWARNING: Der Sonder-Wert SQLWARNING kann für alle Warnungen, bzw. alle SQLSTATEs, die mit 01 (=Warnung) beginnen und bislang noch nicht behandelt wurden, verwendet werden.
• NOT FOUND: Der Sonderwert NOT FOUND kann stellvertretend für alle SQLSTATEs, die mit 02 (=nicht gefunden) und die zuvor nicht behandelt wurden, eingesetzt werden.

Das in Bild 2 gezeigte Beispiel verdeutlicht die Syntax für einen Exit Handler, der im Fehler-Fall aktiviert wird.

Sowohl der SQLSTATE als auch der SQLCODE können für unerwartete Fehler und Warnungen ermittelt werden. Bei dem SQLSTATE handelt es sich um einen 5-stelligen kryptischen Wert, der sich aus 5 Ziffern und/oder Buchstaben von A-Z zusammensetzt.
SQLCODE, SQLSTATE sowie weitere detaillierte Informationen über die Fehlersituation bzw. das zuvor ausgeführte SQL-Statement, können über das SQL-Statement GET DIAGNOSTICS ermittelt werden.

Statement GET DIAGNOSTICS

Mit Hilfe des Statements GET DIAGNOSTICS können Informationen zu dem zuvor ausgeführten SQL-Statement ermittelt werden. GET DIAGNOSTICS kann nicht direkt aufgerufen, sondern nur in embedded SQL-Programmen oder in der SQL-Programmierung in SQL-Routinen verwendet werden.

GET DIAGNOSTICS liefert detaillierte Informationen über das vorherige Statement, die Verbindung (Connection) sowie Bedingungs-Informationen (Condition). Die Informationen werden mit Hilfe von vordefinierten Schlüssel-Worten ermittelt und in Hostvariablen übertragen. Beim Aufruf von GET DIAGNOSTICS können mehrere Schlüssel-Worte angegeben und somit mehrere Informationen gleichzeitig ausgegeben werden. Die Syntax für den Aufruf von GET DIAGOSTICS unterscheidet sich für die unterschiedlichen Informations-Typen minimal.

Für die Statement-Information können unter anderem die folgenden Schlüssel-Worte angegeben werden:

• ROW_COUNT: Gibt die Anzahl der Zeilen zurück, die von dem vorherigen Statement bearbeitet wurden zurück. Sofern es sich bei dem vorherigen Statement um ein INSERT, UPDATE oder DELETE-Statement gehandelt hat, liefert ROW_COUNT die Anzahl der eingefügten, geänderten oder gelöschten Datensätze.
• MORE: Gibt an, ob noch weitere Fehler aufgetreten sind, die behandelt werden können.
• NUMBER: Gibt die Anzahl der Fehler und Warnungen zurück, die bei der Ausführung des vorherigen Statements ausgegeben wurden.

Über das in Bild 3 gezeigte GET DIAGNOSTICS Statement wird die Anzahl der im vorherigen Statement eingefügten, geänderten oder gelöschten Zeilen ermittelt.

Sofern unterschiedliche Informationen über das vorhergehende SQL-Statement ermittelt werden sollen, kann dies im gleichen GET DIAGNOSTICS Statement erfolgen. Die einzelnen Variable=Schlüssel-Wort-Pärchen werden nacheinander durch Kommata getrennt aufgelistet.

Sofern eine Bedingungs-Information (Condition) ermittelt werden soll, muss zusätzlich auch die Bedingung angegeben werden. Für Bedingungs-Informationen über das vorherige Statement kann CONDITION 1 hart angegeben werden.

Zu den Bedingungsinformationen werden u.a. die folgenden Schlüssel-Worte gezählt:

• MESSAGE_TEXT: Gibt den Nachrichten-Text (inclusive aller Variablen-Werte) für Fehler, Warnungen, aber auch die erfolgreiche Ausführung des vorherigen Statements aus.
• DB2_MESSAGE_ID: Gibt die zu dem Nachrichten-Text gehörende Nachrichten/Message-Id aus.
• COLUMN_NAME: Sofern ein Fehler durch eine nicht vorhandene Spalte verursacht wurde, bringt COLUMN_NAME den fehlerhaften Spalten-Namen zurück.
• CONSTRAINT_NAME: Sofern der Fehler durch eine Beschränkung/Constraint-Verletzung verursacht wurde, bringt CONSTRAINT_NAME den Namen der Beschränkung, die verletzt wurde zurück.
• TRIGGER_NAME: Wenn ein Fehler in einem aktivierten Trigger aufgetreten ist, bringt TRIGGER_NAME den Trigger, in dem der Fehler aufgetreten ist zurück.

In dem GET DIAGNOSTICS-Statement (siehe Bild 4) wird gezeigt, wie der Fehler-Text und die zugehörige Message-Id für das vorherige SQL-Statement ermittelt werden.

Eine detaillierte Beschreibung des GET DIAGNOSTICS-Statement inclusive aller zulässigen Schlüssel-Worte kann im IBM i Knowledge Center gefunden werden.

Informationen wie der Nachrichten-Text, der Name des Contraints oder des Triggers, der den Fehler verursacht hat, können für die Protokollierung Fehler bzw. die spätere Fehler-Behebung sehr wichtig sein.

Neben den von GET DIAGNOSTICS bereitgestellten Informationen, könnten noch weitere Informationen für die Protokollierung interessant sein, wie z.B. Welcher Benutzer hat die SQL-Routine ausgeführt oder in welchem Job wurde die SQL-Routine ausgeführt oder auch nur der Name der SQL-Routine.

Special Registers

Über Special Registers kann u.a. der aktuelle Benutzer und die aktuelle Ausführungszeit ermittelt werden. Bei der folgenden Auflistung handelt es sich um eine Teil-Auflistung der aktuell vorhandenen Special Registers:

• USER / SESSION_USER: Gibt den Benutzer zur Laufzeit zurück.
• SYSTEM_USER: Gibt den Benutzer, der die Verbindung zum aktuellen Server aufgebaut hat zurück. SESSION_USER und SYSTEM_USER können unterschiedliche Werte liefern.
• CURRENT USER: Gibt das Benutzer-Profil zurück, das das Programm oder die SQL-Routine ausgeführt hat. Kann von den anderen Special Registers SESSION_USER und SYSTEM_USER abweichen. Sofern das Programm oder die Routine mit der Option Benutzer-Profil = *OWNER erstellt wurde, wird für den CURRENT USER der Eigner des Programms zurückgegeben. Das Special Register CURRENT USER spielt beim Einsatz von RCAC (Row and Column Access Control) eine wichtige Rolle, insbesondere dann, wenn die Zugriffsberechtigung auf die Daten korrekt gesteuert werden müssen.
• CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP: Geben das aktuelle (System-)Datum, die aktuelle (System-)Zeit oder die aktuelle (System-)Zeitmarke zurück.
• CURRENT_TIMEZONE: Gibt die Differenz zwischen der UTC (Universal Time Coordinated) und der System-Zeit auf dem Server aus. Um die UTC zu berechnen, muss lediglich die Zeit-Zone (CURRENT_TIMEZONE) von der aktuellen Zeitmarke (CURRENT_TIMESTAMP) oder aktuellen Zeit (CURRENT_TIME) abgezogen werden.

Detaillierte Informationen über die Special Registers können in dem IBM i Knowledge Center gefunden werden.

Built-In Globale Variablen

Weitere nützliche Informationen können über Built-In-Globale Variablen ermittelt werden. Die Built-In-Globalen Variablen befinden sich entweder in der Bibliothek QSYS2 oder in der Bibliothek SYSIBM und werden vom Datenbanken-Manager bereitgestellt. Der Geltungsbereich (Scope) für Built-In-Globale Variablen ist der Job bzw. die Verbindung. Im Klartext heißt das, dass eine globale Variable in unterschiedlichen Jobs unterschiedliche Werte haben kann. Globale Variablen funktionieren ähnlich wie Datenbereiche in der QTEMP, d.h. auch diese können in unterschiedlichen Jobs mit unterschiedlichen Inhalten bestückt sein.

Die folgende Liste ist eine Teilauflistung der aktuell vorhandenen Built-In-Globalen Variablen:

• JOB_NAME: enthält den Namen des aktuellen Jobs im Format JOB/USER/JOBNR.
• ROUTINE_SPECIFIC_NAME: enthält den Namen der aktuell ausgeführten SQL-Routine.
• CLIENT_HOST: enthält den Host-Namen des aktuellen Clients, so wie er vom System ausgegeben wird.

Detaillierte Beschreibungen der Built-in-Globalen Variablen können im IBM i Knowledge Center gefunden werden:

Auditing Spalten

Seit Release 7.3 können außerdem Auditing Spalten in SQL-Tabellen (nicht in DDS-beschriebenen physischen Dateien!) hinterlegt werden. Auditing Spalten werden mit dem Attribut GENERATE ALWAYS in Verbindung mit einem Special Register oder einer Built-In-Globalen Variablen definiert.

Sofern eine Zeile mit Auditing Spalten eingefügt oder geändert wird, werden die Inhalte der Auditing-Spalten automatisch durch den Datenbanken-Manager aktualisiert.

In dem Code-Beispiel in Bild 5 werden die Auditing Spalten SESSUSER und JOB definiert.

Bei Änderung eines Datensatzes wird in die Spalte SESSUSER der Benutzer der den Datensatz geändert hat übernommen. Ebenso wird bei Änderung eines Datensatzes der qualifizierte Job im Format JOB/USER/JOBNR in dem der Datensatz geändert wurde fortgeschrieben.

Detaillierte Informationen über die Auditing-Spalten können im IBM i Knowledge Center gefunden werden.

Nachdem wir nun alle Einzelteile zusammengetragen haben, werden wir uns im nächsten Teil dieses Artikels mit der Realisierung der Fehler-Protokollierung in SQL-Routinen auseinandersetzten.

Birgitta Hauser

Midrange Techknowletter