Plan Analyzer for DB2 for Z/OS : Can plan analyzer explain a single SQL statement?
Article ID: 63614
Updated On:
Products
Plan Analyzer for DB2 for z/OS
Issue/Introduction
How can a single SQL statement that is contained in a dataset be Explained using Plan Analyzer? This statement is not contained in a either a plan or a program.
Environment
Release: R20
Component: PPADB2
Resolution
This may be done in Plan Analyzer by changing to Expert Mode and using the Quick Explain.
In the PROFILE change Expert Mode to Y
--------------- PPA Profile Variables -------------- yyyy/mm/dd hh:mm
COMMAND ===>
General Execution Parameters:
Qualifier ON/OFF default ===> N (Y=ON or N=OFF)
Explode SQL ON/OFF default ===> Y (Y=Exploded or N=Shrunk)
Strategy List Mode default ===> L (L=List vers or N=Do not list vers)
Expert Mode default ===> Y (Y=Expert, N=Regular)
Display Batch Processor Input
Screen default ===> Y (Y or N)
Package version(s) default ===> A (A=All, L=Latest version)
Package versioning method ===> A (N=None, A=Auto, O=Creation)
Package replace version default ===> Y (Y or N)
Host Language default ===> COB (ASM, COB, PLI, C, or SPF)
Update Statistics Qualifier ===> SYSIBM
Process Remote Packages ===> Y (Y or N)
Floating Point Format ===> ENG (SCI, ENG, or MET)
Use New Report Layouts ===> Y (Y or N)
Use Stored Proc. for Explain ===> Y (Y or N)
Package Copies Display ===> N (Y=Show copies,N=Do not show copies)
Suppress Expl Prof Select panel ===> N (Y=Suppress, N=Do not suppress)
In the Quick Explain Expert Screen....
Specifying a type of S - SQL or F - File below in the "TY" column and "C" in the "C" column.
S - SQL will enable the entry of a freehand SQL statement. F - File will allow the specification of a file that contains the SQL statement.
Set the Target Ruleset SSID to a valid one below.
----------- PPA Quick Explain Data Editor ---------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
LINE 1 OF 1 >
Profile ===> @CURRDBA
----------------------------------------------------------------- authid1
----TARGET--- O
C TY SSID RULE SET P LOCATION SSID PLAN/COLLID PACKAGE STMT
C F ssid ________ _ LOCAL___________ DSN_ __________________ ________ ________
******************************* BOTTOM OF DATA ********************************
The next screen asks for the source file if using the "F" type.
------------- PPA Explain - File Source ------------ yyyy/mm/dd hh:mm
COMMAND ===>
---------------------------------------------------------------------- authid1
Member Selection List ===> Y (Y or N)
Enter Specifications for your File Source Dataset.
ISPF LIBRARY:
PROJECT ===> Edit dataset ===> N (Y or N)
GROUP ===>
TYPE ===>
MEMBER ===> (Blank, pattern or member name)
OTHER PARTITIONED OR SEQUENTIAL DATA SET:
DATA SET NAME ===> 'hlq.sql'
VOLUME SERIAL ===> (If not cataloged)
A member list is presented and the one required is selected with an "S"
-------- Plan Analyzer Member Selection List ------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
DATASET NAME ===> hlq.SQL
MEMBER MASK ===> TESTSQL (Use "%" or "*" to denote wildcard search)
-------------------------------------------------------------------------------
S MEMBER VV_MM CREATED CHANGED SIZE INIT MOD ID
s SQLTEST 01.16 19/02/13 19/03/12 21:59 1 5 1 authid1
******************************** BOTTOM OF DATA *******************************
This pds member contains this SQL:
SELECT CREATOR, NAME, TYPE, TSNAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'AUTHID1';
PF3 to return after member selected with an "S" and then use the EXPLAIN command as below.
----------- PPA Quick Explain Data Editor ---------- yyyy/mm/dd hh:mm
COMMAND ===> EXPLAIN SCROLL ===> CSR
LINE 1 OF 2 >
Profile ===> @CURRDBA
----------------------------------------------------------------- authid1
----TARGET--- O
C TY SSID RULE SET P LOCATION SSID PLAN/COLLID PACKAGE STMT
_ AK DSN_ ________ _ LOCAL___________ DSN_ __________________ ________ ________
_ F DSN_ ________ _ LOCAL DSN hlq.SQL>
******************************* BOTTOM OF DATA ********************************
Select the EXPLAIN options...set explain type to FUTURE. Also update the REPORTS
options to ensure the correct reports and detail level are selected.
-------- PPA Quick Explain - Explain Options ------- yyyy/mm/dd hh:mm
COMMAND ===>
---------------------------------------------------------------------- authid1
Database Options ===> Y Historical Database Options
Primary AUTHID ===> Secondary AUTHID ===>
Update SQL Qualifiers ===> Y (Override Schemas & SQL/View Qualifiers)
Rule Set SSID ===> ssid (Subsystem where Rule Sets are stored)
PLAN_TABLE Option ===> R
Explain Type ===> F Package Copy ===> C
Non-Catalog Isolation ===> CS Optimization Hint ===> >
Process Views (Y, N) ===> Y Parallelism Degree ===>
Target SSID ===> ssid Target Rule Set ===> @@RULDBA
Uppercase Output ===> N Access Path Filter ===> N
Reports ===> Y Search Conditions ===> N
Catalog Statistics ===> N Search Filters ===> N
Press enter to generate the explain cards.....NOTICE the SRCFILE card referencing the pds member selected above.
.CALL EXPLAIN
.DATA
RULESSID = (ssid)
ACM = (N,authid1)
STRATEGY = (ssid,,authid1)
PLANTAB = (ROLLBACK)
LINES = (55)
PROCDDF = (Y)
FLOATFMT = (ENG)
PROCVIEW = (Y)
EXPLTYPE = (FUTURE)
ISOLATE = (CS)
TARGET = (DT32(@@RULDBA))
DATABASE = (AUTO,@FUTRDBA,ssid)
REPORT = (ACCESS/SHORT,COST,DEPENDENCY/SHORT,PHYSRULE/SHORT,
PLANRULE/SHORT,PREDICATE,PREDRULE/SHORT,SQLRULE/SHORT,
SUMMARY,SUPPRPTS)
SRCFILE = (hlq.SQL(SQLTEST))
.ENDDATA
The report is produced according to the report options chosen by the user.
PPA 20.0.04 --------- Plan Analyzer Enhanced Explain ------------- Page 2
DATE yy/mm/dd Summary Report TIME hh:mm
EXPLAIN PAGE
COLLECTION MEMBER DATASET NAME STMT NUM TYPE SQLCODE NUMBER
------------------ -------- -------------- ---------- ---- ------- ------
,SQLTEST ,hlq.SQL , 1,SEL ,+0 -N/A-
Target DB2 SSID: ssid The Most Severe SQLCODE: +0
Source DB2 SSID: ssid
Plan :
Package:
Stmt :
Total Number of
Selects: 1 Updates: 0 Inserts: 0
Deletes: 0 Merges : 0
PPA 20.0.04 --------- Plan Analyzer Enhanced Explain ------------- Page 3
DATE yy/mm/dd Cost Report TIME hh:mm
Total Cost of CPU and I/O utilization. Most expensive SQL listed first.
TOTAL COST PAGE
COLLECTION MEMBER DATASET NAME STMT NUM TYPE (desc order) NUMBER
------------------ -------- -------------- ---------- ---- ------------ ------
,SQLTEST,hlq.SQL , 1,SEL 920.073 -N/A-
Make sure that you use a FUTURE explain option not CURRENT because an SQL statement on a dataset won't have any package stats on the PLAN_TABLE
and so the explain will have to issue a DB2 PREPARE to generate some information for the EXPLAIN report.
Additional Information
Feedback
Yes
No
Powered by
