Analyzing a select with Personal Edition 1

[Powernic]

Hello

I would like to know if it is possible to analyze a select (I mean to see how it is interpreted by the DB with regards to indexes and so on) with DB2 Universal Database Personal Edition, Version 7.2.

If not, can I download somewhere a tool that can do it?


Thank you very much

Nicolas.

 

[gregsimpson]

Nicolas,

you need to use Explain which I believe is available to you.

1. Put your SQL in a file and insert a line before the SQL. In that line enter EXPLAIN ALL FOR. Make sure your SQL ends with statement termination character (usually a semi colon.).

2. Run your SQL by entering db2 -tvf <your file name>. This will cause the sql to be explained and will populate the explain tables. If you haven't created them, you need to by issuingi db2 -tvf EXPLAIN.DDL after locating EXPLAIN.DDL on your box.

If you have the GUI tools then you can explain SQL directly by going into the Control Centre, right clicking on your database and selecting Explain SQL. That brings up another GUI into which you can paste your SQL or pull it up from a file by clicking the Get button and opening up the file that it's in. The GUI is slightly easier to work with because it creates the explain tables for you if they don't already exist and will also throw up the Visual Explain graph of the resulting SQL.

When you've run your SQL it will have populated the explain tables and you can now either produce a graph of it with Visual Explain or produce a less easy to read version of the graph with db2exfmt.

Hope this helps.

Cheers
Greg

 

[Powernic]

Thanks a lot Greg, exactly the kind of answer I was expecting.

Now I can have fun trying to understand with my select requires 1,300,000+ timerons


Nicolas