Redbook : IBM i 7.6 Features and Function を読む Chapter.5-⑤ SQLエラー・ロギング機能 (SELF)

5.5.3章に SQLエラー・ロギング機能 (SELF)の解説があります。

データベースにおける最大の課題の1つは、パフォーマンス・モニター実行のオーバーヘッドなしに、SQLエラーと警告を検出、把握、修正することです。SQLロギング・エラー機能（SELF）を使用すると、ステートメントで特定のエラーや警告が発生したタイミングをより正確に把握できます。また、シグナルの調整にも非常に役立ちます。

以下、SELFの特長として、開発者がその時点で必要とするエラー情報だけを選択表示できる（監視対象のコードはグローバル変数SYSIBMADM.SELFCODESにリストしたものだけ、ほかの無用なエラーの表示を除外できる）、ゆえにパフォーマンス影響も最小化できる、ジョブ内でグローバル変数を設定することで、単一のジョブに対して個別に設定したSELFを有効化できる、等を挙げています。

SELFで監視するログレベル・SQLコードの設定のしかた

方法①

VSCodeで、ファイル　→　ユーザー設定　→　拡張機能　から Db2 for IBM i を探します。Db2 for i の中に SQL Error Logging Facility(SELF) の項目があり、設定の確認・変更が可能です。

方法②

または、VSCodeでDb2 for i への接続ジョブ（QZDASOINITジョブ）を表示して右クリック　→　SET Logging Level for SELF を選択して実行することも可能です。

　　↓

方法③

VSCode の任意のSQL実行画面からもSET xxx でセットできます。

SET SYSIBMADM.SELFCODES = SYSIBMADM.VALIDATE_SELF('-514, -204, -501, +30, -199'); 

ACS でのSELFログレベルの設定例・出力結果例

以上の設定はVSCode以外にもACS のSQLスクリプト実行画面などでも同様に設定が可能です。

ACSの場合もステートメント自体は上記のVSCode例と同じです。設定例の画面ショットを添付します。

**上図でSELECT文が大文字で書かれているのがACS SQLスクリプト実行画面からのログ、小文字のselect文の行がVSCodeからのSQL実行でログされたものです。

右にスクロールすると、、VSCodeとACS SQLスクリプト実行画面で,クライアントOSの表記やクライアント側アプリ名が異なってログされています。

※当然ですが、VSCode と ACS SQLスクリプト実行画面で別々なログレベルを指定するとそれぞれのSQLセッションごとに異なるログレベルで記録がされます。

SELFの使用例

SELF設定や利用ユースケースのサンプルSQL文
https://www.ibm.com/docs/en/i/7.6.0?topic=tools-sql-error-logging-facility-self

SQLエラー・コードのリファレンス

SQLエラーコードの参照URLも記載がありました。
https://www.ibm.com/docs/en/i/7.6.0?topic=codes-listing-sql-messages

こちらの方が調べやすいかも？
SQLメッセージ・ファインダー
https://www.ibm.com/docs/ja/i/7.6.0?topic=codes-sql-message-finder

SELFでモニターするSQLエラー・コードの指定方法

SELFでモニターするSQLエラーは環境変数としてSQLを使用して設定します。

SQL実行時に返されるすべてのSQLCODEをマスターリポジトリQSYS2.SQL_ERRORTに記録する

CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES VARCHAR(256) DEFAULT '*ALL';

SQLコード -913（行またはオブジェクト…が使用中）および-204（&2内の&1…が見つかりません）をQSYS2.SQL_ERRORTに送信する

CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES VARCHAR(256) DEFAULT '-913, -204';

ちなみに、ACSの場合、下記のように values SYSIBMADM.SELFCODES を実行することでSELFでモニターしている設定値を確認できます。

VALIDATE_SELFスカラー関数

上記のコマンドで設定したSELFで指定した構文の妥当性をチェックする関数です。以下のように実行します。

VALUES SYSIBMADM.VALIDATE_SELF('-913, -204');

結果例は、下記です。エラーが無い場合特にメッセージは出ないようです。

試みに存在しないSQLコードを指定してもエラーは出ないので（下記では'0908'(SQL0908)）、現時点ではそのような仕様のようです。

SELFで設定したSQLエラーを確認してみる

エラーはQSYS2.SQL_ERROR_LOGに出力されるので、確認してみます。
RedbookのサンプルSQLは下記ですが、今回はWHERE句以降を省略しました。

SELECT *
FROM QSYS2.SQL_ERROR_LOG
WHERE JOB_NAME = 'jobnumber/jobuser/jobname'
ORDER BY LOGGED_TIME DESC;

実行例がこちら

実行したSQL文、日付などが分かります

サーバー側のジョブ情報に加えて、クライアント側の以下の情報も取れています。
クライアントのOSバージョン Windows11
クライアントのSQL実行アプリケーション　ACS SQLスクリプト実行（日本語化けてますが）
クライアントのプログラムID ACS 1.1.9.9

さらには以下もログされています。
クライアント(Windows11)のログインユーザー名
クライアントワークステーション名
該当ジョブ(ACS SQLスクリプト実行画面）のログイン時刻

Redbookのサンプルに戻ると、下記のようなエラーが出ており、SQLアプリケーションのデバッグに有用である、との説明があります。

上記のステートメントを実行すると（ジョブの適切な詳細を代入すると）、SQL内で何が起こっているかをより深く理解するための出力が得られます。下の図は、問題の1つがSQLCODE -803（「重複するキー値が指定されました」）であることを示しています。2つ目のSQLCODEは -199（「キーワードが予期されていません」）です。

SELFのログファイル QSYS2.SQL_ERRORT のクリア

SELFでログされたSQLログはQSYS2.SQL_ERRORT(QSYS2.SQL_ERROR_LOGと同一だと思います：確認します。)に追加され続けるので、適時クリア―する必要があります。
以下のサンプルSQLを適時修正して古いログを削除します。

DELETE FROM QSYS2.SQL_ERRORT
  WHERE DATE(LOGGED_TIME) < CURRENT DATE - 30 DAYS;

上記では30日より古いログを削除できます。

SELFのロギングを停止する方法

以下のSQLを実行します。

CREATE OR REPLACE VARIABLE SYSIBMADM.SELFCODES VARCHAR(256) DEFAULT '*NONE';

上記の結果は values SYSIBMADM.SELFCODESで確認できます。

続きはこちらです。