db2檢視執行計劃

一、使用package檢視執行計劃
1.找到資料庫中所有的package：
>db2 describe table syscat.packagedep
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
PKGSCHEMA                       SYSIBM    VARCHAR                    128     0 No
PKGNAME                         SYSIBM    VARCHAR                    128     0 No
BINDER                          SYSIBM    VARCHAR                    128     0 No
BINDERTYPE                      SYSIBM    CHARACTER                    1     0 No
BTYPE                           SYSIBM    CHARACTER                    1     0 No
BSCHEMA                         SYSIBM    VARCHAR                    128     0 No
BNAME                           SYSIBM    VARCHAR                    128     0 No
TABAUTH                         SYSIBM    SMALLINT                     2     0 Yes
VARAUTH                         SYSIBM    SMALLINT                     2     0 Yes
UNIQUE_ID                       SYSIBM    CHARACTER                    8     0 No
PKGVERSION                      SYSIBM    VARCHAR                     64     0 Yes
2.查詢某張表對應的package的名字：
db2 "select * from syscat.packagedep where bname = '<table_name>'"
3.使用db2expln命令對package進行解析，檢視執行計劃：
db2expln -d 資料庫名 -c 使用者名稱 -p 繫結包名 -o 輸出檔案 -s 0 -g

二、使用命令列檢視執行計劃
1.如果第一次執行,請先 connect to dbname,
2.執行db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL建立執行計劃表
3.db2 set current explain mode explain
設定成解釋模式,並不真正執行下面將發出的sql命令
4.db2 "select order_number from <table_name>where order_number='000000000036' and mer_ID='111' and order_time='20121111111111' and order_type='01' "
執行你想要分析的sql語句
5.db2 set current explain mode no
取消解釋模式
6.db2exfmt -d sample -g TIC -w -l -s % -n % -o db2exmt.out
執行計劃輸出到檔案db2exmt.out

檢視執行計劃 db2expln 使用說明

db2expln -d wz20901 -u wzgladm wzglpass -t -q "update MAT_MATERIAL set GATHERPLAN_ID=null where GATHERPLAN_ID=2005178 or GATHERPLAN_ID=32"
db2expln -d wz20901 -u wzgladm wzglpass -t -z ; -f tmp.sql

檢視執行計劃需要先建立explain表：
$ db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL
db2 explain plan for "SELECT NAME FROM T2 WHERE ID > 15"
db2exfmt -d sample -o db2exfmt.out

inst105@db2a:~$ cat createproce.txt
CREATE OR REPLACE PROCEDURE PROCEDURE01 ()
        DYNAMIC RESULT SETS 2
P1: BEGIN
        -- Declare cursor
        DECLARE cursor1 CURSOR WITH RETURN for
        SELECT ID FROM T1;

        DECLARE cursor2 CURSOR WITH RETURN for
        SELECT NAME FROM T2 WHERE ID > 15;

        -- Cursor left open for client application
        OPEN cursor1;
        OPEN cursor2;
END P1
@

db2 "select trim (substr (r.routineschema, 1, 10)) as routineschema,trim (substr (r.routinename, 1, 30)) as routinename, r.valid, trim (substr (p.pkgschema, 1, 15)) as pkgschema, trim (substr (p.pkgname, 1, 15)) as pkgname, p.valid from syscat.routines r, syscat.packages p, syscat.procedures a,syscat.routinedep b where b.specificname=r.specificname and r.specificname=a.specificname and r.routinetype = 'P' and b.bname=p.pkgname and a.procname='PROCEDURE01' order by p.create_time desc fetch first 5 rows only"
ROUTINESCHEMA ROUTINENAME                    VALID PKGSCHEMA       PKGNAME         VALID
------------- ------------------------------ ----- --------------- --------------- -----
INST105       PROCEDURE01                    Y     INST105         P1884571328     Y
1 record(s) selected.

檢視包的執行計劃，其中INST105是模式名，因為儲存過程中有兩條SQL，所以結果裡有兩個section：

db2expln -database SAMPLE -schema INST105 -package P1884571328 -graph -terminal

3.檢視package cache中的執行計劃
如果應用程式的SQL中，使用了邦定變數的方式，必須得從package cache中抓取執行計劃
a. 重新整理 package cache：
db2 flush package cache dynamic
b. 執行SQL語句，並且不要在其他地方執行這條SQL
db2 "SELECT NAME FROM T2 WHERE ID > 15"
c. 使用下面的查詢找到SQL對應的executable_id
db2 "SELECT executable_id,STMT_EXEC_TIME,Total_cpu_time,varchar(stmt_text,50) as stmt_text FROM TABLE(MON_GET_PKG_CACHE_STMT ('D', NULL,NULL,-1)) AS T "
d. 根據上一步中的executable_id，執行下面的儲存過程:
db2 "CALL EXPLAIN_FROM_SECTION(x'0100000000000000100100000000000000000000020020170630092841223134' , 'M', NULL, 0, 'INST105', ?, ?, ?, ?, ? )"
e. 使用db2exfmt生成執行計劃：
db2exfmt -d sample -o packagecache.out1