rag swain
New User
Joined: 17 Dec 2007
Posts: 33
Location: pune,INDIA
|
|
|
|
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)'
//
|
|
|
stodolas
Active Member
Joined: 13 Jun 2007
Posts: 632
Location: Wisconsin
|
|
|
|
| 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 |
|
