► CREATE PROCEDURE QSYS2.CHANGE_PLAN_CACHE_SIZE ( IN SIZE_IN_MEG INTEGER )
5. データベース管理
1. IBM i ナビゲーターの機能拡張
IBM i 7.1発表と同時に提供されている新機能・機能拡張は下記資料に解説があります。
【参考】
IBM i 7.1 テクニカル・ワークショップ2010春：DB2 for i & DB2 Web Query 最新情報© 2011 IBM Corporation
42
参考） DB2 for i 7.1 TR2 での拡張
主な機能拡張点
► IBM i 6.1
IBM i 7.1で実装された機能のサポート開始
‒SQEでの論理ファイルのサポート
‒SQE最適化処理効率の向上
‒プラン・キャッシュ設定のIPL前後での保持
►IBM i 6.1 & 7.1
ファイル・メンバー単位でのSSDへの配置
►IBM i 7.1
CONNECT BY句のサポート
►前提PTF
IBM i 6.1
‒DB Group PTF: SF99601 Level 19 + LICレベルに合わせた下記のPTF
‒(LIC V6R1M0): MF52486, MF51952
‒(LIC V6R1M1): MF52487, MF51957
IBM i 7.1
‒DB Group PTF: SF99701 Level 7 + SI43315
IBM i 7.1 TR2発表と同時に提供されている新機能・機能拡張は下記資料に解説があります。
【参考】
Power Systemsスキルアップセミナー IBM i (2011年 5月～6月)：IBM i 機能拡張詳細© 2011 IBM Corporation
43
インデックス活用の最新情報
© 2011 IBM Corporation
44
インデックス概説
© 2011 IBM Corporation
45
インデックス（索引）の重要性
インデックスを作成するメリット
► パフォーマンス向上への貢献
テーブルの検索速度の向上
統計情報としての利用（オプティマイザーの判断材料）
► システム資源使用率（ CPU ）の低減
オプティマイザーがインデックスの作成を必要と判断した場合、一時インデック スが作成され、 CPU 資源等を消費するため
インデックスを作成する際の考慮点
► メンテナンスコストの発生
データの挿入，削除，更新時にオーバーヘッドが発生
‒ ただしハイスペックの最新マシンでの影響は少ない
⇒ 不要なインデックスは削除する （削除方法は後述）
© 2011 IBM Corporation
46
参考） SQL を利用するメリット
SQL を利用するアプリケーションの増加
► DB2 Web Query
► パッケージソフト（ Java 、 RPG 、 PHP 、 COBOL ･･･）
► ODBC接続、JDBC接続・・・
開発生産性の向上
► コード数
► テストが容易（コンパイル不要、STRSQLだけでテスト可能）
► 複数のプログラミング言語から利用可能（ Java 、 RPG 、 PHP 、 COBOL ･･･）
► パフォーマンス
変化（データ量・システム資源など）に応じて動的にプランを変更可能 ← 統計情報が重要
SQL の特徴
► 取り出したいデータの要件（欲しい結果）を記述
データ取り出しの手順を記述しない
プログラムは要件を手順にして、その手順を記述
► データの増減にあわせて手順を自動的に最適化 ← 統計情報が重要
© 2011 IBM Corporation
47
参考）キー付き論理ファイルとインデックスの違い
IBM i ではインデックスの実体は論理ファイル
► ただしインデックスと論理ファイルは異なる
作成方法の違い
► 論理ファイル： DDS から CRTLF コマンドで作成
► インデックス： CREATE INDEX コマンドで作成
パフォーマンス上の違い
► 論理ページサイズ： 1 回の I/O でのデータの読み込みサイズに影響
論理ファイル：8KB
インデックス： 64KB
⇒インデックスを利用すると多くのデータを少ないI/O回数で処理できるためパフォーマンス
が向上（RPG/COBOLではプログラムロジックでアクセスが決定できるため、ページサイ
ズの小さいアクセスが好ましい）
© 2011 IBM Corporation
48
参考） SQE での論理ファイルサポート
下記の環境では SQE での論理ファイルをサポート
► IBM i 7.1 Base
► IBM i 6.1 ： SI44976 、 SI44978
Before ： SQL コード内で論理ファイルをテーブルに指定すると CQE にて実行
After ： SQL コード内で論理ファイルをテーブルに指定しても SQE にて実行
► ただしパフォーマンスの観点からはテーブル指定は「物理ファイル」もしくは「SQLテーブル」を 利用することを推奨
以下の条件の場合には対象外
► 論理ファイル内のフィールド項目として以下を含むものは対象外
マッピングフィールド
派生フィールド
SELECT/OMMIT もしくはJOIN フィールド
日付、時間、タイムスタンプフィールドを文字フィールドにマップしたもの
► Omnifind での論理ファイル利用
► 複数データスペースを持つ論理ファイル
► パーティションテーブルを跨る論理ファイル
► 論理ファイルサポートは読み込み専用、insert/update/deleteでの論理ファイルサポートはなし
© 2011 IBM Corporation
49
インデックスの作成
インデックスの作成のタイミング
①開発段階での作成
②推奨インデックスの作成
推奨インデックス（インデックス・アドバイザー）と一時インデックス
► OS が常にデータベース統計情報を自動収集（ DB2 for i の強み）
► SQLの解析時に、存在していれば実行計画に大きな改善が見られると考えられるイ
ンデックスを「インデックス・アドバイザー」が推奨（ V5R4 以降）
► 推奨されたインデックスのうち、オプティマイザーが関連する SQL の実行頻度を考 慮し、インデックスを作成した方がパフォーマンス上有効だと判断したインデックスは
「一時インデックス」として作成される（V5R4以降）
► 作成された一時インデックスはプラン・キャッシュに蓄積され、他の SQL も含めて再 利用される
IPLされる or プラン・キャッシュ・サイズを越えるまで再利用される
► 一時インデックスは統計情報としては利用されない
SE49173（IBM i 7.1）適用により、一時インデックスの列統計の一部が提供可能に
①インデックス作成のポイントは下記の資料を参考
iMagazine 2011.06 『IBM i SQLパフォーマンスチューニング』
© 2011 IBM Corporation
50
インデックス作成・確認・削除の流れ
①開発段階での インデックス作成
SQL の利用
オプティマイザーによる インデックスの推奨
②推奨インデックスを基にした インデックスの作成
【 Tips1 & Tips2 】
③インデックスの 使用頻度の確認と削除
【Tips3 】
© 2011 IBM Corporation
51
②推奨インデックスを基にしたインデックスの作成
iNavi から推奨インデックスを確認
► 「勧告索引を圧縮」を選択することで、重 複したインデックスが排除され最小限の インデックスのみ表示される
► 勧告索引の圧縮が利用できない場合は 通常の「索引アドバイザー」を選択
インデックスの作成が推奨されている テーブルが表示され、手動でインデック スを作成可能
► 推奨された回数やMTI（一時インデック ス）の作成回数も確認可能
► 一括作成方法 Tips1 を参照（後述）
操作方法
► IBM i Navigator for Windows
→ DB上で右クリック
→ 索引アドバイザー
→ 勧告索引を圧縮（索引アドバイザー）
© 2011 IBM Corporation
52
参考）推奨索引のシステム・テーブル（ QSYS2/SYSIXADV ）
列名 システム列名 データ・タイプ 説明
TABLE_NAME TBNAME VARCHAR(258) 索引が推奨されるテーブル
TABLE_SCHEMA DBNAME VARCHAR(128) SQL テーブルを含むスキーマ
SYSTEM_TABLE_NAME SYS_TNAME CHAR(10) 索引が推奨されるシステム・テーブル名
PARTITION_NAME TBMEMBER CHAR(10) 索引のパーティションの詳細
KEY_COLUMNS_ADVISED KEYSADV VARCHAR(16000) 推奨索引の列名
LEADING_COLUMN_KEYS LEADKEYS VARCHAR(16000) 先頭に来る、順序に依存しないキー。 再配列され、推奨される索引を満たす COLUMNS_ADVISED フィールドの先頭のキー。
INDEX_TYPE INDEX_TYPE CHAR(14) 基数 (デフォルト) または EVI
LAST_ADVISED LASTADV TIMESTAMP この行の最終更新時刻
TIMES_ADVISED TIMESADV BIGTINT この索引が推奨された回数
ESTIMATED_CREATION_TIME ESTTIME INT 索引作成のための見積もり秒数
REASON_ADVISED REASON CHAR(2) 索引が推奨されたされた理由 (コード化)
LOGICAL_PAGE_SIZE PAGESIZE INT 索引の推奨ページ・サイズ
MOST_EXPENSIVE_QUERY QUERYCOST INT 照会の実行時間 (秒)
AVERAGE_QUERY_ESTIMATE QUERYEST INT 照会の平均実行時間 (秒)
TABLE_SIZE TABLE_SIZE BIGINT 索引推奨時のテーブル内の行数
NLSS_TABLE_NAME NLSSNAME CHAR(10) 索引で使用する NLSS テーブル
NLSS_TABLE_SCHEMA NLSSDBNAME CHAR(10) NLSS テーブルのスキーマの名前
MTI_USED MTIUSED BIGINT 最適化プログラムによってこの特定の保守済み一時索引 (MTI) が使用された回数。 最適化プ
ログラムは、永続索引が作成されると、一致する MTI の使用を停止します。
MTI_CREATED MTICREATED INTEGER 最適化プログラムによってこの特定の保守済み一時索引 (MTI) が作成された回数。 MTI は、
システム IPL を通して持続するわけではありません。
LAST_MTI_USED LASTMTIUSE TIMESTAMP この特定の保守済み一時索引 (MTI) が、照会のパフォーマンス向上のために最適化プログラム によって最後に使用された時刻を表すタイム・スタンプ。 「MTI の最終使用 (MTI Last Used)」フィールドはブランクの場合があります。ブランク・フィールドはこの推奨に完全に 一致する MTI が、この索引推奨を生成した照会によって使用されたことがないことを示してい ます。
AVERAGE_QUERY_ESTIMATE _MICRO
QRYMICRO BIGINT 索引推奨を駆動した照会の平均実行時間 (マイクロ秒単位)
EVI_DISTINCT_VALUES EVIVALS INTEGER 推奨された EVI 索引を作成する際に使用する推奨値。CREATE INDEX SQL ステートメントの WITH n DISTINCT VALUES 文節内では、この値は n。
INCLUDE_COLUMNS INCLCOL CLOB(10000) 索引作成のための EVI INCLUDE 式。
FIRST_ADVISED FIRSTADV TIMESTAMP この行がいつ挿入されたか。
SYSTEM_TABLE_SCHEMA SYS_DNAME CHAR(10) テーブル・スキーマのシステム名。
© 2011 IBM Corporation
53
アクセス・プランの実装に使用された回数 最適化のための情報提供元として使用された回数
③インデックスの使用頻度の確認と削除
インデックスの使用回数をリアルタイムで確認可能
► 使用されてないインデックスは更新系作業の無駄な負荷となるため削 除することを推奨
利用頻度が一定以下のインデックス一括削除方法は後述
ただし、オプティマイザーへの情報提供は重要
► 「照会使用回数」が 0 であっても
「照会統計使用回数」がカウントされている場合は削除しない
► 特に EVI は照会に使用されなくとも正確な情報の提供元として重要
確認方法
► iNavi → データベース → スキーマの指定 → 「索引」をクリック
© 2011 IBM Corporation
54
参考）インデックス使用状況の確認方法
DB2 for i のカタログ表を SQL で参照してインデックスの使用状況を確認可能
► インデックスの使用状況が格納されているシステム・ビュー（システム作成のLF）
オブジェクト名 ： QSYS2/SYSIXSTAT
SQL でのビュー名 ： SYSINDEXSTAT
< サンプルのSQL >
SELECT
INDEX_NAME,
SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME,
LAST_QUERY_USE,
LAST_STATISTICS_USE, QUERY_USE_COUNT,
QUERY_STATISTICS_COUNT, LAST_USED_TIMESTAMP, DAYS_USED_COUNT,
INDEX_SIZE FROM QSYS2.SYSINDEXSTAT WHERE
SYSTEM_TABLE_SCHEMA = ‘UTFTEST‘ →ライブラリー名 AND SYSTEM_TABLE_NAME = ‘TOKMASP’ ; →テーブル名
SQLから索引が使用された最新の日時