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 Undefined Report Program socker_dad COBOL Programming 2 Fri Mar 30, 2018 1:45 am
No new posts How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 6 Tue Jan 16, 2018 10:49 pm
No new posts Copying part of the report using SARBCH Aedulla CA Products 1 Tue Oct 24, 2017 9:24 am
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

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