オープンソース協議会 IBM i Ver. 1.0 2017/02/10 ー DB2 機能拡張ー ティアンドトラスト株式会社小川誠
アジェンダ
アジェンダ 1. IBM i 7.3 新機能紹介 2. 監査列 3. 概要 4. の処理 5. 必須フィールドの値の遷移 6. 確認方法 7. データ照会の実際 8. 監査情報のトラッキング 9. その他
IBM i 7.3 新機能紹介
7.3 新機能紹介 Orion / git オープン ソースの Web エディタおよびソース管理 SEU に変わるこれからのエディタ DB2 for i 監査列 OLAP 組み込み関数 権限収集機能 権限収集関連コマンドの提供 STRAUTCOL ENDAUTCOL DLTAUTCOL 本日の内容
監査列
監査列について システムによって自動的にセットされるフィールド 従来の監査情報記録の問題 変更ユーザー レコードの追加 変更 セットし忘れ ( バグなど ) データの変更履歴情報がユーザー 作成プログラムのバグや修正ミスなどでセットされない可能性がある場合は 監査情報として使用することができない 監査列を使 した監査情報のセット 変更ユーザー システムが自動的にセットする レコードの追加 変更 プログラムはセットしない 監査情報のセットをシステムに わせることにより 確実に値がセットされることが保証され 監査情報として使用することが可能となる
監査列の種類 以下の 3 種類を利 可能 データ変更操作 - 最終変更が挿 と更新のどちらだったのかを記録する - 履歴表には の削除をしめす記録を生成することが可能 最終変更が挿 の場合は I 最終変更が更新の場合は U 履歴表の場合は が削除された場合の D が生成される場合あり 特殊レジスター - データベース マネージャーによって定義される情報 様々なアプリケーション情報 1. OS 情報 2. アプリケーション名 3. アプリケーションファイル IBM i のユーザー名サーバー名など グローバル変数 - データベース マネージャーによって用意されるクライアント情報 IBM i のジョブ名 ( 番号 / ユーザー名 / ジョブ名 ) ホスト名 IP アドレスポート番号など
監査列の追加 既存のテーブルに監査列を追加する SQL 文 ALTER TABLE TELIB.TECSMP ADD 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
表記 5250 RUN SQL Scripts 特殊レジスタSESSION_USER QSECOFR QSECOFR グローバ監査列の 成例 DFU(5250) と SQL での生成例 較 ーCURRENT CLIENT_PROGRAMID ( セットなし ) file:/applications/ibm%20i%20access%20client% 20Solutions.app/acsbundle.jar 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 SERVER T10050BP T10050BP QSYS2.JOB_NAME 012725/QSECOFR/QPADEV0005 012703/QUSER/QZDASOINIT SYSIBM.CLIENT_IPADDR ( セットなし ) 172.29.97.129 SYSIBM.CLIENT_PORT ( セットなし ) 62090 5250 インターフェース DFU でデータ更新 MacbookAir に導入した ACS の Run SQL Scripts 機能で UPDATE SQL 文にて更新
概要
とは何か 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 でも可能ただし プロンプト機能は未サポート
必須列 ( フィールド ) について には以下の 3 つの列が必要 TIMESTAMP(12) で定義 *1:0001-01-01-00.00.00.000000000000 *2:9999-12-30-00.00.00.000000000000 テーブル列意味 A B 履歴表 A 開始列 終了列常に最大値 (*2) のデータが変更された 時既存のテーブルに追加される場合は最 の省略値 (*1) トランザクション開始 ID 開始列 ほとんどの場合 開始列と同じ 時 変更前の A の 開始列の値 B 終了列 が履歴表に追加された 時 (= A で が更新された 時 ) トランザクション開始 ID 変更前の A のトランザクション開始 ID の値
既存の物理ファイルの変更手順 既存テーブルあるいは物理ファイルの場合 1. 必須フィールドを ALTER TABLE で追加 2. 履歴表を CREATE TABLE で作成 3. ファイルのジャーナル処理開始 ( 開始されていなければ ) 4. バージョン管理を ALTER TABLE で追加 LF が存在していても可能 3. ジャーナル開始 TELIB (*LIB) *JRN 4. バージョン管理追加 *JRNRCV TECSMP H_TECSMP 1. 必須フィールド追加 2. 履歴表作成 履歴表
1. 必須フィールドを追加 既存テーブルあるいは物理ファイルの場合 必須フィールドを 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 GENERATED ALWAYS ROW BEGIN ROW END TRANSACTION START ID ADD PERIOD FOR SYSTEM_TIME 意味 NULL 値不可 の挿 および更新時にデフォルト値をシステムが生成する が追加された 付および時刻をシステムが生成既存レコードの場合 0001 年 01 月 01 に対応する 付および 0 時 0 分 0 秒 0 を生成 9999 年 12 月 30 に対応する 付および 0 時 0 分 0 秒 0 を生成 が追加された 付および時刻をシステムが生成既存レコードの場合 0001 年 01 月 01 に対応する 付および 0 時 0 分 0 秒 0 を生成 期間定義を追加する () 内に ROW BEGIN および ROW END で追加したフィールドを指定
2. 履歴表を作成 対象ファイルと全く同じレイアウトでなければならない あくまでレイアウトのみ ( ユニーク属性はつかない ) CREATE TABLE TELIB.H_TECSMP LIKE TELIB.TECSMP 3. ジャーナル開始 TELIB (*LIB) *JRN *JRNRCV 1. 必須フィールド追加 4. バージョン管理追加 TECSMP H_TECSMP 2. 履歴表作成 履歴表
3. ジャーナル処理開始 ジャーナル レシーバーおよびジャーナルの作成 ( なければ ) CRTJRNRCV JRNRCV(TELIB/xxxxxxxx) CRTJRN JRN(TELIB/yyyyyyyy) JRNRCV(TELIB/xxxxxxxx) ジャーナル処理開始 STRJRNPF FILE(TELIB/TECSMP TELIB/H_TECSMP) JRN(RELIB/yyyyyyyy) OMTJRNE(*OPNCLO) 3. ジャーナル開始 TELIB (*LIB) *JRN 4. バージョン管理追加 *JRNRCV 1. 必須フィールド追加 TECSMP H_TECSMP 2. 履歴表作成 履歴表
4. バージョン管理を追加 と履歴表のリンクを確 ALTER TABLE TELIB.TECSMP ADD VERSIONING USE HISTORY TABLE TELIB.H_TECSMP 3. ジャーナル開始 TELIB (*LIB) *JRN 4. バージョン管理追加 *JRNRCV TECSMP H_TECSMP 1. 必須フィールド追加 2. 履歴表作成 履歴表 ジャーナル処理されていなくてもリンクは確 できる 実 時に CPF4328 のエラー
新規作成手順 スキーマを前提に解説 1. スキーマを作成 ( なければ ) 2. テーブルを作成 ( 必須フィールドも忘れずに ) 3. 履歴表を作成 4. バージョン管理を ALTER TABLE で追加 OSS0210 (*LIB) *JRN 3. バージョン管理追加 *JRNRCV TECSMP H_TECSMP 1. 必須フィールド追加 2. 履歴表作成 履歴表
スキーマを作成 スキーマの作成 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) ); 自動的にジャーナル処理開始 OSS0210 (*LIB) *JRN 3. バージョン管理追加 *JRNRCV Policy_info 1. テーブルの作成 Hist_policy _info 履歴表 2. 履歴表作成
履歴表を作成 履歴表の作成 CREATE TABLE OSS0210.hist_policy_info LIKE OSS0210.policy_info; 自動的にジャーナル処理開始 OSS0210 (*LIB) *JRN 3. バージョン管理追加 *JRNRCV 1. テーブルの作成 policy_info Hist_policy _info 2. 履歴表作成 履歴表
バージョン管理を追加 バージョン管理を追加 ALTER TABLE OSS0210.policy_info ADD VERSIONING USE HISTORY TABLE OSS0210.hist_policy_info; OSS0210 (*LIB) *JRN *JRNRCV 1. テーブルの作成 Policy_info Hist_policy _info 履歴表 3. バージョン管理追加 2. 履歴表作成
履歴表を作成 履歴表の作成 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) ); 自動的にジャーナル処理開始 OSS0210 (*LIB) *JRN 3. バージョン管理追加 *JRNRCV TECSMP 1. テーブルの作成 H_TECSMP 履歴表 2. 履歴表作成
必須フィールドの値の遷移
必須列 ( フィールド ) セットの実際 (1) に変更される前に存在していたデータのケース CSCSCD = 01030 は変更前に存在していたもの SYS_START には最小値がセットされている A B 履歴表 A 01030 の CSGEND を 8000000 に変更 SYS_START と TS_ID に変更 時がシステムによってセット B 上記変更により履歴表にレコードがシステムによって追加 SYS_START には変更前の A の SYS_START SYS_END には変更 時 TS_ID には変更前の A の TS_ID
時系列で ると 変更日時を境に B に変更前 A に変更後のレコードが保管される A A B 履歴表 B B 01030 01030 A 過去 2017-01-29 02:33:42.171904000244 今
必須列 ( フィールド ) セットの実際 (2) に変更後に追加されたデータのケース CSCSCD = 01031 は変更後に追加されたデータ SYS_START には追加された日時がセットされている A B 履歴表 A 01031 の CSGEND を 10000 に変更 SYS_START と TS_ID にシステムが変更 時をセット B 上記変更により履歴表にレコードがシステムによって追加 SYS_START には変更前の A の SYS_START SYS_END には変更 時 TS_ID には変更前の A の TS_ID
確認方法
の情報の参照 QSYS2 の 4 つのカタログ ビュー ビュー名 SYSTABLES SYSPERIODS SYSCOLUMNS SYSHISTORYTABLES テーブル ビューおよび別名の一覧 および履歴表の一覧を取得可能 の期間の一覧 テーブルまたはビューの各列の一覧 開始列 終了列およびトランザクション開始 ID の一覧を取得可能 履歴表の一覧
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 = 定義済だが確 していない バージョン関係が確 中にバックアップされた履歴表が 関連するのないシステムに単独で復元された場合などに VERSIONING_STATUS が D にセットされる システム内履歴表の名前とバージョン管理の状況一覧を取得する SELECT HISTORY_TABLE_SCHEMA, HISTORY_TABLE_NAME, VERSIONING_STATUS FROM QSYS2.SYSHISTORYTABLES
データの照会
データ照会の方法 通常の SELECT による照会 特別な指定を わない照会 履歴表は検索対象外 システム レジスターを使った照会 履歴表も検索対象 CURRENT TEMPORAL SYSTEM_TIME SYSTEM_TIME 期間指定を使った照会 履歴表も検索対象 通常の検索 システム レジスター SYSTEM_TIME 履歴表
通常の SELECT による照会 常に現在のデータを参照する システム レジスターを使 しない 履歴表は対象外 A B 履歴表 A 1 2 過去 9000000 8000000 7000000 現在 1 = 2017-02-09 13:20:37.303669000244 2 = 2017-02-09 13:22:02.442490000244 SELECT xxxxxxx FROM yyyyyy where CSCSCD = 01030
通常の SELECT による照会 現在 の日時を変更してデータを参照する システム レジスターを使 する 履歴表も検索対象 A B 履歴表 A B 1 2 過去 9000000 現在 8000000 7000000 システム レジスターで過去の一時点を 現在 に設定 1 = 2017-02-09 13:20:37.303669000244 2 = 2017-02-09 13:22:02.442490000244 SET CURRENT TEMPORAL SYSTEM_TIME = TIMESTAMP ('2017-02-09 13:19:15') SELECT xxxxxxx FROM yyyyyy where CSCSCD = 01030
FOR SYSTEM_TIME を使 した照会 照会したい期間を明示的に指定したい場合に使 システムが両方の表から条件に合うデータを検索 指定方法 AS OF value FROM value1 TO value2 BETWEEN value1 AND value2 説明検索したい 時を指定検索したい期間を指定検索したい期間を指定 9000000 8000000 7000000 過去 現在 Value1 = 2017-02-09 13:20:37.303669000244 Value2 = 2017-02-09 13:22:02.442490000244
FROM と BETWEEN の違い 9000000 8000000 7000000 過去 現在 Value1 = 2017-02-09 13:20:37.303669000244 Value2 = 2017-02-09 13:22:02.442490000244 FOR SYSTEM_TIME FROM value1 TO value2 FOR SYSTEM_TIME BETWEEN value1 AND value2 データ FROM TO BETWEEN AND 9000000 含まない含まない 8000000 含む含む FROM と BETWEEN の違いは 開始列の値が value2 と同じ を含むかどうか (FROM は含まない ) 7000000 含まない含む
AS OF value の例 過去のある時点のデータを参照する システム レジスターで指定するのと同じ A B 履歴表 A B 1 9000000 8000000 過去 2017-02-09 13:19:15 2 7000000 現在 1 = 2017-02-09 13:20:37.303669000244 2 = 2017-02-09 13:22:02.442490000244 FOR SYSTEM_TIME AS OF 2017-02-09 13:19:15
FROM value1 TO value2 の例 期間内に存在するデータを参照する A B 履歴表 A B 1 2 9000000 8000000 7000000 過去現在 value1 value2 1 = 2017-02-09 13:20:37.303669000244 2 = 2017-02-09 13:22:02.442490000244 FOR SYSTEM_TIME FROM 2017-02-09 13:19:15 TO 2
BETWEEN value1 AND value2 の例 期間内に存在するデータを参照する 開始列が期間の最後に重なっていれば対象データとする A B 履歴表 A B 1 2 9000000 8000000 7000000 過去現在 value1 value2 1 = 2017-02-09 13:20:37.303669000244 2 = 2017-02-09 13:22:02.442490000244 FOR SYSTEM_TIME BETWEEN 2017-02-09 13:19:15 AND 2
監査情報のトラッキング
と監査列 監査列をに追加 データのタイムトラベル機能に より詳細な監査証跡を記録 どのジョブでデータが変更されたか 誰が変更したか 変更の種類は挿入 更新 削除のどれだったか 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) TELIB (*LIB) TECSMP H_TECSMP 上記 SQL で追加される監査列は 関連する履歴表にも自動的に追加される 履歴表
削除操作について SQL でレコードを追加 INSERT INTO TELIB.TECSMP (CSCSCD, CSGEND) VALUES('07020',100000) DFU でレコードを変更 SQL でレコードを削除 DELETE FROM TELIB.TECSMP WHERE CSCSCD = '07020' TELIB (*LIB) DELETE により TECSMP から該当レコードは削除され H_TECSMP に削除されたデータが追加されるが この履歴では誰が削除したのかがわからない TECSMP H_TECSMP 履歴表
ON DELETE ADD EXTRA ROW ON DELETE ADD EXTRA ROW を指定 ALTER TABLE TELIB.TECSMP ADD VERSIONING USE HISTORY TABLE telib.h_tecsmp ON DELETE ADD EXTRA ROW 前ページと同様の操作を実 SQL で追加 / DFU で変更 / SQL で削除 TELIB (*LIB) DELETE により TECSMP から該当レコードは削除され H_TECSMP に削除されたデータが追加されると同時に 削除操作自体の監査レコードも追加される TECSMP H_TECSMP 履歴表
その他
ディスクの効率的利 パーティショニング 履歴表のサイズの増 に注意 レコードの追加は履歴表には反映されないが トランザクションデータはサイズが きくなる可能性 更新回数が多ければそれだけ履歴表のレコードが増える トランザクション ファイルは要注意 履歴表をパーティション化 ( メンバーに分類 ) 必要に応じてデタッチ パーティション化 2013 2014 2015 2016 デタッチ 2012 2017 履歴表
RPG などのネイティブ入出 に関して 更新操作および削除操作 RPG からであっても履歴表は保守される 読み取り操作 RPG からの読み取りは結果をマージしない CURRENT TEMPORAL SYSTEM_TIME 特殊レジスター RPG はこの値を無視する 常に最新のデータのみを読み取る 必須列に値を設定することは可能 ただし バッファーに何が入っていても正しい値をセット INPLICITLY HIDDEN を指定して隠蔽可能
その他注意事項 保管について と履歴表は同時に保管 復元 ジャーナルについて 両ファイルともジャーナル処理が必要 ジャーナル オブジェクトは別ライブラリーでも良い スキーマ ( ライブラリー ) について 両ファイルとも同じスキーマ ( ライブラリー ) に配置
自己紹介 優しさ と 信頼 でお客様のビジネスに Goode Cycle を T&T α + = http://tat.co.jp 受託開発顧客研修技術開発 信頼性においては定評のある IBM i をメインとしたアプリケーション開発を います お客様とのコミュニケーションを第一に考え 最適なソリューションをご提案しています 外部研修にて IBM i コースを担当する専任のインストラクターがいます また お客様のご要望に応じたオーダーメイドの研修コースの提供も います IBM i の最新技術だけでなく お客様にとって必要になるであろうコンピュータ技術を 々蓄積しています ティアンドトラスト株式会社 111-0053 東京都台東区浅草橋 4-16-4 ウィングエイトビル 6F フリーダイヤル :0120-913-474 代表 :03-5821-3666 http://tat.co.jp