Technical Blog Post
Abstract
50 DB2 Nuggets #29 : Tech Tip - Generating explain output of query which is part of a stored procedure
Body
To collect explain output for a stored procedure (the stored procedure will be executed) :
db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL"
db2 terminate
db2stop
db2start
Run the stored procedure
db2exfmt -d db_name -g TIC -w -1 -n % -s % -# 0 -o exfmt_bad.txt
To collect explain output for a stored procedure without executing it, you need to set db2set DB2_SQLROUTINE_PREPOPTS and db2 set current explain mode.
db2set DB2_SQLROUTINE_PREPOPTS="EXPLAIN ALL"
db2 terminate
db2stop
db2start
db2 connect to sample
db2 set current explain mode = explain
Call the stored procedure. It will throw following warning message:
SQL0217W The statement was not executed as only Explain information requests are being processed. SQLSTATE=01604
db2exfmt -d sample -g TIC -w -1 -n % -s % -# 0 -o exfmt_bad.txt
Thanks!
UID
ibm11141486