How to create Explain Plan of DB2 Stored Procedures?

by | Jun 5, 2008

Step-1 : Create Explain tables

See this entry http://www.db2ude.com/?q=node/65 for how to create explain tables in DB2.

Please note: You need to create explain tables in the USER-ID of the logged-in user. If you created explain plans in the schema name of some userid, other logged-in users can not use the explain tables owned by other users. In other words, each user should have their own explain tables. This is true even when you are using SET CURRENT SCHEMA command to set schema name for the tables that you are using.

Step-2 : Set parameters so that query compiler can populate explain tables

You have 2 options here:

  1. Set it once at the instance level so that every procedure you create is explained
  2. Set it at DB2 session level so that you control which procedures need to be explained.

Personally, I like second option.

Option – 1 : Set at the instance level

$ db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL EXPLSNAP ALL"
$ db2stop
$ db2start

Please note: Instance rebound is necessary for the DB2 registry variables to take effect.

Option – 2 : Set at the DB2 session level

In your DB2 Stored Procedure script, call SET_ROUTINE_OPTS parameter to let query compiler to create explain informations. For example, your stored procedure script may look like following:

testsp.sql
———-

--#SET TERMINATOR ;

CALL SYSPROC.SET_ROUTINE_OPTS('EXPLAIN ALL');

SET CURRENT SCHEMA = 'DB2INST1';

SET CURRENT FUNCTION PATH SYSIBM,SYSCAT,SYSFUN,DB2INST1;

DROP PROCEDURE testsp;

--#SET TERMINATOR @

CREATE PROCEDURE testsp
LANGUAGE SQL
BEGIN
  DECLARE c1 CURSOR WITH RETURN FOR VALUES CURRENT TIMESTAMP;
  OPEN c1;
END
@

Create SQL Procedure

$ db2 connect to sample
$ db2 -tf testsp.sql

Step-3 : Mapping procedure name to package Name

Each DB2 SQL routine has a procedure name (which is used in CALL statements), but is represented internally by a system-generated package name. The data in the explain tables will refer to the package name, not the procedure name, hence we must know the procedure-to-package mapping so that we can give the proper package name to exfmt when dumping plans.

select 	deps.bschema SCHEMA, 
	procs.routinename PROCEDURE, 
	deps.bname PACKAGE, 
	procs.valid VALID
  from 	sysibm.sysdependencies deps,
       	sysibm.sysroutines procs
 where 	deps.dtype = 'F' 
   and 	deps.btype = 'K'
   and 	procs.specificname  = deps.dname
   and 	procs.routineschema = deps.dschema
 order 	by 1,2;

Step -4 : Extract Explain Plan using db2exfmt or db2expln tool.

You can call db2exfmt or db2expln tool using the package name determined in the previous step.

db2exfmt -d <dbname> -e <schema> -s <schema> -w -1 -n <package name> -g -# 0 -o <output file>
db2expln -d <dbname> -c <schema> -p <package name> -s 0 -g -o <output file>

Step -5 : Putting it all together to get the explain plan in a single step.

After you have have created procedures, you need minimum 2 steps to extract the explain plan for a SQL Procedure. First – get the package name used internally by the SQL Procedure and Second – use either db2exfmt or db2expln tool to get the explain plan.

For Linux/Unix Systems

#!/bin/ksh

if [ $# -eq 0 ] ; 
then
   echo Usage: `basename $0` schema.procedure
   exit -1
fi

schema=${1%%.*}
procedure=${1#*.}

if [ "$schema" = "" ] || [ "$schema" = "$procedure" ] ;
then
   schema=$USER
   procedure=$1
fi

dbname=`db2 -tx "select reg_var_value from sysibmadm.reg_variables \
                 where reg_var_name = 'DB2DBDFT' \
                 and dbpartitionnum = 0"`

if [ "$dbname" = "" ] ;
then
   echo DB2 registry variable DB2DBDFT is not set.
   exit -1
fi

pkgname=`db2 -tx "select VARCHAR(deps.bname,25) PACKAGE \
  from  sysibm.sysdependencies deps,\
        sysibm.sysroutines procs\
 where  deps.dtype = 'F' \
   and  deps.btype = 'K'\
   and  procs.specificname  = deps.dname\
   and  procs.routineschema = deps.dschema\
   and  procs.routineschema = upper('$schema')\
   and  procs.routinename = upper('$procedure')"`

echo Creating explain plan in database $dbname for $schema.$procedure using package $pkgname

#db2expln -d $dbname -c $schema -p $pkgname -s 0 -g -o $procedure.exp
db2exfmt -d $dbname -e $schema -s $schema -w -1 -n $pkgname -g -# 0 -o $procedure.exp

How to run in Linux/Unix

$ ./exp testsp

or

$ ./exp db2inst1.testsp

Note: If you do not specify schema name, logged-in user id is used as a schema name. The syntax is schema.procedure.

For Windows

You need 2 scripts to automate db2 explain plan for a SQL Procedure.

Script-1: pkg.cmd

@echo off
REM Package name for a stored procedure

SET SCHEMA=%1%
SET PROCEDURE=%2%

db2 connect to sample > NUL

db2 -x  select VARCHAR(deps.bname,25) PACKAGE ^
  from  sysibm.sysdependencies deps,^
        sysibm.sysroutines procs ^
 where  deps.dtype = 'F' ^
   and  deps.btype = 'K' ^
   and  procs.specificname  = deps.dname ^
   and  procs.routineschema = deps.dschema ^
   and  procs.routineschema = upper('%SCHEMA%') ^
   and  procs.routinename = upper('%PROCEDURE%')

db2 connect reset > NUL

Script-2: exp.cmd

@echo off
cls
@ECHO Executed by: %USERNAME% Machine: %COMPUTERNAME% On %OS% %DATE% 

IF "%1" == "" GOTO :Usage1

IF "%2" == "" GOTO :Usage2

SET SCHEMA=%1
SET PROCEDURE=%2

FOR /F "tokens=1" %%K in ('call pkg.cmd %SCHEMA% %PROCEDURE%') do SET PKGNAME=%%K
db2exfmt -d SAMPLE -e %USERNAME% -s %USERNAME% -w -1 -n %PKGNAME% -g -# 0 -o %PROCEDURE%.EXPLAIN

goto :end

:Usage1
ECHO You did not provide schema name for this script
ECHO.
ECHO Usage: exp schame_name procedure_name
ECHO.

goto :end

:Usage2
ECHO You did not provide procedure name for this script
ECHO.
ECHO Usage: exp schame_name procedure_name
ECHO.

:end

How to run in Windows

C:\exp schemaname procedurename

Please note: The database name was hard coded in the scripts. I did not had time to automate this. If you, please send me revised script at vikram@zinox.com.

All 3 scripts are also attached in a zip file at http://www.db2ude.com/files/explainscript.zip

What if Stored Procedures contain dynamic SQL?

If your stored procedure has dynamic SQL calls such as use of the EXECUTE command or use of the global temporary tables (GTT), you have dynamic SQL statements for which you will not get the explain plan during compile process. You need to execute the stored procedure once to get the explain plan for all dynamic SQL statements after you have completed the above step.

Follow this procedure to get the explain plan when you have used GTTs or dynamic SQL statements.

For example, you have the following stored procedure having dynamic as well as static calls.

dynsp.sql
——

CREATE PROCEDURE DYNSP
language SQL
BEGIN
   DECLARE GLOBAL TEMPORARY TABLE SESSION.tt_mytemp
   (
      c1 INT NOT NULL,
      c2 INT NOT NULL,
      c3 INT NOT NULL
   ) NOT LOGGED WITH REPLACE ON COMMIT PRESERVE ROWS;
   DECLARE GLOBAL TEMPORARY TABLE SESSION.tt_yrtemp
   (
      c1 INT NOT NULL,
      c2 INT NOT NULL,
      c3 INT NOT NULL
   ) NOT LOGGED WITH REPLACE ON COMMIT PRESERVE ROWS;


  INSERT INTO SESSION.tt_mytemp VALUES (1,2,3),(2,3,4),(3,4,5),(4,5,6),(5,6,7);
  INSERT INTO SESSION.tt_yrtemp VALUES (5,6,7),(4,5,6),(8,9,10),(9,10,11),(10,11,12);

  BEGIN
     DECLARE c1 CURSOR WITH RETURN FOR SELECT t1.c1,t1.c2,t1.c3 
                                       FROM SESSION.tt_mytemp t1, SESSION.tt_yrtemp t2
                                       WHERE t1.c1 = t2.c2;
     OPEN c1;
  END;   
END
@

db2 connect to sample
db2 "CALL SYSPROC.SET_ROUTINE_OPTS('EXPLAIN ALL')"
db2 -tf dynsp.sql

The above step gives you explain plan for all static SQL calls in the SP. Since you have used GTT, you need to do one extra step of getting the explain plan

db2 connect to sample
db2 set current explain mode yes
db2 "call dynsp()"
db2 set current explain mode no
./exp dynsp

Now, look at the dynsp.exp file and it will have explain plan for all dynamic SQL statements as well as static SQLs contained in your stored procedure.

Acknowledgment: Thanks to John Hornibrook and Sunil Kamath of DB2 Toronto Lab to provide help on this.