Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Function of EXPLAIN in Db2

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
sirigirisuresh
Warnings : 2

New User


Joined: 29 Apr 2006
Posts: 3

PostPosted: Wed Jan 03, 2007 3:34 pm    Post subject: Function of EXPLAIN in Db2
Reply with quote

Please tell me what EXPLAIN will do in Db2[/quote]
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Jan 03, 2007 3:38 pm    Post subject:
Reply with quote

Hi There,

Please have a look

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db2tools.adb.doc.ug/h1xexchap.htm
Back to top
View user's profile Send private message
blpraveenspi
Currently Banned

New User


Joined: 03 Jan 2007
Posts: 3
Location: Mysore

PostPosted: Wed Jan 17, 2007 3:39 pm    Post subject:
Reply with quote

Explain is a DB2 Administration function .You must have Db2 Admin tailored into your system.IF DB2 Admin tool is Activated, Type E on command line to use DB2 Admin utiltiy Explain.
*Before you can use EXPLAIN, you must create a table called PLAN_TABLE to
hold the results of EXPLAIN
*populate PLAN_TABLE by executing the SQL statement EXPLAIN.
The EXPLAIN statement captures information about the access plan
chosen for the supplied explainable statement and places this information
into the Explain tables. An explainable statement is a DELETE, INSERT,
SELECT, SELECT INTO, UPDATE, VALUES, or VALUES INTO SQL
statement.
*You can populate a plan table when you bind or rebind a plan or package.
Specify the option EXPLAIN(YES).
*Cloumns pf Plan-Table
QUERYNO QBLOCKNO APPLNAME PROGNAME PLANNO METHOD CREATOR TNAME TABNO
ACCESSTYPE MATCHCOLS ACCESSCREATOR ACCESSNAME INDEXONLY SORTN_UNIQ
SORTN_JOIN SORTN_ORDERBY SORTN_GROUPBY SORTC_UNIQ SORTC_JOIN SORTC_ORDERBY
SORTC_GROUPBY



Simple Query to Extract all the SQL Statements in Packages
SELECT * FROM JOE.PLAN_TABLE
WHERE PROGNAME = 'PACK1' AND COLLID = 'COLL1' AND VERSION = 'PROD1'
ORDER BY QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;
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 -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts What is the Extract Function in JCL dharmaraok JCL & VSAM 3 Wed Jun 14, 2017 9:12 am
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Strange EXEC function call in z/VM Willy Jensen CLIST & REXX 3 Wed Oct 05, 2016 2:07 pm
No new posts Replace function abdulrafi COBOL Programming 13 Mon Sep 12, 2016 10:12 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us