EXPLAIN SQLSTMT Command
This section shows an example of an Explain report generated with the EXPLAIN SQLSTMT command.
For details on how to specify EXPLAIN commands refer to the Report Command Reference.
Explain Report - Example for SQL Text
Use the following EXPLAIN SQLSTMT command to generate the following example of an explain report.GLOBAL PLANEX(KO2EXPL)
SSID(DA11)
SQLID(XRK)
EXPLAIN SQLSTMT
(
SELECT * FROM SYSIBM.SYSPACKAGE
WHERE NAME = 'DGO@TPG3'
AND COLLID = 'KO2EX520' ;
)
LEVEL(INDEXES)
EXECACTUAL AT: 02/21/16 11:57:21 OMEGAMON XE DB2 PE (V5.4) PAGE : 1-1
EXPLAIN SQL STATEMENT DB2 VERSION: V10
LOCATION : PMODA11 USER AUTHID: XRK
SUBSYSTEM: DA11 INDEX CURR.SQLID : XRK
SQL STATEMENT TEXT :
SELECT * FROM SYSIBM.SYSPACKAGE
WHERE NAME = 'DGO@TPG3'
AND COLLID = 'KO2EX520'
-------------------------------------------------------------------------------
EXPLAIN TABLE: XRK.DSN_STATEMNT_TABLE -----------------------------------------
EXPLAIN_TIME : 2016-02-21-11.57.21.680000
PROGNAME : DGO@TPT2 , COLLID : KO2EX520
VERSION : OMPE_FINAL , APPLNAME (Plan) : N/P
QUERYNO : 999735911 , SECTNOI : 0
STMT_ENCODE : U - Unicode , STMT_TYPE : SELECT
PROCMS (Cost MS) : 1 , COST_CATEGORY : A - No default *
PROCSU (Cost SU) : 11 , REASON (Category) : N/P
TOTAL_COST : 1 , GROUP_MEMBER : N/P
COST_CATEGORY : A - Cost estimate without using default valuesEXPLAIN TABLE: XRK.PLAN_TABLE -------------------------------------------------
EXPLAIN_TIME : 2016-02-21-11.57.21.680000
TIMESTAMP : 2016-02-21-11:57:21.68
PROGNAME : DGO@TPT2 , COLLID : KO2EX520
VERSION : OMPE_FINAL , APPLNAME (Plan) : N/P
QUERYNO : 999735911 , SECTNOI : 0
QBLOCKNO : 1 , PARENT_QBLOCKNO : 0
PLANNO : 1 , PARENT_PLANNO : 0
MIXOPSEQ : 0 , QBLOCK_TYPE : SELECT
TNAME (Table) : SYSPACKAGE , CREATOR (Table) : SYSIBM
TABNO (Table) : 1 , CORRELATION_NAME : N/P
TABLE_TYPE : T - Table , CTEREF : 0
TABLE_ENCODE : U - Unicode , TABLE_MCCSID : 1208
TABLE_SCCSID : 367 , TABLE_DCCSID : 1200
TSLOCKMODE : N - No lock * , GROUP_MEMBER : N/P
ACCESSTYPE : I - Index scan , PRIMARY_ACCESSTYPE: BLANK
ACCESSNAME (Index): DSNKKX01 , ACCESSCREATOR : SYSIBM
MATCHCOLS : 0 , INDEXONLY : NO
METHOD (Join) : 0 - First table , JOIN_DEGREE : 0
JOIN_TYPE : b - INNER or NO , MERGN : NO
MERGE_JOIN_COLS : 0 , MERGC : NO
PREFETCH : S - Sequential , PAGE_RANGE : NO
WHEN_OPTIMIZE : b - At bind time , ACCESS_DEGREE : 0
COLUMN_FN_EVAL : BLANK , ROUTINE_ID : 0
HINT_USED : N/P , OPTHINT : N/P
SCAN_DIRECTION : N/A
SORTN_PGROUP_ID : 0 , SORTN_UNIQ : NO , SORTC_UNIQ : NO
SORTC_PGROUP_ID : 0 , SORTN_JOIN : NO , SORTC_JOIN : NO
ACCESS_PGROUP_ID : 0 , SORTN_ORDERBY: NO , SORTC_ORDERBY: NO
JOIN_PGROUP_ID : 0 , SORTN_GROUPBY: NO , SORTC_GROUPBY: NO
REMARKS : N/P , STMTTOKEN : N/P
PARALLELISM_MODE : BLANK , BIND_EXPLAIN_ONLY : NO
EXPANSION_REASON : N/A
TSLOCKMODE : N - No lock (UR isolation)
-------------------------------------------------------------------------------THE ACCESS PATH CHOSEN BY DB2 AT 11:57:21.6 ON 2016-02-21
+------------------------------------------------------------------+
| NON-MATCHING INDEX SCAN WITH SCAN OF REFERENCED DATA PAGES |
| NON-CLUSTERED INDEX SCAN WILL BE USED |
| PURE SEQUENTIAL PREFETCH WILL BE PERFORMED |
| PAGE RANGE SCAN WILL NOT BE USED |
+------------------------------------------------------------------+
INDEX: SYSIBM.DSNKKX01 --------------------------------------------------------
STATSTIME: 2016-02-16-14.45.54.186869
CREATED : 0001-01-01-00.00.00.000000 ALTERED: 2016-09-21-23.30.17.962937
FULL KEY CARD: 885,PAGES : 16,LEVELS: 2,CLUSTRING: Y
1"ST KEY CARD: 1,SPACE : 245.760K,UNIQUE: YES,CLUSTERED: N
INDEX TYPE : 2,PGSIZE: 4096,BFPOOL: BP0,DB.NAME : DSNDB06
CLUSTERRATIO : 84.6328%,ERRULE: NO,CLRULE: NO,IXSPACE : DSNKKX01
MAX.PIECESIZE: 0,COPY : NO,COPYLRSN: X'000000000000'
TABLE: SYSIBM.SYSPACKAGE ------------------------------------------------------
STATSTIME: 2016-02-16-14.45.54.186869, TB TYPE : TABLE
CREATED : 1985-04-01-00.00.00.000000, ALTERED : 2016-07-01-09.00.57.417442
ROWS : 885, COLUMNS : 60, ROWLENGTH: 3913, EDIT PROC.:
% PAGES : 16, DBASE ID: 6, AUDITING : NONE, VALIDPROC.:
ACT.PAGES: 62, TABLE ID: 128, STATUS : COMPX, TABCREATOR: SYSIBM
TAB.STAT.: , ENC.SCHEME: UNICODE
TABLESPACE: DSNDB06.SYSTSPKG --------------------------------------------------
NAME : SYSTSPKG , DATABASE : DSNDB06
CREATOR : SYSIBM , CREATED BY: SYSIBM
CREATED : 2016-02-15-13.39.20.690282, ALTERED : 2016-02-15-13.39.20.690282
STATSTIME : 2016-02-16-14.45.54.186869,
ACTIVE PGS: 372, DBASE ID : 6, TS STATUS : A, TS TYPE : G
PAGE SIZE : 4KB, OBJ ID : 2067, ERASERULE : NO, STORGROUP :
SPACE : 1.720M, PAGESET ID: 2068, CLOSERULE : NO, BUF.POOL : BP0
DS SIZE : 68.719G, OLD VERS : 0, LOCKPART : N/A, ENC.SCHEME: UNICODE
LOCKMAX : SYSTEM, CUR VERS : 1, LOCKRULE : ROW, SBCS CCSID: 367
TABLES/TS : 1, PARTITIONS: 1, LOG : YES, DBCS CCSID: 1200
MAXROWS : 255, SEG SIZE : 32, IMPLICIT : NO, MIX. CCSID: 1208
AVG ROWLEN: 201,REPORT ON: 02/21/16 11:57:21 OMEGAMON XE DB2 PE (V5.4) PAGE : SUMMARY
EXPLAIN SUMMARY REPORT USER AUTHID: XRK
THE FOLLOWING 1 EXPLAIN REQUESTS WERE PROCESSED: PAGE NO
1: DA11 SQL STMT
INDEXES REPORT REQUESTED
SELECT * FROM SYSIBM.SYSPACKAGE
WHERE NAME = 'DGO@TPG3'
AND COLLID = 'KO2EX520'
+ NON-MATCHING INDEX SCAN-DATA PAGES SCAN 1-2
OMEGAMON XE for DB2 PE (V5.4) EXPLAIN PROCESSING COMPLETED.