Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
EXPLAIN in DB2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
maddukurivasu
Warnings : 1

New User


Joined: 14 Apr 2006
Posts: 4

PostPosted: Thu May 25, 2006 1:23 am    Post subject: EXPLAIN in DB2
Reply with quote

any body knows EXPLAIN in bind process.
Back to top
View user's profile Send private message

Gurmeet

New User


Joined: 22 Feb 2006
Posts: 46
Location: Pune

PostPosted: Thu May 25, 2006 4:49 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
humpty
Warnings : 1

New User


Joined: 01 May 2005
Posts: 23

PostPosted: Fri May 26, 2006 10:13 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
umeshkmrsh

New User


Joined: 21 Sep 2005
Posts: 79
Location: India

PostPosted: Fri May 26, 2006 11:36 am    Post subject:
Reply with quote

Could you give an example of - how the details of access path look like for a simple SQL query.
Back to top
View user's profile Send private message
anamikak

New User


Joined: 10 May 2006
Posts: 64
Location: Singapore

PostPosted: Wed Jun 14, 2006 6:21 pm    Post subject:
Reply with quote

If you have APPTUNE installed you can directly have a look at the explain option.
Back to top
View user's profile Send private message
Hanfur

Active User


Joined: 21 Jun 2006
Posts: 104

PostPosted: Mon Jul 03, 2006 11:32 am    Post subject: Hi
Reply with quote

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
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Can someone explain me this JCL line ... PeterHolland JCL & VSAM 9 Sat Dec 12, 2015 7:22 am
No new posts DB2 Explain agkshirsagar DB2 3 Wed Aug 28, 2013 6:23 pm
No new posts can some one explain the below code... ram_vizag JCL & VSAM 3 Thu Aug 22, 2013 4:35 pm
No new posts Please explain this SQL CODE. tamminenisidhartha DB2 4 Wed Mar 13, 2013 5:05 pm
No new posts Explain Comparision Report gylbharat DB2 4 Tue Jun 05, 2012 5:48 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us