EXPLAIN in DB2

maddukurivasu · New User Joined: 14 Apr 2006 Posts: 4

any body knows EXPLAIN in bind process.

Gurmeet · New User Joined: 22 Feb 2006 Posts: 46 Location: Pune

maddukurivasu,

If we use EPLAIN YES option while binding the packages, DB2 writes the details of access paths choosen by all the SQLs(assigning each of them a unique no.) in the plan_table. You can analyze these paths and can change the SQL or add/remove indexes to improve the performance.

Please let me know if you need further clarification.

~Gurmeet

humpty · New User Joined: 01 May 2005 Posts: 23

EXPLAIN is a very typical thing.
it gives you the following information.
1. Whether the DB2 has made use of index, and if yes which all indexes.
2. Whether a join criteria satisfied in the table.
3. Whether the table uses sort.
4. All the information about the access path used.
5. All the information about the user-defined functions used.
6. Based on these it gives the estimation cost of the QUREY, the most important thing to know.#
all this information is published in the PDS PLAN_TABLE.

thanks

umeshkmrsh · New User Joined: 21 Sep 2005 Posts: 79 Location: India

Could you give an example of - how the details of access path look like for a simple SQL query.

anamikak · New User Joined: 10 May 2006 Posts: 64 Location: Singapore

If you have APPTUNE installed you can directly have a look at the explain option.

Hanfur · Active User Joined: 21 Jun 2006 Posts: 104

Assuming the PLAN table has been in place on your id ;

the u can issue EXPLAIN from QMF too

EXPLAIN ALL SET QUERY NO=1
FOR
SELECT * FROM F6417DB.EMPLOYEE
;

ONce executed successfully u can see theresults by querying the table
yourid.PLAN table by giving the Query number as '1' in predicates.Go thru IBM manuals to understand each columns of PLAN table values and its meanings.

Otherwise go for soem third party tool like File-Aid for Db2 ,Platinum tools etc

-Han