第４章 DB2運用の基本

(1)

第４章

DB2運用の基本

本書に含まれている情報は、正式なIBMのテストを受けていません。また、明記にしろ、暗黙的にしろ、なんらの保証もなしに配布されるものです。

この情報の使用またはこれらの技術の実施は、いずれも、使用先の責任において行われるべきものであり、それらを評価し、実際に使用する環境に統合する使用先の判断に依存しています。それぞれの項目は、ある特定の状態において正確であることがIBMによって調べられていますが、他のところで同じまたは同様の結果が得られる保証はありません。これらの技術を自身の環境に適用することを試みる使用先は、自己の責任において行う必要があります。

(2)

内容

• データベースの運用管理とは

• 表のメンテナンス

• データの投入・抽出・移動

• 統計情報の収集

• 表の再編成

• DB2のセキュリティ機能概要

(3)

SYSADM SYSCTRL SYSMAINT DBADM 権限・特権適切なアクセス権限、実行権限の付与監査活動履歴の取得、解析回復管理定期的なバックアップの取得と障害復旧の計画 --- --- --- ---- ---

---データベースの運用管理に伴う主なタスク

データ移動データの抽出・投入・移動モニタリングアクセス状況やリソースの状態の把握と問題判別

RUNSTATS

REORG

表のメンテナンス統計情報や再編成の実施

(4)

DB2の主な運用管理項目

定例運用

データベースメンテナンス -REORGCHK -REORG -RUNSTATS データ移動ユーティリティ -LOAD -IMPORT -EXPORT 稼動管理 - db2プロセス - ログ管理 - 障害モニタリング

非定例運用

変更管理 -レジストリー変数変更 -パラメーター変更 -バッファープール変更 -コンテナー追加 -コンテナーサイズ拡張 -フィックスパック適用等回復管理 -RESTORE 回復管理 -BACKUP -回復履歴ファイル起動/停止 -インスタンス -DB ワークロード管理 -WLM セキュリティ管理 - 認証 - 許可 - 監査パフォーマンス管理 - パフォーマンスモニタリング

設計・構築

モデリング・論理設計

物理設計

高可用性設計

第7章

第6章

第5章

第4章

パラメータ設計

第6章

(5)

• データの抽出・投入・移動

• 統計情報の収集

• 表の再編成

(6)

データの抽出・投入・移動のためのユーティリティ

• DB2の世界ではDEL形式（CSV）のファイルを経由することが一般的

• データ抽出用のユーティリティ：EXPORT

• データベースからフラット・ファイルへデータを抽出

• EXPORTコマンドで記述されたSELECT文の結果のデータを抽出

• データ投入用のユーティリティ：IMPORT/LOAD

• フラットファイルからデータベースへデータを投入

• IMPORTは内部的にSQLを利用する

• LOADはデータページを生成して直接表スペースへ書き込む

データベース

id

name tel

001 nm1

12 002 nm2

34 003 nm3

56 _ASC形式

IXF形式

DEL形式

EXPORT

IMPORT/

LOAD

データベース

id

name tel

001 nm1

12 002 nm2

34 003 nm3

56

(7)

• **Oracle SQL*Loaderの移行**

• ダイレクトパス・ロード

• データベース・バッファーをバイパスし、データベースに直接フォーマット済みページを

書き込む

• 高速なデータ投入が可能

• 従来型パス・ロード

• SQL*LoaderがINSERTを生成して、データベースに対してデータを投入する

• ダイレクトパスと比較して遅い

• 参照制約やチェック制約の制約で従来型パスを使用している場合、DB2ではLOADが使用可能。（LOAD完了後にSET INTEGRITYコマンドを実行する）

データ移動ユーティリティ

DB2 LOAD utility

DB2 IMPORT utility

(8)

• **SQL*Loader のコントロール・ファイル**

• 固定長フォーマットをロードするコントロールファイルの例

• 固定長フォーマットの場合、POSITION() 指定を METHOD Lを用いた指定に

変更する

データ移動ユーティリティ

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 )

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 );

(9)

• 可変長データの場合のコントロールファイル

• 可変長データの場合のコマンド比較

• シンプルなコントロールファイルについては、下記の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

データ移動ユーティリティ

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 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

Oracle SQL*Loader control file DB2 Load command

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

(10)

• LOAD

• db2move.lstファイルの情報を元に、LOADによるデータの投入を行う

• COPY

• テーブルのコピーを行うオプション。同じデータベース内だけでなく、複数のデータベースをまたがったコピーが可能

• db2move

• Oracleのimp/expと同様、スキーマを指定したデータの抽出/投入/移動が可能。

• 指定可能なアクション

• EXPORT

• 指定した条件に一致するテーブルを抽出する。条件指定がない場合は全てのテーブルが対象。 • 抽出したテーブルの情報はdb2move.lstファイルに記録される

• IMPORT

• db2move.lstファイルの情報を元にIMPORTによるデータの投入を行う

データ移動ユーティリティ

Table Table Index Index Sequence Views Trigger Routine Schema: SANTA Table Table Index Index Sequence View Trigger Routine Schema TEST DB2MOVE

db2move SRCDB COPY –sn “SANTA” -co target_db

SRCDB

(11)

• カーソルからのLOAD実行

• 通常LOADはフラットファイルを入力データとするが、カーソルを使用して

SELECT結果を直接ロードすることも可能

• 移動するデータを一旦ファイルへ蓄積する必要がない

• 複数のデータベース間でカーソルを使用したLOADも可能

• カーソル宣言の中で、ターゲットとなるデータベース接続情報を指定

データ移動ユーティリティ

DECLARE C1 CURSOR FOR SELECT * FROM SOURCE_TABLE;

LOAD FROM C1 OF CURSOR REPLACE INTO TARGET_TABLE;

1. ソースDBへ

接続

2. 結果セットをカーソルから取得し、ターゲット・ SELECT結果をターゲット表

カーソルからのロードリモート・データベースからのロード

DECLARE C1 CURSOR

DATABASE SRCDB USER user1 USING

password

FOR SELECT * FROM SOURCE_TABLE;

LOAD FROM C1 OF CURSOR REPLACE INTO TARGET_TABLE;

(12)

(13)

統計情報の収集

• 統計情報を最新の状態に更新する

• 現時点の統計情報に更新することで、最新の統計情報によるアクセス・パスが選

択される

• 静的SQLの場合、BIND時に既にアクセス・パスが決まっており、RUNSTATS後にREBINDしない限り、アクセス・パスへの影響は受けない • 動的SQLの場合、実行時にBINDが行われアクセス・パスが決まるので、RUNSTATS後のパフォーマンスは変化する可能性有り

• RUNSTATSを取得すべきタイミング

• 索引が作成された時

• 表のデータがＲＥＯＲＧされた時

• 表および索引のデータの１０－２０％がUPDATE/DELETE/INSERTされた時

• アプリケーションをＢＩＮＤする前

• RUNSTATS実行中には表への書き込み・読み込みアクセスが共に可能

・表統計・列統計・列分布統計

システム・カタログ表

SQLステートメント

統計情

オプティマイザー

最適なアクセス・プラン

(14)

統計情報の収集

• 基本的なRUNSTATSコマンド

• 表及び索引の統計情報を収集する

• ターゲットとなる表にはスキーマ名の指定が必要

• 分散統計を収集するRUNSTATSコマンド

• 表及び索引の統計情報に加え、列ごとの値の分布を収集する

• パラメータマーカーを使用しないSQLの効率的なアクセスプラン決定に効果的

• サンプリングを使用したRUNSTATSコマンド

• 巨大な表の統計情報を収集する際に、全てのデータを評価するのではなく、一部の

データのみサンプリングを行う

• SYSTEMサンプリングを使用した場合、指定された比率のページだけを読み込むため、

実行時間の短縮に効果的

db2 RUNSTATS ON TABLE FOR DB2INST1.TABLE1 AND INDEXES ALL

db2 RUNSTATS ON TABLE FOR DB2INST1.TABLE1 WITH

DISTRIBUTION AND INDEXES ALL

db2 RUNSTATS ON TABLE DB2INST1.TABLE1 AND INDEXES ALL

TABLESAMPLE SYSTEM (10)

(15)

統計情報の自動的な収集

• DB2のオートノミック機能の一環として、統計情報を自動的に収集する機能が強化され

ている

• 自動RUNSTATSとリアルタイムRUNSTATS • 統計情報の自動収集を有効にした場合の流れ • ① 非同期統計情報収集 • できる限り、バックグラウンドで実行される統計情報収集を利用 • ② リアルタイム統計情報収集（同期統計情報収集） • 統計情報が正確でないと判断した場合、SQLステートメントが最適化される前に統計情報を取得 • ③ ファブリケート統計情報の利用 • なんらかの理由で、リアルタイム統計取得ができなかった場合、索引マネージャからのメタ・データを使用して統計情報を作成（ファブリケート）する。 • ④ 上記②、③により統計情報が利用された場合、非同期統計情報の取得を要求する。 ①非同期統計情報収集． ②リアルタイム_{統計情報収集} ③ファブリケート統計の利用

大量更新

より実情に

即したアク

セスプラン

①非同期統計情報収集． ①非同期統計情報収集． RUNSTATS 時間の超過

(16)

参考：リアルタイム統計情報収集機能の設定方法

• 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 (AUTO_STATS_PROF) = OFF

Automatic profile updates (AUTO_PROF_UPD) = OFF Automatic reorganization (AUTO_REORG) = OFF

リアルタイム統計情報収集機能

(17)

表の再編成

• 表データのフラグメンテーションの解消、指定した索引順にデータを並び替えるた

めのユーティリティー

• 目的

• フラグメンテーションの解消による

• ディスク容量の削減 • データ読み取りの処理効率向上

• 指定した索引順のデータの並び替えによる

• 索引スキャンのパフォーマンス向上 • 順次先読みの効率向上

• 再編成が必要なケース

• SQLによる更新（UPDATE/DELETE/INSERT）により、フラグメンテーションが発生し

た場合

• クラスター率が低下し、索引スキャン、順次先読みの効率が悪化した場合

• 再編成が必要でないケース

• SQLによる更新処理のない、読み取りのみの表

• ユニーク索引の列を条件とした１件検索の場合には、クラスター率が低くても問題なし

(18)

REORG TABLEコマンドの実行

テーブル再編成の基本的な流れ

Table1 TS1 Tempspace1 1. REORG対象の表から 全レコードを取得し、一時表スペースでソート temp1 2. ソートが完了したデー タを元の表スペースに書き戻し INDEX_TS1 3. 既存の索引を削除し、 新たに索引を作成 drop & create

• REORG TABLEコマンドは表・索引を対象として実行可能

• 表の再編成（索引は強制的に再作成される）

• 索引の再編成

db2 REORG TABLE TUKIV97.TABLE1 INDEX TUKIV97.INDEX_TS1 USE

TEMPSPACE1

(19)

SYSADM SYSCTRL SYSMAINT DBADM ・・・・・・・・・ 1. 認証本人確認成りすましの防止 USER user1 PASSWORD a@x?s ACCESS OK 2. 権限・特権適切なアクセス権限、実行権限の付与 password ;@[- ^*;:@_?>;sfd -032;l/mg,fdsl; Fdsa;;lkb[¥¥- :o;/.; 5. 暗号化格納データの暗号化ファイルの暗号化 4. LBAC 行単位、列単位のアクセス制御 (9.1-) ACCESS OK 3. 監査活動履歴の取得、解析 6. トラスティッド接続 3層アプリケーションでのセキュリティー強化(9.5-) 5. 暗号化ネットワーク・トラフィック上のデータ暗号化 --- --- --- ---- --- ---認証OK！私はXXです。アクセス権ありますか？アクセス権はありません。アクセスできるのはここまでです。

DB2の提供するセキュリティ機能の概要

(20)