過去データにタイム・トラベル！簡単・便利なIBM i テンポラル表

記事をシェアする:

まるでタイム・トラベルのように、過去に遡ってIBM iに蓄積されたデータを確認したいことはありませんか？ こうした要望に対して、IBM i 7.2 まではデータの履歴管理を⼿組みで行う必要がありました。しかし、IBM i 7.3および最新版のIBM i 7.4ではシステム期間テンポラル表がサポートされており、簡単・便利に過去データにアクセスすることが可能になりました。このシステム期間テンポラル表は、システムが履歴を管理し、時間に基づく状態情報がデータに関連付けされ、過去の⾏データへのタイムトラベル照会が可能になります。システムが管理することにより、⼈的ミスもなくなり、過去のデータの照会のためにバックアップデータを復元したり、⽉ごとにファイルを分けて保管をしたりする必要もなくなります。

 

概要

システム期間テンポラル表は、CREATE TABLE ステートメントまたは ALTER TABLE ステートメントを使⽤して作成できる SQL 表です。表の履歴管理は、 SQL データ操作⾔語 (DML) ステートメントとネイティブ DB ⼊出⼒操作の両⽅に対して⾏われます。データベース・マネージャーは、 SYSTEM_TIME 期間を使⽤して、更新操作または削除操作の影響を受けた各⾏の履歴バージョンを保存します。システム期間テンポラル表と関連付けられた履歴表に、⾏の履歴バージョンを保管します。 ALTER TABLE を使⽤してバージョン管理を追加すると、 システム期間テンポラル表と履歴表の間のリンクが確⽴されます。照会では、システム期間テンポラル表のみを参照することによって、下記のように過去の時点のデータにアクセスできます。

2 年前のお客様担当者を抽出する

　　　SELECT CLIENT_REP FROM ACCOUNTS
　　　FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP – 2 YEARS

過去の特定⽇時の在庫情報を抽出する

　　　SET CURRENT TEMPORAL SYSTEM_TIME ‘2016-03-22 17:00:00’;
　　　CALL GENERATE_INVENTORY_REPORT();

 

構成要素

システム期間テンポラル表の構成要素は下記の3 つです。システム期間テンポラル表にバージョン管理が
定義されると、それ以降、その表に対して更新や削除が⾏われると、変更前のバージョンの⾏が履歴表に
1 つの⾏として挿⼊されます。

• システム期間テンポラル表
－ 新規もしくは既存の表に、追加で3 つのタイム・スタンプ列と1 つのシステム期間を追加

• 履歴表
－ システム期間テンポラル表と同じ列を持つように定義

• バージョン管理関係の構成
－ システム期間テンポラル表と履歴表をALTER TABLE ステートメントで接続

 

システム期間テンポラル表の作成例

1. 既存テーブルにシステム期間テンポラル表で使⽤するカラムを追加します

　　　ALTER TABLE account
　　　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)

2. 履歴表を作成します（システム期間テンポラル表と同じ列を持つ表）

CREATE TABLE account_hist LIKE account

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

ALTER TABLE account ADD VERSIONING USE HISTORY TABLE account_hist

 

システム期間テンポラル表と履歴表の操作例

・システム期間テンポラル表からSELECT ステートメントを実施
現時点のデータが表⽰されます。

SELECT * FROM account WHERE ACCT_ID = ‘88880001’;

・履歴表からSELECT ステートメントを実施
⾏に対する変更履歴の⼀覧が表⽰されます。

SELECT * FROM account_hist WHERE ACCT_ID = ‘88880001’;

・過去のある時点でのクエリを実⾏
指定したタイムスタンプの時点でのデータが表⽰されます。

　　　SELECT * FROM account FOR SYSTEM_TIME AS OF timestamp(‘2014-12-20’) – 1
　　　year WHERE ACCT_ID = ‘88880001’;

・ある⾏の過去の⼆時点のデータを⽐較
過去の⼆時点のデータが表⽰されます。

　　　SELECT T1.BALANCE AS BALANCE_2013,
　　　T2.BALANCE AS BALANCE_2014
　　　FROM account FOR SYSTEM_TIME AS OF ‘2013-12-31’ T1,
　　　account FOR SYSTEM_TIME AS OF ‘2014-12-31’ T2
　　　WHERE T1.ACCT_ID = ‘88880001’ AND T2.ACCT_ID = ‘88880001’;

 

監査機能の拡張（監査列）

さらに、表に監査列を追加しますと、誰がデータを追加・変更したのかを追跡することも可能です。
・ 表に監査列を追加

　　　ALTER TABLE account
　　　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)

・ON DELETE ADD EXTRA ROW 節を追加
デフォルトでは、履歴表には削除操作⾃体は記録されませんが、ALTER TABLE ADD VERSIONING ステートメントに ON DELETE ADD EXTRA ROW 節を指定することで履歴表に削除操作も記録されます。

システム期間テンポラル表との組み合わせでいつ誰が変更したのかをトラッキングすることができます。この機能を使うことで、データベースのレコード更新前の過去のある時点で、そのデータの内容がどうだったのかを参照することができます。更新履歴の調査や更新前の値と更新後の値の⽐較など業務でのデータ活⽤にぜひご利⽤ください。

ご参考リンク：IBM Knowledge Center IBM i : システム期間テンポラル表の処理