既成概念にとらわれずにSQLを考えてみる
ジャーナル・データからさらに情報を引き出す方法を見つける
過去の記事で、SQL を使って、データ・ファイルに基づいてコマンド・スクリプトや HTML Web ページを作成する方法をお話ししました。SQL で SQL スクリプトを生成し、Generate Command Documentation (GENCMDDOC) コマンドを呼び出して、IBM i コマンドのキーワードとヘルプの Web 対応文書を生成する方法をお話ししました。
ただ、面白いのはこれだけではありません。この最新の連載では、新しく、独創性があり、望むべくは生産的な方法で SQL を「既成概念にとらわれずに」使う別の方法をお話しします。今回は、ジャーナルから抽出されたデータを扱う場合に SQL をどのように利用できるか探ってみることにします。
IBM i で監査を制御するコマンド
IBM i では、ジャーナルはさまざまな目的で使います。そのうち、システム監査関数に利用するジャーナルがあります。QSYS/QAUDJRN ジャーナルは、システム上で発生する幅広いアクティビティーの情報を自動取得する場合に使います。この関数を有効にするには、Change Security Auditing (CHGSECAUD) コマンドを使います。このコマンドは、システム全体の監査関数を制御する複数のシステム値を設定する便利な方法です。
また、ライブラリーに常駐する特定のオブジェクトを細分化した設定は、Change Object Auditing (CHGOBJAUD) コマンドで管理することができます。さまざまなオブジェクトについて監査するイベントの種類を指定するには CHGOBJAUD コマンドを使います。オブジェクトを変更する操作だけ監査したり、あるいは、すべてのオブジェクトへのアクセスを監査したりできるようオブジェクトを構成することができます。
また、Change Auditing Value (CHGAUD) と Change DLO Auditing Level (CHGDLOAUD) の両方のコマンドを使って、それぞれ IFS オブジェクトと DLO オブジェクトの監査設定を調整することもできます。
こうした監査関数を有効にした結果、システム上で発生しているあらゆる種類のイベントを記録する、情報豊富なジャーナル項目のセットが得られます。
セキュリティー・ソフトウェア・パッケージや高可用性レプリケーション製品を使っている場合、そうした製品が QAUDJRN ジャーナル中の情報に極めて依存していることに気が付いていることでしょう。このソフトウェアは、ソフトウェア要件を満たすために監査設定を管理していることが少なくありません。監査設定がソフトウェアで管理されている場合、そのソフトウェアを妨害する変更はしないよう、注意してください。
ジャーナル記録変更アクティビティー
ジャーナルは、 4 種類のオブジェクトの詳細な変更アクティビティーをログに記録する場合にも使います。
データベース・ファイル (物理ファイルと論理ファイル)
- データ域
- データ・キュー
- IFS ストリーム・ファイル
これらの種類のオブジェクト、特にデータベース・ファイルを、ジャーナル記録する、たくさんのもっともな理由があります。IBM i DB2 以外のほぼすべての、非常に強力なデータベース実装では、他のシステムではしばしば「ロギング」と呼ばれるジャーナル記録はそのまま指定されています。無効にするオプションはありません。データをジャーナル記録すると、システムがクラッシュしてもデータは失われません。データへの変更はすべて、まずジャーナルに書き込まれ、すぐに強制的にディスク (または、データがディスクに書き込まれるまで変更内容が保存される、バッテリー保護された入出力アダプター書き込みキャッシュ) に書き込まれます。
システムを IPL すると (または独立 ASP が変化すると)、ジャーナルに記録されている変更はジャーナル・オブジェクトに適用されます。クラッシュ直前に行われた変更はすべてデータベースに反映されます。ジャーナル記録がないと、データベースへの変更内容は主記憶に残り、重大なクラッシュから生き延びることができない場合があります。データベースと他のオブジェクトをなぜジャーナル記録するかの 2 つ目の理由は、発生した変更、ユーザー、変更に関連したプログラムの監査履歴を、日時を付けて提供するためです。
注:Websphere MQ for IBM i もジャーナルを利用して、キュー・オブジェクトに保存された情報を追跡します。ただし、この記事では、その製品のジャーナル記録についてはお話ししません。
QAUDJRN 対複数ジャーナル
上記の監査関数はすべて QAUDJRN ジャーナルを使い、ジャーナル・リポジトリーはパーティションごとに 1 つあります。オブジェクト変更アクティビティーの詳細については、好きなだけ異なるジャーナルを作成できます。アプリケーションごとに、ジャーナルが 1 つまたは、ライブラリーごとに、ジャーナルが 1 つあるケースが多いのですが、他のさまざまなアレンジも可能です。SQL CREATE COLLECTION ステートメントを使ってコレクションを作成すると、中に QSQJRN という名前のジャーナルが入ったライブラリーが作成されます。コレクション内のすべてのデータベース・ファイルは、そのジャーナルに自動的にジャーナル記録されます。
ジャーナル項目エレメント
ジャーナルの使用目的を問わず、すべてのジャーナル項目には共通のフォーマット・エレメントがいくつかあります。
- シーケンス番号:
ジャーナル項目には、それぞれ一意の昇順のシーケンス番号が割り当てられています。MAXOPT3 の受信側サイズ・オプションで作成されたジャーナルは 64 ビットのシーケンス番号を使います。このシーケンス番号は 20 桁の 10 進数を提供します。 - タイムスタンプ:
各ジャーナル項目には、イベントが発生して、エントリーがジャーナルに書き込まれた時間を記録するタイムスタンプがあります。 - エントリー・コード:
この 1 文字コードは、ジャーナル項目の汎用クラスを示します。'U' コードは、サード・パーティー・アプリケーションや企業内アプリケーションなど、IBM 以外のコンポーネントで作成されたエントリーを指します。'U' エントリーは、Send Journal Entry (SNDJRNE) コマンドでジャーナルに書き込むことができます。他のコードはすべて IBM コンポーネントにより生成され、中でもオブジェクトの作成、データベースへのアクセスや変更、失敗した認証の試行などのイベントを記録します。 - エントリー・タイプ:
これは 2 文字のコードで、実質的にエントリー・コード内のサブタイプです。ジャーナル項目のタイプをさらに詳しく定義します。
すべてのジャーナル項目に共通のエレメントに加えて、オプションのジャーナル項目ヘッダー・エレメントがあります。ジャーナルが作成される、または取り付けられているジャーナル・レシーバーが変更される場合、Fixed Length Data (FIXLENDTA) パラメーターを指定して、ジャーナル項目ヘッダーにオプションのエレメントを組み込むことができます。これらのオプション・エレメントで最も興味深いエレメントは *JOB、*USR、*PGM、*PGMLIB です。
OUTFILE を使ってジャーナル項目にアクセスする
SQL でジャーナル項目情報にアクセスする場合、2 通りの方法があります。1980 年代から利用できる最も一般的な方法は、Display Journal (DSPJRN) コマンドで OUTPUT(*OUTFILE) を指定して、項目をデータベース出力ファイルに抽出する方法です。この方法では、どの項目を抽出するか選ぶことができる、非常に幅広い選択基準が設けられています。SQL を使ってジャーナル項目情報を照会している場合は、DSPJRN コマンドで最低限の選択基準を使い、フィルター選択操作はデータの照会に使う SQL SELECT ステートメントに任せることができます。DSPJRN メソッドの欠点は、項目が多い場合に、出力ファイルのジャーナル項目に必要なストレージ量が極めて大きくなる可能性があるという点です。
SQL 表関数を使ってジャーナル項目にアクセスする
ibm.co/ql5Esm で説明している最新の IBM i Technology Update を使って、ジャーナル項目にアクセスすることもできます。DISPLAY_JOURNAL SQL 表関数はジャーナル項目データを動的に抽出するため、データの中間ストレージが必要ありません。SQL 表関数は、この場合 IBM により作成されたユーザー定義関数であり、データをプログラム的に抽出して、まるで表のように SQL が使うことができる行のセットとしてそれを表示します。表関数には、ジャーナル名、開始日時、開始ジャーナル・シーケンス番号、さらにジャーナル・コード、ジャーナル・タイプ、オブジェクト、ジョブ、ユーザーなどのフィルターを指定する多数のパラメーターが必要です。
SQL ビューを作成して SQL 表関数を「ラップ」すると便利です。Figure 1 はそうしたビューの例を示しています。DISPLAY_JOURNAL 表関数のフィルター・パラメーターにより、興味のあるジャーナル項目のみの細分化した抽出を行うことができます。パラメーターを空白 (または NULL) にしておくと、その選択基準のフィルター処理は行われません。SQL WHERE 節を使うよりも、表関数パラメーターを使ってフィルター操作を行う方が効率的でしょう。
動作中の SQL 表関数
以下の例では、ジャーナルの SQL 表関数を使って、どのデータベース・ファイルを最も頻繁に変更するか判断しています。この単純な照会により、最も変更されたファイルがわかります。
SELECT OBJECT, COUNT(*) FROM JRN_VIEW WHERE journal_code = 'R' GROUP BY OBJECT ORDER BY 2 DESC
データベース・リカバリーのためにジャーナルを使う
では、もっと複雑な作業を考えてみましょう。物理ファイルまたは SQL 表の項目を含んでいるジャーナルがあるとします。何らかの不幸な状況のせいで、レコードをうっかり削除してしまった、またはレコードが正しく更新されなかったなど、ファイル中のデータを破損してしまったとします。多くの場合、Remove Journaled Changes (RMVJRNCHG) コマンドを使って、行われた変更を「取り消す」ことができます。RMVJRNCHG はジャーナル項目を逆に読み取り、「取り消し」操作を行います。
しかし RMVJRNCHG は、イメージ・ジャーナリングが有効になる前にのみ動作します。更新前イメージには、変更の直前に表示されたレコードの内容が含まれています。デフォルトでは、Start Journal Physical File (STRJRNPF) コマンドは更新前イメージのジャーナル記録は指定しません。更新前イメージが使用できても、変更を削除しすぎるなど、RMVJRNCHG はファイルの扱いに不器用なのかもしれません。データベース・リカバリーに役立つよう、ジャーナル項目データとからめて SQL を使い、必要なデータ修正のみ行い、それ以上でもそれ以下でもなくすることができます。場合によっては、更新前イメージをジャーナル記録していなくても、記録前イメージをリカバリーできる方法をお教えします。
この作業では、まず最初に、ファイルのレコード長を決めます。これは、Display File Description (DSPFD) コマンドで、FILENAME パラメーターの物理ファイルの名前を指定して行うことができます。コマンドの出力が表示されたら、Page Down キーを 2 回押して、Maximum record length 項目を見つけます。私の例では、ファイルのレコード長は 300 バイトと想定しています。次に、Display Journal (DSPJRN) コマンドを使ってファイルに関連したジャーナル項目を抽出し、出力ファイルに出力するよう指示します。
DSPJRN JRN(myjrn) FILE((mylib/myfile)) OUTPUT(*OUTFILE) JRNCDE((R)) OUTFILFMT(*TYPE1) OUTFILE(jrnout) ENTDTALEN(recordLen + 126) RCVRNG(*CURCHAIN)
レコード長が 300 バイトのため、この例の ENTDTALEN パラメーターは 426 バイトになるはずです。追加分の 126 バイトは、ジャーナル・ヘッダー情報の内容に該当します。この時点で必要なデータはすべて揃っていますが、まだファイル修正に使用できるフォーマットにはなっていません。
次の手順は、リカバリーのための新しいファイルを作成することです。ファイルには、ジャーナル・ヘッダー情報を含むフィールドだけでなく、破損ファイルのフィールドも入っています。この SQL ステートメントでリカバリー・ファイルを作成することから始めます。
ALTER TABLE recoveryFile DROP COLUMN JOESD
これにより、DSPJRN 出力ファイルと破損ファイルの JOIN を表す表が作成されます。ファイルにはデータはありません。JOIN は 2 つの別々の表からの列定義すべてを備えた表を簡単に作成できます。新しい表には、すべてのジャーナル・ヘッダー・データ列、さらに破損ファイルの列などの列定義が入っています。
しかし DSPJRN 出力ファイルは、削除の必要がある JOESD という列を導きます。JOESD は Journal Entry Specific Data の略で、ジャーナル・コード 'R' がある項目については、1 つの連続したチャンクのレコード・イメージです。破損ファイルの列はすでにリカバリー・ファイルにあるため、JOESD 列は必要ありません。単純な ALTER TABLE ステートメントでその列を削除できます。
CREATE TABLE recoveryFile AS SELECT jrnout.*, myfile.* FROM jrnout, myfile) WITH NO DATA
表にデータがないため、このステートメントは非常に動作が速いです。ステートメントを実行する場合、データ損失の可能性を示す警告メッセージを受け取る場合があります。このメッセージは無視してかまいません。
次の手順では魔法が起きます。Copy File (CPYF) コマンドを使って Copy the DSPJRN 出力ファイルのデータをリカバリー・ファイルにコピーします。
CPYF FROMFILE(jrnout) TOFILE(recoveryFile) MBROPT(*REPLACE) FMTOPT(*NOCHK) ERRLVL(*NOMAX)
FMTOPT(*NOCHK) オプションが指定されているため、DSPJRN 出力ファイルのフォーマットされていない JOESD レコード・イメージ・データが、破損ファイルから引き出された列のちょうど上にコピーされます。最終結果として、すべてのジャーナル・ヘッダー情報の列、さらに破損ファイルのデータを記述する列を含むファイルが生成されます。
ここから先に進む前に、さらに誤ってファイルを破損した場合に、リカバリーを試すことができるよう、破損ファイルのバックアップ・コピーを取っておきます。
この時点で、SQL をフル活用して、どのレコード・イメージがあなたにとって大切か判断しながら、リカバリー・ファイルのデータを検査し、分析し、サブセットを作成できます。あるレコードが大切だと判断したら、別の CPYF コマンドでそのレコードをリカバリー・ファイルから破損ファイルにコピーできます。以下のコマンドはレコードを 1 つだけ (リカバリー・ファイルの相対レコード nnn) 破損ファイルにコピーし戻します。
CPYF FROMFILE(recoveryFile) TOFILE(myfile) MBROPT(*ADD) FMTOPT(*DROP) FROMRCD(nnn) TORCD(nnn)
FMTOPT(*DROP) により、ジャーナル・ヘッダーに関連した列はコピーされません。
では、破損ファイルにコピーし戻すレコードを、どのように決めるのでしょうか。目的のレコードを 1 つずつコピーしていたら永遠に終わりません。通常は、WHERE 節のある SQL VIEW を作成するか、リカバリー・ファイルから選択した行をデータ登録した一時表を別途作成することができます。WHERE 節は、元のファイルの列とジャーナル項目ヘッダーの列を最大限に活用できます。その後、VIEW または一時表全体を破損ファイルにコピーすることができます。
リカバリー・ファイルには各データベースの変更の変更後イメージだけでなく、変更前イメージからのデータも入っている点に注意してください。変更前イメージは、IMAGES(*BOTH) が STRJRNPF コマンドで指定されている場合、またはコミットメント制御が使われている場合に存在します。データ破損の性質により、変更後イメージまたは変更前イメージのデータをリカバリーできます。変更前イメージがなくても、更新操作または削除操作の変更前イメージが利用できる点に注意して下さい。直前に発生した同じレコードの更新操作または挿入操作を見つけることができれば、その操作の変更後イメージは実質的に、以降の更新操作または削除操作の変更前イメージとなります。
第一線でリカバリーを成功させる
私は、大規模病院などさまざまな顧客先で、このリカバリー手法を使ったことがあります。すべて同じジャーナルに記録された大規模なファイル・セットの抽出中に、異なるツールを使いました。
どのレコード・イメージをリカバリーするか正確に判断するのは、簡単なプロセスではありません。病院の場合は、顧客、ソフトウェア・ベンダーとともに、データがどのように破損したのか何日もかけて調べ、正しいイメージだけを適用するにはどのリカバリー・アプローチが正しいのか、注意深く検討しました。我々の苦労と分析が報われ、データは無事にリカバリーされて、顧客はその結果に大いに満足しました。
このデータ・リカバリー手法は、ある種の列定義があるファイルには効果がない点を覚えておいてください。たとえば、ヌル対応フィールドがあるファイルや Large Object データ型 (BLOB、CLOB、DBCLOB) のあるファイルは、この手法ではリカバリーできません。また、MINENTDTA(*NONE) パラメーター・オプションが Create Journal (CRTJRN) コマンドまたは Change Journal (CHGJRN) コマンドで指定されていない場合、一部のレコード更新操作のジャーナル項目を使うことはできません。項目データは圧縮され、最小化されており、破損ファイルのフォーマットに正しくマップされないためです。
貴重なツール
IBM i のジャーナル記録関数は、SQL で活用できる便利なデータを豊富に提供します。そのデータをマイニングして、システムで何が起きているのか詳細に見つけるか、ジャーナルに記録された情報を使ってデータ破損の後にリカバリーするか、いずれにしても、ジャーナルはツールボックスの貴重なリソースに違いはありません。
