第４章 DB2運用の基本

第４章 DB2運用の基本
本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。
この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合する
使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまたは同
様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。
© Copyright IBM Japan Co., Ltd. 2011
内容
• データベースの運用管理とは
• 表のメンテナンス
• データの投入・抽出・移動
• 統計情報の収集
• 表の再編成
• DB2のセキュリティ機能概要
2
© 2011 IBM Corporation
データベースの運用管理に伴う主なタスク
表のメンテナンス
RUNSTATS
統計情報や再編成
の実施
REORG
権限・特権
SYSADM
モニタリング
アクセス状況やリ
ソースの状態の把
握と問題判別
DBADM
SYSCTRL
適切なアクセス権限、
実行権限の付与
SYSMAINT
監査
活動履歴の取得、
解析
-----------------------------------------------------------------------
回復管理
定期的なバックアップの
取得と障害復旧の計画
3
データ移動
データの抽出・投入・
移動
© 2011 IBM Corporation
DB2の主な運用管理項目
設計・構築
第5章
モデリング・論理設計
物理設計
高可用性設計
定例運用
第4章
データベースメンテナンス
-REORGCHK
-REORG
-RUNSTATS
第7章
- ログ管理
第7章
パフォーマンス管理
- パフォーマンスモニタリング
4
回復管理
-BACKUP
-回復履歴ファイル
セキュリティ管理
- 認証
- 許可
- 監査
ワークロード管理
-WLM
- db2プロセス
- 障害モニタリング
パラメータ設計
非定例運用
起動/停止
-インスタンス
-DB
稼動管理
第6章
第6章
第6章
回復管理
-RESTORE
第4章
第4章
変更管理
-レジストリー変数変更
-パラメーター変更
-バッファープール変更
-コンテナー追加
-コンテナーサイズ拡張
-フィックスパック適用等
データ移動ユーティリティ
-LOAD
-IMPORT
-EXPORT
© 2011 IBM Corporation
表のメンテナンス
• データの抽出・投入・移動
• 統計情報の収集
• 表の再編成
5
© 2011 IBM Corporation
データの抽出・投入・移動のためのユーティリティ
• DB2の世界ではDEL形式（CSV）のファイルを経由することが一般的
• データ抽出用のユーティリティ：EXPORT
•
データベースからフラット・ファイルへデータを抽出
•
EXPORTコマンドで記述されたSELECT文の結果のデータを抽出
• データ投入用のユーティリティ：IMPORT/LOAD
•
フラットファイルからデータベースへデータを投入
•
IMPORTは内部的にSQLを利用する
•
LOADはデータページを生成して直接表スペースへ書き込む
データベース
id
001
002
003
6
name
nm1
nm2
nm3
tel
12
34
56
EXPORT DEL形式
IXF形式
ASC形式
IMPORT/
LOAD
データベース
id
001
002
003
name
nm1
nm2
nm3
tel
12
34
56
© 2011 IBM Corporation
データ移動ユーティリティ
• Oracle SQL*Loaderの移行
• ダイレクトパス・ロード
• データベース・バッファーをバイパスし、データベースに直接フォーマット済みページを
書き込む
• 高速なデータ投入が可能
DB2 LOAD utility
• 従来型パス・ロード
• SQL*LoaderがINSERTを生成して、データベースに対してデータを投入する
• ダイレクトパスと比較して遅い
DB2 IMPORT utility
• 参照制約やチェック制約の制約で従来型パスを使用している場合、DB2ではLOADが使用
可能。（LOAD完了後にSET INTEGRITYコマンドを実行する）
7
© 2011 IBM Corporation
データ移動ユーティリティ
• SQL*Loader のコントロール・ファイル
• 固定長フォーマットをロードするコントロールファイルの例
• 固定長フォーマットの場合、POSITION() 指定を METHOD Lを用いた指定に
変更する
Oracle SQL*Loader control file
LOAD DATA
INFILE '/home/ora_usr/accounts.dat'
INTO TABLE accounts
( acct_id POSITION(0001:0003) NUMBER
,dept_code POSITION(0004:0006) CHAR
,acct_desc POSITION(0009:0100) VARCHAR2
,max_employees POSITION(0101:0103) NUMBER
,current_employees POSITION(0104:0106) NUMBER
,num_projects POSITION(0107:0107) NUMBER )
8
DB2 Load command
LOAD FROM
'/home/ora_usr/accounts.dat'
of ASC
METHOD L
( 0001 0003
,0004 0006
,0009 0100
,0101 0103
,0104 0106 )
INSERT INTO accounts
( acct_id
,dept_code
,acct_desc
,max_employees
,current_employees );
© 2011 IBM Corporation
データ移動ユーティリティ
• 可変長データの場合のコントロールファイル
•
可変長データの場合のコマンド比較
•
シンプルなコントロールファイルについては、下記のIBM Redbookで変換用のPerlスクリ
プトが使用可能
• Redbook "Oracle to DB2 Conversion Guide for LUW", Appendix E, “Converter for
SQL*Loader” on page 673.
• http://www.redbooks.ibm.com/abstracts/sg247048.html
Data file to load
101,"ACT","Major Bank Co.",30,11,4
301,"ACT","Large Telco Inc.",30,0,4
101,"IT","Huge Software Co.",50,0,4
203,"MKT","Basic Insurance Co.",15,0,3
Oracle SQL*Loader control file
INFILE '/home/ora_usr/accounts.dat'
INTO TABLE accounts
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED
BY '"'
( acct_id
,dept_code
,acct_desc
,max_employees
,current_employees
,num_projects )
9
DB2 Load command
LOAD FROM
'/home/ora_usr/accounts.dat'
of ASC
MODIFIED BY CHARDEL”” COLDEL,
METHOD P (1, 2, 3, 4, 5 )
INSERT INTO accounts
( acct_id
,dept_code
,acct_desc
,max_employees
,current_employees );
© 2011 IBM Corporation
データ移動ユーティリティ
• db2move
• Oracleのimp/expと同様、スキーマを指定したデータの抽出/投入/移動が可能。
• 指定可能なアクション
• EXPORT
•
•
指定した条件に一致するテーブルを抽出する。条件指定がない場合は全てのテーブルが対象。
抽出したテーブルの情報はdb2move.lstファイルに記録される
• IMPORT
•
db2move.lstファイルの情報を元にIMPORTによるデータの投入を行う
• LOAD
•
db2move.lstファイルの情報を元
に、LOADによるデータの投入を
行う
• COPY
•
テーブルのコピーを行うオプション。
同じデータベース内だけでなく、複
数のデータベースをまたがったコ
ピーが可能
SRCDB
DB2MOVE
Schema: SANTA
Views
Table
Table
Schema TEST
Trigger
Index
Index
Sequence
View
Table
Table
Trigger
Routine
db2move SRCDB COPY –sn “SANTA” -co target_db
TESTDB –u db2admin –p password
10
TESTDB
Index
Index
Sequence
Routine
© 2011 IBM Corporation
データ移動ユーティリティ
• カーソルからのLOAD実行
• 通常LOADはフラットファイルを入力データとするが、カーソルを使用して
SELECT結果を直接ロードすることも可能
• 移動するデータを一旦ファイルへ蓄積する必要がない
DECLARE C1 CURSOR FOR SELECT * FROM SOURCE_TABLE;
LOAD FROM C1 OF CURSOR REPLACE INTO TARGET_TABLE;
• 複数のデータベース間でカーソルを使用したLOADも可能
• カーソル宣言の中で、ターゲットとなるデータベース接続情報を指定
DECLARE C1 CURSOR DATABASE SRCDB USER user1 USING
password FOR SELECT * FROM SOURCE_TABLE;
LOAD FROM C1 OF CURSOR REPLACE INTO TARGET_TABLE;
カーソルからのロード
リモート・データベースからのロード
ソース・データベース
ターゲット・データベース
1. ソースDBへ
接続
SELECT結果をターゲット表
に直接LOADする
11
2. 結果セットをカーソルから取得し、ターゲット・
データベースに直接投入
© 2011 IBM Corporation
ブランク・ページ
12
© 2011 IBM Corporation
統計情報の収集
• 統計情報を最新の状態に更新する
• 現時点の統計情報に更新することで、最新の統計情報によるアクセス・パスが選
択される
•
•
静的SQLの場合、BIND時に既にアクセス・パスが決まっており、RUNSTATS後にREBINDしない
限り、アクセス・パスへの影響は受けない
動的SQLの場合、実行時にBINDが行われアクセス・パスが決まるので、RUNSTATS後のパ
フォーマンスは変化する可能性有り
• RUNSTATSを取得すべきタイミング
•
•
•
•
索引が作成された時
表のデータがＲＥＯＲＧされた時
表および索引のデータの１０－２０％がUPDATE/DELETE/INSERTされた時
アプリケーションをＢＩＮＤする前
• RUNSTATS実行中には表への書き込み・読み込みアクセスが共に可能
SQLステートメント
最適なアクセス・プラン
システム・カタログ表
・表統計
・列統計
・列分布統計
・索引統計
13
統計情
報
オプティマイザー
© 2011 IBM Corporation
統計情報の収集
• 基本的なRUNSTATSコマンド
•
•
表及び索引の統計情報を収集する
ターゲットとなる表にはスキーマ名の指定が必要
db2 RUNSTATS ON TABLE FOR DB2INST1.TABLE1 AND INDEXES ALL
• 分散統計を収集するRUNSTATSコマンド
•
•
表及び索引の統計情報に加え、列ごとの値の分布を収集する
パラメータマーカーを使用しないSQLの効率的なアクセスプラン決定に効果的
db2 RUNSTATS ON TABLE FOR DB2INST1.TABLE1 WITH
DISTRIBUTION AND INDEXES ALL
• サンプリングを使用したRUNSTATSコマンド
•
•
巨大な表の統計情報を収集する際に、全てのデータを評価するのではなく、一部の
データのみサンプリングを行う
SYSTEMサンプリングを使用した場合、指定された比率のページだけを読み込むため、
実行時間の短縮に効果的
db2 RUNSTATS ON TABLE DB2INST1.TABLE1 AND INDEXES ALL
TABLESAMPLE SYSTEM (10)
14
© 2011 IBM Corporation
統計情報の自動的な収集
• DB2のオートノミック機能の一環として、統計情報を自動的に収集する機能が強化され
ている
•
•
自動RUNSTATSとリアルタイムRUNSTATS
統計情報の自動収集を有効にした場合の流れ
•
① 非同期統計情報収集
•
•
② リアルタイム統計情報収集（同期統計情報収集）
•
•
統計情報が正確でないと判断した場合、SQLステートメントが最適化される前に統計情報を取得
③ ファブリケート統計情報の利用
•
•
できる限り、バックグラウンドで実行される統計情報収集を利用
なんらかの理由で、リアルタイム統計取得ができなかった場合、索引マネージャからのメタ・データを
使用して統計情報を作成（ファブリケート）する。
④ 上記②、③により統計情報が利用された場合、非同期統計情報の取得を要求する。
①非同期統
計情報収集．
②リアルタイム
統計情報収集
大量更新
③ファブリケート
統計の利用
RUNSTATS
時間の超過
より実情に
即したアク
セスプラン
← ④非同期統計取得要求
15
© 2011 IBM Corporation
参考：リアルタイム統計情報収集機能の設定方法
• DB CFG “AUTO_STMT_STATS” をONに設定する
•
同期統計収集のON/OFFを設定するパラメーター
•
動的に変更可能
•
DB2 V9.7からデフォルトでONとなっている
•
階層構造となっている点に注意
• AUTO_MAINT, AUTO_TBL_MAINT, AUTO_RUNSTATSが全てONに
なっている必要がある
•
データベースがアクティブにされてから最低 5 分間は、リアルタイム統計収集アクティ
ビティーは行われない
Automatic maintenance
(AUTO_MAINT) = ON
Automatic database backup
(AUTO_DB_BACKUP) = OFF
Automatic table maintenance
(AUTO_TBL_MAINT) = ON
Automatic runstats
リアルタイム統計情報収集
機能
(AUTO_RUNSTATS) = ON
Automatic statement statistics (AUTO_STMT_STATS) = ON
Automatic statistics profiling
Automatic profile updates
Automatic reorganization
16
(AUTO_STATS_PROF) = OFF
(AUTO_PROF_UPD) = OFF
(AUTO_REORG) = OFF
© 2011 IBM Corporation
表の再編成
• 表データのフラグメンテーションの解消、指定した索引順にデータを並び替えるた
めのユーティリティー
• 目的
•
フラグメンテーションの解消による
•
•
•
ディスク容量の削減
データ読み取りの処理効率向上
指定した索引順のデータの並び替えによる
•
•
索引スキャンのパフォーマンス向上
順次先読みの効率向上
• 再編成が必要なケース
•
SQLによる更新（UPDATE/DELETE/INSERT）により、フラグメンテーションが発生し
た場合
•
クラスター率が低下し、索引スキャン、順次先読みの効率が悪化した場合
• 再編成が必要でないケース
17
•
SQLによる更新処理のない、読み取りのみの表
•
ユニーク索引の列を条件とした１件検索の場合には、クラスター率が低くても問題なし
© 2011 IBM Corporation
REORG TABLEコマンドの実行
• REORG TABLEコマンドは表・索引を対象として実行可能
• 表の再編成 （索引は強制的に再作成される）
db2 REORG TABLE TUKIV97.TABLE1 INDEX TUKIV97.INDEX_TS1 USE
TEMPSPACE1
• 索引の再編成
db2 REORG INDEXES ALL FOR TABLE TABLE1
テーブル再編成の基本的な流れ
TS1
Tempspace1
1. REORG対象の表から
全レコードを取得し、一
時表スペースでソート
2. ソートが完了したデー
タを元の表スペースに書
き戻し
Table1
temp1
INDEX_TS1
drop &
create
18
3. 既存の索引を削除し、
新たに索引を作成
© 2011 IBM Corporation
DB2の提供するセキュリティ機能の概要
私はXXです。
USER
user1
PASSWORD a@x?s
1. 認証
・・・
本人確認
成りすましの防止
・・・
・・・
認証OK！
2. 権限・特権
アクセス権あ
りますか？
適切なアクセス権限、実
行権限の付与
SYSADM
アクセス権は
ありません。
DBADM
SYSCTRL
SYSMAINT
3. 監査
5. 暗号化
ネットワーク・トラ
フィック上のデー
タ暗号化
活動履歴の取得、解析
ACCESS
OK
-----------------------------------------------------------------------
password
ACCESS
;@[^*;:@_?>;sfd
OK
-032;l/mg,fdsl;
Fdsa;;lkb[¥¥:o;/.;
6. トラスティッド接続
3層アプリケーションでの
セキュリティー強化(9.5-)
19
4. LBAC
行単位、列単位のアク
セス制御 (9.1-)
アクセスできるの
はここまでです。
5. 暗号化
格納データの暗号化
ファイルの暗号化
© 2011 IBM Corporation
セキュリティ設計 参考資料
• DB2セキュリティ・デザインガイド
• DB2の提供するセキュリティ機能をシステム構築時に使用する際のデザイン
ガイド
• 目次
•
•
•
•
1. 概要、認証権限および特権
2. 監査機能編
3. LBAC編
4. 暗号化編
• IBM Web Siteにて入手可能
•
20
http://www-06.ibm.com/jp/domino01/mkt/dminfo.nsf/doc/00299AFE
© 2011 IBM Corporation