Display Explain data for a package using RC/Query
Article ID: 248593
Updated On:
Products
RC/Query for DB2 for z/OS
Issue/Introduction
Existing Explain data stored on a users own PLAN_TABLE is able to be displayed by RC/Query for Db2 for z/OS (RCQ).
Resolution
Starting with a Package List report , enter the "E" line command as below:
RQPKL 20.0 -------- RC/Q Application Package List -------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> PK Option ===> L Where => N
Package ===> TST% > Owner ===> authid1 >
Collection ===> TSTPKG > Grantor ===> * >
Version ===> * >
Loc: LOCAL ---------- SSID: ssid ----------authid1 - LINE 1 OF 1 >
CMD NAME OWNER COLLECTION LOCATION V O E BINDDATE
E_______ TSTPROG authid1 TSTPKG Y Y Y 2021/02/17
******************************* BOTTOM OF DATA ********************************
For this query this is the SQL that is executed. Note that the data needs to be on the
PLAN TABLE already, the package having been explained.
SELECT A.QUERYNO , A.QBLOCKNO , A.PROGNAME , A.PLANNO , A.METHOD , A.CREATOR
, A.TNAME , A.TABNO , A.ACCESSTYPE , A.MATCHCOLS , A.ACCESSCREATOR ,
A.ACCESSNAME , A.INDEXONLY , A.SORTN_UNIQ , A.SORTN_JOIN ,
A.SORTN_ORDERBY , A.SORTN_GROUPBY , A.SORTC_UNIQ , A.SORTC_JOIN ,
A.SORTC_ORDERBY , A.SORTC_GROUPBY , A.TSLOCKMODE , A.TIMESTAMP ,
A.REMARKS , A.PREFETCH , A.COLUMN_FN_EVAL , A.MIXOPSEQ , A.VERSION ,
A.COLLID , A.ACCESS_DEGREE , A.ACCESS_PGROUP_ID , A.JOIN_DEGREE ,
A.JOIN_PGROUP_ID , A.SORTC_PGROUP_ID , A.SORTN_PGROUP_ID ,
A.PARALLELISM_MODE , A.MERGE_JOIN_COLS , A.CORRELATION_NAME ,
A.PAGE_RANGE , A.JOIN_TYPE , A.GROUP_MEMBER , A.WHEN_OPTIMIZE ,
A.QBLOCK_TYPE , A.BIND_TIME
FROM authid1.PLAN_TABLE A
WHERE A.PROGNAME = 'TSTPROG'
AND A.COLLID ¬= ' '
AND A.COLLID = 'TSTPKG'
AND UPPER ( A.VERSION ) = 'TSTPKG'
ORDER BY A.PROGNAME , A.COLLID , A.VERSION , A.TIMESTAMP DESC FOR FETCH ONLY
WITH UR
This is the report that is displayed:
RQPKE 20.0 ---------- RC/Q Package Explain Inq ----------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> PK Option ===> E Where => N
Package ===> TSTPROG > Owner ===> authid1 >
Collection ===> TSTPKG > Grantor ===> * >
Version ===> TSTPKG >
Loc: LOCAL ---------- SSID: ssid LVL: 01 -authid1 - FRAME 1 OF 2
********************************* TOP OF DATA *********************************
CMD: ________
PACKAGE: TSTPROG
:
COLLECTION: TSTPKG
:
VERSION: TSTPKG
CREATOR: authid1
STATEMENT: 135 SELECT NUMBER: 1 STEP: 1 TABNO: 1
STATEMENT TYPE: SELECT
DATE: 2021/02/17 TIME: 01:07:09
TABLESPACE LOCK MODE: INTENT SHARE
METHOD: ACCESS FIRST TABLE CREATOR: authid1 TABLE: tbname
ACCESS: INDEX ONLY MTCH COLS: 2 CRTR: authid1 NDX: ixname
SORTS FOR NEW/COMPOSITE TABLE
REMOVE DUPLICATES: N / N ORDER BY CLAUSE: N / N
MERGE SCAN JOIN: N / N GROUP BY CLAUSE: N / N
PREFETCH: NO PREFETCH / UNKNOWN COL FUNC EVAL: AT DATA MANIPULATION / UNKNOWN
WHEN OPTIMIZE: DETERMINED AT BIND TIME
BIND DATE: BIND TIME:
REMARKS:
--------------- END OF DATA ITEM ---------------
second page using PF8:
RQPKE 20.0 ---------- RC/Q Package Explain Inq ----------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DB2 Object ===> PK Option ===> E Where => N
Package ===> TSTPROG > Owner ===> authid1 >
Collection ===> TSTPKG > Grantor ===> * >
Version ===> TSTPKG >
Loc: LOCAL ---------- SSID: ssid LVL: 01 -authid1 - FRAME 2 OF 2
CMD: ________
PACKAGE: TSTPROG
:
COLLECTION: TSTPKG
:
VERSION: TSTPKG
CREATOR: authid1
STATEMENT: 135 SELECT NUMBER: 1 STEP: 2 TABNO: 2
STATEMENT TYPE: SELECT
DATE: 2021/02/17 TIME: 01:07:09
TABLESPACE LOCK MODE: INTENT SHARE
METHOD: NESTED LOOP JOIN CREATOR: authid1 TABLE: tbname
ACCESS: INDEX ONLY MTCH COLS: 2 CRTR: authid1 NDX: ixname
SORTS FOR NEW/COMPOSITE TABLE
REMOVE DUPLICATES: N / N ORDER BY CLAUSE: N / N
MERGE SCAN JOIN: N / N GROUP BY CLAUSE: N / N
PREFETCH: NO PREFETCH / UNKNOWN COL FUNC EVAL: AT DATA MANIPULATION / UNKNOWN
WHEN OPTIMIZE: DETERMINED AT BIND TIME
BIND DATE: BIND TIME:
REMARKS:
******************************** BOTTOM OF DATA *******************************
If an explain report is needed and an explain has not been carried out then one option
apart from carrying out an explain is to use a PLAN ANALYZER for DB2 for Z/OS FUTURE EXPLAIN report.
An explain report can also be generated in Batch using the "BR" (batch reporting) command.
One of the reports is an Explain Report:
RQBRS 20.0 --------- RC/Q Batch Report Selection --------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
Enter Report Selection Criteria (spaces for EQF Selection suppress EQFs)
Rpt. Selection: DB2 Object => PK Option => *
EQF Selection: EQF Name => Creator => Where => N
Loc: LOCAL ---------- SSID: ssid LVL: 01 -authid1 - >
ENTER "S" TO SELECT BATCH REPORTS.
S REPORT/QUERY DESCRIPTION ITEM NAME CREATOR
_ PACKAGE (PK) INFORMATION ABOUT PACKAGES __________________ ________
_ CONNECTN (CN) CONNECTIONS ENABLED FOR PACKS __________________ ________
_ DETAIL (D) DETAIL INFO. ABOUT PACKAGES __________________ ________
_ DEPNDNCY (DP) OBJECTS REFERENCED BY PACKAGES __________________ ________
s EXPLAIN (E) EXPLAIN - DBRM STMTS IN PACKS TSTPROG AUTHID1_
_ LIST (L) LIST AVAILABLE PACKAGES __________________ ________
_ PLAN (P ) PLANS WITHIN PACKAGE __________________ ________
_ STMTS (ST) SQL STMTS WITHIN PACKAGE __________________ ________
_ USERAUTH (UA) USERS AND AUTHS HELD ON PACKS __________________ ________
******************************* BOTTOM OF DATA ********************************
When submitted it looks like this:
FOR REQ: OBJECT=> PK OPTION=> E .
ITEM=> TSTPROG CREATOR=> authid1 WHERE=> N .
QUALIFIER=> * GRANTOR=> * .
VERSION=> * .
DB2 SSID=> ssid LOCATION=> LOCAL .
RQE00015: NORMAL COMPLETION .
RQE01000: INPUT FILE: SYSIN, AT END; EXECUTION COMPLETED .
yyyy/mm/dd hh:mm SSID:ssid --------- RC/Q PACKAGE EXPLAIN INQ ----------
LOCATION: LOCAL
CMD:
PACKAGE: TSTPROG
:
COLLECTION: TSTPKG
:
VERSION: TSTPKG
CREATOR: authid1
STATEMENT: 135 SELECT NUMBER: 1 STEP: 1 TABNO: 1
STATEMENT TYPE: SELECT
DATE: yyyy/mm/dd TIME: hh:mm:ss
TABLESPACE LOCK MODE: INTENT SHARE
METHOD: ACCESS FIRST TABLE CREATOR: authid1 TABLE: tbname
ACCESS: INDEX ONLY MTCH COLS: 2 CRTR: authid1 NDX: ixname
SORTS FOR NEW/COMPOSITE TABLE
REMOVE DUPLICATES: N / N ORDER BY CLAUSE: N / N
MERGE SCAN JOIN: N / N GROUP BY CLAUSE: N / N
PREFETCH: NO PREFETCH / UNKNOWN COL FUNC EVAL: AT DATA MANIPULATION / UNKNOWN
WHEN OPTIMIZE: DETERMINED AT BIND TIME
BIND DATE: BIND TIME:
REMARKS:
yyyy/mm/dd hh:mm SSID:ssid SSID:ssid --------- RC/Q PACKAGE EXPLAIN INQ ----------
LOCATION: LOCAL
CMD:
PACKAGE: TSTPROG
:
COLLECTION: TSTPKG
:
VERSION: TSTPKG
CREATOR: authid1
STATEMENT: 135 SELECT NUMBER: 1 STEP: 2 TABNO: 2
STATEMENT TYPE: SELECT
DATE: yyyy/mm/dd TIME: hh:mm:ss
TABLESPACE LOCK MODE: INTENT SHARE
METHOD: NESTED LOOP JOIN CREATOR: authid1 TABLE: tbname
ACCESS: INDEX ONLY MTCH COLS: 2 CRTR: authid1 NDX: ixname
SORTS FOR NEW/COMPOSITE TABLE
REMOVE DUPLICATES: N / N ORDER BY CLAUSE: N / N
MERGE SCAN JOIN: N / N GROUP BY CLAUSE: N / N
PREFETCH: NO PREFETCH / UNKNOWN COL FUNC EVAL: AT DATA MANIPULATION / UNKNOWN
WHEN OPTIMIZE: DETERMINED AT BIND TIME
BIND DATE: BIND TIME:
REMARKS:
yyyy/mm/dd hh:mm SSID:ssid SSID:ssid --------- RC/Q PACKAGE EXPLAIN INQ ----------
LOCATION: LOCAL
CMD:
PACKAGE: TSTPROG
:
COLLECTION: TSTPKG
:
VERSION: TSTPKG
CREATOR: authid1
STATEMENT: 135 SELECT NUMBER: 1 STEP: 1 TABNO: 1
STATEMENT TYPE: SELECT
DATE: yyyy/mm/dd TIME: hh:mm:ss
TABLESPACE LOCK MODE: INTENT SHARE
METHOD: ACCESS FIRST TABLE CREATOR: authid1 TABLE: tbname
ACCESS: INDEX ONLY MTCH COLS: 2 CRTR: authid1 NDX: ixname
SORTS FOR NEW/COMPOSITE TABLE
REMOVE DUPLICATES: N / N ORDER BY CLAUSE: N / N
MERGE SCAN JOIN: N / N GROUP BY CLAUSE: N / N
PREFETCH: NO PREFETCH / UNKNOWN COL FUNC EVAL: AT DATA MANIPULATION / UNKNOWN
WHEN OPTIMIZE: DETERMINED AT BIND TIME
BIND DATE: BIND TIME:
REMARKS:
LOCATION: LOCAL
CMD:
PACKAGE: TSTPROG
:
COLLECTION: TSTPKG
:
VERSION: TSTPKG
CREATOR: authid1
STATEMENT: 135 SELECT NUMBER: 1 STEP: 2 TABNO: 2
STATEMENT TYPE: SELECT
DATE: yyyy/mm/dd TIME: hh:mm:ss
TABLESPACE LOCK MODE: INTENT SHARE
METHOD: NESTED LOOP JOIN CREATOR: authid1 TABLE: tbname
ACCESS: INDEX ONLY MTCH COLS: 2 CRTR: authid1 NDX: ixname
SORTS FOR NEW/COMPOSITE TABLE
REMOVE DUPLICATES: N / N ORDER BY CLAUSE: N / N
MERGE SCAN JOIN: N / N GROUP BY CLAUSE: N / N
PREFETCH: NO PREFETCH / UNKNOWN COL FUNC EVAL: AT DATA MANIPULATION / UNKNOWN
WHEN OPTIMIZE: DETERMINED AT BIND TIME
BIND DATE: BIND TIME:
REMARKS:
*** END OF QPRINT REPORT
Additional Information
Feedback
Yes
No
Powered by
