Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Explain - is it posible to execute explain from input file

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Grofaty,
    If by "input file" you mean a script, yes, you should be able to since the EXPLAIN statement is considered SQL.

    Andy

  3. #3
    Join Date
    Mar 2004
    Posts
    448
    You can do that as embedded C, it is an executable statement.

    regards,

    mujeeb

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,

    ARWinner, can you please post any simple sample command.

    Thanks,
    Grofaty

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    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

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Can you use 'dynexpln -f <your sql file>'?

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    In LUW it is db2expln. The full syntax in in the "Administration Guide: Performance" for V8.1

    Andy

  10. #10
    Join Date
    Jan 2003
    Posts
    1,605
    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

  11. #11
    Join Date
    May 2004
    Posts
    4
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •