DB2 如何获得存储过程对应的包中的执行计划

编写一个 shell 脚本,可以直接根据 存储过程名字来查看执行计划.


vi ck_proc_expln.sh
### ck_proc_expln.sh
# 将第1个 输入参数 的值,赋予 base_dir,用于shell 执行的 根目录
base_dir=${1}


# 将第2个 输入参数 的值,赋予 v_proc_schema,用于 存储 存储过程的模式名
v_proc_schema=${2}


# 将第3个 输入参数 的值,赋予 v_proc_name,用于 存储 存储过程的名字
v_proc_name=${3}


# 设置数据库名
database_name=testdb


# 连接数据库.
db2 +o "connect to ${database_name}"


# 根据存储过程名获取数据库中对应的包名.
packe_name_tmp=`db2 -x "select substr(b.bname,1,40) package_name from syscat.procedures a,syscat.routinedep b where a.specificname = b.routinename and procschema=upper('${v_proc_schema}') and procname=upper('${v_proc_name}') and b.btype='K'"`
packe_name=`echo ${packe_name_tmp}`


echo ""
# 断开数据库.
db2 +o "terminate"


# 查看存储过程对应的包中的执行计划
db2expln -d ${database_name} -g -c ${v_proc_schema} -p ${packe_name} -s 0 -t


# 调用例子:
# ./ck_proc_expln.sh /home/db2inst1 db2inst1 proc_get_app_handle
# 第1个 参数为shell 执行的 根目录
# 第2个 参数为存储过程的模式名
# 第3个 参数为存储过程的名字


[db2inst1@db2-005 ~]$ ./ck_proc_expln.sh /home/db2inst1 db2inst1 proc_get_app_handle


DB2 Universal Database Version 10.1, 5622-044 (c) Copyright IBM Corp. 1991, 2011
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool


******************** PACKAGE ***************************************


Package Name = "DB2INST1"."P1120771772"  Version = ""


        Prep Date = 2015/01/15
        Prep Time = 21:20:54


        Bind Timestamp = 2015-01-15-21.20.54.456606


        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5


        Partition Parallel       = No
        Intra-Partition Parallel = No


        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", 
                                   "DB2INST1"


-------------------- SECTION ---------------------------------------
Section = 1




Statement:
  
  select APPLICATION_HANDLE, APPLICATION_NAME into :HV00009 :HI00009 ,
          :HV00010 :HI00010 
  from SYSIBMADM.MON_CURRENT_SQL 
  where ACTIVITY_TYPE='CALL' and LOCATE(UPPER(:HV00008 :HI00008 ), 
          UPPER(STMT_TEXT))> 0 and LOCATE(UPPER(:HV00012 :HI00012 ), 
          UPPER(STMT_TEXT))=0




Section Code Page = 1208


Estimated Cost = 1.299523
Estimated Cardinality = 0.003495


Access User Defined Table Function
|  Name = SYSPROC.MON_GET
|  Specific Name = MON_GET_PKG_CACHE_STMT
|  SQL Access Level = No SQL
|  Language = C
|  Parameter Style = 


ERROR -- Unknown Parameter Style (9)


|  Not Fenced                   Not Deterministic
|  Called on NULL Input         Disallow Parallel
|  Threadsafe                   Secured
|  #Columns = 3
|  May participate in Scan Sharing structures
|  Scan may start anywhere and wrap, for completion
|  Scan can be throttled in scan sharing management
|  Relation Scan
|  |  Prefetch: Eligible
|  Residual Predicate(s)
|  |  #Predicates = 2
Hash Join
|  Estimated Build Size: 4000   
|  Estimated Probe Size: 4000   
|  Access User Defined Table Function
|  |  Name = SYSPROC.WLM_GET
|  |  Specific Name = WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES
|  |  SQL Access Level = No SQL
|  |  Language = C
|  |  Parameter Style = 


ERROR -- Unknown Parameter Style (9)


|  |  Not Fenced                   Not Deterministic
|  |  Called on NULL Input         Disallow Parallel
|  |  Threadsafe                   Secured
|  |  #Columns = 6
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Residual Predicate(s)
|  |  |  #Predicates = 1
Hash Join
|  Early Out: Single Match Per Inner Row
|  Estimated Build Size: 4000   
|  Estimated Probe Size: 36000  
|  Bit Filter Size: 4096
|  Access User Defined Table Function
|  |  Name = SYSPROC.WLM_GET
|  |  Specific Name = WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES
|  |  SQL Access Level = No SQL
|  |  Language = C
|  |  Parameter Style = 


ERROR -- Unknown Parameter Style (9)


|  |  Not Fenced                   Not Deterministic
|  |  Called on NULL Input         Disallow Parallel
|  |  Threadsafe                   Secured
|  |  #Columns = 4
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  Insert Into Sorted Temp Table  ID = t1
|  |  #Columns = 4
|  |  #Sort Key Columns = 3
|  |  |  Key 1: (Ascending)
|  |  |  Key 2: (Ascending)
|  |  |  Key 3: (Ascending)
|  |  Sortheap Allocation Parameters:
|  |  |  #Rows     = 1000.000000
|  |  |  Row Width = 28
|  |  Piped
|  Access Temp Table  ID = t1
|  |  #Columns = 4
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Sargable Predicate(s)
|  |  |  Predicate Aggregation
|  |  |  |  Group By
|  |  |  |  Column Function(s)
|  |  |  Process Probe Table for Hash Join
|  Aggregation Completion
|  |  Group By
|  |  Column Function(s)
Hash Join
|  Estimated Build Size: 4000   
|  Estimated Probe Size: 116000 
|  Bit Filter Size: 4096
|  Access User Defined Table Function
|  |  Name = SYSPROC.MON_GET
|  |  Specific Name = MON_GET_CONNECTION
|  |  SQL Access Level = No SQL
|  |  Language = C
|  |  Parameter Style = 


ERROR -- Unknown Parameter Style (9)


|  |  Not Fenced                   Not Deterministic
|  |  Called on NULL Input         Disallow Parallel
|  |  Threadsafe                   Secured
|  |  #Columns = 3
|  |  May participate in Scan Sharing structures
|  |  Scan may start anywhere and wrap, for completion
|  |  Fast scan, for purposes of scan sharing management
|  |  Scan can be throttled in scan sharing management
|  |  Relation Scan
|  |  |  Prefetch: Eligible
Return Data to Application
|  #Columns = 2


End of section




Optimizer Plan:


         Operator 
           (ID)   
                 
          RETURN 
           ( 1)  
            |    
          HSJOIN 
           ( 2)  
         /      \
  TBSCAN          HSJOIN 
   ( 3)            ( 4)  
   |             /      \
 TFunc:    GRPBY        HSJOIN 
 SYSPROC   ( 5)          ( 9)  
 MON_GET    |          /      \
          TBSCAN   TBSCAN   TBSCAN 
           ( 6)     (10)     (11)  
            |       |        |     
           SORT   TFunc:   TFunc:  
           ( 7)   SYSPROC  SYSPROC 
            |     WLM_GET  MON_GET 
          TBSCAN 
           ( 8)  
           |     
         TFunc:  
         SYSPROC 
         WLM_GET