はじめに
テンポラル表に関する検証を実施中です。
新規にテーブルを作成してテンポラル表を作成する記事はすでにあるので、今回は既存DBをテンポラル表化します！
関連参考記事
テンポラル表の使用方法
Db2 for i テンポラル表を簡単に作って試してみた
テンポラル表とは
テンポラル表は、時間に基づいてデータの変更履歴を管理するための機能です。
既存DBにタイムスタンプ列と監査列を追加して、履歴表を作って使います。
「テンポラル表=既存DBにタイムスタンプ列とか監査列を追加したもの」です！
実際にテンポラル表化したデータベーステーブルです。
監査列とタイムスタンプ列にいつ、誰が、何をしたかが記録されます。
「ジャーナル見ればよくない？」
と思ったそこのあなたに向けて、テンポラル表利用のメリットをご紹介します。
過去データの取得がとにかく簡単
テンポラル表はSQLで簡単に時点指定検索ができます。FOR SYSTEM_TIME AS OF '2024-03-01' のように、特定の時点のデータの取得も可能です。
また、通常の SQL クエリで過去データも取得可能なため、テンポラル表を活用する場合アプリケーションの変更が最小限で済みます。
一方ジャーナルの場合は、DSPJRN コマンドやSQLのQSYS2.DISPLAY_JOURNAL関数を使って抽出・解析する必要があり、手間がかかります。
一応、ジャーナルの確認は以下のIBM i サービスにあるSQLを実行すればできますが細かな解析はやりづらいのが現状です。
SELECT journal_code, journal_entry_type, object, object_type, X.*
FROM TABLE (QSYS2.Display_Journal('DEMOLIB', 'JRN01',
JOURNAL_CODES => 'D,F,R',
STARTING_RECEIVER_NAME => '*CURCHAIN',
OBJECT_OBJTYPE=>'*FILE',
OBJECT_LIBRARY=>'DEMOLIB',
OBJECT_NAME=>'TOKMASP2',
OBJECT_MEMBER=>'*ALL'
) ) AS X
WHERE entry_timestamp > CURRENT TIMESTAMP - 1 HOUR
ORDER BY entry_timestamp DESC ;
履歴検索の負荷が低い
テンポラル表では、SQL のインデックスやパーティションを利用でき、効率的に過去データを参照できます。
一方、ジャーナルはログベースの管理であり、特定のデータを検索するときはログ全体をスキャンする必要があるため、パフォーマンスが落ちる可能性があります。
考えられるユースケース
1.監査列の利用
履歴表に変更ログを維持するため、データの変更履歴を正確に証明することが可能です。
誰がいつ何をしたかが一目瞭然です。
2. 簡単なBIツールとして利用
履歴表に過去のデータが全て保存されているため、過去の簡単に比較できるため簡単な傾向分析が可能。
例えばですが、以下のSQLを利用することで得意先番号01010の顧客に対し2月25日のデータと3月11日の売り上げ高を比較して抽出ができます。
SELECT T1.TKGURI AS TKGURI_0225,
T2.TKGURI AS TKGURI_0311
FROM SAKURALIB.TOKMSP3 FOR SYSTEM_TIME AS OF '2025-02-25' T1,
SAKURALIB.TOKMSP3 FOR SYSTEM_TIME AS OF '2025-03-11' T2
WHERE T1.TKBANG = '01010' AND T2.TKBANG = '01010';
結果はこんな感じ
テンポラル表の操作
データ挿入(INSERT)時
テンポラル表にデータ(行)を挿入した際は、テンポラル表にのみ更新があり、履歴表に更新は記録されません。
以下の例ではユーザーSAKURAがデータを3つ挿入しました、その場合挿入したデータのほか、INSTANCE_BEGIN列(IB列)にデータが挿入された時間、CHG列に挿入のI、USR列に変更を加えたユーザーSAKURAが記録されます。
- INSTANCE_BEGIN列(IB列)：行が挿入された時刻
- INSTANCE_END列(IE列)：終了時刻
- AUDIUT_TYPE_CHANG(CHG列)：挿入(INSERT)のI
- AUDIT_USR(USR列)：操作したユーザー
データ更新(UPDATE)時
テンポラル表のデータ(行)を更新すると履歴表も更新されます。
以下の例では1行目のデータをユーザーGOMAが書き換えました。
そうするとテンポラル表更新したデータのほか、INSTANCE_BEGIN列(IB列)にデータが更新された時間、CHG列に更新のU、USR列に更新をかけたユーザーGOMAが記録されます。
一方、履歴表には上書きされてしまったSAKURAのデータが移動します。
テンポラル表に記録されていたデータ＋INSTANCE_END列(IE列)にデータが上書きされた時間が記録され、履歴表に格納されます。
- INSTANCE_BEGIN列(IB列)：行が挿入された時刻
- INSTANCE_END列(IE列)：テンポラル表の行が更新されると、履歴表のIE時刻はテンポラル表のIB時刻と同じものを表示
- AUDIUT_TYPE_CHANG(CHG列)：更新(UPDATE)のU
- AUDIT_USR(USR列)：操作したユーザー
データ削除時
テンポラル表のデータ(行)を削除すると、テンポラル表からは行が消え、履歴表に追加されます。
以下の例では、SAKURAの3行目のデータを削除しました。
履歴表には削除されてしまったSAKURAのデータが移動します。
テンポラル表に記録されていたデータ＋INSTANCE_END列(IE列)にデータが削除された時間が記録され、履歴表に格納されます。
- INSTANCE_END列(IE列)：行が削除された時刻
- AUDIT_USR(USR列)：操作したユーザー
既存DBテーブルからテンポラル表を構成する手順
大まかな流れは以下です。
① タイムスタンプ列/監査列を既存DBテーブルに追加する
② 履歴表を作る
③ ジャーナルを開始する
④ ①でテンポラル表化した既存DBテーブルと履歴表をリンクさせる
環境情報
- 使用環境：IBM i 7.5
- テンポラル表化する既存DBテーブル：SAKURALIB/TOKMSP3
- 作成する履歴表：SAKURLIB/RIREKI
- SQL実行環境：ACS
IBM i 7.3以上であればテンポラル表の使用ができます。
SQLの実行は5250画面でもVS Codeでもどこでも、SQLが実行できればOKです。
① タイムスタンプ列/監査列を既存DBテーブルに追加する
SAKURALIB/TOKMSP3にタイムスタンプ列と監査列を追加します。
SQLを実行できる環境(今回はACS)で以下のコマンドを実行します。
- タイムスタンプ列の追加
ALTER TABLE SAKURALIB.TOKMSP3
ADD COLUMN instance_begin
TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN
ADD COLUMN instance_end
TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END
ADD COLUMN transaction_id
TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID
ADD PERIOD SYSTEM_TIME (instance_begin, instance_end)
- 監査列の追加
ALTER TABLE SAKURALIB.TOKMSP3
ADD COLUMN audit_type_change CHAR (1)
GENERATED ALWAYS AS (DATA CHANGE OPERATION)
ADD COLUMN audit_user VARCHAR(128)
GENERATED ALWAYS AS (SESSION_USER)
ADD COLUMN audit_client_IP VARCHAR(128)
GENERATED ALWAYS AS (SYSIBM.CLIENT_IPADDR)
ADD COLUMN audit_job_name VARCHAR(28)
GENERATED ALWAYS AS (QSYS2.JOB_NAME);
② 履歴表を作る
履歴表であるSAKURALIB/RIREKIの作成をします。
CREATE TABLE SAKURALIB.RIREKI LIKE SAKURALIB.TOKMSP3;
③ ジャーナルを開始する
履歴表にジャーナルをかけろと怒られましたのでジャーナルを開始します。
今回は5250画面でジャーナルを開始します、ACSからも開始できます。
- ジャーナルレシーバーの作成
CRTJRNRCV JRNRCV(SAKURALIB/JRNRCV01)
- ジャーナルの作成
CRTJRN JRN(SAKURALIB/JRN02) JRNRCV(SAKURALIB/JRNRCV02)
- ジャーナルの開始
STRJRNPF FILE(SAKURALIB/TOKMSP3) JRN(SAKURALIB/JRN02)
④ ①でテンポラル表化した既存DBテーブルと履歴表をリンクさせる
ALTER TABLE SAKURALIB.TOKMSP3 ADD VERSIONING USE HISTORY TABLE SAKURALIB.RIREKI
これでテンポラル表の構成は終わりです！
よくある質問
テンポラル表化したDB/履歴表に別のテンポラル表を切り替えて紐づけることはできる？
できます！
1つの履歴表に対し紐付けできるDBは１つですので、ALTER TABLE SAKURALIB.TOKMSP3 DROP VERSIONING;を実施し、新しいテーブル(テンポラル表化およびジャーナル処理はしておく必要あり)と履歴表をこちらALTER TABLE SAKURALIB.TOKMSP31 ADD VERSIONING USE HISTORY TABLE SAKURALIB.RIREKI;
で結合することによって切り替えができました。
テンポラル表化したDBにカラムの追加はできる？
できます！
テンポラル表DBにカラムを追加すると自動的に履歴表にもカラムが追加され、更新や挿入等の変更の履歴の確認もできました。
RPGプログラムで更新した場合の履歴も保存される？
されます！
ただ、テンポラル表で使用されるカラム名は6文字以上なのでRPGⅣを使用する必要があります。RPGⅢであれば、CVTRPGSRCで変換します。
参考：RPGⅢ→RPGⅣへのコンバートをやってみた。～CVTRPGSRCの仕様紹介～
テンポラル表化でストレージエリアを圧迫するのでは？
テンポラル表単体であれば、そこまで心配には及びません。
テンポラル表はレコードが更新されるたびにスナップショットとして記録するので、従前より容量は増えますがテンポラル表自体の容量はあまり大きくありません。
ただジャーナルを開始させる必要があるのでジャーナル容量の最適化を考慮する必要があります。
必要量例：
1Kバイト/レコード×1,000回更新/日×365日/年×10年
=3,650,000Kバイト ≒ 約3.5Gバイト
IBM Power内蔵NVMeの実容量例： 3Tバイト
3.5Gバイト÷3Tバイト ≒ ストレージ容量の1%未満の使用量です。
ジャーナルイメージはレコード・イメージと除外するジャーナル項目をそれぞれ指定することで最適化できます。
