Db2 for i テンポラル表を簡単に作って試してみた

テンポラル表とは

SQL:2011で定義された（業界標準の）機能です。IBM i 7.3以降で提供されます。
RDBのレコード更新情報を履歴表にログし、SQLで日付指定等で検索できるようにする機能です。これにより過去の特定時刻・日時のレコードの値を取得することできます。
テンポラル表には監査列を追加することもでき、レコード更新したユーザー情報やIP等をログすることもできます。

テンポラル表はIBM DB2 LUWでも提供されますし、他社のRDBMSでも提供されるようです。
以下、当URL記事より引用
Oracleでは「フラッシュバッククエリ（FlashBack Query）」、MicrosoftではSQL Server2016の「テンポラル表」、PostgreSQLでは9.2からの「System temporal table」

参考マニュアル：IBM i 7.5システム期間テンポラル表の作成
※Db2 for i では正式にはシステム期間テンポラル表とよばれます。（以下ではテンポラル表と表記）

作成手順①テンポラル表を作成する

早速作成してみます。DDLは下記です。

CREATE TABLE GOMALIB.DEPART
(DEPTNO CHAR(6) NOT NULL,
DEPTNAME CHAR(50) NOT NULL,
MGRNO CHAR(6) NOT NULL,
START_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
END_TS TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
TS_ID TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
PERIOD SYSTEM_TIME(START_TS,END_TS),
PRIMARY KEY (DEPTNO));

※上記の説明
テンポラル表名：GOMALIB/DEPART
START_TS TIMESTAMP(12)：タイムスタンプカラムSTART_TS 開始日時
END_TS TIMESTAMP(12)：タイムスタンプカラムEND_TS 終了日時を追加
TS_ID TIMESTAMP(12)：トランザクション開始時の時刻(TS-ID)を追加
PERIOD SYSTEM_TIME(START_TS,END_TS)：このテンポラル表をパーティション化する際使用するシステム時刻（開始時刻・終了時刻）を追加

ACSほかSQL実行画面からDDLを実行します。

テンポラル表は作成されましたが、警告が出ました（水色の線）。
理由は作成したスキーマ（GOMALIB）が以前のIBM i バージョンからSAV/RSTしたライブラーの為、ジャーナルが自動取得開始にならないためでした。
参考：Db2 for i のテーブル、スキーマの2つの作成方法での差異

今回のスキーマにはジャーナル、ジャーナルレシーバーは既存だったので、STRJRNPF でDEPART テーブルのジャーナル取得を開始しました。

SQL実行画面から、CL:STRJRNPFと入力したところでおっと、ジャーナル名がわからなかったのでACS IFSブラウザーで調べました。 かなり速いのでこれならもう5250はいらないですね♪

IFSブラウザーとF4キープロンプトで5250を使わずに下記コマンドが完成しました。実行します。（実行結果は省略）

CL:STRJRNPF FILE(GOMALIB/DEPART) JRN(GOMALIB/JRN01);

またテーブルにラベルを追加しておきます。

LABEL ON COLUMN GOMALIB.DEPART
(DEPTNO IS '部門コード',
DEPTNAME IS '部門名称',
MGRNO IS 'MGR社員番号',
START_TS IS 'START_TS',
END_TS IS 'END_TS',
TS_ID IS 'TS ID');

作成手順② 履歴表を作成する

①のテンポラル表の更新レコードを格納する履歴表を作成します。

SQL LIKE命令でテーブルDEPARTと同じカラムを持つテンポラル表DEPART_HISTORY を作成します。

CREATE TABLE GOMALIB.DEPART_HISTORY LIKE GOMALIB.DEPART;

今回の環境では履歴表作成時にもジャーナルが自動開始されない旨メッセージが出ます。テンポラル表DEPART作成時と同様にジャーナル取得を開始しておきます。注意点としてDAPART_HISTORYが10文字以上なので、IBM i OS上のネイティブなオブジェクト名は10文字省略が起こって別な名前になっている点です。実際には DEPAR00001 で作成されていました。

STRJRNPF CLコマンドはSQL名でなく、IBM i OSのネイティブなオブジェクト名を指定しないといけないので、

CL:STRJRNPF FILE(GOMALIB/DEPART_HISTORY) JRN(GOMALIB/JRN01);

だとエラーとなり、

CL:STRJRNPF FILE(GOMALIB/DEPAR00001) JRN(GOMALIB/JRN01);

と指定する必要があります。

作成手順③テンポラル表と履歴表を関係づける

ALTER TABLEで作成したテンポラル表と履歴表を関連づけます。

ALTER TABLE GOMALIB.DEPART ADD VERSIONING USE HISTORY TABLE GOALIB.DEPAR00001;

※ここでも（この例での）注意点は履歴表の名前をIBM i OSネイティブ名を指定する点です。※SQL別名でもできるかな？と試行したのですがエラーとなりました。）

実行結果は特にメッセージなどないので省略しますが、以上でテンポラル表と履歴表が作成・関連づけられました。

テンポラル表を使ってみる

①テンポラル表に初期レコードをINSERTする

まず単純にテンポラル表にデータを追加してみます。テンポラル表と言っても通常操作は特に差がありません。下記SQLでレコードを追加します。

INSERT INTO GOMALIB.DEPART (DEPTNO,DEPTNAME,MGRNO) VALUES('A00001','社長室','000001');
INSERT INTO GOMALIB.DEPART (DEPTNO,DEPTNAME,MGRNO) VALUES('A00010','生産本部','003302');
INSERT INTO GOMALIB.DEPART (DEPTNO,DEPTNAME,MGRNO) VALUES('A00020','開発本部','005823');
INSERT INTO GOMALIB.DEPART (DEPTNO,DEPTNAME,MGRNO) VALUES('A00030','管理本部','059478');
INSERT INTO GOMALIB.DEPART (DEPTNO,DEPTNAME,MGRNO) VALUES('A00040','営業本部','000002');
INSERT INTO GOMALIB.DEPART (DEPTNO,DEPTNAME,MGRNO) VALUES('A00011','生産管理部','000003');
INSERT INTO GOMALIB.DEPART (DEPTNO,DEPTNAME,MGRNO) VALUES('A00012','阪神工場','000004');
INSERT INTO GOMALIB.DEPART (DEPTNO,DEPTNAME,MGRNO) VALUES('A00013','中部工場','000005');
INSERT INTO GOMALIB.DEPART (DEPTNO,DEPTNAME,MGRNO) VALUES('A00014','関東工場','000006');
INSERT INTO GOMALIB.DEPART (DEPTNO,DEPTNAME,MGRNO) VALUES('A00015','和歌山工場','000007');

この時点でテンポラル表をのぞいてみますと、

START_TS カラムにレコードがINSERTされたタイムスタンプがセットされています
END_TSカラムは9999-12-30... がセットされています
TS_IDトランザクションIDは - がセットされています

履歴表は・・・

当然ですが、何もレコードがありませんね。

②テンポラル表のレコードをUPDATEする

以下の2つのSQL文を実行してみます。

UPDATE GOMALIB.DEPART SET DEPTNAME='生産技術本部' WHERE DEPTNO ='A00010’;

COMMIT;

まず1つ目のUPDATEを実行します。DEPARTテーブルを見ると、生産技術本部にUPDATEされています。

履歴表は・・

こちらもUPDATEのレコードが追加されています。
START_TSのタイムスタンプは元のテンポラル表でレコードが作成された時点が記録されています
END_TSは元のテンポラル表が更新された時刻タイムスタンプが記録されています。

お作法ですので、2つめのCOMMITも実行しておきます。

③テンポラル表のレコードを削除する

以下の2つのSQL文を実行します。

DELETE  GOMALIB.DEPART  WHERE DEPTNO ='A00015';

COMMIT;

元テーブルの　A00015のレコードは削除されます。

履歴表は・・

A00015のレコードが追加されています。履歴表自体には操作概要（ADD/UPD/DEL)は記載されていない点がDb2 for iのジャーナルとは違う点でしょうか。

③過去の時刻を指定して検索してみる。

上で確認したように最新ではA00015レコードは削除済みです。削除時刻は09:19頃ですね
09:19より少し前の時刻、例として9:00を指定してテンポラル表を検索してみます。

SELECT * FROM GOMALIB.DEPART FOR SYSTEM_TIME AS OF '2024-06-07 09:00:00'  ORDER BY DEPTNO;

結果は、、A00015が復活してみえています。履歴表を組合せて09:00時点のテーブル状態を検索結果として返しています。また、A00010のレコードも更新前の生産本部と表示されています。

③過去の時刻指定検索　１つのレコードのみ更新された時刻で検索

A00010レコードが更新され、かつ、A00015が未更新の時刻は・・09:15あたりですね・・。このタイムスタンプで検索してみます。

SELECT * FROM GOMALIB.DEPART FOR SYSTEM_TIME AS OF '2024-06-07 09:15:00'  ORDER BY DEPTNO;

A00010が更新後の生産技術本部で表示され、かつA00015レコードも存在しており、09:15時点のテーブル断面が取得できました。

テンポラル表のパーティション化、監査列の追加

・・・つづく