Process to run explain plan on iseries v3

[cmmrfrds]

I just got a ODBC connection setup to use Universal SQL Editor to run my SQL against DB2. I would like to run explain plans on my SQL. Could somebody give me the steps I need to go through to do this. Does the process put the plan results in a table and what is this table normally called. Also, can somebody show me some SQL to run against the plan table.

Thank you.

 

[v0520]

One of the best ways to run explain plans is to download a free ibm tool called visual explain, this tool has a way to graphically create an explain plan of a sql statement and can tell you what type of index the statement is using etc..

I use it all the time to tune up queries that I feel are not performing right.


Gilbert M. Vanegas
Programmer Analyst III
County of San Bernardino - ISD
Email : gvanegas@isd.sbcounty.gov

 

[cmmrfrds]

Thank you.
I was able to get INavigator and have been running visual explain from that product.

 

[v0520]

Sure thing, so are the plans from visual explain useful to you?
I find them useful, they are graphical, so are a little different than you would get under spufi or text-based tools residing on the database server.

Gilbert M. Vanegas
Senior Programmer Analyst
County of San Bernardino - ISD

 

[cmmrfrds]

Yes, I like the visual plans along with the text window that has descriptions on indexes etc... The only thing that holds me back a little is that the administator does not let me have permissions to read all the tables in the QSYS and QSYS2 schemas so that I can't set up my own queries to provide information on indexes created through DDS. Of course I can read all the objects in the SQL views and tables but this is only part of the picture since it leaves out all the logical files/indexes.

 

[Mercury2]

FYI Index Advisor and condensed index advice are stored in the system index advice table QSYS2/SYSIXADV and view QSYS2/CondensedIndexAdvice respectively.

Check out

http://www.ibm.com/developerworks/db2/library/techarticle/dm-0701forstie/

 

[cmmrfrds]

It looks like those tables/views were added on V5R4. We are still at V5R3. I will check them out when we move to V5R4.
Thank you.