IBM Support

50 DB2 Nuggets #29 : Tech Tip - Generating explain output of query which is part of a stored procedure

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!

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

UID

ibm11141486

Page Feedback