ジャーナルから有意義な監査情報を取得する
ジャーナルは、データ回復、データ複製、コミットメント制御、そしてもちろん、監査のために使用される非常に有用なツールです。しかし、監査情報を使用しやすい形で取得しようとすると、煩雑になってしまう場合があります。この記事では、簡単な変更の監査に使用できる、任意の表/物理ファイルに対する監査表とその監査表に対応するビューを作成するストアード プロシージャーについて紹介します。
たとえば、EMPLOYEE表のSALARY列に対する変更について監査する場合は（ここでは標準Db2サンプル データベースのEMPLOYEE表を使用）、以下のシンプルなSQLステートメントを使用します。
select audit_type, jodate, jotime, jojob, jouser, jonbr, jopgm,
old_empno, new_empno, old_salary, new_salary
from AUDIT_EMPLOYEE_VIEW
where audit_type in ('INSERT', 'DELETE') or
old_salary $lt;%gt; new_salary
order by joseqn;結果セットは次のようになります。
監査プロセス
監査プロセスには3つの段階があります。
- 必要な監査表およびビューを定義します。監査表は、監査を行いたいジャーナル処理された表ごとに作成する必要があります。このステップは、ジャーナル処理された表の定義が変更された場合のみ繰り返す必要があります（たとえば、表に新たな列が追加された場合など）。
- ジャーナルから監査表へジャーナル項目をコピーすることによって、監査表にデータを取り込みます。これは、1時間ごと、週ごと、月ごと、または必要に応じて随時、実行することができます。
- 必要な監査分析を実行します（上の例を参照）。
ジャーナル監査情報を表に取り込む
ジャーナル表示（DSPJRN）コマンドは、ジャーナル処理されたデータをデータベース ファイルに出力できるようにします。ここで着目しているのは、レコードの変更の監査であるため、以下のような、特定の日付の、特定の表についてのジャーナルからすべてのレコード ジャーナルをコピーするコマンドを使用します。
DSPJRN JRN(SQLSTAND/QSQJRN) FILE((SQLSTAND/EMPLOYEE)) RCVRNG(*CURCHAIN)
FROMTIME('03/01/21') ENTTYP(*RCD) OUTPUT(*OUTFILE)
OUTFILE(QTEMP/JRNOUT1) 結果の表には、ジャーナル項目ごとに行が1行あります。各行には、ジャーナル情報（変更の日付と時刻、変更を実行したジョブおよびプログラムなど）が格納されている複数の列と、行のイメージが格納されている1つの列（JOESD）があります。ジャーナル項目タイプ（JOENTT）列は、行イメージが、挿入、更新、更新前、または更新後のイメージであるかどうかを示します。
ジャーナル情報の列が詳細であるのに対して、JOESD列の内容はそのままではほとんど役に立ちません。
監査表およびビューを作成する
監査表は、DSPJRNコマンドによって生成されるファイルのコピーであり、JOESD列が、ジャーナル処理された表の列定義によって置き換えられています。
監査ビューは、前および後のイメージを組み合わせる監査表のビューであるため、それぞれの行で、影響があった行のoldおよびnewのイメージが提供されます。削除された行はold列に表示され、挿入された行はnew列に表示されます。
監査表とビューのスキーマは、別々にすることをお勧めします。そうすることにより、必要に応じて監査情報のセキュリティを確保しやすくなり、また、データがバックアップに含められるかどうかを制御しやすくなります。ただし、これはジャーナルが保管される場合は必要ないかもしれません。
任意の表に対する監査表およびビューを作成するストアード プロシージャーを書くことができます。以下は、MAKE_AUDIT_TABLE()ストアード プロシージャーを呼び出す例です。このプロシージャーは、スキーマSQLSTAND内のEMPLOYEE表に対する監査表およびビューを作成します。監査表は、スキーマAUDITLIB内のAUDIT_EMPLOYEEになります。表のシステム名は、AUDITEMPになります。監査ビューには、自動的にAUDIT_EMPLOYEE_VIEWという名前が付けられます。これは、監査表の名前に_VIEWが追加されたものです。
call make_audit_table(JOURNAL_TABLE_SCHEMA => 'SQLSTAND',
JOURNAL_TABLE => 'EMPLOYEE',
AUDIT_SCHEMA => 'AUDITLIB',
AUDIT_TABLE => 'AUDIT_EMPLOYEE',
AUDIT_TABLE_SYSTEM => 'AUDITEMP'); 以下は、MAKE_AUDIT_TABLE()ストアード プロシージャーを作成するSQLです。これをコピーして、「SQLスクリプトの実行」にペーストして実行します。以下に、このプロシージャーで注意すべき大事な点を記します。コードの中のコールアウトを参照してください。
- QTEMP内の*OUTFILEテンプレート ファイル（QSYS.QADSPJRN）のコピーを作成して、定義からJOESD列の定義を除去します。QSYS2.override_qaqqini()ストアード プロシージャーの呼び出しは、列が除去されないようにする照会メッセージが無視されるようにします。この他にも、必要に応じて、さらに詳細なジャーナル列が提供されるようにするために使用できる、テンプレート ファイルが4つあります。こうした情報が必要な場合は、QSYS.QADSPJRNのすべての参照を、別のテンプレートの名前（たとえば、QSYS.QADSPJRN2など）に置き換えるだけで使用できます。
- 動的SQLを使用して、(A)で定義された表内のすべての列、および監査される表内のすべての列から成る監査表を作成します。実質的に、監査表はDSPJRNコマンドによって生成されるファイルですが、JOESD列の定義は、監査される表内のすべての列の定義によって置き換えられます。
- LISTAGG()集約関数を使用して、監査ビューで必要とされる列リストを取得します。ジャーナル列では、ビューには、JOESD（監査される表の列によって置き換えられるため）およびJORES（埋め込み用であり、情報を何も含まないため）は含まれません。
- 動的SQLを使用して監査ビューを作成します。ビューの各行は、ジャーナル列、監査される表に対する前の（OLD_）列、および監査される表に対する後の（NEW_）列から成ります。ビューは、監査表での3つのselectステートメントの結合です。1つは変更用、1つは挿入用、および1つは削除用です。AUDIT_TYPE列は、変更のタイプを示します。
create or replace procedure make_audit_table
(
journal_table_schema varchar(256),
journal_table varchar(256),
audit_schema varchar(256),
audit_table varchar(256),
audit_table_system varchar(10) default ''
)
set option dbgview = *source,
commit = *none
BEGIN
declare audit_qualified varchar(512);
declare journal_table_qualified varchar(512);
declare for_system_name varchar(100);
declare execute_statement varchar(32000);
declare journal_columns varchar(32000);
declare journal_columns_old varchar(32000);
declare old_name_select varchar(32000);
declare old_name_list varchar(32000);
declare new_name_select varchar(32000);
declare new_name_list varchar(32000);
declare default_list varchar(32000);
-- Ignore File not found when deleting temporary table
declare CONTINUE HANDLER for SQLSTATE '42704'
BEGIN
END;
-- Set qualified names
set audit_qualified = audit_schema concat
'.' concat
audit_table;
set journal_table_qualified = journal_table_schema concat
'.' concat
journal_table;
-- Copy the *OUTFILE template to a work file in QTEMP and
-- drop the JOESD (record image) column
-- (A)
drop table QTEMP.MYAUDIT;
create table QTEMP.MYAUDIT like QSYS.QADSPJRN;
call QSYS2.override_qaqqini(1, '', '');
call QSYS2.override_qaqqini(2, 'SUPPRESS_INQUIRY_MESSAGES', '*YES');
alter table QTEMP.MYAUDIT drop column JOESD;
call QSYS2.override_qaqqini(3, '', '');
-- (B)
-- Create the Audit table.
-- This is the *OUTFILE template but JOESD is replaced with the
-- definition of the columns in the journaled table
if (audit_table_system <> '') then
set for_system_name = ' for system name ' concat audit_table_system;
end if;
set execute_statement =
'create or replace table ' concat audit_qualified concat
for_system_name concat ' as ( ' concat
'select MYAUDIT.*, ' concat journal_table concat '.* ' concat
'from QTEMP.MYAUDIT cross join ' concat
journal_table_qualified concat ') WITH NO DATA';
prepare execute_make_table from execute_statement;
execute execute_make_table;
drop table QTEMP.MYAUDIT;
-- Create the required Column Name lists for the Journal Entry Columns,
-- the OLD and NEW columns in the journaled table and a set of defaut
-- values for the columns in the journaled table
-- (C)
select LISTAGG(COLUMN_NAME, ', ')
WITHIN GROUP(ORDER BY ordinal_position)
into journal_columns
from QSYS2.SYSCOLUMNS
where (table_schema, table_name) = ('QSYS', 'QADSPJRN')
and column_name not in ('JORES', 'JOESD');
select LISTAGG('OLD.' concat COLUMN_NAME, ', ')
WITHIN GROUP(ORDER BY ordinal_position)
into journal_columns_old
from QSYS2.SYSCOLUMNS
where (table_schema, table_name) = ('QSYS', 'QADSPJRN')
and column_name not in ('JORES', 'JOESD');
select LISTAGG('OLD.' concat COLUMN_NAME, ', ')
WITHIN GROUP(ORDER BY ordinal_position)
into old_name_select
from QSYS2.SYSCOLUMNS
where (table_schema, table_name)
= (journal_table_schema, journal_table);
select LISTAGG('NEW.' concat COLUMN_NAME, ', ')
WITHIN GROUP(ORDER BY ordinal_position)
into new_name_select
from QSYS2.SYSCOLUMNS
where (table_schema, table_name)
= (journal_table_schema, journal_table);
select LISTAGG('OLD_' concat COLUMN_NAME, ', ')
WITHIN GROUP(ORDER BY ordinal_position)
into old_name_list
from QSYS2.SYSCOLUMNS
where (table_schema, table_name)
= (journal_table_schema, journal_table);
select LISTAGG('NEW_' concat COLUMN_NAME, ', ')
WITHIN GROUP(ORDER BY ordinal_position)
into new_name_list
from QSYS2.SYSCOLUMNS
where (table_schema, table_name)
= (journal_table_schema, journal_table);
select LISTAGG(CASE
WHEN NUMERIC_SCALE IS NOT NULL THEN '0'
WHEN DATA_TYPE = 'DATE' THEN 'CURRENT_DATE'
WHEN DATA_TYPE = 'TIME' THEN 'CURRENT_TIME'
WHEN DATA_TYPE = 'TIMESTAMP' THEN 'CURRENT_TIMESTAMP'
ELSE ''''''
END, ', ') WITHIN GROUP(ORDER BY ordinal_position)
into default_list
from QSYS2.SYSCOLUMNS
where (table_schema, table_name)
= (journal_table_schema, journal_table);
-- Create the Audit View over the Audit Table
-- Each row in the view contains:
-- - an AUDIT_TYPE column (contains CHANGE, INSERT or DELETE)
-- - the Journal entry columns
-- - the values of the Before Change/Delete columns
-- - the values of the After Change/Insert columns
set execute_statement =
'create or replace view ' concat audit_schema concat
'.' concat audit_table concat '_view ' concat
'( AUDIT_TYPE, ' concat journal_columns concat ', ' concat
old_name_list concat ', ' concat new_name_list concat
') as select ''CHANGE'' as AUDIT_TYPE, ' concat
journal_columns_old concat ', ' concat
old_name_select concat ', ' concat
new_name_select concat ' from ' concat
audit_qualified concat ' OLD inner join ' concat
audit_qualified concat ' NEW on ' concat
'(OLD.JODATE, OLD.JOTIME, OLD.JOJOB, OLD.JOUSER, OLD.JONBR, ' concat
'OLD.JOPGM, OLD.JOOBJ, OLD.JOLIB, OLD.JOMBR, OLD.JOCTRR) = ' concat
'(NEW.JODATE, NEW.JOTIME, NEW.JOJOB, NEW.JOUSER, NEW.JONBR, ' concat
'NEW.JOPGM, NEW.JOOBJ, NEW.JOLIB, NEW.JOMBR, NEW.JOCTRR) ' concat
'where (OLD.JOENTT, NEW.JOENTT) = (''UB'', ''UP'') or ' concat
'(OLD.JOENTT, NEW.JOENTT) = (''BR'', ''UR'') ' concat
'UNION select ''INSERT'' as AUDIT_TYPE, ' concat
journal_columns concat ', ' concat
default_list concat ', ' concat
old_name_select concat ' from ' concat
audit_qualified concat ' OLD ' concat
'where JOENTT IN (''PT'', ''PX'') ' concat
'UNION select ''DELETE'' as AUDIT_TYPE, ' concat
journal_columns concat ', ' concat
old_name_select concat ', ' concat
default_list concat ' from ' concat
audit_qualified concat ' OLD ' concat
'where JOENTT IN (''DL'', ''DR'') '
;
prepare execute_make_view from execute_statement;
execute execute_make_view;
END; 監査表にデータを取り込む
ジャーナルから監査表へ情報を取得する処理は、ジャーナル表示（DSPJRN）コマンドを使用して、必要なジャーナルを出力ファイルへ書き込むステップと、ファイル・コピー（CPYF）コマンドを使用して、その出力ファイルを監査表へコピーするステップの、2つのステップから成るプロセスです。
以下は、「SQLスクリプトの実行」でどのようにすればこれを実現できるかについて示した例です。CPYFについては以下の点に注意してください。
TOFILEパラメーターは、監査表のシステム名を使用します。
フォーマット オプション（FMTOPT）パラメーターの値は、*NOCHKです。これは、JOESD列の値（レコード イメージ）が、監査された表に対するすべての列（監査表で定義）へコピー/オーバーレイされることを意味します。ここでは、バイト単位でコピーを行います。
CL: DSPJRN JRN(SQLSTAND/QSQJRN) FILE((SQLSTAND/EMPLOYEE)) RCVRNG(*CURCHAIN)
FROMTIME('03/01/21') ENTTYP(*RCD)
OUTPUT(*OUTFILE) OUTFILE(QTEMP/JRNOUT1);
CL: CPYF FROMFILE(QTEMP/JRNOUT1) TOFILE(AUDITLIB/AUDITEMP) MBROPT(*REPLACE)
FMTOPT(*NOCHK) ; 本番環境では、こうした処理は、スケジューリングされたCLプログラムで行われるでしょう。開始日付/時刻は、必要に応じてプログラムによって設定されます。また、必要な監査プログラムを書いていた場合は、このCLプログラムがそのプログラムも実行するでしょう。
しかし、ここには、簡単に照会を行えるビューがあります（以前の例と同様）。
その他の検討事項
検討してみることが有益だと思われるいくつかの項目を以下に示します。
- ジャーナル列の既存の列名（JODATE、JOTIMEなど）を使用する代わりに、ビューでより有意義な名前がハードコーディングされたリストを提供するようにします。このリストがあれば、ビューで列リストのjournal_columnsの使用が置き換られるでしょう。
- CASEを使用して、デフォルト値がどのような値であるべきかを判定するのではなく、COLUMN_DEFAULT列（SYSCOLUMNS内）の値を使用するようにします。ここでCASEを使用した理由は、確実に、NULL値では終わらないようにするためでした。
- 「ビジーな」表を監査する場合は、ジャーナルから大量のデータをコピーするのではなく、より頻繁に抽出/監査を実行するようにします。つまり、より小さなまとまりごとにコピーするようにします。
- DSPJRNコマンドの他の出力ファイル形式（OUTFILFMT）によってどのような情報が提供されるか調べてみて、何か有用な追加情報がないか確認します。この例では、デフォルト値の*TYPE1を使用しています。
このストアード プロシージャーが何かの役に立つことを願っています。
