Q. Db2 for i テンポラル表で色々な操作をしてもトランザクションID列にタイムスタンプ日付が記録されません。
こんな感じです。
※注意 以下で掲載したDDL等とは異なるテーブル(PF)のサンプルです…
A. テーブル作成時のDDLに誤りが見つかりました。
色々調べたところ、下記のように元テーブル、テンポラル表作成時のDDLに誤りがあったようです。
エラー時のDDL
CREATE TABLE GOMALIB.PRODUCT
(PROD_ID CHAR(6) NOT NULL,
PROD_NAME CHAR(50) NOT NULL,
CAT_ID CHAR(2) NOT NULL,
PRICE INT 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,
CHANGE_TYPE CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION),
PRIMARY KEY (CAT_ID)
);
※TS_ID がタイムスタンプ列です。TS_ID列にNOT NULLが指定されていませんでした。
※また、PERIOD FOR SYSTEM_TIME(START_TS,END_TS) というお約束行も記述されておらず。
上記二点を修正して再作成いただきました。
修正したDDL
CREATE TABLE GOMALIB.PRODUCT
(PROD_ID CHAR(6) NOT NULL,
PROD_NAME CHAR(50) NOT NULL,
CAT_ID CHAR(2) NOT NULL,
PRICE INT 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,
CHANGE_TYPE CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION),
PERIOD FOR SYSTEM_TIME(START_TS,END_TS),
PRIMARY KEY (CAT_ID)
);
※テンポラル表GOMALIB.PROD_HISも同じDDLで作成
続けて元テーブルとテンポラル表を関連付け
ALTER TABLE GOMALIB.PRODUCT ADD VERSIONING USE HISTORY TABLE GOMALIB.PROD_HIS;
テスト結果
一応簡単に載せておきます。
実行したSQL
INSERT INTO GOMALIB.PRODUCT (PROD_ID,PROD_NAME,CAT_ID,PRICE) VALUES('A00001','FCRキャプレター・シングル','02', 49800);
UPDATE GOMALIB.PRODUCT SET CAT_ID='F3' WHERE PROD_ID ='A00001' ;
COMMIT;
SQLで検索すると、、
元テーブルには、、
SELECT * FROM GOMALIB.PRODUCT ;
テンポラル表には、、
SELECT * FROM GOMALIB.PROD_HIS ;
余談ですが、このサンプルではCHANGE_TYPE で更新タイプをログしています。(I=インサート)
そもそも TS_IDに指定した、 AS TRANSACTION START ID とは?
かみ砕くと、テンポラル表で最初にレコードをCreate(Updateのケースもある?変更ステートメントとあるので)した際に、Db2 for i(IBM i)のタイムスタンプが記録される、という事だと思います。
以下の処理を実行した時点でのテンポラル表と履歴表の状態を調べてみました。
・テンポラル表、履歴表とも、START_TSとEND_TSの期間がそのレコードが最新(最終)レコードだった時点を示す。
※テンポラル表の場合、END_TSは常に9999-12-30となり最新レコードと識別できる
・最新のレコードのSTART_TSの時刻と1つ前の世代レコードのEND_TS が同一になる。(この時刻で最新レコードが置き換わった=レコードが更新された)
・同じレコード中では、START_TS と TS_IDは必ず同一時刻がセットされる。