DB2のSQL/PLによるストアドプロシージャは通常、CREATE PROCEDURE時にSQLがコンパイルされパッケージBINDが行われるため、CやCOBOLの埋込SQLプログラムと同様に静的SQLとしての実行計画管理が必要となります。
運用の原則
RUNSTATS未実施の状態でCREATE PROCEDUREを行うと、統計情報がない状態で実行計画が決定されます。そのまま実行するとそこで確定した実行計画が使用されるため、パフォーマンス問題が発生するリスクがあります。
適切な実行計画をDB2に選択させるためには、CREATE PROCEDUREをRUNSTAS後に実施するか、RUNSTATS後にREBINDを実施してください。
パッケージの確認
以下のカタログビューからストアドプロシージャが使用しているパッケージに関する情報を取得することができます。
- SYSCAT.ROUTINES
- SYSCAT.ROUTINEDEP
- SYSCAT.PACKAGES
SELECT
SUBSTR(rtn.routineschema, 1, 8) AS rtnschema
,SUBSTR(rtn.routinename, 1, 32) AS rtnname
,SUBSTR(rtn.specificname, 1, 32) AS specificname
,SUBSTR(pkg.pkgschema, 1, 8) AS pkgschema
,SUBSTR(pkg.pkgname, 1, 12) AS pkgname
,pkg.last_bind_time
FROM
syscat.routines AS rtn
INNER JOIN syscat.routinedep AS dep
ON rtn.routineschema = dep.routineschema
AND rtn.specificname = dep.specificname
INNER JOIN syscat.packages AS pkg
ON dep.bschema = pkg.pkgschema
AND dep.bname = pkg.pkgname
WHERE
rtn.routineschema NOT IN
(
'SYSIBM'
,'SYSIBMADM'
,'SYSIBMINTERNAL'
,'SQLJ'
,'SYSCAT'
,'SYSFUN'
,'SYSPROC'
,'SYSPUBLIC'
,'SYSSTAT'
,'SYSTOOLS'
,'NULLID'
,'NEWTON'
,'OBSIEGER'
)
AND rtn.routinetype = 'P'
AND dep.btype = 'K'
ORDER BY
rtn.routineschema
,rtn.routinename
,rtn.specificname
,dep.bschema
,dep.bname
;
RTNSCHEMA RTNNAME SPECIFICNAME PKGSCHEMA PKGNAME LAST_BIND_TIME
--------- -------------------------------- -------------------------------- --------- ------------ --------------------------
SVC34 TESTPROC SQL170215172851122 SVC34 P693021293 2017-02-15-22.28.51.196170
REBIND
SYSCAT.ROUTINEDEPから取得したパッケージ名をREBINDコマンドに直接指定することも可能ですが、このパッケージ名は数字の羅列であるため扱いが面倒です。
管理プロシージャREBIND_ROUTINE_PACKAGEを利用すれば、ストアドプロシージャ名を指定してパッケージをREBINDすることが可能です。
CALL REBIND_ROUTINE_PACKAGE('P', 'SVC34.TESTPROC', '') ;
EXPLAIN
管理プロシージャEXPLAIN_FROM_CATALOGもしくはシステムコマンドdb2explnにより、パッケージで使用されているSQLのEXPLAINを行うことができます。
CALL EXPLAIN_FROM_CATALOG('SVC34', 'P693021293', '', 1, 'SYSTOOLS', ?, ?, ?, ?, ?) ;
$ db2expln -d maildb -terminal -graph -schema SVC34 -package P693021293 -section 0
ただし、これらによって確認できる実行計画はあくまでもEXPLAIN実行タイミングのものであることに注意してください。確定している実行計画を見るには、CREATE時またはREBIND時のEXPLAIN結果を取得しておく必要があります。
CREATEおよびREBINDと同時にEXPLAINが実行されるようにするには、管理プロシージャSET_ROUTINE_OPTSを使用します。これはストアドプロシージャ用パッケージのBINDオプションを変更するもので、ここにEXPLAIN YESを設定した状態でCREATE PROCEDUREを実施します。
CALL SET_ROUTINE_OPTS('EXPLAIN YES') ;
CREATE PROCEDURE ... ;
CALL SET_ROUTINE_OPTS('') ;
この状態で作成されたパッケージはREBIND時にも毎回EXPLAINが行われ、結果がEXPLAIN表に追加されていきます。
参考資料
- IBM Support
- IBM developerWorks