Explain Utility

Prabha · New User Joined: 05 Dec 2005 Posts: 79

Hi,

could you pleae let me know the usage of EXPLAIN utility and where we are handling?

Regards,
Prabha

dbzTHEdinosauer · Posted: Fri Jul 09, 2010 2:52 pm

5.58 EXPLAIN SQL REFERENCE

6.4 Chapter 6-4. Using EXPLAIN to improve SQL performance SQL Appl Programmers guide

also, talk to your db2 support to determine what facilities already exist.
it may be that your site has VISUAL Explain.

LearningDb2 · New User Joined: 19 Jun 2009 Posts: 40 Location: India

Hi Prabha,

We use Explain to collect the information abt the access path used by the optimizer for the statement which is explainable.
Suppose you have a stored procedure having hundred's of SQL statements.Then u can use Explain for the sql's whose access path u want to see.Eg:- whether the predicates that u have specified in the select clause are using proper indexes or not.

We assign a query number through which we identify that statement in user created PLAN_TABLE.

Eg-: exlpain all set query number = nn for select.......(explainable statement)

All the informations derived by the Explain are stored in PLAN_TABLE.
Usually GroupID.PLAN_TABLE.

For the description of the columns of Plan_table you can hv a look at SQL reference(Statements-Explain).

Hope this Helps.

Thanks