DB2 EXPLAIN - 1

DB2 EXPLAIN - Generating Explain Plan

        Query tuning is one of most import jobs of a DBA. It takes lot of skill and effort to perform the query tuning. There can be several reasons for bad performance of a query, it may be due to low CPU available, low system resources and so on. In most of the cases SQL statement is the culprit.so it is required that we tune the query and see that proper indexes available for optimal performance of the query. 




Generating Explain Plan



Follow the steps to generate explain plan in DB2 CLP


Connect to DB - db2 connect to <DB_NAME>
Set Explain Mode -db2 SET CURRENT EXPLAIN MODE  EXPLAIN
Execute the query -db2 -tvf query.sql 
Query will not be executed instead just the access path is noted as the mode set to EXPLAIN
DB2 has a inbuilt tool db2exfmt which provides formatted output from explain tables.
db2exfmt -d <DBNAME>  -1 -o query.sql.exfmt
Reset the Explain mode to no - db2 SET CURRENT EXPLAIN MODE  NO




**Note1: -1 is the overall option in db2 and is functionally equivalent to   
“-e % -n % -s % -v % -w -1 -# 0″


Generating Explain Plan for stored procedure in DB2





Connect to DB - db2 connect to <DB_NAME>
Set Explain Mode -db2 SET CURRENT EXPLAIN MODE  EXPLAIN
Set the following SQLROUTINE_PREPOPTS parameters and do the session restart using            > db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL EXPLSNAP ALL"   > db2start     > db2stop
Map Procedure name to package name using following SQL


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;





5.Extract explain plan using db2exfmt or db2expln using package name obtained from above sql

>   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>



**Note2: Please make sure that explain tables are setup before you start to generate the explain plan, if they are not set up you can do that by executing a predefined procedure available in db2 



Explain Tables



CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', 


        CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))



If you want to create explain tables in your own schema enter schema name as last parameter for the stored proc.



**Please click here to see detail view of explain tables** 






Please check my next article to know how to analyze an explain plan.