首页 > itarticle > DB2

DB2

admin 11月 13, 2020 0

DB2 execution plan for stored procedure:

collecting explain data

Configuration

1
2
 
db2 "connect to dbname user username using password"
db2 -tvf ~/sqllib/misc/EXPLAIN.DDL

Obtain the explain plan when creating the stored procedure:

  1. Turn on explain

    • Dynamically within the scope of the current session by:

      1
      		 
      db2 "call SET_ROUTINE_OPTS('EXPLAIN ALL')"

    • globally at the instance level if the procudure is not being called within the current session

      1
      2
      3
      4
      		 
      db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL"
      db2 terminate 
      db2stop 
      db2start

  2. Run the create statement for the SQL procedure that requires explaining. If one of the same name already exists, you may need to drop the procedure first or create a similar procedure under a different schema or name.

  3. The resulting explain data will be stored in the explain tables and can be extracted with a command such as the following:

    1
    		 
    db2exfmt -d dbname -u username password -e username -g TIC -w -1 -n % -s % -# 0 -o filename.out

  4. Disable explain by either issuing either one of these commands depending on how it was enabled.

    • If it was turned on for the current session

      1
      		 
      db2 "CALL SYSPROC.SET_ROUTINE_OPTS('')"

    • If it was turned on globally

      1
      2
      3
      4
      		 
      db2set DB2_SQLROUTINE_PREPOPTS=
      db2 terminate 
      db2stop 
      db2start

SQLs

get package name

1
2
3
4
5
6
7
8
 
select
r.routineschema,r.routinename,rd.bname as packagename
from syscat.routines r,syscat.routinedep rd
where r.specificname=rd.specificname
and r.routineschema=rd.routineschema
and rd.btype='K'
and r.routineschema = upper('schema')
and r.routinename = upper('spname')

get section of sp

1
2
 

select sectno, cast(text as varchar(32000)) from syscat.statements where pkgschema='schema' and pkgname='P0000000001'

get table size

1
 
select TABSCHEMA, tabname, CARD as ROWS from SYSCAT.TABLES where lower(tabname) in ('tablename') and TABSCHEMA in ('schema') with ur
微海报分享

近期文章

近期评论

标签

热门

文章归档

分类目录

功能