1.创建存储过程http://www.cndba.cn/hbhe0316/article/108671

[db2inst1@node01 tmp]$ cat 2.ddl
create procedure sales_status
(in quota integer)
dynamic result sets 2
language sql
begin
declare SQLSTATE char(5);

declare rs cursor with return for
select * from t1;

open rs;

end
@

2.执行创建存储过程脚本

[db2inst1@node01 tmp]$ db2 -td@ -f 2.ddl
DB20000I  The SQL command completed successfully.

3.调用存储过程http://www.cndba.cn/hbhe0316/article/108671http://www.cndba.cn/hbhe0316/article/108671http://www.cndba.cn/hbhe0316/article/108671

[db2inst1@node01 tmp]$ db2 "call sales_status(1)"


  Result set 1
  --------------

  INCOMING_TIME              OUTGOING_TIME              DURATION                 HOST_NAME                                                        SERVER_NAME                                                      KTHREAD          UTHREAD          TX_ID            TX_NAME                                                          PRIVILEGE_ID                                                     USER_ID                                                          CLIENT_HOST                                                      RETURN_CODE MESSAGE                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          EQP_ID                                                           PARAMETER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        INSERT_TIME
  -------------------------- -------------------------- ------------------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------- ---------------- ---------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------

  0 record(s) selected.

  Return Status = 0

4.利用表函数MON_GET_PKG_CACHE_STMT抓取static的信息，获取PACKAGE_NAME及SQL语句http://www.cndba.cn/hbhe0316/article/108671

[db2inst1@node01 tmp]$ db2 "select varchar(PACKAGE_NAME,20),SECTION_NUMBER,EXECUTABLE_ID from TABLE(MON_GET_PKG_CACHE_STMT ( 'S', NULL, NULL, -1)) as T"

1                    SECTION_NUMBER       EXECUTABLE_ID
-------------------- -------------------- -------------------------------------------------------------------
DB2L2O21                                7 x'0100000000000000570100000000000007000000010020220424160220107259'
DB2L2O21                               15 x'010000000000000057010000000000000F000000010020220424160220107259'
DB2L2O21                              165 x'01000000000000005701000000000000A5000000010020220424160220107259'
DB2L2O21                              166 x'01000000000000005701000000000000A6000000010020220424160220107259'
SQLUAO20                                8 x'0100000000000000360100000000000008000000010020220310114421624529'
SQLUFO17                               15 x'01000000000000003C010000000000000F000000010020220310114421655893'
P344186723                              1 x'01000000000000005A0100000000000001000000010020220927093129202218'
SQLC2O29                              228 x'01000000000000004201000000000000E4000000010020220310114421666040'
SQLUCO09                                7 x'0100000000000000370100000000000007000000010020220310114421631583'

  9 record(s) selected.

5.利用EXECUTABLE_ID，获取SQL语句http://www.cndba.cn/hbhe0316/article/108671

[db2inst1@node01 tmp]$  db2 "SELECT STMT_TEXT FROM TABLE(MON_GET_PKG_CACHE_STMT (null, x'01000000000000005A0100000000000001000000010020220927093129202218', null, -2))"

STMT_TEXT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE RS cursor with return for
select * from T1

6.查看package_name信息，valid列信息需要重点关注，信息中心解释如下：
N = Needs rebinding
V = Validate at run time
X = Package is inoperative because some function instance on which it depends has been dropped; explicit rebind is needed
Y = Validhttp://www.cndba.cn/hbhe0316/article/108671http://www.cndba.cn/hbhe0316/article/108671

6.查看package

[db2inst1@node01 tmp]$ db2 list packages for all | grep -i 3441867
P3441867>   DB2INST1              DB2INST1              1 Y       0        CS        U

7.获取该package_name的执行计划信息http://www.cndba.cn/hbhe0316/article/108671

[db2inst1@node01 tmp]$ db2expln -d testdb -g -c db2inst1 -p P344186723 -s 0 -t

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

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

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

Package Name = "DB2INST1"."P344186723"  Version = ""

        Prep Date = 2022/09/27
        Prep Time = 09:31:29

        Bind Timestamp = 2022-09-27-09.31.29.202218

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

        Partition Parallel       = No
        Intra-Partition Parallel = No

        Reoptimization           = No

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

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


Statement:
  DECLARE RS cursor
  with return
  for
     select *
     from T1


Section Code Page = 1208

Estimated Cost = 209.793137
Estimated Cardinality = 0.000000

Access Table Name = DB2INST1.T1  ID = -6,-32766
|  #Columns = 17
|  Data-Partitioned Table
|  Skip Inserted Rows
|  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
|  All data partitions will be accessed
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  Return Data to Application
|  |  |  #Columns = 17
Return Data Completion

End of section


Optimizer Plan:

    Rows
  Operator
    (ID)
    Cost

     0
  RETURN
   ( 1)
  209.793
    |
     0
  TBSCAN
   ( 2)
  209.793
    |
    0
 Table:
 DB2INST1
 T1

版权声明：本文为博主原创文章，未经博主允许不得转载。

DB2