テンポラル表の使用方法

IBM i 初心者で基本的な勉強を始めていくにあたりテンポラル表の作成を行ってみたので、備忘録代わりに記載します。
本記事では、テンポラル表の作成から使用方法までの手順を紹介します。

1. テンポラル表とは

そもそもテンポラル表とは一言で言うと、データの更新履歴を確認することができる機能を持つ表のことを表します。IBM i V7R3で標準搭載されている無料の機能で、誰でも使用することができます。
もう少し詳しく説明させていただくと、以下の図のような構成になっており、通常運用する現行DB（テンポラル表）と更新履歴を残す履歴DB(履歴表)の2つのデータベースを組み合わせたものです。

※ここからは
現行DB→テンポラル表、履歴DB→履歴表
と記載させていただきます。

左側のテンポラル表でデータの作成、更新、削除の操作を行うと、右側の履歴表にシステムが自動で履歴の保存を行ってくれます。
さらにこの表は更新したユーザーの情報を取得する監査用の列を追加することができます。監査列を追加すると、いつ、誰が、どのような操作をDBに加えたのか確認することが可能です。

つまりテンポラル表はデータ更新の照会機能に加えて、DBのセキュリティの管理ができる一石二鳥な機能なのです。

2. テンポラル表の作成

2.1 検証環境

今回使用はV7R5の環境で実施します。
（V7R3以降であれば問題ございません）

今回作成するテンポラル表は以下の図のイメージで作成していきます。

まず最初に部門コード、部門名称、部門長コードからなる組織マスターを例に下記のデータベースを新規に作成します。
テンポラル表名：TESTLIB.DEPART
履歴表：TESTLIB.DEPART_HISTORY
とします。
また、今回はIBM i Access Client SolutionsのSQL機能を使用して作成していきます。
5250画面を開いて画面上部にあるバーからオレンジ枠で示すのボタンをクリックすることでSQL入力画面が開きます。

2.2 テンポラル表の作成

1.テンポラル表：TESTLIB.DEPARTの作成
以下のSQL文を記載してテンポラル表を作成します。
テンポラル表の作成には組織マスターの他にタイムスタンプ列、トランザクション、システム期間をSQL文に記載して作成します。

CREATE TABLE TESTLIB.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) GENERATED ALWAYS AS TRANSACTION START ID,　　　<-トランザクションID
PERIOD SYSTEM_TIME(START_TS,END_TS),　　　　　　　　　　　　　　  <-システム期間
PRIMARY KEY (DEPTNO));

2.列ラベルを設定するSQLを実行

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

監査列を追加する場合以下のSQLを実行

ALTER TABLE TESTLIB.DEPART 
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) 

3.履歴表： TESTLIB.DEPART_HISTORY の作成

CREATE TABLE TESTLIB.DEPART_HISTORY LIKE TESTLIB.DEPART;      <-テンポラル表から履歴表を作成することができる

4.バージョン管理関係を追加して、テンポラル表と履歴表の間にリンクを確立します。

ALTER TABLE TESTLIB.DEPART ADD VERSIONING USE HISTORY TABLE TESTLIB.DEPART_HISTORY;

5.サンプルデータの挿入

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

2.3 テンポラル表の挿入データの照会

1.テンポラル表の照会

SELECT * FROM TESTLIB.DEPART ORDER BY DEPTNO;

データの挿入時に、START_TSとEND_TSが自動で入力されるようになっております。

2.履歴表の照会

SELECT * FROM TESTLIB.DEPART_HISTORY ;

履歴表はこの時点では更新をかけていないため空になっております。

2.4 テンポラル表のレコードの更新・削除・古いデータの確認

1.データの更新
下記要件でデータの更新をかけていきます
部門コードA00010
生産本部⇨生産技術本部に変更

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

下記コマンドで更新した表の確認を行います。

SELECT * FROM TESTLIB.DEPART_HISTORY ORDER BY DEPTNO;

2.データの削除
下記の要件でデータの削除を行います。
部門コードA00015
和歌山工場の削除

DELETE  TESTLIB.DEPART  WHERE DEPTNO ='A00015';
COMMIT;

下記コマンドで和歌山工場が削除されていることがわかります。

SELECT * FROM TESTLIB.DEPART ORDER BY DEPTNO;

3.古いデータの確認
削除後の履歴表の照会：履歴表には削除前の古いデータが保管されております

SELECT * FROM TESTLIB.DEPART_HISTORY ORDER BY DEPTNO;

更新、削除前データの確認について
更新・削除 前の日時(ここでは2024/06/06 14:48:00を指定して、過去データを照会できます。

SELECT * FROM TESTLIB.DEPART FOR SYSTEM_TIME AS OF '2024-06-06 14:48:00'  ORDER BY DEPTNO;

更新前の「生産本部」と「和歌山工場」が表示されていることが確認できます。

4.参考
監査列を追加した場合以下のように編集したユーザー・JOB情報などについて確認することができるようになります。

3. まとめ

以上より、テンポラル表と履歴表を紐付ける際に必要だったプログラミングスキルなどが必要なくなり、SQLを使用することで簡単に連携させることができるようになります。V7R3以前のバージョンで履歴表と紐付けをする同じような機能を利用したい場合、時間やスキルなど必要となっていたので開発効率が大きく向上いたします。
また、使用したことがない場合はタイムマシンのようにデータベースを遡って参照をすることができ、誰がいつどのような更新作業を行なったかを確認できるので大変便利なツールとなっております。
重要なマスター、トランザクションファイルだけでも作成することで予期せぬトラブルでも、削除、更新の日時が分かるので保守開発の最適化につながります。無料で使用できるので是非お試しください。