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

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

テンポラル表の概要や使用方法については、[オープンソース協議会様作成の資料](https://i5php.jp/wp-content/uploads/2016/12/170210_seminar.pdf)を参考にさせて頂きました。
環境準備では、上記資料の「システム期間テンポラル表新規作成手順」を実践します。

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

# 環境準備

```sql:スキーマ作成
CREATE SCHEMA TEMPORALLB;
```

```sql:システム期間テンポラル表の作成
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で必要なフィールドです。

```sql:履歴表の作成
CREATE TABLE TEMPORALLB.hist_policy_info LIKE TEMPORALLB.policy_info;
```

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

なお、リンクの解除は

```sql:テンポラル表と履歴表のリンクを解除
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で参照してみましょう。

```sql:過去のある時点のデータを参照する
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|

```sql:期間内に存在するデータを参照する
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の結果から見て行きましょう。

```sql: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」を入れてあげれば良さそうです。

```sql:期間内に発生したデータを参照する
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|

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

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

```sql:期間内に発生した削除データを参照する
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)
として考えます。

```sql:値の差を取得する
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|