SQLEASE - Enhanced Explain Where is JCL for Batch?
Article ID: 275149
Updated On:
Products
Issue/Introduction
SQLEASE has a menu item 3-EE -Enhanced EXPLAIN that is used for running explain using MODE online or Batch.
Option ===> ( S - Execute X - Explain E - Edit P - Profile )
DB2 SSID ===> ssid Version: 121M500 Mode ===> O ONLINE
ONLINE works fine and Batch creates Batch Statements in output Dataset defined but no JOB CARD/JCL etc. to run those statements?
How do I get that JCL to Run Batch EXPLAIN?
Environment
Release : 20.0
Resolution
For Batch , change the Mode ===> O ONLINE to B(BATCH) in the below panel
SQEXPL6 ---------- SQL-EASE Enhanced Explain ---------- yyyy/mm/dd hh:mm
COMMAND ===> SCROLL ===> CSR
LINE 1 OF 3
Option ===> ( X - Explain S - Execute E - Edit P - Profile )
DB2 SSID ===> ssid Version: 121M500 Mode ===> B
---------------------------------------------------------------------- authid1
CONTROL PARAMETERS:
Update Report Options ===> N Primary Authid ===> authid1
Update Catalog Statistics ===> N Secondary Authid ===> authid1
Commit Catalog Updates ===> R SQL Qualifier Id ===> authid1 >
PLAN_TABLE Option ===> C Optimization Hint ===> >
Parallelism Degree ===> 1 Path Schemas ===> N
Isolation ===> CS Process Views ===> Y
CURRENT SQL STATEMENT:
SELECT *
FROM authid1.TBEMP
;
Then on the next screen.....enter the dataset(member) to receive the explain cards.
---------------- Batch Specification --------------- yyyy/mm/dd hh:mm
COMMAND ===>
Enter Specifications for your Batch Data Set.
ISPF LIBRARY:
PROJECT ===>
GROUP ===>
TYPE ===>
MEMBER ===> (Blank or pattern for member selection list)
OTHER PARTITIONED OR SEQUENTIAL DATA SET:
DATA SET NAME ===> 'your.hlq.cntl(explain)'
VOLUME SERIAL ===> (If not cataloged)
DATA SET OPTIONS:
IF PARTITIONED, REPLACE EXISTING MEMBERS ===> YES (YES or NO)
ENTER DATA SET DISPOSITION ===> OLD (OLD or SHR)
IF SEQUENTIAL, ENTER DATA SET DISPOSITION ===> MOD (OLD,SHR, or MOD)
Then enter and this message is displayed: PP156 PP156I: Your current Mode is set to BATCH.
Enter option 'X' to generate the explain cards: Option ===> x
This message is seen : PP035 PP035I: The Batch Processor input has been written to the Batch Mode
dataset.
Change the mode back to online: Mode ===> O
Then this screen is displayed:
--------------- Online Specification --------------- yyyy/mm/dd hh:mm
COMMAND ===>
OPTION ===>
1) Enter ONLINE Mode, and CLOSE and UNALLOCATE batch dataset.
2) Enter ONLINE Mode, leave batch dataset ALLOCATED and OPEN.
3) Enter ONLINE Mode, and CLOSE and UNALLOCATE batch dataset
and call the Batch Processor.
-------------------------------------------------------------------------------
CURRENT ALLOCATION STATUS:
DDNAME : SYS00134
DATASET NAME : 'your.hlq.CNTL(explain)'
DISPOSITION : OLD
Then select menu item 3 above...........
The Batch processor screen is displayed:
PTBP 20.0.10 --------- Batch Processor Interface --------- yyyy/mm/dd hh:mm
COMMAND ===>
EXECUTION SPECIFICATIONS:
EXECUTION MODE ===> ( B - Batch mode, O - Online mode )
DB2 SSID ===> ssid
BATCH PROCESSOR INPUT DATA SET:
DATA SET NAME ===> 'your.hlq.CNTL'
MEMBER ===> EXPLAIN ( Blank or pattern for member selection list )
VOLUME SERIAL ===> ( If not cataloged )
EDIT DATA SET ===> N ( Y - Yes, N - No )
PROCESSING OPTIONS:
UNLOAD MODE ===> A SQL FORMAT ===> S
RESTART ===> N CONTINUE IF:
TERM UTILITY ===> N -WARNING ===> Y
RETRY LIMIT ===> 10 -SQL ERROR ===> N
WRAP/TRUNCATE ===> T -BIND ERROR ===> N
RE-EXEC CHECK ===> N -LOAD DISCARDS ===> N
AUDIT OPTIONS
AUDIT List Dest ===> P ( P - print; D - data set; N - No audit; U - Update)
Print Setting - Class X Dest A
On the above screen the explain can be submitted. The generated explain cards can also be edited.
To edit the explain cards change N to Y here : EDIT DATA SET ===> Y
Review the explain cards and if ok to submit then PF3 back.
PTBP 20.0.10 --------- Batch Processor Interface --------- yyyy/mm/dd hh:mm
COMMAND ===>
EXECUTION SPECIFICATIONS:
EXECUTION MODE ===> ( B - Batch mode, O - Online mode )
DB2 SSID ===> ssid
BATCH PROCESSOR INPUT DATA SET:
DATA SET NAME ===> 'your.hlq.CNTL'
MEMBER ===> EXPLAIN ( Blank or pattern for member selection list )
VOLUME SERIAL ===> ( If not cataloged )
EDIT DATA SET ===> N ( Y - Yes, N - No )
Then enter "B" in the EXECUTION MODE ===> B
Then on the next screen, Batch JCL Specification, submit as normal when using Batch Processor. Using "P" on the DESTINATION ===> P field on this screen will allow for review/edit/save of the JCL before submission using the usual Command ===> sub'
Your sysout will contain the explain parms submitted and the "PPA/SQL EASE ENHANCED EXPLAIN" report varying depending on the explain reports chosen on the SQL-EASE Enhanced Explain screen above.....the Update Report Options ===> field using "Y" will transfer to a SQL-EASE Enhanced Explain Report Options screen.
Note that the explain cards generated above in your.hlq.CNTL(EXPLAIN) are able to be submitted again at any time without the need to enter into SQL-EASE by simply entering BATCH PROCESSOR from any screen using the BP command or the menu item C DB2 Command Processor on the main menu. An experienced user is able to then make manual alterations to the explain cards quickly as required.
Additional Information
Feedback
