Need help for db2exfmt tool

Replies to This Discussion

Permalink Reply by Yves-Antoine Emmanuelli on November 2, 2011 at 4:21am

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.

Regards

-- 

Yves-Antoine Emmanuelli

Permalink Reply by Manohar Viswanatha on November 2, 2011 at 9:42am

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 ?

 

 

Permalink Reply by Anas Mosaad Mesrah on November 2, 2011 at 9:48am

You may check this, developed by Serge Rielau:

https://www.ibm.com/developerworks/mydeveloperworks/blogs/SQLTips4D...

Permalink Reply by David Fachini on November 2, 2011 at 1:07pm

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!

 

Permalink Reply by Manohar Viswanatha on November 3, 2011 at 5:43am

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 .