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)
EXECHere is an example of an SQL
Text.ACTUAL AT: 02/21/16 11:57:21 IBM OMEGAMON FOR DB2 PERFORMANCE EXPERT (V5.5.0) 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 values
EXPLAIN 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 IBM OMEGAMON FOR DB2 PERFORMANCE EXPERT (V5.5.0) 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 IBM OMEGAMON FOR DB2 PERFORMANCE EXPERT (V5.5.0) EXPLAIN PROCESSING COMPLETED.