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.
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 noand now we can use the db2exfmt with -n optionIs that the way we do it Antoine ?
You may check this, developed by Serge Rielau:
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
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 .