LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@yamurata(Yasuyuki Murata)

IBM i テンポラルテーブルを使ってデータベースの更新履歴を取得する

Posted at

IBM i 7.3では（システム期間）テンポラル表が使用できます。
過去のある時点のデータの状態を参照できる（タイムトラベル照会）機能ですが、この機能を利用しデータベースの更新履歴を取得する方法を紹介します。

テンポラル表の概要や使用方法については、オープンソース協議会様作成の資料を参考にさせて頂きました。
環境準備では、上記資料の「システム期間テンポラル表新規作成手順」を実践します。

実現したい機能

  1. ある一定期間内に発生したデータベースの登録・更新・削除の履歴を取得したい。
  2. 何で更新されたか（登録・更新・削除のいずれか）変更の種類が知りたい。
  3. 更新前の値と更新後の値が知りたい。

環境準備

スキーマ作成
CREATE SCHEMA TEMPORALLB;
システム期間テンポラル表の作成
CREATE TABLE TEMPORALLB.policy_info 
(
  poliy_id      CHAR(4) NOT NULL,
  coverage      INT NOT NULL,
  sys_start     TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
  sys_end       TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
  ts_id         TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID,
  change_type   CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION),
  PERIOD        FOR SYSTEM_TIME (sys_start,sys_end)
);

change_typeは「実現したい機能」の2で必要なフィールドです。

履歴表の作成
CREATE TABLE TEMPORALLB.hist_policy_info LIKE TEMPORALLB.policy_info;
テンポラル表と履歴表のリンクを確立
ALTER TABLE TEMPORALLB.policy_info ADD VERSIONING USE HISTORY TABLE TEMPORALLB.hist_policy_info ON DELETE ADD EXTRA ROW;

なお、リンクの解除は

テンポラル表と履歴表のリンクを解除
ALTER TABLE TEMPORALLB.policy_info DROP VERSIONING;

で出来ます。

データ投入のシナリオ

分かりやすくするために、約1分間隔でSQLを発行します。

№ 種類 更新時間 キー 値
1 挿入 18:28:37 PO10 1000
2 挿入 18:29:48 PO20 1000
3 挿入 18:30:42 PO30 1000
4 更新 18:31:44 PO10 1500
5 更新 18:32:44 PO20 1500
6 更新 18:33:41 PO10 4000
7 削除 18:34:41 PO30

全データ投入後の状態

POLIY_ID COVERAGE SYS_START SYS_END CHANGE_TYPE
PO10 4000 2018-12-17-18.33.41.689689000244 9999-12-30-00.00.00.000000000000 U
PO20 1500 2018-12-17-18.32.44.621552000244 9999-12-30-00.00.00.000000000000 U

タイムトラベル照会の確認（１）

テンポラルテーブルの使い方を確認します。
データを3件挿入した後の状態、18:31:00で参照してみましょう。

過去のある時点のデータを参照する
select * from TEMPORALLB.policy_info FOR SYSTEM_TIME AS OF '2018-12-17 18:31:00'
POLIY_ID COVERAGE SYS_START SYS_END CHANGE_TYPE
PO10 1000 2018-12-17-18.28.37.337094000244 2018-12-17-18.31.44.906254000244 I
PO20 1000 2018-12-17-18.29.48.245095000244 2018-12-17-18.32.44.621552000244 I
PO30 1000 2018-12-17-18.30.42.207028000244 2018-12-17-18.34.41.958047000244 I

FOR SYSTEM_TIME AS OFがミソですね。正しく18:31:00時点の状態が取得できました。

タイムトラベル照会の確認（２）

テンポラル表の照会では、期間を指定して取得する方法もあります。
まずこの照会方法を確認し、以下の期間でデータを照会してみます。

№ From To この時間帯での対象データ
1 18:28:00 18:31:00 1,2,3
2 18:31:00 18:31:30 対象無し
3 18:31:30 18:35:00 4,5,6,7
期間内に存在するデータを参照する
select * from TEMPORALLB.policy_info FOR SYSTEM_TIME from '2018-12-17 18:28:00' to '2018-12-17 18:31:00' order by SYS_START
select * from TEMPORALLB.policy_info FOR SYSTEM_TIME from '2018-12-17 18:31:00' to '2018-12-17 18:31:30' order by SYS_START
select * from TEMPORALLB.policy_info FOR SYSTEM_TIME from '2018-12-17 18:31:30' to '2018-12-17 18:35:00' order by SYS_START

18:28:00～18:31:00の結果

POLIY_ID COVERAGE SYS_START SYS_END TS_ID CHANGE_TYPE
PO10 1000 2018-12-17-18.28.37.337094000244 2018-12-17-18.31.44.906254000244 2018-12-17-18.28.37.337094000244 I
PO20 1000 2018-12-17-18.29.48.245095000244 2018-12-17-18.32.44.621552000244 2018-12-17-18.29.48.245095000244 I
PO30 1000 2018-12-17-18.30.42.207028000244 2018-12-17-18.34.41.958047000244 2018-12-17-18.30.42.207028000244 I

18:31:00～18:31:30の結果

POLIY_ID COVERAGE SYS_START SYS_END TS_ID CHANGE_TYPE
PO10 1000 2018-12-17-18.28.37.337094000244 2018-12-17-18.31.44.906254000244 2018-12-17-18.28.37.337094000244 I
PO20 1000 2018-12-17-18.29.48.245095000244 2018-12-17-18.32.44.621552000244 2018-12-17-18.29.48.245095000244 I
PO30 1000 2018-12-17-18.30.42.207028000244 2018-12-17-18.34.41.958047000244 2018-12-17-18.30.42.207028000244 I

18:31:30～18:35:00の結果

POLIY_ID COVERAGE SYS_START SYS_END TS_ID CHANGE_TYPE
PO10 1000 2018-12-17-18.28.37.337094000244 2018-12-17-18.31.44.906254000244 2018-12-17-8.28.37.337094000244 I
PO20 1000 2018-12-17-18.29.48.245095000244 2018-12-17-18.32.44.621552000244 2018-12-17-18.29.48.245095000244 I
PO30 1000 2018-12-17-18.30.42.207028000244 2018-12-17-18.34.41.958047000244 2018-12-17-18.30.42.207028000244 I
PO10 1500 2018-12-17-18.31.44.906254000244 2018-12-17-18.33.41.689689000244 2018-12-17-18.31.44.906254000244 U
PO20 1500 2018-12-17-18.32.44.621552000244 9999-12-30-00.00.00.000000000000 2018-12-17-18.32.44.621552000244 U
PO10 4000 2018-12-17-18.33.41.689689000244 9999-12-30-00.00.00.000000000000 2018-12-17-18.33.41.689689000244 U

18:31:00～18:31:30の結果は、18:28:00～18:31:00の結果と同じになりました。
これはもちろん正しく、18:31:00～18:31:30の期間に存在するデータだからです。

18:31:30～18:35:00の結果からは、削除データが表示されません。
削除されているので、存在しないデータですから表示されません。

履歴データの取得

今回の目的は履歴データを取得することですから、18:31:00～18:31:30の間にトランザクションが発生していない事を捉えたいです。

ここで改めて「SYS_START」と「SYS_END」の動きをPO01の結果から見て行きましょう。

18時28分00秒～18時35分00秒のPO10の動き
select * from TEMPORALLB.policy_info FOR SYSTEM_TIME from '2018-12-17 18:28:00' to '2018-12-17 18:35:00' where POLIY_ID = 'PO10' order by SYS_START
POLIY_ID COVERAGE SYS_START SYS_END
PO10 1000 2018-12-17-18.28.37.337094000244 2018-12-17-18.31.44.906254000244
PO10 1500 2018-12-17-18.31.44.906254000244 2018-12-17-18.33.41.689689000244
PO10 4000 2018-12-17-18.33.41.689689000244 9999-12-30-00.00.00.000000000000

見てわかる通り、「SYS_START」と「SYS_END」は、そのレコードの存在期間が記されています。
最終レコードは今生きているレコードですから、「SYS_END」には「9999-12-30-00.00.00.000000000000」がセットされています。

履歴データは、「データが生まれたタイミング」を捉えれば良いので、Where句に「SYS_START」を入れてあげれば良さそうです。

期間内に発生したデータを参照する
select * from TEMPORALLB.policy_info FOR SYSTEM_TIME from '2018-12-17 18:28:00' to '2018-12-17 18:31:00' where SYS_START between '2018-12-17 18:28:00' and '2018-12-17 18:31:00' order by SYS_START
select * from TEMPORALLB.policy_info FOR SYSTEM_TIME from '2018-12-17 18:31:00' to '2018-12-17 18:31:30' where SYS_START between '2018-12-17 18:31:00' and '2018-12-17 18:31:30' order by SYS_START
select * from TEMPORALLB.policy_info FOR SYSTEM_TIME from '2018-12-17 18:31:30' to '2018-12-17 18:35:00' where SYS_START between '2018-12-17 18:31:30' and '2018-12-17 18:35:00' order by SYS_START

18:28:00～18:31:00の結果

POLIY_ID COVERAGE SYS_START SYS_END TS_ID CHANGE_TYPE
PO10 1000 2018-12-17-18.28.37.337094000244 2018-12-17-18.31.44.906254000244 2018-12-17-18.28.37.337094000244 I
PO20 1000 2018-12-17-18.29.48.245095000244 2018-12-17-18.32.44.621552000244 2018-12-17-18.29.48.245095000244 I
PO30 1000 2018-12-17-18.30.42.207028000244 2018-12-17-18.34.41.958047000244 2018-12-17-18.30.42.207028000244 I

18:31:00～18:31:30の結果

POLIY_ID COVERAGE SYS_START SYS_END TS_ID CHANGE_TYPE

18:31:30～18:35:00の結果

POLIY_ID COVERAGE SYS_START SYS_END TS_ID CHANGE_TYPE
PO10 1500 2018-12-17-18.31.44.906254000244 2018-12-17-18.33.41.689689000244 2018-12-17-18.31.44.906254000244 U
PO20 1500 2018-12-17-18.32.44.621552000244 9999-12-30-00.00.00.000000000000 2018-12-17-18.32.44.621552000244 U
PO10 4000 2018-12-17-18.33.41.689689000244 9999-12-30-00.00.00.000000000000 2018-12-17-18.33.41.689689000244 U

これで挿入と更新の履歴（差分情報）が取れるようなりました。

削除データの取得

削除データは、履歴表から取得するしかなさそうです。

期間内に発生した削除データを参照する
select * from TEMPORALLB.hist_policy_info where SYS_START between '2018-12-17 18:28:00' and '2018-12-17 18:31:00' and CHANGE_TYPE='D' order by SYS_START
select * from TEMPORALLB.hist_policy_info where SYS_START between '2018-12-17 18:31:00' and '2018-12-17 18:31:30' and CHANGE_TYPE='D' order by SYS_START
select * from TEMPORALLB.hist_policy_info where SYS_START between '2018-12-17 18:31:30' and '2018-12-17 18:35:00' and CHANGE_TYPE='D' order by SYS_START

18:28:00～18:31:00の結果

POLIY_ID COVERAGE SYS_START SYS_END TS_ID CHANGE_TYPE

18:31:00～18:31:30の結果

POLIY_ID COVERAGE SYS_START SYS_END TS_ID CHANGE_TYPE

18:31:30～18:35:00の結果

POLIY_ID COVERAGE SYS_START SYS_END TS_ID CHANGE_TYPE
PO30 1000 2018-12-17-18.34.41.958047000244 2018-12-17-18.34.41.958047000244 2018-12-17-18.34.41.958047000244 D

更新前の値と更新後の値が知りたい。（値の差分が取りたい）

最後に値の差分を取得する方法を考えてみます。
在庫数のように入荷でプラス１０、出荷でマイナス５のようなイメージです。

タイムトラベル照会で参照できるのは指定した時点での値なので、履歴表から更新前のデータが参照出来れば実現出来そうです。
テンポラル表(t1)・・更新後の値(after)
履歴表(t2)・・更新前の値(befor)
として考えます。

値の差を取得する
select 
 t1.POLIY_ID,t2.COVERAGE as befor,
 t1.COVERAGE as after,
 case when t2.COVERAGE is null then t1.COVERAGE else t1.COVERAGE - t2.COVERAGE end as diff,
 t1.SYS_START
from TEMPORALLB.policy_info FOR SYSTEM_TIME from '2018-12-17 18:28:00' to '2018-12-17 18:35:00' t1
left join TEMPORALLB.hist_policy_info t2 on t1.SYS_START = t2.SYS_END
where t1.SYS_START between '2018-12-17 18:28:00' and '2018-12-17 18:35:00'
order by t1.SYS_START

挿入は履歴表に存在しないので、left joinで結合しました。

|POLIY_ID|BEFOR|AFTER|DIFF|SYS_START|
|---|---:|---:|---:|---|---|
|PO10|-|1000|1000|2018-12-17-18.28.37.337094000244|
|PO20|-|1000|1000|2018-12-17-18.29.48.245095000244|
|PO30|-|1000|1000|2018-12-17-18.30.42.207028000244|
|PO10|1000|1500|500|2018-12-17-18.31.44.906254000244|
|PO20|1000|1500|500|2018-12-17-18.32.44.621552000244|
|PO10|1500|4000|2,500|2018-12-17-18.33.41.689689000244|

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?