Ver.
オープンソース協議会 IBM i
ー DB2 機能拡張 ー
ティアンドトラスト株式会社 小川 誠
アジェンダ
1.
IBM i 7.3 新機能紹介
2.
監査列
3.
システム期間テンポラル表概要
4.
システム期間テンポラル表の処理
5.
必須フィールドの値の遷移
6.
確認方法
7.
データ照会の実際
8.
監査情報のトラッキング
9.
その他
7.3 新機能紹介
Orion / git
オープン・ソースのWebエディタおよびソース管理
SEU に変わるこれからのエディタ
DB2 for i
監査列
システム期間テンポラル表
OLAP組み込み関数
権限収集機能
権限収集関連コマンドの提供
STRAUTCOL、ENDAUTCOL、DLTAUTCOL
本日の内容
監査列について
変更ユーザー セットし忘れ （バグなど） 変更ユーザー プログラムは セットしない システムが 自動的に セットする従来の監査情報記録の問題
監査列を使⽤した監査情報のセット
レコードの追加・変更 レコードの追加・変更 データの変更履歴情報がユーザー・作成プログラムのバグや修正ミ スなどでセットされない可能性がある場合は、監査情報として使用 することができない 監査情報のセットをシステムに⾏わせることにより、確実に値が セットされることが保証され、監査情報として使用することが可 能となるシステムによって自動的にセットされるフィールド
監査列の種類
以下の3種類を利⽤可能
データ変更操作
- 最終変更が挿⼊と更新のどちらだったのかを記録する - 履歴表には⾏の削除をしめす記録を生成することが可能特殊レジスター
- データベース・マネージャーによって定義される情報グローバル変数
- データベース・マネージャーによって用意されるクライアント情報 IBM i のジョブ名（番号/ユーザー名/ジョブ名） ホスト名 IPアドレス ポート番号など 様々なアプリケーション情報 1. OS 情報 2. アプリケーション名 3. アプリケーションファイル IBM i のユーザー名 サーバー名など 最終変更が挿⼊の場合は I 最終変更が更新の場合は U 履歴表の場合は⾏が削除された場合の D が 生成される場合あり監査列の追加
既存のテーブルに監査列を追加する SQL 文
ALTER TABLE TELIB.TECSMPADD COLUMN EMP_CHANGE_TYPE CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION) ADD COLUMN EMP_CHANGE_ACCTNG VARCHAR(255) GENERATED ALWAYS AS (CURRENT CLIENT_ACCTNG) ADD COLUMN EMP_CHANGE_APPLNAME VARCHAR(255) GENERATED ALWAYS AS (CURRENT CLIENT_APPLNAME) ADD COLUMN EMP_CHANGE_PROGRAMID VARCHAR(255) GENERATED ALWAYS AS (CURRENT CLIENT_PROGRAMID) ADD COLUMN EMP_CHANGE_SERVER VARCHAR(18) GENERATED ALWAYS AS (CURRENT SERVER)
ADD COLUMN EMP_CHANGE_SESSION_USER VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER) ADD COLUMN EMP_JOBNAME VARCHAR(28) GENERATED ALWAYS AS (QSYS2.JOB_NAME)
ADD COLUMN EMP_CLIENT_IPADDR VARCHAR(128) GENERATED ALWAYS AS (SYSIBM.CLIENT_IPADDR) ADD COLUMN EMP_CLIENT_PORT INTEGER GENERATED ALWAYS AS (SYSIBM.CLIENT_PORT)
データ・タイプは生成される項目の必要な定義に 完全に一致していなければならない 記述方法については、以下ページを参照 • 特殊レジスター • https://www.ibm.com/support/knowledgecenter/ja/ssw_ibm_i_73/db2/rbafzspecreg.htm • グローバル変数 • https://www.ibm.com/support/knowledgecenter/ja/ssw_ibm_i_73/db2/rbafzgvars.htm
監査列の⽣成例
DFU(5250)とSQLでの生成例⽐較
表記 5250 RUN SQL Scripts 特 殊 レ ジ ス タ ーDATA CHANGE OPERATION U U
CURRENT CLIENT_ACCTNG (セットなし) Mac OS X;SSL=false;admin_user=false
CURRENT CLIENT_APPLNAME (セットなし) IBM i Access Client Solutions - Run SQL Scripts CURRENT CLIENT_PROGRAMID (セットなし) file:/Applications/IBM%20i%20Access%20Client%20Solutions.app/acsbundle.jar
CURRENT SERVER T10050BP T10050BP
SESSION_USER QSECOFR QSECOFR
グ
ロ
ー
バ
ル
QSYS2.JOB_NAME 012725/QSECOFR/QPADEV0005 012703/QUSER/QZDASOINIT SYSIBM.CLIENT_IPADDR (セットなし) 172.29.97.129
SYSIBM.CLIENT_PORT (セットなし) 62090
5250インターフェース
システム期間テンポラル表とは何か
System-period temporal table
システムがデータ変更を追跡してその履歴を管理する
2つのテーブルで構成される
システム期間テンポラル表
履歴表
ユーザーの処理対象はシステム期間テンポラル表のみ
システム期間テンポラル表
システム期間テンポラル表 履歴表テンポラル表の種類
システム期間テンポラル表
IBM i 7.3 で提供される機能はこのタイプのみ
⾏の有効期間をDBMSが保守した値で判別
アプリケーション期間テンポラル表
⾏の有効期間をユーザーまたはアプリケーションが保守した
値で判別
バイテンポラル表
システム期間テンポラル表とアプリケーション期間テンポラ
ル表を結合したもの
誰が保守するのか
データ更新時に実⾏されなければならないこと
⾏の有効期間を識別する日時データ
変更前の過去のデータの履歴表への移動と日時の更新
システム期間テンポラル表
システム期間テンポラル表 履歴表 データ更新 IBM i (DBMS) が上記作業を自動的に⾏う ユーザーおよびプログラムは意識しないシステム期間テンポラル表で何ができる︖
タイムトラベル
過去のある時点のデータの状態を参照できる
過去 現在 未来先月の商品 A の価格はいくら︖
昨年の顧客 B の担当者は誰︖
1.
特定の日時のデータを参照
2.
範囲を指定して、その間のデータの遷移を参照
3.
監査列の利⽤により詳細なデータのトラッキング
設定手順
1.
新規作成もしくは既存テーブル（PF）に追加
⾏開始列、⾏終了列、トランザクション開始ID
システムタイム期間
2.
履歴表の作成
3.
バージョン管理を追加して履歴表へのリンクを確⽴
設定は SQL インターフェースのみ
システム期間テンポラル表
システム期間テンポラル表 履歴表SQL
DDS
SQL 実⾏インターフェース
ACS の Run SQL Scripts を使⽤
5250 STRSQL でも可能必須列（フィールド）について
システム期間テンポラル表には以下の３つの列が必要
TIMESTAMP(12) で定義
テーブル 列 意味 A ⾏開始列 ⾏のデータが変更された⽇時既存のテーブルに追加される場合は最⼩の省略値(*1) ⾏終了列 常に最大値(*2) トランザクション開始ID ほとんどの場合、⾏開始列と同じ⽇時 B ⾏開始列 変更前の A の⾏開始列の値 ⾏終了列 ⾏が履歴表に追加された⽇時（= A で⾏が更新された⽇時） トランザクション開始ID 変更前の A のトランザクション開始 ID の値 *1:0001-01-01-00.00.00.000000000000 *2:9999-12-30-00.00.00.000000000000 A システム期間テンポラル表 履歴表 B既存の物理ファイルの変更手順
既存テーブルあるいは物理ファイルの場合
1. 必須フィールドを ALTER TABLE で追加
2. 履歴表を CREATE TABLE で作成
3. ファイルのジャーナル処理開始（開始されていなければ）
4. バージョン管理を ALTER TABLE で追加
TECSMP
H_TECSMP
TELIB (*LIB)
システム期間テンポラル表 履歴表 *JRN *JRNRCV 3. ジャーナル開始 1. 必須フィールド追加 2. 履歴表作成 4. バージョン管理追加 LF が存在して いても可能１．必須フィールドを追加
既存テーブルあるいは物理ファイルの場合
必須フィールドを ALTER TABLE コマンドで追加
ALTER TABLE TELIB.TECSMP
ADD COLUMN sys_start TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN ADD COLUMN sys_end TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END
ADD COLUMN ts_id TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID ADD PERIOD for SYSTEM_TIME (sys_start, sys_end)
キーワード 意味
NOT NULL NULL 値不可
GENERATED ALWAYS ⾏の挿⼊および更新時にデフォルト値をシステムが生成する ROW BEGIN ⾏が追加された⽇付および時刻をシステムが生成 既存レコードの場合0001年01月01⽇に対応する⽇付および0時0分0秒0を生成 ROW END 9999年12月30⽇に対応する⽇付および0時0分0秒0を生成 TRANSACTION START ID ⾏が追加された⽇付および時刻をシステムが生成 既存レコードの場合0001年01月01⽇に対応する⽇付および0時0分0秒0を生成 ADD PERIOD FOR SYSTEM_TIME 期間定義を追加する
２．履歴表を作成
対象ファイルと全く同じレイアウトでなければならない
あくまでレイアウトのみ（ユニーク属性はつかない）
CREATE TABLE TELIB.H_TECSMP LIKE TELIB.TECSMP
TECSMP
H_TECSMP
TELIB (*LIB)
システム期間テンポラル表 履歴表 *JRN *JRNRCV 3. ジャーナル開始 1. 必須フィールド追加 4. バージョン管理追加2. 履歴表作成
３．ジャーナル処理開始
ジャーナル・レシーバーおよびジャーナルの作成（なければ）
ジャーナル処理開始
CRTJRNRCV JRNRCV(TELIB/xxxxxxxx) CRTJRN JRN(TELIB/yyyyyyyy) JRNRCV(TELIB/xxxxxxxx)TECSMP
H_TECSMP
TELIB (*LIB)
システム期間テンポラル表 履歴表 *JRN *JRNRCV 1. 必須フィールド追加 4. バージョン管理追加 2. 履歴表作成3. ジャーナル開始
４．バージョン管理を追加
システム期間テンポラル表と履歴表のリンクを確⽴
ジャーナル処理されていなくてもリンクは確⽴できる
実⾏時に CPF4328 のエラー
ALTER TABLE TELIB.TECSMP ADD VERSIONING USE HISTORY TABLE TELIB.H_TECSMP
TECSMP
H_TECSMP
TELIB (*LIB)
システム期間テンポラル表 履歴表 *JRN *JRNRCV 3. ジャーナル開始 1. 必須フィールド追加 2. 履歴表作成4. バージョン管理追加
システム期間テンポラル表新規作成手順
スキーマを前提に解説
1. スキーマを作成（なければ）
2. テーブルを作成（必須フィールドも忘れずに）
3. 履歴表を作成
4. バージョン管理を ALTER TABLE で追加
TECSMP
H_TECSMP
OSS0210 (*LIB)
システム期間テンポラル表 履歴表 *JRN *JRNRCV 1. 必須フィールド追加 2. 履歴表作成 3. バージョン管理追加スキーマを作成
スキーマの作成
CREATE SCHEMA OSS0210
項目
作成オブジェクト
ライブラリー
OSS0210
ジャーナル
QSQJRN
ジャーナル・レシーバー
QSQJRN0001
各カタログ・ビュー
SYSxxxxxxx
【参考】DSPLIBD OSS0210 現⾏ジャーナル処理中 . . . : YES 現⾏または最終ジャーナル . . . : QSQJRN ライブラリー . . . : OSS0210 この属性により、OSS0210にファイル が作成されると自動的にジャーナルが 開始される（STRJRNLIBコマンド)システム期間テンポラル表を作成
システム期間テンポラル表の作成
CREATE TABLE OSS0210.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,
PERIOD FOR SYSTEM_TIME (sys_start, sys_end) );
Policy_info
Hist_policy
_info
OSS0210 (*LIB)
システム期間テンポラル表 履歴表 *JRN *JRNRCV 2. 履歴表作成 3. バージョン管理追加1. テーブルの作成
自動的にジャーナル処理開始履歴表を作成
履歴表の作成
CREATE TABLE OSS0210.hist_policy_info LIKE OSS0210.policy_info;
policy_info
Hist_policy
_info
OSS0210 (*LIB)
システム期間テンポラル表 履歴表 *JRN *JRNRCV2. 履歴表作成
3. バージョン管理追加 1. テーブルの作成 自動的にジャーナル処理開始バージョン管理を追加
バージョン管理を追加
ALTER TABLE OSS0210.policy_info
ADD VERSIONING USE HISTORY TABLE OSS0210.hist_policy_info;
Policy_info
Hist_policy
_info
OSS0210 (*LIB)
システム期間テンポラル表 履歴表 *JRN *JRNRCV 2. 履歴表作成 1. テーブルの作成3. バージョン管理追加
履歴表を作成
履歴表の作成
CREATE TABLE OSS0210.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,
PERIOD FOR SYSTEM_TIME (sys_start, sys_end) );
TECSMP
H_TECSMP
OSS0210 (*LIB)
システム期間テンポラル表 履歴表 *JRN *JRNRCV 2. 履歴表作成 3. バージョン管理追加1. テーブルの作成
自動的にジャーナル処理開始必須列（フィールド）セットの実際（１）
システム期間テンポラル表に変更される前に存在していたデータのケース
CSCSCD = 01030 は変更前に存在していたもの
SYS_START には最小値がセットされている
A B A システム期間テンポラル表 履歴表 B01030 の CSGEND を ‘8000000’ に変更 SYS_START と TS_ID に変更⽇時がシステムによってセット
上記変更により履歴表にレコードがシステムによって追加 SYS_START には 変更前の A の SYS_START SYS_END には 変更⽇時
時系列で⾒ると・・・
過去 2017-01-29 02:33:42.17190400024401030
01030
今⽇ A B B A変更日時を境に B に変更前、A に変更後のレコードが保管される
A システム期間テンポラル表 履歴表 B必須列（フィールド）セットの実際（２）
システム期間テンポラル表に変更後に追加されたデータのケース
CSCSCD = 01031 は変更後に追加されたデータ
SYS_START には追加された日時がセットされている
A B A システム期間テンポラル表 履歴表 B01031 の CSGEND を ‘10000’ に変更 SYS_START と TS_ID にシステムが変更⽇時をセット
上記変更により履歴表にレコードがシステムによって追加 SYS_START には 変更前の A の SYS_START SYS_END には 変更⽇時
システム期間テンポラル表の情報の参照
QSYS2の４つのカタログ・ビュー
ビュー名
SYSTABLES
テーブル、ビューおよび別名の一覧
システム期間テンポラル表および履歴表の一覧を取得可能
SYSPERIODS
システム期間テンポラル表の期間の一覧
SYSCOLUMNS
テーブルまたはビューの各列の一覧
⾏開始列、⾏終了列およびトランザクション開始IDの一覧を
取得可能
SYSHISTORYTABLES 履歴表の一覧
QSYS2/SYSTABLES
システム期間テンポラル表および履歴表一覧
列名
タイプ
説明
TEMPORAL_TYPE CHAR(1) H = 履歴表
S = システム期間テンポラル表
SELECT TABLE_NAME, TABLE_SCHEMA, TEMPORAL_TYPE
FROM QSYS2.SYSTABLES
WHERE TEMPORAL_TYPE in ('S','H')
QSYS2/SYSPERIODS
システム期間テンポラル表の期間の一覧
ADD PERIOD SYSTEM_TIME で追加された期間
列名
タイプ
説明
PERIOD_NAME
VARCHAR(128) 期間の名前
PERIOD_TYPE
CHAR(1)
S = システム期間
SYSTEM_HISTORY_TABLE_NAME CHAR(10)
NULL可能
履歴表の名前
（バージョン管理が追加
されている場合）
SELECT PERIOD_NAME, TABLE_NAME, TABLE_SCHEMA, PERIOD_TYPE,
BEGIN_COLUMN_NAME, END_COLUMN_NAME,
SYSTEM_HISTORY_TABLE_NAME
FROM QSYS2.SYSPERIODS
QSYS2/SYSHISTORYTABLES
すべての履歴表の一覧
列名
タイプ
説明
HISTORY_TABLE_SCHEMA
VARCHAR(128) 履歴表のスキーマ名
HISTORY_TABLE_NAME
VARCHAR(128) 履歴表の名前
VERSIONING_STATUS
CHAR(1)
E = バージョン関係が確⽴中
D = 定義済だが確⽴していない
SELECT HISTORY_TABLE_SCHEMA, HISTORY_TABLE_NAME,
VERSIONING_STATUS
FROM QSYS2.SYSHISTORYTABLES
システム内履歴表の名前とバージョン管理の状況一覧を取得する
バージョン関係が確⽴中にバックアップされた履歴表が、関連する システム期間テンポラル表のないシステムに単独で復元された場合 などに、VERSIONING_STATUS が D にセットされる。データ照会の方法
通常の SELECT による照会
特別な指定を⾏わない照会
履歴表は検索対象外
システム・レジスターを使った照会
履歴表も検索対象
CURRENT TEMPORAL SYSTEM_TIME
SYSTEM_TIME期間指定を使った照会
履歴表も検索対象
システム期間テンポラル表
システム期間テンポラル表 履歴表
通常の SELECT による照会
SELECT xxxxxxx FROM yyyyyy where CSCSCD = ‘01030’
A システム期間テンポラル表 履歴表 B A 過去 ① = 2017-02-09 13:20:37.303669000244 9000000 現在 8000000 ② = 2017-02-09 13:22:02.442490000244 7000000 ① ②
常に現在のデータを参照する
システム・レジスターを使⽤しない 履歴表は対象外通常の SELECT による照会
SET CURRENT TEMPORAL
SYSTEM_TIME = TIMESTAMP ('2017-02-09 13:19:15') SELECT xxxxxxx FROM yyyyyy where CSCSCD = ‘01030’
A システム期間テンポラル表 履歴表 B A B 過去 ① = 2017-02-09 13:20:37.303669000244 9000000 8000000 ② = 2017-02-09 13:22:02.442490000244 7000000 ① ②
「現在」の日時を変更してデータを参照する
システム・レジスターを使⽤する 履歴表も検索対象 「現在」 システム・レジスターで過去の 一時点を「現在」に設定FOR SYSTEM_TIME を使⽤した照会
照会したい期間を明示的に指定したい場合に使⽤
システムが両方の表から条件に合うデータを検索
指定方法
説明
AS OF value
検索したい⽇時を指定
FROM value1 TO value2
検索したい期間を指定
BETWEEN value1 AND value2
検索したい期間を指定
過去 Value1 = 2017-02-09 13:20:37.303669000244 9000000 7000000 現在 8000000 Value2 = 2017-02-09 13:22:02.442490000244
FROM と BETWEEN の違い
データ
FROM TO
BETWEEN AND
9000000
含まない
含まない
8000000
含む
含む
7000000
含まない
含む
過去 Value1 = 2017-02-09 13:20:37.3036690002449000000
7000000
現在8000000
Value2 = 2017-02-09 13:22:02.442490000244FOR SYSTEM_TIME FROM value1 TO value2
FOR SYSTEM_TIME BETWEEN value1 AND value2
FROM と BETWEEN の違いは、⾏ 開始列の値が value2 と同じ⾏を含 むかどうか（FROM は含まない）
AS OF value の例
FOR SYSTEM_TIME AS OF ‘2017-02-09 13:19:15’ A システム期間テンポラル表 履歴表 B A B 過去 ① = 2017-02-09 13:20:37.303669000244 9000000 現在 8000000 ② = 2017-02-09 13:22:02.442490000244 7000000 ① ② 2017-02-09 13:19:15過去のある時点のデータを参照する
システム・レジスターで指定するのと同じFROM value1 TO value2 の例
FOR SYSTEM_TIME FROM ‘2017-02-09 13:19:15’TO ②
A システム期間テンポラル表 履歴表 B A B 過去 ① = 2017-02-09 13:20:37.303669000244 9000000 現在 8000000 ② = 2017-02-09 13:22:02.442490000244 7000000 ① ②
期間内に存在するデータを参照する
value1 value2BETWEEN value1 AND value2 の例
FOR SYSTEM_TIME BETWEEN ‘2017-02-09 13:19:15’AND ②
A システム期間テンポラル表 履歴表 B A B 過去 ① = 2017-02-09 13:20:37.303669000244 9000000 現在 8000000 ② = 2017-02-09 13:22:02.442490000244 7000000 ① ②
期間内に存在するデータを参照する
⾏開始列が期間の最後に重なっていれば対象データとする value1 value2システム期間テンポラル表と監査列
監査列をシステム期間テンポラル表に追加
データのタイムトラベル機能に、より詳細な監査証跡を記録
どのジョブでデータが変更されたか
誰が変更したか
変更の種類は挿入、更新、削除のどれだったか …
ALTER TABLE TELIB.TECSMP
ADD COLUMN EMP_CHANGE_TYPE CHAR(1) GENERATED ALWAYS AS (DATA CHANGE OPERATION) ADD COLUMN EMP_CHANGE_SESSION_USER VARCHAR(128) GENERATED ALWAYS AS (SESSION_USER)
ADD COLUMN EMP_JOBNAME VARCHAR(28) GENERATED ALWAYS AS (QSYS2.JOB_NAME)
TECSMP
H_TECSMP
TELIB (*LIB)
システム期間テンポラル表 履歴表 上記SQLで追加される監査列 は、関連する履歴表にも自動 的に追加される削除操作について
INSERT INTO TELIB.TECSMP (CSCSCD, CSGEND) VALUES('07020',100000)
SQL でレコードを追加
DFU でレコードを変更
SQL でレコードを削除
DELETE FROM TELIB.TECSMP WHERE CSCSCD = '07020'
DELETE により TECSMP から該当レコードは削除され、 H_TECSMP に削除されたデータが追加されるが、この
履歴では誰が削除したのかがわからない
TECSMP
H_TECSMP
TELIB (*LIB)
ON DELETE ADD EXTRA ROW
ALTER TABLE TELIB.TECSMP ADD VERSIONING USE HISTORY TABLE telib.H_TECSMP ON DELETE ADD EXTRA ROW
ON DELETE ADD EXTRA ROW を指定
前ページと同様の操作を実⾏
SQL で追加 / DFU で変更 / SQL で削除
DELETE により TECSMP から該当レコードは削除され、 H_TECSMP に削除されたデータが追加されると同時に、 削除操作自体の監査レコードも追加されるTECSMP
H_TECSMP
TELIB (*LIB)
システム期間テンポラル表 履歴表2017 2016 2015 2014 2013
