blpraveenspi
Currently Banned
New User
Joined: 03 Jan 2007
Posts: 3
Location: Mysore
|
|
|
|
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; |
|
.jpg)
