Hi,

I have used the following command and it works fine.
db2 explain all with snapshot for "select * from db2admin.test"

Is it posible to execute "explain" from input file?

Thanks,
Grofaty

Grofaty,
If by "input file" you mean a script, yes, you should be able to since the EXPLAIN statement is considered SQL.

Andy

You can do that as embedded C, it is an executable statement.

regards,

mujeeb

Hi,

ARWinner, can you please post any simple sample command.

Thanks,
Grofaty

Grofaty,
(I have never done this myself but from reading the manuals, this is what you should do).
First you need to have the EXPLAIN tables created. See Appendix J in Vol 1 of the V8.1 SQL Reference manual, if they have not bee created.
Then you would create a script file something like:

EXPLAIN ALL SET QUERYNO = 13 SET QUERYTAG = 'MyQUERY ' FOR SELECT * from mytable;
-- get info from explain tables
SELECT * FROM EXPLAIN_STATEMENT WHERE QUERYNO = 13 and QUERYTAG = 'MyQUERY; '
-- any other SELECT statements against the EXPLAIN tables (see Appendix J again for layouts) that you may want

Then run it :
DB2 -tvsf myscript.ddl

HTH

Andy

Hi,

ARWinner, I know all of this! Ooooo, I haven't been clear enough. I would like to execute "explain sql" from sql file.

Now I have to write:
db2 explain all with snapshot for "select * from db2admin.test"

I would like to do this something like this:
db2 explain all with snapshot from sqlfile.ddl

I would like to have SQL statement in file! Now I must constantly convert multi row SQL statement to one row SQL statement to execute db2 explain from command line. That is time consuming. Any help will be appreciated.

Thanks,
Grofaty

Grofaty,
I am, by no means, a scripting expert. I am just breaking into this now. But it seems to me that you need a script to do what you want. There is a good example of one here:

http://searchdatabase.techtarget.com...ffer=dbtraff09

It is for Linux/Unix. You should be able to modify it to suit your needs.

Andy

Can you use 'dynexpln -f <your sql file>'?

In LUW it is db2expln. The full syntax in in the "Administration Guide: Performance" for V8.1

Andy

Hi,

ARWinner, I already have a script and I execute this script from batch file witch is executed from command prompt. But my script requires the SQL statement to be put in command prompt. So I just need somekind of option to execute SQL explain from file. BTW, the link you have provided doesn't show the explain option. It uses the reorg option I just don't need at this time.

n_i, I have tested "dynexpln" utility. The command is equal to "db2expln". It provides interesting output, but at the moment I don't need this option.

NOTE: My question is still the same: How to execute Explain from SQL file? Is it posible?

Thanks to all of you,
Grofaty

Hi Grofaty,

i hope i didn't get you wrong, but
why don't you use the register variable [explain mode]?
db2 connect to sample
set current explain mode=explain
db2 -vtsf sqlfile.ddl
set current explain mode=no

now, the access paths are inserted into the explain tables and you can use
db2exfmt -d sample -g TIC -e [schema] -n % -s % -u [userid] [password] -w -1 -# 0

and analyze the access paths

cheers
Fynn