IBM Presentations: Smart Planet Template

(1)

DB2 for i 最新情報 & 再認識

日本アイ･ビー･エム株式会社

システム製品テクニカル・セールス

Power Systems テクニカル・セールス

Revision 1.3

(2)

特記事項

当資料で解説される項目の更に詳細な説明は、製品から提供されるマニュアル、オンライン・ヘルプ、Web上の情報を参照してください。当資料は、2011年11月現在のIBMその他の製品情報に基づいて作成されております。この資料に含まれる情報は可能な限り正確を期しておりますが、日本アイ・ビー・エム株式会社による正式なレビューは受けておらず、当資料に記載された内容に関して日本アイ・ビー・エム株式会社が何ら保証をするものではありません。したがって、この情報の利用またはこれらの技法の実施はひとえに使用者の責任においてなされるものであり、当資料の内容によって受けたいかなる被害に関しても一切の保証をするものではありませんのでご了承ください。

(3)

3

商標

以下の用語は、アメリカ合衆国、あるいは他国、あるいは両国でのInternational Business Machines Corporationの商標です。

"Oracle" "Java"はOracle Corporation およびその子会社、関連会社の米国およびその他の国における登録商標。

"Microsoft" "Windows" "Windows NT" および"Windows"ロゴはMicrosoft Corporationの米国およびその他の国における商標。 "UNIX"はThe Open Groupの米国およびその他の国における登録商標。

"Linux"はLinus Torvaldsの米国およびその他の国における商標。他の会社、製品、およびサービス名は、その会社の商標あるいはサービスマークかもしれません。このプレゼンテーションに含まれるサードパーティーに関連する題材は、これらのサードパーティーから得られた情報に基づいています。これらの情報の正確さの確認のための、いかなる努力もなされていません。このプレゼンテーションは、いかなるサードパーティー製品またはサービスの、IBMによる推薦あるいは指示を表したり、ほのめかすものではありません。 IBM ® IBM (logo) ® AIX ® AIX 5L AS/400 ® AS/400e BladeCenter ® DB2 ® DB2 Universal Database e(logo)server ®

Enterprise Storage Server ®

eServer iSeries ® i5/OS ® i5/OS(logo) Lotus ® Lotus Domino ® MQSeries ® Operating System/400® OS/400 ® POWER POWER5 POWER5+ POWER6 ® POWER6+ POWER7 Power Architecture ® Power Systems pSeries ® Rational ® System i ® System i5 ® System p ® System p5 ® System Storage ® System Storage DS ® stylized ® Tivoli ® TotalStorage ® WebSphere ® Workplace xSeries ® zSeries ® 400 ®

(4)

1. DB2 for i 再認識

1. DB2 for i 概説

2. DB2 for i の競合優位性

1. 運用管理

2. パフォーマンス

2. DB2 for i 最新情報

1. 機能拡張

3. インデックス

活用の最新情報

1. インデックス

概説

2. インデックス

活用 Tips

(5)

5

(6)

6

(7)

DB2 for i フォーカスエリア



自己管理データベース

► 自動化によるTCOの削減

► セキュリティ機能と監査機能の実装



信頼性とスケーラビリティ

► 簡単で最高のスケーラビリティ

（1coreから32coreまで）

► 統合されたトランザクション管理

► 高度で柔軟なログ機能



オープン化・業界標準のサポート

► 戦略的なインターフェースであるSQL対応

►

► ポータビリティ、互換性



アプリケーションの柔軟性

► SQL、RPG、COBOL･･･

► SOA対応



ビジネス・インテリジェンス

► エンドユーザーへのデータ照会、レポーティング

IBM SOA Foundation

Skills & Support

Software

(8)

SQL標準対応とマルチプラットフォームからの互換性

DB2 for I

へのアク

セス方法

プラットフォーム

AIX

Linux

Windows

IBM i

DRDA



JDBC



ODBC



.NET



OLE DB



DDM



CLI



0 10 20 30 40 50 60 70 80 90

DB②

DB①

DB2 for i 7.1

DB④

DB③

ISO/ANSI SQL 標準との適合

マルチプラットフォームから

DB2 for i にアクセス可能

SQLやRPG、COBOL、C、C++、Java などからDB2 for i にアクセス可能

DB2 for i はSQL2008 Coreに100%準拠

(9)

参考）標準SQL規格



（SQLは登場）当初は特に統一標準規格が存在しない状況で、各リレーショナルデータベース管理

システム

(RDBMS) ベンダーごとにさまざまな拡張がなされてきた。

近年になってANSI、後にISO

で言語仕様の標準化が行われており、制定された年ごとにSQL86, SQL89, SQL92, SQL:1999,

SQL:2003, SQL:2008 などの規格があるが、対応の程度はベンダーごとにバラバラなのが実情で

ある。これは標準SQL策定に時間がかかりすぎたことにより、ビジネスの現状から早期の機能拡張

が迫られたベンダーの都合と、独自構文を頻繁に利用していたユーザに対し、互換性保持を保証す

る必要もあったためである。

► 出典：http://ja.wikipedia.org/wiki/SQL

年規格名称別称説明 1986 SQL86 SQL87 ANSIによって最初に発表された最初の規約。1987年にISOによって批准された 1989 SQL89 マイナーバージョン 1992 SQL92 SQL2 メジャーバージョン

1995 SQL/CLI コールレベルインターフェース(Call Level Interface)

業界標準になったODBCAPIのインタフェースに相当する機能を国際標準化した規格 1996 SQL/PSM 永続格納モジュール(Persistent Storage Module)

一般的にストアドプロシージャと呼ばれる機能を国際標準化した規格 1999 SQL:1999(SQL99) SQL3 RDBMSのための完全な言語になることを目指した仕様

2003 SQL:2003 2008 SQL:2008

(10)

データベースアクセス：ODBCサポート/JDBCサポート



オープン標準のDBアクセス規格をサポート

► EXCEL、VBアプリケーション、.NETアプリケーション等からDB2 for i へアクセス可能



DB2 for i 用ODBCドライバー



DB2 for i 用JDBC(Java) ドライバー

(11)

DB2 for i と

IBM DB2 ファミリー



IBM DB2ファミリーの3つの製品カテゴリーの親和性が高い

► SQLなど基本機能については各DB2ファミリー間での互換性が高い

► ODBC, JDBCなどからのアクセスも同様にファミリー間での互換性を高めている



今後も更にファミリー間の互換性を高める方針

► 例）XMLデータベース対応：DB2 LUW → DB2 for i （IBM i 7.1）

DB2 for z/OS

DB2 for i

DB2 for LUW

L – Linux

U – UNIX

W - Windows

z/OS 用 DB2

IBM i 用 DB2

_{（OSに統合）}

Linux, UNIX,

Windows用 DB2

アプリケーションからはどのDB2ファミリーに対しても同様な操作が可能

(12)

IBM DB2ファミリー間の接続・連携



DB2ファミリーではDRDA接続をサポート

► DB2 for i ではDRDA機能はデータベース（OS）標準機能として別途費用必要なし

► Dprop-R（別途有償ソフトウェア）を使用してDB2ファミリー間でのレプリケーションも

可能



DB2 for i はEBCDICをサポートし、既存データベースとの接続、連携、コード変

換も標準機能で提供



DB2 for i UNICODEでのデータ保持サポート

DB2 for IBM i

DB2 for LUW

DB2 for z/OS

DB2 for IBM i

DB2 for LUW

DB2 for z/OS

DRDA接続

・データベース接続・コード変換（必要な場合）・Dprop-Rによるレプリケーション

(13)

13

(14)

DB2 for i のアーキテクチャー上のアドバンテージ



DB2 for i はOSより下位層のマイクロコードで実装



DB2 for i 以外の他のデータベースはOS上のアプリケーションとして実行



データベース使用者の作業

► データベースパフォーマンス監視

► データベースの物理設計

► 日常のデータベース管理

► バックアップ・回復設計が複雑

DB2 for i

他の

データベースアプリ

ケーション

IBM i (OS/400)

Linux, UNIX, Windows

などのOS

業務アプリケーション

ハードウェア

DB2 for i は

上記のようなDBの煩雑性から

解放されるDB

DBオブジェクト

=OSオブジェクト

(15)

テーブルスペース

ログファイル

制御ファイル

テーブル

ビュー

インデックス

OS から見た時の「ファイル」

「データベース」がひとつの世界

ex) バックアップ・リストアの単位はデータベース全体

データベース

直接データベースの状態/状況はわからない

データベースを起動し、その都度の状況を確認

一般的なデータベース製品の構造

(16)

16

一般的なDBではファイルの中身が理解不能

OSからデータベースファイルのデータやファイル構造を把握することは不可。OSからの統計情報の取得

や、DBファイルのバックアップなどは、DBアプリケーションを起動した上で操作が必要。

(17)

テーブル

ビュー

インデックス

OS から見た時の「オブジェクト」

OS オブジェクト

= DB オブジェクト

ex) オブジェクト

= 個別のテーブル = バックアップ・リストア可能

ジャーナル

テーブルスペース

ログファイル

制御ファイル

DB2 for i の構造

(18)

18

どんな制約がついているか、やキー項目の数、レコード件数、フィールド数、レコード長など、

通常のデータベース製品では統計を取らないとわからない情報がリアルタイムで反映されている

(19)

DB2 for i

機能

他のDBアプリケーション

OS組み込みのため

常にバージョンの整合性は保障される

管理不要

バージョン管理

OSのバージョンに合わせて

データベースのバージョンを

ユーザーが管理

OSとデータベースの整合性チェックは

IBMで全て実施して出荷

ユーザーの追加コスト不要

OSバージョンアップ時、

PTF適用時のDB互換性検証

OSとデータベースは

別S/Wとして管理が必要

ユーザーが費用負担して検証必要

OS組み込みのためOSと

データベースのセキュリティは共通

個別設定不要

セキュリティ設定

OSのセキュリティ設定は

データベースと別個

データベース用に別個に設定

OS組み込みのため

データベースの起動、停止は不要

個別管理不要

データベースの起動・停止、

バックアップなどのコマンド

OSコマンドとは別個のデータベース

専用コマンドが必要

個別に管理が必要

DB2 for i は高パフォーマンスかつTCO削減・長期的なデータベース資産の保護に優位

DB2 for i の運用上の優位性



DB2 for i はOSに組み込まれています。



他のデータベース

はOS上のアプリケーションとして動作するため、無駄な管理コストやさまざまな

問題が潜在しています。

(20)

DB管理者の標準的なタスク

IBM i DB2 UDBでのオートノミック機能、ユーザータスク

DASDスペースの割振りの管理 (完全自動 IBM i) テーブルスペースの割振りと拡張の見直し (完全自動 IBM i) インデックスそのものの見直しとその分散化 (完全自動 IBM i) アプリケーションとDBとの再結び付け (完全自動 IBM i) データベースの整合性の保持 (完全自動 IBM i) データベース統計情報の更新 (完全自動 IBM i) OSとDBに対するユーザーセキュリティの同期化 (完全自動 IBM i) ハードやソフトのアップグレード時にデータのリロード (完全自動 IBM i) データベースのバックアップと・カバリー IBM iコマンドで実施。OSの他のオブジェクトと全く同一の手法にてバックアップ・リカバリーが可能(DB独自の管理コマンドは無い) テーブルのインデックスを生成・見直し IBM iのオートノミック機能で推奨値を提示。高度なデータベーススキル不要パフォーマンス分析とチューニング IBM i組込み機能で詳細なパフォーマンスデータを収集、分析可能データベース統計情報の収集 IBM iが自動収集・更新データのレプリケーションや一元化 IBMおよびサードベンダー複数製品で他データベース製品とも連携可能

一般のデータベース製品、ストレージは管理に多くの作業・時間を必要とします。しかし

IBM i ではディスク空き

スペース管理、テーブルスペース管理、エクステント管理等ストレージ管理、データベース管理はSLSアーキテ

クチャーにより抽象化されています。このためユーザーの作業が不要か、非常に簡単な操作で可能です。

データベース運用

(21)

21

参考）SAP R3導入事例によるデータベース管理工数の比較



SAP 利用ユーザーでの UNIX + 他DB 使用時と

IBM i 移行後の管理工数の比較



他DB で必要となる以下の項目はIBM i では一切不要

発生しうる問題放置した場合の影響度合対処方法作業時間* 作業がもたらすユーザーへの影響テーブルが一杯になるシステム停止エクステント領域の調整 30分程度関連業務の停止表領域が一杯になるシステム停止表領域の拡張 30分程度関連業務の停止作業後1日かけてバックアップバックアップ時は R/3全体停止ファイルシステムが一杯になるシステム停止ファイル・システムの拡張 1時間程度関連業務の停止作業後1日かけてバックアップバックアップ時は R/3全体停止ログ領域が一杯になるシステム停止古いログをバックアップ後に削除 30分程度 R/3全体停止データの断片化パフォーマンス劣化テーブル再編成 30分程度なし DB再編成バックアップ含めて 2～3日程度 R/3全体停止

専任のDBアドミニストレータが

要した作業工数実績

*これらのデータは1事例での値です。全てのケースに当てはまるものではありません。

(22)

22

(23)

23

SAP BI Mixed Load (BI-MXL) ベンチマーク結果

(24)

24

参考）

_{SAP BI Mixed Load (BI-MXL)}



SAP BI Mixed Load 標準アプリケーション・ベンチマーク

► SAP NetWeaver （SAPの統合技術基盤）

► クエリー・アクティビティーおよびロード/アップデート・アクティビティーを並行して実施

► 3億件のレコードが対象

► 詳細は下記に定義されています



http://www.sap.com/solutions/benchmark/bimxl.epx



SAP NetWeaver ベンチマーク（Business Intelligence カテゴリー）

► BI Data Mart (BI-D)：あらかじめロードされているデータを単純に検索

► Mixed Load (BI-MXL)：データの挿入/更新が行われている最中に変化するデータをリアルタイ

ムに検索



【参考】

Mixed Load (BI-MXL)の特徴

► 静的なデータを読み出すのみのベンチマークに比べ、挿入/更新が行われている最中に、デー

タに対して動的にリアルタイム検索する方が、処理の複雑性が高いといえる

► 日本のオンライン・トランザクションでは、このように挿入

/ 更新が行われるデータに対して照会

や分析帳票の生成が実行されるケースが多い

► そのため、「Mixed Load (BI-MXL)」は、SAP のベンチマークの中でも複雑でありながら、日本

での実際のアプリケーションの使われ方に近いと言える

(25)

25

SAP BI Mixed Load (BI-MXL) ベンチマーク結果（再掲）

(26)

POWER6

→ POWER7

56% 向上

IBM i 6.1 → 7.1

15% 向上

Query N avi gati on Steps/hour

Power 570 i 6.1 Power 750 i 6.1 Power 750 i 7.1

プロセッサー P6, 8-core 4.20 GHz P7, 8-core 3.30 GHz P7, 8-core 3.30 GHz メモリー 128 GB 128 GB 128 GB スループット (Query Navig.Steps/ hr) 154,920 241,526 278,462

POWER6からPOWER7への更改で56%向上、IBM i 6.1からIBM i 7.1へのアップグレードで15%向上

POWER5

→ POWER6

IBM i 5.4 → 6.1

59% 向上

(27)

27

(28)

28

(29)

29

developerWorks：DB2 for i 最新情報の提供

カテゴリー別最新情報

PTFの提供スケジュール

(30)

30

DB2 for i グループPTF：提供スケジュール（2011年）

(31)

31

(32)

32

(33)

33

参考）CONNECT BY句のサポート



IBM i 7.1 – CONNECT BY句のサポート

► 再帰照会の際に利用可能でOracleの階層問い合わせに相当

例> 社員一覧から所属長コードに沿って階層順に照会

SELECT level, lastname, firstname

FROM nakalib.emplist

START WITH mgrno IS NULL

CONNECT BY PRIOR empno = mgrno

ORDER BY level

NAKALIB/EMPLIST

(34)

34

参考）CONNECT BY句のサポート



IBM i 7.1 – CONNECT BY句のサポート

► CONNECT BY句を利用しない場合はWITH, UNION ALLを利用して記述が必要

例>上記と同等の結果を取得

WITH n(level, empno, lastname, firstname) AS

(SELECT 1, empno, lastname, firstname

FROM nakalib.emplist

WHERE mgrno IS NULL

UNION ALL

SELECT n.level + 1, nplus1.empno, nplus1.lastname, nplus1.firstname

FROM nakalib.emplist AS nplus1, n

WHERE n.empno = nplus1.mgrno)

SELECT level, lastname, firstname

FROM n

ORDER BY level

参考:

http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzhierquery.htm

(35)

35

(36)

36

(37)

37

(38)

38

(39)

IPL前後でのSQEプラン・キャッシュ・サイズの保持



従来

► IPLを実行するとプランキャッシュのサイズがデフォルト値（512MB）に戻る

► プランキャッシュサイズを変更している場合はIPLの度に再設定が必要



PTF適用後

► 設定済みのプラン・キャッシュ・サイズの設定がIPL後にも保持される



プロシージャー

► CREATE PROCEDURE QSYS2.CHANGE_PLAN_CACHE_SIZE ( IN

SIZE_IN_MEG INTEGER )



IN SIZE_IN_MEG INTEGER : 設定したいプランキャッシュサイズ（MB）



サンプル）プランキャッシュサイズを1024MBに設定したい場合

(40)

IPL前後でのSQEプラン・キャッシュ・サイズの保持

①変更前

③変更後

②設定

(41)

参考）DB2 for i 7.1 最新情報

1. XML サポート

2. DB2 ファミリー、他のDBMSとの互換性向上

1. MERGE

2. CREATE時のREPLACE オプション

3. ロック待機の回避による並行性の改善

4. グローバル変数

5. CALLステートメント中の表現の拡張

6. パラメータマーカーの拡張

7. ルーチンの中で配列のサポート

3. アプリケーション開発の強化

1. フィールドプロシジャ

(FIELDPROCS)

2. 組込みSQLでの結果セット受取りのサポート

3. QSYS2.SQL_CANCEL プロシジャー

4. 三部構成名称

5. BIT スカラー関数

6. 区分化表の拡張

7. SQL中でMQ Series 関数の呼出し

4. パフォーマンス強化

1. SQE機能拡張

5. データベース管理

1. IBM i ナビゲーターの機能拡張

IBM i 7.1発表と同時に提供されている新機能・機能拡張は下記資料に解説があります。

(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発表と同時に提供されている新機能・機能拡張は下記資料に解説があります。

(43)

43

(44)

44

(45)

インデックス（索引）の重要性

インデックスを作成するメリット

► パフォーマンス向上への貢献



テーブルの検索速度の向上



統計情報としての利用（オプティマイザーの判断材料）

► システム資源使用率（CPU）の低減



オプティマイザーがインデックスの作成を必要と判断した場合、一時インデック

スが作成され、CPU資源等を消費するため

インデックスを作成する際の考慮点

► メンテナンスコストの発生



データの挿入，削除，更新時にオーバーヘッドが発生

‒

ただしハイスペックの最新マシンでの影響は少ない

⇒ 不要なインデックスは削除する

（削除方法は後述）

(46)

参考）SQLを利用するメリット



SQLを利用するアプリケーションの増加

► DB2 Web Query

► パッケージソフト（Java、RPG、PHP、COBOL･･･）

► ODBC接続、JDBC接続・・・



開発生産性の向上

► コード数

► テストが容易（コンパイル不要、STRSQLだけでテスト可能）

► 複数のプログラミング言語から利用可能（Java、RPG、PHP、COBOL･･･）

► パフォーマンス



変化（データ量・システム資源など）に応じて動的にプランを変更可能

← 統計情報が重要



SQLの特徴

► 取り出したいデータの要件（欲しい結果）を記述



データ取り出しの手順を記述しない



プログラムは要件を手順にして、その手順を記述

► データの増減にあわせて手順を自動的に最適化

←

統計情報が重要

(47)

参考）キー付き論理ファイルとインデックスの違い



IBM i ではインデックスの実体は論理ファイル

► ただしインデックスと論理ファイルは異なる



作成方法の違い

► 論理ファイル：DDSから

CRTLFコマンド

で作成

► インデックス：

CREATE INDEXコマンド

で作成



パフォーマンス上の違い

► 論理ページサイズ：1回のI/Oでのデータの読み込みサイズに影響



論理ファイル：8KB



インデックス：64KB

⇒インデックスを利用すると多くのデータを少ないI/O回数で処理できるためパフォーマンス

が向上（RPG/COBOLではプログラムロジックでアクセスが決定できるため、ページサイ

ズの小さいアクセスが好ましい）

(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での論理ファイルサポートはなし

(49)

インデックスの作成



インデックスの作成のタイミング

①開発段階での作成

②推奨インデックスの作成



推奨インデックス（インデックス・アドバイザー）と一時インデックス

► OSが常にデータベース統計情報を自動収集（DB2 for i の強み）

► SQLの解析時に、存在していれば実行計画に大きな改善が見られると考えられるイ

ンデックスを「インデックス・アドバイザー」が推奨（V5R4以降）

► 推奨されたインデックスのうち、オプティマイザーが関連する

SQL の実行頻度を考

慮し、インデックスを作成した方がパフォーマンス上有効だと判断したインデックスは

「一時インデックス」として作成される（V5R4以降）

► 作成された一時インデックスはプラン・キャッシュに蓄積され、他の

SQL も含めて再

利用される



IPLされる

or プラン・キャッシュ・サイズを越えるまで再利用される

► 一時インデックスは統計情報としては利用されない



SE49173（IBM i 7.1）適用により、一時インデックスの列統計の一部が提供可能に

①インデックス作成のポイントは下記の資料を参考

iMagazine 2011.06 『IBM i SQLパフォーマンスチューニング』

(50)

インデックス作成・確認・削除の流れ

①開発段階での

インデックス作成

SQLの利用

オプティマイザーによる

インデックスの推奨

②推奨インデックスを基にした

インデックスの作成

【Tips1 & Tips2 】

③インデックスの

使用頻度の確認と削除

(51)

②推奨インデックスを基にしたインデックスの作成



iNaviから推奨インデックスを確認

► 「勧告索引を圧縮」を選択することで、重

複したインデックスが排除され最小限の

インデックスのみ表示される

► 勧告索引の圧縮が利用できない場合は

通常の「索引アドバイザー」を選択



インデックスの作成が推奨されている

テーブルが表示され、手動でインデック

スを作成可能

► 推奨された回数やMTI（一時インデック

ス）の作成回数も確認可能

► 一括作成方法Tips1を参照（後述）



操作方法

► IBM i Navigator for Windows

→ DB上で右クリック

→ 索引アドバイザー

(52)

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 この行がいつ挿入されたか。

(53)

アクセス・プランの実装に使用された回数

最適化のための情報提供元として使用された回数

③インデックスの使用頻度の確認と削除



インデックスの使用回数をリアルタイムで確認可能

► 使用されてないインデックスは更新系作業の無駄な負荷となるため削

除することを推奨



利用頻度が一定以下のインデックス一括削除方法は後述



ただし、オプティマイザーへの情報提供は重要

► 「照会使用回数」が

0 であっても

「照会統計使用回数」がカウントされている場合は削除しない

► 特に EVI は照会に使用されなくとも正確な情報の提供元として重要



確認方法

► iNavi → データベース

→ スキーマの指定 → 「索引」をクリック

(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’ ; →テーブル名

(55)

55

(56)

Tips1：推奨インデックスの一括作成

(1/2)



推奨インデックスを一括作成するプロシージャー

► index_adviceプロシージャーは推奨インデックスに関する情報が蓄積されているシステム提供

テーブルSYSIXADVを基に、インデックス・アドバイス情報を得ることができる機能

► テーブルSYSIXADVそのものだけではなく、コピーを利用することもできるため、同一システム

のある特定時点、または他システムから移行してきたSYSIXADVを用いた分析も可能



create procedure QSYS2.index_advice(in advice_library_name char(10),in

advice_file_name char(10), in advice_option integer)

► advice_library_name

char(10)

複製されたSYSIXADVが存在するライブラリー

► advice_file_name

char(10)

複製されたアドバイス・ファイル名(通常SYSIXADV)

► advice_option

integer 0:索引アドバイスのためのセットアップを行い結果セットは戻さない

1:結果セットで「勧告索引を圧縮」を戻す

2:結果セットで「索引アドバイザー」を戻す



前提条件

(57)

Tips1：推奨インデックスの一括作成(2/2)



推奨インデックス一括作成のスクリプトサンプル

► QSYS2.INDEX_ADVICEプロシージャーを利用して得られる推奨INDEXの情報を元に、圧縮

された推奨INDEX(CONDENSED INDEX)を一括作成するストアード・プロシージャー

1. ストアードプロシージャーの登録

► SQLインターフェース（iNaviやSTRSQLコマンド）から次ページのスクリプトサンプルを実行

2. ストアードプロシージャーの実行

► SQLインターフェースから下記コマンドを実行

► CALL qgpl.crtcndidx ('QSYS2', 'SYSIXADV', ‘xxxxxxxx')

 xxxxxxx：推奨インデックスを作成したいライブラリー名



サンプル利用上の注意点

► 作成されるインデックスの名称は、「IDX_基になるTABLE名_連番」で作成されます。

► 同一のライブラリーに複数回実施する場合、インデックス名が重複するためエラーとなります。

► システムが推奨するINDEXの内容は、DB関連のPTFを適用などにより、推奨される判断基準が変わること

があります。そのため、DB2のグループPTF適用後には、 IBM i Navigator の索引アドバイザーの機能を使

用し、新たに推奨されたINDEXがないかを確認ください。現行のINDEXの内容と大きな差があった場合、既

存のINDEXを削除した後、本プロシージャーを再度実行し、新しい推奨INDEXを作成することで、良好なパ

フォーマンスを得られることがあります。

当サンプルは「index_adviceプロシージャー」の利用例を示すことを目的としており、IBMとして稼働を保証するものではありません。サンプル利用者の責任においての実施となることをご了承ください。

(58)

58

推奨インデックス一括作成のスクリプトサンプル（1/2）

create procedure qgpl.crtcndidx (IN adv_libCHAR(10), adv_file CHAR(10), tgt_lib CHAR(10)) LANGUAGE SQL

SET OPTION OUTPUT=*PRINT,DBGVIEW=*SOURCE BEGIN

DECLARE sql_stmt1 VARCHAR(300); /* 実行SQLステートメント格納用 */

DECLARE sql_stmt2 VARCHAR(10000); /* 実行SQLステートメント格納用 */

DECLARE t_idxVARCHAR(360); /* インデックス名生成用*/

DECLARE cnt NUMERIC; /* カウンター*/

/* 結果受け取り用 */

DECLARE table_name VARCHAR(258);

DECLARE key_columns_advised VARCHAR(1600);

DECLARE index_type CHAR(14);

DECLARE table_schema VARCHAR(128);

/* 制御用 */

DECLARE not_found INT DEFAULT 0; DECLARE cur1 CURSOR FOR sql1;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET not_found= 1;

/* 複製された SYSIXADV を用いてQTEMPに情報を構築する */

CALL QSYS2.index_ADVICE(adv_lib , adv_file, 0);

/* 複製された SYSIXADV を用いてQTEMPに情報を構築する */

CALL QSYS2.index_ADVICE(adv_lib , adv_file, 0);

/* 圧縮された推奨インデックス情報を取得するSQL文 */

SET sql_stmt1 = 'select TABLE_SCHEMA , TABLE_NAME , KEY_COLUMNS_ADVISED , INDEX_TYPE from QTEMP.CONDENSEDINDEXADVICE where SYSTEM_TABLE_SCHEMA = ?';

(59)

59

推奨インデックス一括作成のスクリプトサンプル（2/2）

/* カーソル・オープン*/

OPEN cur1 USING tgt_lib;

FETCH cur1 INTO table_schema, table_name, key_columns_advised, Index_type ; SET cnt = 1;

WHILE not_found =0 DO

/* INDEX名生成:インデックス名の先頭にIDX_を使用し、作成されたインデックスであることを判別*/

SET t_idx = 'IDX_' || table_name|| '_' || CHAR(cnt) ;

/* INDEX_typeが RADIXの場合基数索引を、ENCODED VECTORの場合コード化ベクトル索引を作成*/

CASE

WHEN INDEX_type = 'RADIX' THEN

SET sql_stmt2 = 'create INDEX ' || table_schema|| '.' || t_idx|| ' ON ' || table_schema|| '.' || table_name || '(' || key_columns_advised|| ')';

WHEN INDEX_type = 'ENCODED VECTOR' THEN

SET sql_stmt2 = 'create encoded vector INDEX ' || table_schema|| '.' || t_idx || ' ON ' || table_schema|| '.' || table_name|| '(' || key_columns_advised|| ')';

ELSE

SET sql_stmt2 = 'create INDEX ' || table_schema|| '.' || t_idx|| ' ON ' || table_schema|| '.' || table_name || '(' || key_columns_advised|| ')'; END CASE; /* 生成された推奨インデックスを作成するsql文を実行 */ PREPARE sql2 FROM sql_stmt2; EXECUTE sql2; SET cnt = cnt+ 1;

FETCH cur1 INTO table_schema, table_name, key_columns_advised , INDEX_type ; END WHILE;

CLOSE cur1; END;

(60)

Tips2：一時インデックスの一括オブジェクト化



指定回数使用した一時インデックスを一括でオブジェクト化するプロシージャー

► オプティマイザーが必要と判断し、既に作成済みの「一時インデックス」のうち、利用

回数が一定回数を越えた一時インデックスをオブジェクト化するプロシージャー

► 既に一時インデックスとして作成されており、かつ一定数の利用が確認できることか

ら、高確率で必要なインデックスを作成可能



サンプル

► 1,000回以上使用されている一時インデックスをオブジェクトにする場合



CALL SYSTOOLS.ACT_ON_INDEX_ADVICE ( '<library-name>', NULL, NULL,

1000, NULL)



（<library-name>は、そのライブラリーの中のテーブルについて当該処理を実行）



前提条件

► IBM i 6.1：SI35751

► IBM i 7.1：提供なし（2011/12/14時点）



参考情報

► SE38389 - OSP-DB Extend Index Advisor

(61)

Tips3：指定条件のインデックスの一括削除



指定条件のインデックスを一括削除するプロシージャー

► 不要なインデックスはシステム負荷につながるため一定期間利用していないイン

デックスや、一定回数を満たないインデックスを削除するプロシージャー



サンプル

► 作成されたインデックス(*)について、一定期間（たとえば7日間）に一定の回数（たと

えば 500回以下）しか使用されなかったインデックスを削除する場合



CALL SYSTOOLS.REMOVE_INDEXES( '<library-name>', 500, ' 7 days ')



前提条件

► IBM i 6.1 ＋ SF99601 Level 21（SI45174）

► IBM i 7.1 ＋ SF99701 Level 12



参考情報

► OSP-DB ADD SYSTOOLS.REMOVE_INDEXES

(62)

62

参考情報



developerWorks ：

DB2 for i - Technology Updates （英語）

► DB2 for i 最新情報（新機能・機能拡張）

 http://ibm.co/rX2INe



developerWorks ：

IBM DB2 for i – Technical topics （英語）

► http://www.ibm.com/developerworks/data/products/db2i5OS/



IBM i 7.1 テクニカル・ワークショップ

2010春：DB2 for i & DB2 Web Query 最新情報

► IBM i 7.1 DB2 for i の最新情報

 http://www.ibm.com/systems/jp/i/library/



IBM i 最新情報・勉強会～2009 冬～：『2. 使ってみよう、IBM i 最新機能』

► データベースオブジェクトのSQLからの作成

► SQLのパフォーマンス・チューニング

QZDASOINITの設定

► ビジュアル・エクスプレインの利用ガイド、Tipsなどが

 http://www.ibm.com/systems/jp/i/library/pdf/20090202.pdf



DB2 UDB for iSeries パフォーマンス改善のポイント

► 特にこれまで iSeries 経験のない方に向けた、DB2 UDB for iSeries (DB2/400) のパフォーマンス・チューニ

ング・ガイドです。

(63)

63

(64)

アンケートにご記入いただいたご質問/回答



ユーザーから「最近SQLが遅くなった、速くして欲しい」とのリクエストがあります。イン

デックスの作成が解決策となるのでしょうか？なお、SQLが遅くなったと思われる理由と

して、「SQLユーザーの増加」、「利用プログラムの増加」が挙げられます。

–

ご質問の状況下では、Indexの作成による解決を断言することはできません。ご質問から察す

るに、ユーザー数やプログラム数、データ容量の増加などにより、システム資源のいずれか

（主にCPU、メモリー、ディスクBusy率）がボトルネックとなり、レスポンスタイムに影響が出て

いることが考えられます。下記のガイドに従い、まずはパフォーマンスの現状を調査すること

を推奨いたします。

–

なお、Indexが適切に作成されていない状況であれば、Indexを作成することで、照会速度の

向上やシステム資源の利用率の抑制が期待できます。まずは当資料に掲載している索引ア

ドバイザーを確認し、「推奨するIndex」があれば、作成ください。また、SQLの書き方を改善す

ることでパフォーマンスが改善される場合があります。下記資料をご参照ください。

–

パフォーマンス分析実践ガイド

–

http://www.ibm.com/jp/domino01/mkt/cnpages3.nsf/page/default-003A29EC

–

Power Systems スキルアップセミナー

IBM i (5月～6月) ：

IBM i サイジング情報（5.37MB）

–

http://www.ibm.com/systems/jp/i/library/pdf/skillup5.pdf

–

DB2 UDB for iSeries パフォーマンス改善のポイント

(65)

アンケートにご記入いただいたご質問/回答



論理ファイルとインデックスの違いがよくわかりません。今後は論理ファイルで

はなくインデックスを利用した方が良いのでしょうか？

► 論理ファイルとインデックスの違いは当資料のp.47を参照ください。

► 利用しているアプリケーションが例えばRPGのみであり、今後もRPGのみ利用する

ということであれば、インデックスではなく、論理ファイルの利用のみで構いません。

► ただし、p.46に記載した通り、RPGではなくSQLを利用するメリットがあるため、

SQLのご利用をぜひご検討ください。SQLを利用する場合には、論理ファイルでは

なくインデックスを利用することを強く推奨いたします。

► IBM i でのSQLの情報は、下記リンク内の「SQL解説書」を参照ください。



http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafzintro.htm



「SQL 解説書」の

PDF ファイル ⇒ 「SQL解説書」を選択

(66)

66

IBM Presentations: Smart Planet Template

DB2 for i 最新情報 & 再認識

日本アイ･ビー･エム株式会社

システム製品 テクニカル・セールス

Power Systems テクニカル・セールス

Revision 1.3

特記事項

商標

目次

1. DB2 for i 再認識

1.

DB2 for i 概説

2.

DB2 for i の競合優位性

1.

運用管理

2.

パフォーマンス

2. DB2 for i 最新情報

1.

機能拡張

3. インデックス

活用の最新情報

1.

インデックス

概説

2.

インデックス

活用 Tips

DB2 for i フォーカスエリア



自己管理データベース

►

自動化によるTCOの削減

►

セキュリティ機能と監査機能の実装



信頼性とスケーラビリティ

►

簡単で最高のスケーラビリティ

（1coreから32coreまで）

►

統合されたトランザクション管理

►

高度で柔軟なログ機能



オープン化・業界標準のサポート

►

戦略的なインターフェースであるSQL対応

►

最新の業界標準機能の実装

►

ポータビリティ、互換性



アプリケーションの柔軟性

►

SQL、RPG、COBOL･･･

►

SOA対応



ビジネス・インテリジェンス

►

エンドユーザーへのデータ照会、レポーティング

SQL標準対応とマルチプラットフォームからの互換性

DB2 for I

へのアク

セス方法

プラットフォーム

AIX

Linux

Windows

IBM i

DRDA









JDBC





システム製品テクニカル・セールス

_{（OSに統合）}