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 = ''"
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 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