Hi i'm having a problem while i'm trying to collect explain plan with the db2exfmt tool ,

Can some body explain me the process of how to use that tool.

My requirement is to collect cost of a stored proc ,

I have the EXPLAIN tables created in instance DB2INST1 schema and think i have a proc named "UNNAMED" which have the package name P123456 and my source schema i.e., where my proc is settled is ABCD ,

so i have entered the following syntax

! db2exfmt -d SAMPLE -e DB2INST1 -s ABCD -n P123456 -g TIC -w -1 -# 0 -t

Err: The error it's throwing out is could not find the EXPLAIN TABLES in the schema defined

But when i query the db with " list tables for schema DB2INST1" the EXPLAIN tables were shown up ...

I'm successful in taking explain plan with the db2expln utility but have a problem with db2exfmt ...................Please somebody help me.

Views: 2330

Replies to This Discussion

Hi Manohar,


Which user is executing the script containing the db2exfmt ?

If it is not db2inst1, EXPLAIN tables should be created in the user's schema.

Also, db2exfmt only formats information residing in EXPLAIN tables.

To populate the EXPLAIN tables :

set current explain mode explain

run the query

set current explain mode no

! db2exfmt ...


Hope this helps.



Yves-Antoine Emmanuelli

Thanks for that reply Antoine ,

I just need some clarification in this db2exfmt concept ....... I wish you would spare some of your time


I just did that "

=> set current explain mode explain

=> run the query

=> set current explain mode no

=> ! db2exfmt .............


Now say i want to explain a stored proc , to be more precise i want to explain the procedure with the name itself rather than copy the SELECT (query) part and executing it .

If that is what my requirement is what should i do ...

Shall i use the call SCHEMA.PROCNAME( IN , IN , IN , IN ........OUT , OUT ) between the statements

  • current explain mode explain and current explain mode no
and now we can use the db2exfmt with -n option
Is that the way we do it Antoine ?



You may check this, developed by Serge Rielau:

Hello Manohar,


I think you could try the steps:


db2 connect to <dbname>

db2 -tvf $HOME/sqllib/misc/EXPLAIN.DDL

db2exfmt -d SAMPLE -e DB2INST1 -s ABCD -n P123456 -g TIC -w -1 -# 0 -t

Take care!


I really thank you all people for replying me back , @Anas Mosaad Mesrah : I found the link you shared a bit interesting , Ill have to look into it, i need some time and ill post if im successful or not after going through  your shared link .


@ David fachini : I have tried the syntax you have mentioned already but before that we need to explain the select statement from which db2exfmt helps to format the output .

Coming to the syntax the error i'm getting is there were no EXPLAIN tables in the instance (DB2INST1) specified , but they were in that instance.


Anyway thank you all guys , let me give some more hits .....ill come back to you .


Try BLU Acceleration on Cloud

© 2017   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service