How To
Summary
Db2 はアプリケーションから SQL ステートメントを受け取ると、SQL コンパイラーが実行計画を作成し、実行可能オブジェクト (セクション) を作成してからセクションをランタイムで実行します。
SQL のパフォーマンスが期待通りでない場合や、SQL がアクセスする表や索引を確認したい場合、結合順序や結合方式を確認したい場合などは、Explain 機能を使用して実行計画を取得できます。
Objective
この文書では Db2 で利用可能な実行計画の取得方法を列挙し、それぞれの使用方法を解説します。
Db2 で SQL の実行計画を取得する方法は以下の 2 つです。
a) 実行計画の Explain 表への挿入 (内容は db2exfmt コマンドでテキストへ出力可能)
b) db2expln コマンドによる実行可能オブジェクト (セクション) のフォーマット
b) db2expln コマンドによる実行可能オブジェクト (セクション) のフォーマット
各 Explain 機能の比較は以下のページを参照してください。
Explain 情報の収集および分析用のツール
それぞれの Explain 機能がソースとする情報は異なります。ほとんどの場合 Explain 表を使用した Explain が適切ですが、一部の情報は db2expln でのみ確認できます。詳細は以下のページを参照してください。
Explain 情報の収集および分析用のツール
それぞれの Explain 機能がソースとする情報は異なります。ほとんどの場合 Explain 表を使用した Explain が適切ですが、一部の情報は db2expln でのみ確認できます。詳細は以下のページを参照してください。
Environment
- この文書は Db2 V10.1 以降が対象です。
- "db2 " (db2 とスペース) から始まるコマンドやステートメントは、対象となる SQL を実行するデータベースに接続済みであることを前提にしています。
- 実行計画を Explain 表へ挿入する場合、Explain を取得するセッションの許可 ID と同じスキーマの Explain 表が使用されます。許可 ID と同じスキーマの Explain 表が存在しない場合、代替として SYSTOOLS スキーマの Explain 表が使用されます。SYSTOOLS スキーマの Explain 表も存在しない場合、実行計画の Explain 表への挿入は失敗します。
Explain 表の作成方法は以下の Technote を参照してください。
[DB2 LUW] Explain 表の作成方法と削除方法
Steps
a-1) 実行計画の Explain 表への挿入
SQL のコンパイル時に Explain 情報を Explain 表へ挿入する方法
- コマンド行プロセッサーで指定した SQL の Explain 情報を Explain 表へ挿入
- EXPLAIN ステートメントの使用
EXPLAIN ステートメントで SQL を指定すると、その SQL は実行されず、Explain 情報が Explain 表に挿入されます。
例db2 "explain plan for select * from T1"
- CURRENT EXPLAON MODE 特殊レジスターの使用
CURRENT EXPLAIN MODE が EXPLAIN の場合、以後このセッションで実行される SQL はコンパイルして Explain 情報が Explain 表に挿入されますが、SQL 自体は実行されません。
SQL を db2 -tvf <sql_file> などの形式で実行する場合に向いています。
例db2 set current explain mode explain db2 "select * from T1" db2 set current explain mode no
- EXPLAIN ステートメントの使用
- SQL プロシージャー作成時もしくは再バインド時に Explain 情報 Explain 表へ挿入
ルーチン・オプションとして EXPLAIN を指定してから SQL ルーチンを作成したり、SQL ルーチンを再バインドすると Explain 情報が Explain 表に格納されます。
プロシージャーの作成時の例db2 "call SET_ROUTINE_OPTS(GET_ROUTINE_OPTS() || ' EXPLAIN YES')" db2 "create procedure ...."
db2 "call REBIND_ROUTINE_PACKAGE('P','PROC1','EXPLAIN YES')"
- パッケージのバインド時に Explain 情報を Explain 表へ挿入
バインドはバインド・ファイルに格納された SQL をコンパイルし、パッケージとしてシステム・カタログ表に登録する作業です。
バインドで EXPLAIN を指定すると、コンパイル時に Explain 情報が Explain 表に格納されます。db2 bind <bind_file> explain yes
コンパイル済みステートメントの Explain 情報を Explain 表へ挿入する方法
- パッケージ・キャッシュにあるステートメントの Explan 情報を Explain 表へ挿入
EXPLAIN_FROM_SECTION プロシージャーを使ってパッケージ・キャッシュにキャッシュされたステートメントの Explain を挿入できます。詳細は以下の Technote を参照してください。
[DB2 LUW] パッケージ・キャッシュから Explain を採取する方法 - システム・カタログに登録された静的 SQL の Explain 情報を Explain 表へ挿入
EXPLAIN_FROM_CATALOG プロシージャーを使って、バインドによってシステム・カタログ表に登録済みの静的 SQL から Explain を取得できます。
例db2 "select pkgschema,pkgname,version,sectno from SYSCAT.STATEMENTS where text= '<SQL ステートメント>' db2 "call EXPLAIN_FROM_CATALOG( '<pkgschema>','<pkgname>', '<version>', <sectno>, '', ?, ?, ?, ?, ? )
- アクティビティー・イベント・モニターから Explain 情報を Explain 表へ挿入
EXPLAIN_FROM_ACTIVITY プロシージャーを使って、アクティビティ・イベント・モニターが収集したデータから Explain を取得できます。ただし、アクティビティー・データの収集を有効にするとき、COLLECT ACTIVITY DATA WITH SECTION 節を指定する必要があります。
例db2 "select appl_id,uow_id,activity_id,user_cpu_time from ACTIVITY_<actevmon> order by user_cpu_time" db2 "CALL EXPLAIN_FROM_ACTIVITY( '<appl_id>', <uow_id>, <activity_id>, '<actevmon>', '', ?, ?, ?, ?, ? )"
a-2) Explain 表に挿入された Explain 情報のフォーマット
Explain 表に挿入された Explain 情報は db2exfmt コマンドでフォーマットできます。-1 オプションを指定した場合スキーマやパッケージを指定せず、最後に取得された Explain 情報がフォーマットされます。
例:Explain 表に最後に挿入された Explain 情報をフォーマットして db2exfmt.out ファイルへ出力
db2exfmt -d <db_name> -1 -o db2exfmt.out
注1: フォーマット時に対象の絞り込みを行いたい場合など、コマンドオプションは以下のページを参照してください。
db2exfmt - Explain 表フォーマット・コマンド
注2: db2exfmt の出力に含まれる日本語が文字化けする場合、-no_map_char オプションを付加してください。
注3: Db2 11.1 および 11.5.7 以下で SQL に以下の特徴がある場合、db2exfmt がループして巨大な結果を生成します。
IT31325: DB2EXFMT MAY HANG WITH SQL LONGER THAN 4096 AND COMMA OCCURS AT POSITION 4096 IN THE FORMATTED ORIGINAL STATEMENT (11.5.8 以降で修正済み)
db2exfmt - Explain 表フォーマット・コマンド
注2: db2exfmt の出力に含まれる日本語が文字化けする場合、-no_map_char オプションを付加してください。
注3: Db2 11.1 および 11.5.7 以下で SQL に以下の特徴がある場合、db2exfmt がループして巨大な結果を生成します。
IT31325: DB2EXFMT MAY HANG WITH SQL LONGER THAN 4096 AND COMMA OCCURS AT POSITION 4096 IN THE FORMATTED ORIGINAL STATEMENT (11.5.8 以降で修正済み)
この条件にあてはまる場合、db2exfmt に -f C オプションを追加してください。
b) db2expln コマンドによる実行可能オブジェクト (セクション) のフォーマット
db2expln コマンドを使って、指定した SQL、パッケージ・キャッシュにある SQL、システム・カタログにある SQL のセクションから Explain を取得できます。
- 指定した動的 SQL の Explain 情報を標準出力またはファイルへ出力
例db2expln -d <db_name> -q "<sql_stmt>" -g -o <out_file>
- パッケージ・キャッシュにあるステートメントの Explan を表示
- db2pd コマンドで Explain したい SQL の anchID, stmtUID, envID, varID を確認します。
db2pd -db <db_name> -dynamic
Dynamic SQL Statements: Address AnchID StmtUID NumEnv NumVar NumRef NumExe Text 0x0A0002002120B300 611 2 1 1 2 2 select * from t1 Dynamic SQL Environments: Address AnchID StmtUID EnvID Iso QOpt Blk 0x0A00020020BF0080 611 2 2 CS 5 B Dynamic SQL Variations: Address AnchID StmtUID EnvID VarID NumRef 0x0A00020020BF0400 611 2 2 1 1
- db2expln コマンドで anchID, stmtUID, envID, varID を指定して Explain を取得します。
db2expln -d <db_name> -cache <anchID>,<stmtUID>,<envID>,<varID> -g -o <out_file>
- db2pd コマンドで Explain したい SQL の anchID, stmtUID, envID, varID を確認します。
- 静的 SQL の Explain 情報の出力
- システム・カタログから Explain したい静的 SQL のパッケージ名とセクション番号を識別します。
参考:該当の静的 SQL が実行されてパッケージ・キャッシュにキャッシュされている場合、db2pd -db <db_name> -static コマンドの結果からもパッケージ名とセクション番号を識別できます。db2 "select pkgschema,pkgname,sectno from SYSCAT.STATEMENTS where text= '<SQL ステートメント>'
- db2expln コマンドでパッケージ名とセクション番号を指定して Explain を取得します。
db2expln -d <db_name> -c <pkgschema> -p <pkgname> -s <sectno> -g -o <out_file>
- システム・カタログから Explain したい静的 SQL のパッケージ名とセクション番号を識別します。
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"ARM Category":[{"code":"a8m500000008PkxAAE","label":"Compiler-\u003EExplain"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1.0;10.5.0;11.1.0;11.5.0"}]
Was this topic helpful?
Document Information
Modified date:
17 October 2023
UID
ibm16497739