Explain report example
This example is an Explain report generated from the JCL in the previous section.
This example report has been split into its constituent report blocks for clarity. Similarly, the page header is only shown once for the report, and once for the report summary.
Explain report – page header
ACTUAL AT: 02/16/13 15:26:53 OMEGAMON XE DB2 PE (V5.3) PAGE : 1-1
EXPLAIN PACKAGE DB2 VERSION: V10
LOCATION : PMODB2A USER AUTHID: XRK
SUBSYSTEM: SDA2 DETAIL CURR.SQLID : XRK
Explain report – package version details
FPEY0166I PACKAGE DGO@TPG3 IN COLLECTION KO2EX520 HAS THE FOLLOWING VERSIONS
PRE-COMP'D EXP GEN VERSION IDENTIFICATION
---------- --- ---- -----------------------
2013-02-16 YES 0 O510_PM81053C
2011-08-16 YES -1 O510_PM24082C
2011-07-15 YES -2*OMPE_FINAL
2011-06-14 YES -3 O510_TEST_3
2011-05-13 YES -4 O510_TEST_2
2011-04-12 YES -5 O510_TEST_1
2011-03-11 YES -6 < VERSION IDENTIFICATION NOT SPECIFIED >
START VERSION GENERATION NUMBER SPECIFIED: -2
NUMBER OF VERSION GENERATIONS REQUESTED: 1
Explain report – statement details
PACKAGE LOCATION :PMODB2A
PACKAGE COLLECTION:KO2EX520
PACKAGE ID :DGO@TPG3
PACKAGE VERSION ID:OMPE_FINAL
STATEMENT NUMBER : 1011071
SQL STATEMENT READ FROM SYSIBM.SYSPACKSTMT:
DECLARE C_DGOYTPG_71 CURSOR WITH HOLD FOR
SELECT LOCATION, COLLID, NAME, CONTOKEN, OWNER, CREATOR, TIMESTAMP, BINDTIME,
QUALIFIER, PKSIZE, AVGSIZE, SYSENTRIES, VALID, OPERATIVE, VALIDATE,
ISOLATION, RELEASE, EXPLAIN, QUOTE, COMMA, HOSTLANG, CHARSET, MIXED, DEC31,
DEFERPREP, SQLERROR, REMOTE, PCTIMESTAMP, IBMREQD, VERSION, PDSNAME, DEGREE,
GROUP_MEMBER, DYNAMICRULES, REOPTVAR, DEFERPREPARE, KEEPDYNAMIC, PATHSCHEMAS,
TYPE, DBPROTOCOL, FUNCTIONTS, OPTHINT, ENCODING_CCSID, IMMEDWRITE, RELBOUND,
CATENCODE, REMARKS
FROM DGO_SYSPACKAGE
WHERE LOCATION LIKE :HV_LOC71_LOCATION AND COLLID LIKE :HV_LOC71_COLLID AND
NAME LIKE :HV_LOC71_NAME AND VERSION LIKE :HV_LOC71_VERSION
ORDER BY LOCATION, COLLID, NAME, PCTIMESTAMP DESC QUERYNO 001011071
STATUS : COMPILED-BOUND USING DEFAULTS FOR INPUT VARIABLES
ISOLATION: UNCOMMITTED READ / FROM SYSPACKAGE
-------------------------------------------------------------------------------
EXPLAIN TABLE: PMDEV52.DSN_STATEMNT_TABLE -------------------------------------
EXPLAIN_TIME : 2013-02-16-15.24.35.310181
PROGNAME (Package): DGO@TPG3 , COLLID : KO2EX520
VERSION : N/A , APPLNAME (Plan) : N/P
QUERYNO : 1011071 , SECTNOI : N/A
STMT_ENCODE : U - Unicode , STMT_TYPE : SELECT
PROCMS (Cost MS) : 1 , COST_CATEGORY : B - Default *
PROCSU (Cost SU) : 18 , REASON (Category) : HOST VARIABLES
TOTAL_COST : N/A , GROUP_MEMBER : SDA2
COST_CATEGORY : B - Cost estimate using default values (Details in REASON)
Explain report – PLAN_TABLE details
EXPLAIN TABLE: PMDEV52.PLAN_TABLE ---------------------------------------------
BIND_TIME : 2013-02-16-15.24.35.310181
TIMESTAMP : 2013-02-16-15:24:35.34
PROGNAME (Package): DGO@TPG3 , COLLID : KO2EX520
VERSION : OMPE_FINAL , APPLNAME (Plan) : N/P
QUERYNO : 1011071 , SECTNOI : N/A
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 : SDA2
ACCESSTYPE : I - Index scan , PRIMARY_ACCESSTYPE: BLANK
ACCESSNAME (Index): DSNKKX01 , ACCESSCREATOR : SYSIBM
MATCHCOLS : 1 , INDEXONLY : NO
METHOD (Join) : 0 - First table , JOIN_DEGREE : 0
JOIN_TYPE : b - INNER or NO , MERGN : N/A
MERGE_JOIN_COLS : 0 , MERGC : N/A
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 : N/A
EXPANSION_REASON : N/A
TSLOCKMODE : N - No lock (UR isolation)
Explain report – access path
THE ACCESS PATH CHOSEN BY DB2 AT 15:24:35.3 ON 2013-02-16
+------------------------------------------------------------------+
| MATCHING INDEX SCAN WITH SCAN OF REFERENCED DATA PAGES |
| NUMBER OF MATCHING COLUMNS: 1 - THE INDEX HAS 4 COLUMNS |
| NON-CLUSTERED INDEX SCAN WILL BE USED |
| PURE SEQUENTIAL PREFETCH WILL BE PERFORMED |
| PAGE RANGE SCAN WILL NOT BE USED |
+------------------------------------------------------------------+
Explain report – index details
INDEX: SYSIBM.DSNKKX01 --------------------------------------------------------
STATSTIME: 2013-02-16-15.02.47.358366
CREATED : 0001-01-01-00.00.00.000000 ALTERED: 2003-09-21-23.30.17.962937
FULL KEY CARD: 1093,PAGES : 20,LEVELS: 2,CLUSTRING: Y
1"ST KEY CARD: 1,SPACE : 196.608K,UNIQUE: YES,CLUSTERED: N
INDEX TYPE : 2,PGSIZE: 4096,BFPOOL: BP0,DB.NAME : DSNDB06
CLUSTERRATIO : 83.5316%,ERRULE: NO,CLRULE: NO,IXSPACE : DSNKKX01
MAX.PIECESIZE: 0,COPY : NO,COPYLRSN: X'000000000000'
Explain report – key column details
KEY KEY KEY
NO. COLUMN NAME COL.TYPE LNG NULL CARD. ORDER LOW2KEY HIGH2KEY USED
COL.STAT LENGTH2 TYPESCHEMA TYPENAME
CREATED
--- ----------------- -------- --- ---- ----- ----- ---------- ---------- ----
1 LOCATION VARCHAR 128 NO 1 ASC. X'40404040 X'40404040 <===
0 SYSIBM VARCHAR
0001-01-01-00.00.00.000000
2 COLLID VARCHAR 128 NO 60 ASC. X'4144424C X'55545255
0 SYSIBM VARCHAR
0001-01-01-00.00.00.000000
3 NAME VARCHAR 128 NO 504 ASC. X'41444232 X'535F5550
0 SYSIBM VARCHAR
0001-01-01-00.00.00.000000
4 VERSION VARCHAR 122 NO 87 ASC. X'31404040 X'56385231
0 SYSIBM VARCHAR
0001-01-01-00.00.00.000000
Explain report – table details
TABLE: SYSIBM.SYSPACKAGE ------------------------------------------------------
STATSTIME: 2013-02-16-15.02.47.358366, TB TYPE : TABLE
CREATED : 1985-04-01-00.00.00.000000, ALTERED : 2003-09-21-23.30.17.962937
ROWS : 1093, COLUMNS : 47, ROWLENGTH: 3894, EDIT PROC.:
% PAGES : 90, DBASE ID: 6, AUDITING : NONE, VALIDPROC.:
ACT.PAGES: 65, TABLE ID: 128, STATUS : COMPX, TABCREATOR: SYSIBM
TAB.STAT.: , ENC.SCHEME: UNICODE
Explain report – table space details
TABLESPACE: DSNDB06.SYSPKAGE --------------------------------------------------
NAME : SYSPKAGE , DATABASE : DSNDB06
CREATOR : SYSIBM , CREATED BY: SYSIBM
CREATED : 0001-01-01-00.00.00.000000, ALTERED : 2003-09-21-23.30.17.962937
STATSTIME : 2013-02-16-15.02.47.358366,
ACTIVE PGS: 8640, DBASE ID : 6, TS STATUS : A, TS TYPE : NORMAL
PAGE SIZE : 4KB, OBJ ID : 120, ERASERULE : NO, STORGROUP :
SPACE : 22.118M, PAGESET ID: 121, CLOSERULE : NO, BUF.POOL : BP0
DS SIZE : 0, OLD VERS : 2, LOCKPART : N/A, ENC.SCHEME: UNICODE
LOCKMAX : SYSTEM, CUR VERS : 2, LOCKRULE : ANY, SBCS CCSID: 367
TABLES/TS : 7, PARTITIONS: UNPAR, LOG : YES, DBCS CCSID: 1200
MAXROWS : 255, SEG SIZE : 4, IMPLICIT : NO, MIX. CCSID: 1208
AVG ROWLEN: 235,
Explain report – host variables details
HOST VAR. TYPE LENGTH IND. HOST VARIABLE NAME
--------------- ------ ---- ------------------------------------------------
VAR. CHARACTER 16 NO HV_LOC71_LOCATION
VAR. CHARACTER 18 NO HV_LOC71_COLLID
VAR. CHARACTER 8 NO HV_LOC71_NAME
VAR. CHARACTER 64 NO HV_LOC71_VERSION
Explain report – language and compile details
===============================================================================
LOCATION : PMODB2A
COLLECTION ID: KO2EX520
PACKAGE ID : DGO@TPG3
VERSION ID : OMPE_FINAL
CONSIST.TOKEN: X'18C8F8D602A50FF6'
PDSNAME : D01OMPE.BASE51.TKO2DBRM
OWNER : PMDEV52 QUOTE : APOSTROPHE
CREATOR : XRK COMMA : PERIOD
BIND DATE : 2013-02-16 HOSTLANG : ASSEMBLER
BIND TIME : 15.24.35.310181 CHARSET : ALPHANUMERIC
CREATE DATE : 2013-02-16 MIXED : NO
CREATE TIME : 15.24.23.620690 DEC31 : NO
QUALIFIER : PMDEV52 DATA CURRENCY: INHIBIT BLOCKING
BASE SIZE : 5048 SQLERROR : NOPACKAGE
AVERAGE SIZE : 49764 SOURCE : DBRM
SYSENTRIES : 0 PRECOMP. DATE: 2011-07-15
SQL STATEMENT: 17 PRECOMP. TIME: 13.20.55.105599
VALIDATE : BIND VALID : YES
ISOLATION : UNCOMMITTED READ OPERATIVE : YES
RELEASE : CHECK PLAN REOPTIMIZAT. : NO
DEGREE : 1 DEFERPREPARE : INHERITED FROM PLAN
KEEP DYNAMIC : DRDA DDF PROTOCOL : INHERITED FROM PLAN
TYPE OF PACK.: BIND PACKAGE OPT_HINT_ID :
FNCT.RESOLVED: 2013-02-16-15.24.35.298423
....5....0....5....0....5....0....5....0....5....0....5....0....5..
PATH:
===============================================================================
Explain report – statement details (repeated for second statement step)
PACKAGE LOCATION :PMODB2A
PACKAGE COLLECTION:KO2EX520
PACKAGE ID :DGO@TPG3
PACKAGE VERSION ID:OMPE_FINAL
STATEMENT NUMBER : 1011071
SQL STATEMENT READ FROM SYSIBM.SYSPACKSTMT:
DECLARE C_DGOYTPG_71 CURSOR WITH HOLD FOR
SELECT LOCATION, COLLID, NAME, CONTOKEN, OWNER, CREATOR, TIMESTAMP, BINDTIME,
QUALIFIER, PKSIZE, AVGSIZE, SYSENTRIES, VALID, OPERATIVE, VALIDATE,
ISOLATION, RELEASE, EXPLAIN, QUOTE, COMMA, HOSTLANG, CHARSET, MIXED, DEC31,
DEFERPREP, SQLERROR, REMOTE, PCTIMESTAMP, IBMREQD, VERSION, PDSNAME, DEGREE,
GROUP_MEMBER, DYNAMICRULES, REOPTVAR, DEFERPREPARE, KEEPDYNAMIC, PATHSCHEMAS,
TYPE, DBPROTOCOL, FUNCTIONTS, OPTHINT, ENCODING_CCSID, IMMEDWRITE, RELBOUND,
CATENCODE, REMARKS
FROM DGO_SYSPACKAGE
WHERE LOCATION LIKE :HV_LOC71_LOCATION AND COLLID LIKE :HV_LOC71_COLLID AND
NAME LIKE :HV_LOC71_NAME AND VERSION LIKE :HV_LOC71_VERSION
ORDER BY LOCATION, COLLID, NAME, PCTIMESTAMP DESC QUERYNO 001011071
STATUS : COMPILED-BOUND USING DEFAULTS FOR INPUT VARIABLES
ISOLATION: UNCOMMITTED READ / FROM SYSPACKAGE
Explain report – PLAN_TABLE (for second statement step)
EXPLAIN TABLE: PMDEV52.PLAN_TABLE ---------------------------------------------
BIND_TIME : 2013-02-16-15.24.35.310181
TIMESTAMP : 2013-02-16-15:24:35.34
PROGNAME (Package): DGO@TPG3 , COLLID : KO2EX520
VERSION : OMPE_FINAL , APPLNAME (Plan) : N/P
QUERYNO : 1011071 , SECTNOI : N/A
QBLOCKNO : 1 , PARENT_QBLOCKNO : 0
PLANNO : 2 , PARENT_PLANNO : 0
MIXOPSEQ : 0 , QBLOCK_TYPE : SELECT
TNAME (Table) : N/P , CREATOR (Table) : N/P
TABNO (Table) : 0 , CORRELATION_NAME : N/P
TABLE_TYPE : N/P , CTEREF : 0
TABLE_ENCODE : BLANK , TABLE_MCCSID : 0
TABLE_SCCSID : 0 , TABLE_DCCSID : 0
TSLOCKMODE : BLANK , GROUP_MEMBER : SDA2
ACCESSTYPE : BLANK , PRIMARY_ACCESSTYPE: BLANK
ACCESSNAME (Index): N/P , ACCESSCREATOR : N/P
MATCHCOLS : 0 , INDEXONLY : NO
METHOD (Join) : 3 - Sort needed , JOIN_DEGREE : 0
JOIN_TYPE : b - INNER or NO , MERGN : N/A
MERGE_JOIN_COLS : 0 , MERGC : N/A
PREFETCH : BLANK , 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: YES
JOIN_PGROUP_ID : 0 , SORTN_GROUPBY: NO , SORTC_GROUPBY: NO
REMARKS : N/P , STMTTOKEN : N/P
PARALLELISM_MODE : BLANK , BIND_EXPLAIN_ONLY : N/A
EXPANSION_REASON : N/A
Explain report – access path (for second statement step)
THE ACCESS PATH CHOSEN BY DB2 AT 15:24:35.3 ON 2013-02-16
+------------------------------------------------------------------+
| ADDITIONAL SORT FOR ORDER BY |
| PAGE RANGE SCAN WILL NOT BE USED |
+------------------------------------------------------------------+
Explain summary report
REPORT ON: 02/16/13 15:26:53 OMEGAMON XE DB2 PE (V5.3) PAGE : SUMMARY
EXPLAIN SUMMARY REPORT USER AUTHID: XRK
THE FOLLOWING 1 EXPLAIN REQUESTS WERE PROCESSED: PAGE NO
1: SDA2 PACKAGE : KO2EX520 .DGO@TPG3
DETAIL REPORT REQUESTED
DBRM/PACK STMT TYP
FOR A LIST OF VERSIONS, PLEASE REFER TO PAGE 1-1
DGO@TPG3 1011020 P MATCHING INDEX SCAN(2/4)-DATA PAGES 1-3
DGO@TPG3 1011021 P S TABLE SPACE SCAN-NO INDEX WILL BE USED 1-7
DGO@TPG3 1011021 P ADDITIONAL SORT FOR ORDER BY 1-10
DGO@TPG3 1011070 P MATCHING INDEX SCAN(3/4)-DATA PAGES 1-12
DGO@TPG3 1011071 P S MATCHING INDEX SCAN(1/4)-DATA PAGES 1-16
DGO@TPG3 1011071 P ADDITIONAL SORT FOR ORDER BY 1-20