[Lab 7] D DB2

[Lab 7]
DB2 の特徴的な
特徴的な機能紹介
2009 年 12 月
日本アイ
日本アイ･
アイ･ビー･
ビー･エム株式会社
エム株式会社
Contents
Contents
CONTENTS............................................................................................................2
CONTENTS
1.
はじめに .........................................................................................................3
2.
XML を含むパーティション表
パーティション表の作成 .................................................................4
2.1
2.2
2.3
2.4
2.4.1
2.4.2
3.
パーティション表
パーティション表への XML データの
データの投入..........................................................9
投入
3.1
3.2
4.
LOAD コマンドの実行.................................................................................9
LOAD 結果の確認 ...................................................................................10
パーティション表
パーティション表のメンテナンス......................................................................12
メンテナンス
4.1
4.2
5.
事前準備...................................................................................................4
パーティション表の DDL 編集 .....................................................................5
パーティション表の作成..............................................................................6
パーティション情報の確認 ..........................................................................6
db2look による DDL の取得 ....................................................................7
DESCRIBE DATA PARTITION コマンド ...................................................7
パーティションのデタッチ ..........................................................................12
パーティションのアタッチ...........................................................................13
XML データへの
データへのアクセス
へのアクセスの
アクセスの監査.....................................................................15
監査
2
1.
はじめに
このハンズオンでは、DB2 の特徴的な機能を利用したハンズオンを行います。このハンズオン
で行う作業は下記となります。
XML を含むパーティション表の作成
パーティション表への XML データの投入
パーティション表のメンテナンス
XML データへのアクセスの監査
このハンズオンで使用するディレクトリーの構成は下記の様になっています。各種スクリプトや
データは/workshop/lab7 に格納されています。
/workshop
└─lab7
:各種スクリプトなど
└─data
:Lab7 で使う XML データ
このハンズオンは、db2inst3 ユーザーで行います。下記のコマンドを実行して
db2inst3 ユーザーにスイッチし、DB2 インスタンスを起動してください。パスワードは
「password」です。
su – db2inst3
db2start
3
2.
XML を含むパーティション表
パーティション表の作成
このステップでは、XML データを含むパーティション表を作成します。パーティション表とは、大
量データを効率的に処理するために、あらかじめ定義したパーティションキーの値に応じて異
なるパーティションに格納することができる表です。大量データの保持や、定期的な削除処理
の効率化に非常に効果的です。DB2 9.7 からは、XML 列を含む表をパーティション表として作
成することができるようになりました。XML データは DB2 の pureXML 機能によって DB2 に取り
込まれます。pureXML 機能は DB2 9.1 から導入され、XML データの構造を DB2 が解析した上
で効率的に保持することができる機能です。また、XML データ中の要素や属性に対して索引を
定義することで、特定の要素を持つ XML データを索引経由で効率的に取得することも可能で
す。
このハンズオンで使用する XML データは週次で編成されています。そのため、週次でのデー
タ投入や削除が容易となるように、パーティション表の編成も週次で行います。
2.1
事前準備
まず、このハンズオンで使用するデータベース及び、表スペースを作成します。下記のスクリプ
トを実行してください。
cd /workshop/lab7
db2 –tvf 01.create.database.clp
変更例
db2inst3@DB2V97onSLES10:/workshop/lab7> db2 -tvf 01.create.database.clp
create database lab7 on /db2
DB20000I CREATE DATABASE コマンドが正常に完了しました。
connect to lab7
データベース接続情報
= DB2/LINUX 9.7.0
= DB2INST3
= LAB7
データベース・サーバー
SQL 許可 ID
ローカル・データベース別名
create bufferpool bp32k pagesize 32k
DB20000I SQL コマンドが正常に完了しました。
create tablespace ts1 pagesize 32k bufferpool bp32k
DB20000I SQL コマンドが正常に完了しました。
create tablespace its1 pagesize 32k bufferpool bp32k
DB20000I SQL コマンドが正常に完了しました。
正常に実行されると、上記のようなメッセージが出力されます。
4
2.2
パーティション表
パーティション表の DDL 編集
このステップでは、パーティション表の DDL を編集します。前述のように、このハンズオンでは
週次で投入されるデータのために、週次編成のパーティション表を作成します。下記に示した
DDL は、/workshop/lab7 ディレクトリーの 02.create.table.clp に格納されています。下記のコマ
ンドでファイルの内容を表示して確認してください。
cd /workshop/lab7
cat 02.create.table.clp
パーティション表 XML_PART1 の DDL
CREATE TABLE XML_PART1 (
SEQKEY INTEGER,
GENKEY INTEGER,
CREATE_DATE DATE,
FILENAME CHAR(25),
XML XML)
PARTITION BY RANGE (CREATE_DATE)
( STARTING '2009-01-05' ENDING '2009-01-11',
STARTING '2009-01-12' ENDING '2009-01-18',
STARTING '2009-01-19' ENDING '2009-01-25',
STARTING '2009-01-26' ENDING '2009-02-01',
STARTING '2009-02-02' ENDING '2009-02-08',
STARTING '2009-02-09' ENDING '2009-02-15',
STARTING '2009-02-16' ENDING '2009-02-22',
STARTING '2009-02-23' ENDING '2009-03-01'
);
また、この表に投入するデータは/workshop/lab7/data 配下に存在します。下記のコマンドで、
データファイルを確認してください。
ls -l data
パーティション表に投入するデータ
db2inst3@DB2V97onSLES10:/workshop/lab7> ls -l data
合計 673
drwxr-xr-x 2 db2inst3 db2grp 12848 2009-09-14 18:43
drwxr-xr-x 2 db2inst3 db2grp 14448 2009-09-14 18:43
drwxr-xr-x 2 db2inst3 db2grp 12848 2009-09-14 18:43
drwxr-xr-x 2 db2inst3 db2grp 19248 2009-09-14 18:43
drwxr-xr-x 2 db2inst3 db2grp 16048 2009-09-14 18:43
drwxr-xr-x 2 db2inst3 db2grp 16048 2009-09-14 18:43
drwxr-xr-x 2 db2inst3 db2grp
9648 2009-09-14 18:43
drwxr-xr-x 2 db2inst3 db2grp 12848 2009-09-14 18:43
drwxr-xr-x 2 db2inst3 db2grp
9616 2009-09-14 18:43
drwxr-xr-x 2 db2inst3 db2grp 16016 2009-09-14 18:43
drwxr-xr-x 2 db2inst3 db2grp
6416 2009-09-14 18:43
20090105
20090112
20090119
20090126
20090202
20090209
20090216
20090223
20090302
20090309
20090316
上記の DDL では、2009/1/5 の週から 2009/2/23 の週まで 8 週間分のパーティションが既に
定義されていますが、投入すべきデータは 2009/1/5 の週から 2009/3/16 の週まで 11 週間分
存在します。パーティション表は、パーティションキーとして定義した列の値によって格納するパ
ーティションを決定します。そのため、このままでは 3 週間分のデータを投入することができず、
データ投入時にエラーとなってしまいます。
5
＿vi エディターを使用して 02.create.table.clp ファイルを開き、既に存在する定義を参考にしな
がらパーティションの定義を追加してください。追加すべき期間は、下記の 3 週間分です。
開始日
2009-03-02
2009-03-09
2009-03-16
終了日
2009-03-08
2009-03-15
2009-03-22
02.create.table_modifled.clp には追加のパーティション定義を行った DDL が格納されています。
お時間のない方はこちらのファイルをご利用ください。
2.3
パーティション表
パーティション表の作成
編集の完了した DDL ファイルを使用して、パーティション表を作成します。下記のコマンドを実
行して DDL を投入してください。
db2 connect to lab7
db2 -tvf 02.create.table.clp
パーティション表 XML_PART1 の DDL
$ db2 connect to lab7
データベース接続情報
= DB2/LINUX 9.7.0
= DB2INST3
= LAB7
データベース・サーバー
SQL 許可 ID
ローカル・データベース別名
$ db2 -tvf 02.create.table.clp
CREATE TABLE XML_PART1 ( SEQKEY INTEGER, GENKEY INTEGER, CREATE_DATE DATE, FILENAME
CHAR(25), XML XML) PARTITION BY RANGE (CREATE_DATE) ( STARTING '2009-01-05' ENDING
'2009-01-11', STARTING '2009-01-12' ENDING '2009-01-18', STARTING '2009-01-19' ENDING
'2009-01-25', STARTING '2009-01-26' ENDING '2009-02-01', STARTING '2009-02-02' ENDING
'2009-02-08', STARTING '2009-02-09' ENDING '2009-02-15', STARTING '2009-02-16' ENDING
'2009-02-22', STARTING '2009-02-23' ENDING '2009-03-01', STARTING '2009-03-02' ENDING
'2009-03-08', STARTING '2009-03-09' ENDING '2009-03-15', STARTING '2009-03-16' ENDING
'2009-03-22' ) in ts1 index in its1
DB20000I SQL コマンドが正常に完了しました。
2.4
パーティション情報
パーティション情報の
情報の確認
パーティション表 XML_PART1 は正常に作成されました。このステップでは、パーティションの定
義情報をどのようにして取得するかを学習します。
6
2.4.1
db2look による DDL の取得
DB2 は、オブジェクトの DDL を取得するための機能として、db2look ユーティリティを提供してい
ます。db2look によって DDL を取得することで、既存の DDL の再利用や、既に存在しているオ
ブジェクトの定義を確認することが可能です。下記のコマンドを実行して、XML_PART1 表の
DDL を取得してください。
db2look –d lab7 –e –t xml_part1
実行例
$ db2look -d lab7 -e -t xml_part1
<省略>
CREATE TABLE "TUKIV97 "."XML_PART1" (
"SEQKEY" INTEGER ,
"GENKEY" INTEGER ,
"CREATE_DATE" TIMESTAMP(0) ,
"FILENAME" CHAR(25) ,
"XML" XML )
INDEX IN "ITS1" PARTITION BY RANGE("CREATE_DATE")
(PART "PART0" STARTING('2009-01-05-00.00.00') ENDING('2009-01-11-00.00.00') IN "TS1",
PART "PART1" STARTING('2009-01-12-00.00.00') ENDING('2009-01-18-00.00.00') IN "TS1",
PART "PART2" STARTING('2009-01-19-00.00.00') ENDING('2009-01-25-00.00.00') IN "TS1",
PART "PART3" STARTING('2009-01-26-00.00.00') ENDING('2009-02-01-00.00.00') IN "TS1",
PART "PART4" STARTING('2009-02-02-00.00.00') ENDING('2009-02-08-00.00.00') IN "TS1",
PART "PART5" STARTING('2009-02-09-00.00.00') ENDING('2009-02-15-00.00.00') IN "TS1",
PART "PART6" STARTING('2009-02-16-00.00.00') ENDING('2009-02-22-00.00.00') IN "TS1",
PART "PART7" STARTING('2009-02-23-00.00.00') ENDING('2009-03-01-00.00.00') IN "TS1",
PART "PART8" STARTING('2009-03-02-00.00.00') ENDING('2009-03-08-00.00.00') IN "TS1",
PART "PART9" STARTING('2009-03-09-00.00.00') ENDING('2009-03-15-00.00.00') IN "TS1",
PART "PART10" STARTING('2009-03-16-00.00.00') ENDING('2009-03-22-00.00.00') IN "TS1");
2.4.2
DESCRIBE DATA PARTITION コマンド
DESCRIBE DATA PARTITION コマンドは、パーティションに関するシステムカタログ表から、パ
ーティション構成を取得して表示します。一つ目のコマンドではパーティション ID と各パーティシ
ョンの範囲が出力されます。SHOW DETAIL オプションを追加した二つ目のコマンドでは、パー
ティション名やパーティションが格納されている表スペースの ID 等が追加で出力されます。
db2 describe data partitions for table xml_part1
db2 describe data partitions for table xml_part1 show detail
7
実行例
$ db2 describe data partitions for table xml_part1
PartitionId Inclusive (y/n)
Low Value
----------- - ---------------------0 Y '2009-01-05-00.00.00'
1 Y '2009-01-12-00.00.00'
2 Y '2009-01-19-00.00.00'
3 Y '2009-01-26-00.00.00'
4 Y '2009-02-02-00.00.00'
5 Y '2009-02-09-00.00.00'
6 Y '2009-02-16-00.00.00'
7 Y '2009-02-23-00.00.00'
8 Y '2009-03-02-00.00.00'
9 Y '2009-03-09-00.00.00'
10 Y '2009-03-16-00.00.00'
Inclusive (y/n)
High Value
- --------------------Y '2009-01-11-00.00.00'
Y '2009-01-18-00.00.00'
Y '2009-01-25-00.00.00'
Y '2009-02-01-00.00.00'
Y '2009-02-08-00.00.00'
Y '2009-02-15-00.00.00'
Y '2009-02-22-00.00.00'
Y '2009-03-01-00.00.00'
Y '2009-03-08-00.00.00'
Y '2009-03-15-00.00.00'
Y '2009-03-22-00.00.00'
$ db2 describe data partitions for table xml_part1 show detail
<省略>
PartitionId PartitionName TableSpId
PartObjId
IndexTblSpId LongTblSpId AccessMode
Status
----------- ------------- ----------- ----------- ------------ ----------- - -----0 PART0
2
4
2
2 F
1 PART1
2
5
2
2 F
2 PART2
2
6
2
2 F
3 PART3
2
7
2
2 F
4 PART4
2
8
2
2 F
5 PART5
2
9
2
2 F
6 PART6
2
10
2
2 F
7 PART7
2
11
2
2 F
8 PART8
2
12
2
2 F
9 PART9
2
13
2
2 F
10 PART10
2
14
2
2 F
SHOW DETAIL オプション付きで実行した際に、右端に出力される「AccessMode」と「Status」は、
各パーティションのステータスを表す項目です。AccessMode=F（フルアクセス）及び Status=ブ
ランクとなっている場合が通常の状態です。例えば、パーティションへのアクセスが不可能にな
っている場合、AccessMode=N となります。
8
3.
パーティション表
パーティション表への XML データの
データの投入
このステップでは、パーティション表へのデータ投入を行います。パーティション表へのデータ投
入は、通常のデータ投入と同様 LOAD ユーティリティが使用可能です。DB2 は投入されたデー
タを自動的に適切なパーティションへと振り分けます。
3.1
LOAD コマンドの
コマンドの実行
下記のコマンドを実行して、XML データを投入してください
db2 connect to lab7
db2 load from ./xmldata.del of del replace into xml_part1
正常にコマンドが実行されると、下記の様に 4547 レコードのデータが投入されます。
実行例
db2inst3@DB2V97onSLES10:/workshop/lab7> db2 connect to lab7
<省略>
db2inst3@DB2V97onSLES10:/workshop/lab7> db2 load from ./xmldata.del of del replace into
xml_part1
<省略>
SQL3520W ロード整合点が成功しました。
SQL3515W ユーティリティーは、"2009-09-14 09:36:59.975411" に "LOAD"
フェーズを完了しました。
SQL3500W ユーティリティーが "2009-09-14 09:36:59.975538" に "BUILD"
フェーズを開始しています。
SQL3213I
索引付けモードは "REBUILD" です。
SQL3515W ユーティリティーは、"2009-09-14 09:37:04.572583" に
"BUILD" フェーズを完了しました。
読み込まれた行数
スキップされた行数
ロードされた行数
拒否された行数
削除された行数
コミットされた行数
= 4547
= 0
= 4547
= 0
= 0
= 4547
Note：適切なパーティションが無いデータが投入された場合、「レンジ制約の違反」が発生し、そのデータは格納されませ
ん。LOAD 実行時に例外表を指定する事で、そのようなデータを別の表に出力することが可能です。
例外表の指定は下記の様にして行います。
db2 load from ./xmldata.del of del replace into xml_part1
for exception xml_part1_exception
この例では例外表は XML_PART1_EXCEPTION です。例外表には、パーティション定義をのぞいて LOAD の対象表と同じ構
成の表が必要です、パーティション表が指定できないのは、レンジ制約の違反が再度発生する事を防ぐためです。
9
3.2
LOAD 結果の
結果の確認
このステップでは、各パーティションへのデータの格納状況を確認します。
＿各パーティションへの格納件数
パーティションに関連した関数として、DATAPARTITIONNUM()関数が用意されています。この
関数は、各レコードがどのパーティションに属しているかをパーティション番号で表します。下記
の様な SQL に組み込むことで、パーティションごとに何件のデータが格納されているかを調査
できます。
db2 "SELECT DATAPARTITIONNUM(CREATE_DATE) PARTITION_NUMBER ,COUNT(*)
COUNT FROM XML_PART1 GROUP BY DATAPARTITIONNUM(CREATE_DATE)"
下記のスクリプトを実行して、この SQL の出力結果を確認してください。
db2 -tvf 03.get.tableCount.clp
実行例
$ db2 -tvf 03.get.tableCount.clp
SELECT DATAPARTITIONNUM(CREATE_DATE) PARTITION_NUMBER ,COUNT(*) COUNT FROM
XML_PART1 GROUP BY DATAPARTITIONNUM(CREATE_DATE)
PARTITION_NUMBER COUNT
---------------- ----------0
400
1
450
2
400
3
600
4
500
5
500
6
300
7
400
8
299
9
499
10
199
11 レコードが選択されました。
この実行例では、各パーティションに約 200 件から 600 件程度のレコードが格納されているこ
とが読み取れます。パーティション番号とレンジの関係は、前ステップで紹介した DESCRIBE
DATA PARTITION コマンドで確認可能です。
＿各パーティションのデータ容量
次に、各パーティションのデータ容量を確認します。表や索引、XML オブジェクトの容量を確認
するための管理ビューとして、SYSIBMADM.ADMINTABINFO 表が用意されています。この表に
は数多くの項目がありますが、下記の SQL ではデータオブジェクト、XML オブジェクトの容量
に関する項目のみを取得します。
db2 "SELECT SUBSTR(TABNAME,1,10) TABNAME,
DATA_PARTITION_ID,
DATA_OBJECT_L_SIZE,
XML_OBJECT_L_SIZE
FROM SYSIBMADM.ADMINTABINFO
10
WHERE TABNAME='XML_PART1'"
下記のスクリプトを実行して、この SQL の出力結果を確認してください。
db2 -tvf 04.get.dataSize.clp
実行例
$ db2 -tvf 04.get.dataSize.clp
SELECT SUBSTR(TABNAME,1,10) TABNAME, DATA_PARTITION_ID, DATA_OBJECT_L_SIZE,
XML_OBJECT_L_SIZE FROM SYSIBMADM.ADMINTABINFO WHERE TABNAME='XML_PART1'
TABNAME
DATA_PARTITION_ID DATA_OBJECT_L_SIZE
XML_OBJECT_L_SIZE
---------- ----------------- -------------------- -------------------XML_PART1
0
256
31488
XML_PART1
1
256
35968
XML_PART1
2
256
28160
XML_PART1
3
256
40320
XML_PART1
4
256
36608
XML_PART1
5
256
35456
XML_PART1
6
256
25600
XML_PART1
7
256
32256
XML_PART1
8
256
22272
XML_PART1
9
256
36480
XML_PART1
10
256
11776
11 レコードが選択されました。
この出力結果から、それぞれのパーティションがデータオブジェクトと XML オブジェクトに分か
れており、それぞれ独立して容量が管理されていることが読み取れます。今回のハンズオンで
使用している表は、ほとんどが XML データで占められているため、データ部の容量はごくわず
かです。
11
4.
パーティション表
パーティション表のメンテナンス
DB2 のパーティション表は、パーティションを単位としてデータを投入したり切り出したりするこ
とが可能です。この機能は、大容量データを一括して処理する必要がある場合に効果的です。
4.1
パーティションの
パーティションのデタッチ
XML_PART1 表に 11 週分存在するパーティション表のうち、2009/1/5 の週のパーティションが
保存期間を過ぎたと仮定します。通常の表であれば、SQL によるデータの消し込みを行います
が、SQL によるデータの削除には下記の様な課題があります。
・ 1 件ずつ SQL により削除を行うため、速度が遅い
・ ロールバックを可能とするためログを大量に生成する
・ 適度な間隔でコミットを行わないと大量のロックにより他の SQL の実行を阻害する場合
がある
パーティションのデタッチによるデータの削除は、これらの課題を解決します。
・ カタログ情報の書き換えによるパーティションの切り離しであるため、処理時間がデータ
量に依存しない
・ ログを必要とするのはシステムカタログ表の書き換え部分のみであるため、必要とするロ
グが非常に少量
・ データ部の切り離しの後、索引を非同期でメンテナンスするため、他の SQL 実行を阻害
しない。
パーティションのデタッチを行うと、デタッチされたパーティションは独立した表として切り出され
ます。デタッチに伴うデータの移動は発生しません。デタッチが完了した時点で元の表とは関連
しなくなるため、DROP TABLE により削除することが可能です。
＿下記のコマンドを実行して、パーティション PART0 のデタッチを行ってください
db2 "alter table xml_part1 detach partition part0 into
xml_part1_partition0"
実行例
$ db2 "alter table xml_part1 detach partition part0 into
xml_part1_partition0"
DB20000I SQL コマンドが正常に完了しました。
db2look コマンドや、DESCRIBE DATA PARTITION コマンドで、2009/1/5 の週のパーティション
が無くなっていることを確認してください。
また、下記のコマンドで新たに XML_PART1_PARTITION0 表が作成されていることが確認でき
ます。
db2 list tables |grep
XML
12
実行例
$ db2 list tables |grep XML
XML_PART1
DB2INST3
XML_PART1_PARTITION0
DB2INST3
4.2
T
T
2009-09-14-07.24.17.246685
2009-09-14-07.45.28.584311
パーティションの
パーティションのアタッチ
次に、新たな XML データをパーティションのアタッチを利用して投入します。この方法では、
XML_PART1 表に直接ロードするのではなく、別の表にいったんロードした後で、その表を
XML_PART1 表へアタッチします。アタッチを利用することでデータ投入に伴って XML_PART1
表が書き込みできない時間を短縮することが可能です。
XML_PART1表
表
② ロード
ロード後
後、新パーティ
ションとして
ションとしてアタッチ
としてアタッチ
2009-01-12
2009-01-19
・
・
ATTACH
①別表を
別表を新規作成して
新規作成してデ
してデ
ータロード
2009-03-23
2009
03-30
2009-03-30
LOAD
＿新規表の作成
CREATE TABLE ステートメントを使用してデータをロードする表を作成します。この表は次のス
テップで XML_PART1 表にアタッチするため、XML_PART1 表と同じ構成で作成する必要があり
ます。CREATE TABLE ステートメントの like オプションを使用する事で、同じ構成の表を簡単に
作成可能です。
db2 create table xml_0330 like xml_part1 in ts1 index in its1
実行例
$ db2 create table xml_0330 like xml_part1 in ts1 index in its1
DB20000I SQL コマンドが正常に完了しました。
13
＿XML_0330 表へのデータ投入
新規に作成した XML_0330 表へデータを投入します。投入するデータは/workshop/lab7/data2
ディレクトリーに存在しています。下記のコマンドを実行して、データの LOAD を行ってください。
db2 load from xml_0330.del of del replace into xml_0330
実行例
$ db2 load from xml_0330.del of del replace into xml_0330
<省略>
SQL3520W ロード整合点が成功しました。
SQL3515W ユーティリティーは、"2009-09-14 09:40:52.182317" に "LOAD"
フェーズを完了しました。
SQL3500W ユーティリティーが "2009-09-14 09:40:52.182501" に "BUILD"
フェーズを開始しています。
SQL3213I
索引付けモードは "REBUILD" です。
SQL3515W ユーティリティーは、"2009-09-14 09:40:52.400424" に
"BUILD" フェーズを完了しました。
読み込まれた行数
スキップされた行数
ロードされた行数
拒否された行数
削除された行数
コミットされた行数
= 800
= 0
= 800
= 0
= 0
= 800
正しくロードされれば、800 件のデータが XML_0330 表へ投入されます。
＿XML＿PART1 表へのアタッチ
XML_0330 表へのデータ投入が完了した後、XML＿PART1 表へのアタッチを行います。
db2 "alter table xml_part1 attach partition starting '2009-03-30'
ending '2009-04-05' from xml_0330"
db2 set integrity for xml_part1 immediate checked
実行例
db2inst3@DB2V97onSLES10:/workshop/lab7> db2 "alter table xml_part1 attach partition
starting '2009-03-30' ending '2009-04-05' from xml_0330"
SQL3601W ステートメントにより 1 つ以上の表が自動的に SET
INTEGRITY ペンディング状態になりました。 SQLSTATE=01586
db2inst3@DB2V97onSLES10:/workshop/lab7> db2 set integrity for xml_part1 immediate
checked
DB20000I SQL コマンドが正常に完了しました。
SET INTEGRITY ステートメントは、アタッチ後にアタッチしたパーティションを有効にするために
必要です。SET INTEGRITY の処理の中で、アタッチしたデータのレンジの確認や、追加したデ
ータのキーを索引に追加する処理などが行われます。
14
5.
XML データへの
データへのアクセス
へのアクセスの
アクセスの監査
このステップでは、XML データへのアクセスを監査するハンズオンを行います。XML データを
DBMS に格納する際のメリットとして、企業の基幹業務や情報系業務に向けて拡張されてきた
DBMS の様々な機能をそのまま活用できる点が挙げられます。監査機能の使用はその一例で、
ファイルとして保持されている状態では困難な、データへのアクセスに対する監査証跡の記録
を DBMS が行うことができます。
＿監査ポリシーの作成
DB2 の監査機能は、監査ポリシーの作成と、作成したポリシーをターゲットに紐付ける作業の
2 点で構成されます。下記のコマンドを実行して、監査ポリシーを作成してください。
db2 connect to lab7
db2 create audit policy ap1 categories all status both error type normal
この監査ポリシーは、全てのカテゴリーのアクティビティを監査します。監査のカテゴリーとして、
SQL の実行、監査の構成変更、オブジェクト使用時の権限チェックなどが存在します。
＿監査ポリシーのターゲットへのアサイン
作成した監査ポリシーを、AUDIT ステートメントを使用してターゲットへアサインします。AUDIT
ステートメントが成功し、コミットが完了した時点から監査が開始されます。監査機能の開始/停
止は必要ありません。
db2 audit database using policy ap1
実行例
db2inst3@DB2V97onSLES10:/workshop/lab7> db2 connect to lab7
データベース接続情報
= DB2/LINUX 9.7.0
= DB2INST3
= LAB7
データベース・サーバー
SQL 許可 ID
ローカル・データベース別名
db2inst3@DB2V97onSLES10:/workshop/lab7> db2 create audit policy ap1 categories all
status both error type normal
DB20000I SQL コマンドが正常に完了しました。
db2inst3@DB2V97onSLES10:/workshop/lab7> db2 audit database using policy ap1
DB20000I SQL コマンドが正常に完了しました。
＿サンプル SQL の実行
監査機能が稼働していることを確認するため、サンプルとなる SQL を実行します。
db2 -tvf 05.sample.clp
このスクリプトには下記の SQL が含まれ、2 つの SELECT ステートメントを発行しています。
connect to lab7;
15
select count(*) from xml_part1 where create_date between '2009-2-1' and
'2009-2-12';
select xml from xml_part1 where seqkey=2530;
＿監査ログのアーカイブ
監査ログの抽出を行う前に、監査ログの切替を行う必要があります。監査ログ・ファイルはバイ
ナリ・ファイルとして DB2 が監査データを抽出するファイルです。db2audit コマンドを使用して
archive を実行すると、DB2 は監査ログ・ファイルを切り替えます。これまでの監査ログ・ファイ
ルは、切替時点のタイムスタンプを付加されてアーカイブ・ファイルとなります。
ls -l ~/sqllib/security/auditdata/
db2audit archive database lab7
ls -l ~/sqllib/security/auditdata/
実行例
db2inst3@DB2V97onSLES10:/workshop/lab7> ls -l ~/sqllib/security/auditdata/
合計 228
-rw------- 1 db2inst3 staff 229399 2009-09-14 11:07 db2audit.db.LAB7.log.0
db2inst3@DB2V97onSLES10:/workshop/lab7> db2audit archive database lab7
AUD
アーカイブされたまたは一時的なログ・ファイル
メッセージ
-------- -------- --------------------------------------------------0 AUD0000I db2audit.db.LAB7.log.0.20090914110803
ノード
AUD0000I 操作が成功しました。
db2inst3@DB2V97onSLES10:/workshop/lab7> ls -l ~/sqllib/security/auditdata/
合計 228
-rw------- 1 db2inst3 staff 229399 2009-09-14 11:08
db2audit.db.LAB7.log.0.20090914110803
上記の例では出力されていませんが、DB2 は次回に監査情報を出力する際に新たな監査・ロ
グファイルを作成します。いったんアーカイブになったファイルは操作しません。
＿監査ログの抽出
アーカイブされた監査ログ・ファイルはバイナリ形式のファイルです。これを人が読める形式に
変換するため、db2audit の extract オプションを使用します。
db2audit extract file db2audit.extract.log from files <アーカイブした監査ログ・ファイル名>
ls -l
実行例
db2inst3@DB2V97onSLES10:/workshop/lab7> db2audit extract file db2audit.extract.log from
files db2audit.db.LAB7.log.0.20090914110803
AUD0000I 操作が成功しました。
db2inst3@DB2V97onSLES10:/workshop/lab7> ls -l *.log
-rw-rw-rw- 1 db2inst3 staff 418241 2009-09-14 11:18 db2audit.extract.log
抽出された監査ログはコマンド実行時のカレント・ディレクトリに出力されます。
＿監査ログの確認
抽出された監査ログを参照し、サンプルとして実行したＳＱＬを特定してください。ＳＱＬの実行
時の監査ログは、audit event=STATEMENT というイベントタイプとして出力されます。
16
実行例
timestamp=2009-09-14-11.03.47.779705;
category=EXECUTE;
audit event=STATEMENT;
event correlator=6;
event status=0;
database=LAB7;
userid=db2inst3;
authid=DB2INST3;
session authid=DB2INST3;
origin node=0;
coordinator node=0;
application id=*LOCAL.db2inst3.090914020344;
application name=db2bp;
client application name=CLP 05.sample.clp;
package schema=DB2INST3;
package name=SQLC2H20;
package section=201;
local transaction id=0x3e03000000000000;
global transaction id=0x0000000000000000000000000000000000000000;
uow id=1;
activity id=1;
statement invocation id=0;
statement nesting level=0;
activity type=READ_DML;
statement text=select count(*) from xml_part1 where create_date between '2009-2-1'
and '2009-2-12';
statement isolation level=CS;
Compilation Environment Description
isolation: CS
query optimization: 5
min dec div 3: NO
degree: 1
SQL rules: DB2
refresh age: +00000000000000.000000
resolution timestamp: 2009-09-14-11.03.47.000000
federated asynchrony: 0
schema: DB2INST3
maintained table type: SYSTEM;
rows modified=0;
rows returned=1;
ここではテキストファイルとして抽出する方法を紹介していますが、ＣＳＶファイルに変換してテ
ーブルにロードすることも可能です。詳細な手順については、下記のマニュアルをご参照くださ
い。
<DB2 9.7 Information Center 監査ログの保管と分析>
http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.sec.doc/d
oc/c0052328.html
以上です。
17
© Copyright IBM Corporation 2009
All Rights Reserved.
本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布され
るものです。
この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用
する環境に統合する使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べら
れていますが、他のところで同じまたは同様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使
用先は、自己の責任において行う必要があります。
18