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 Report

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

New User


Joined: 02 Oct 2007
Posts: 7
Location: Delhi

PostPosted: Fri Jan 25, 2008 10:14 pm    Post subject: Explain Report
Reply with quote

Please update me on Explain reports and the process.

Thanks!
Back to top
View user's profile Send private message

stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Jan 25, 2008 10:47 pm    Post subject:
Reply with quote

Have you checked the manuals? They detail the output of EXPLAIN and what each column means.
Back to top
View user's profile Send private message
rag swain

New User


Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA

PostPosted: Fri Jan 25, 2008 11:10 pm    Post subject: Reply to: Explain Report
Reply with quote

EXPLAIN basically used to get the access path info that is being used in bind process. the process is before you run EXPLAIN you need to have a PLAN TABLE(might be prod, test, or your own).
Code:
SELECT PARALLELISM_MODE,                                         
       TSLOCKMODE, MATCHCOLS, ACCESSNAME, INDEXONLY, ACCESSTYPE 
     , METHOD, TNAME, PREFETCH                                   
     , SORTN_UNIQ||' '||SORTN_JOIN||' '||SORTN_ORDERBY||' '||   
       SORTN_GROUPBY                                             
     , SORTC_UNIQ||' '||SORTC_JOIN||' '||SORTC_ORDERBY||' '||   
       SORTC_GROUPBY                                             
     , COLUMN_FN_EVAL, PROGNAME                                 
     , SUBSTR(TIMESTAMP,5,2)||'/'||SUBSTR(TIMESTAMP,7,2)||'/'|| 
       SUBSTR(TIMESTAMP,3,2)||' '||SUBSTR(TIMESTAMP,9,2)||':'|| 
       SUBSTR(TIMESTAMP,11,2)                                   
     , QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ, SUBSTR(COLLID, 3, 2)
  FROM AUTHD6$1.PLAN_TABLE                                       
    WHERE PROGNAME          = 'JDBB8900'                         
      AND DATE(BIND_TIME)  >= '2008-01-20'                       
  ORDER BY QUERYNO ASC, QBLOCKNO, PLANNO, MIXOPSEQ;             


Besides SPUFI and other interactive tools, you can also achieve this through a batch job like
Code:

//STP1     EXEC EXPLAIN,                       
//             SOUT='H',                       
//             INPUT='DJD0B.AUTH.RPTSQL(TEST2)'
//                                             

Back to top
View user's profile Send private message
stodolas

Active Member


Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin

PostPosted: Fri Jan 25, 2008 11:14 pm    Post subject:
Reply with quote

Explain is much more than what happens in the bind process. It analyzes the access path for any query. It also gives indications about how the query will perform when executed. This can identify un-optimized SQL or SQL that forces a full table scan, or doesn't use indexes when doing a query, etc
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 Report cost in CA-dispatch Nileshkul CA Products 3 Wed Jun 07, 2017 10:32 pm
No new posts Splitting of single report to seperat... abdulrafi TSO/ISPF 7 Mon Mar 27, 2017 3:59 pm
No new posts Easytrieve report shalem CA Products 4 Sun Feb 26, 2017 9:08 am
No new posts Detailed Transaction History report f... Kyle Carroll CICS 5 Tue Oct 18, 2016 12:42 am
No new posts Easytrieve - Report writing - Only 1 ... Abhi Nature CA Products 2 Wed Sep 14, 2016 11:40 am

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