You can create EXPLAIN report for an existing package thru File-aid. The Demo is below.
Select Option 4 from the file-aid main menu
File-AID for DB2 - 6.2 ------ Primary Option Menu -----------------------------
OPTION ===> 4
More: +
0 DEFAULTS and USER PARMS USERID - TXUXXXX
1 BROWSE TIME - 20:28
2 EDIT TERMINAL - 3278
3 UTILITIES PF KEYS - 24
3.1 - Object Administration CCSID - 00037
3.2 - Create, Model, Drop, Alter DB2 Objects DB2 SSID ===> DBXT
3.3 - Copy Rows Between Tables
3.4 - Object List Processing
3.5 - SQL Command Manager
3.6 - Display, Grant or Revoke DB2 Privileges
3.7 - Extract and Load Tables
3.8 - DBA-XPERT Column Impact Analysis
3.9 - Batch Execute SQL with Auto-COMMIT Processing
4 SQL ANALYSIS - SQL Development and Analysis
5 PRINT - Print table Data or Audit Trail
6 RELATIONSHIPS - Relationship Facilitator
7 DATA DISGUISE - Define Disguise Criteria and Disguise Extract files
R File-AID/RDX - Transfer to File-AID/Related Data XPERT
XT XPEDITER - Transfer to XPEDITER
Select Option 1
File-AID for DB2 ----------- SQL Analysis and Development Menu ---- SSID: DBXT
OPTION ===> 1
0 Create or Maintain Explain Table(s)
1 SQL Plan Analysis
2 Review or Print DB2 Explain Reports
3 ISPF/PDF SQL Source Development and Analysis
Select package and fill in the highlighted fields. That is the fill in program name, select batch processing mode and qualifier of the package
File-AID for DB2 ------- SQL Plan Analysis Specification ---------------------
COMMAND ===> Scroll ===> CSR
SSID: DBXT
Select one Object Type to be Analyzed:
_ Plan
_ DBRM
_ Collection ID
S Package
Specify Selection Criteria:
Object Name ===> TESTPGM (* or pattern for selection list)
Use Filters ===> YES (Yes or No, Not valid for Collection ID)
Specify Processing Option:
Processing Mode ===> 2 (1 = Online, 2 = Batch)
Host variable Substitution ===> 1 (1 = Literal, 2 = Parameter Marker)
Specify Object Qualifier Options:
Qualify by ===> 2 (1 = CURRENT SQLID, 2 = Bind Qualifier
3 = Default Qualifier , 4 = CURRENT SCHEMA)
Default Qualifier ===> MYDBID
In the below screen, put “*” to see all the packages of the selected program
File-AID for DB2 ------- SQL Plan Analysis Specification ---------------------
COMMAND ===> Scroll ===> CSR
SSID: DBXT
Select one Object Type to be Esssssss Filters Used to Select PACKAGE sssssssN
_ Plan e COMMAND ===> Scroll ===> CSR e
_ DBRM e e
_ Collection ID e Object Name: TESTPGM e
S Package e Creator ===> * (* or pattern) e
e e
Specify Selection Criteria: e e
Object Name ===> TESTPGM e e
Use Filters ===> YES e e
DssssssssssssssssssssssssssssssssssssssssssssssM
Specify Processing Option:
Processing Mode ===> 2 (1 = Online, 2 = Batch)
Host variable Substitution ===> 1 (1 = Literal, 2 = Parameter Marker)
Specify Object Qualifier Options:
Qualify by ===> 2 (1 = CURRENT SQLID, 2 = Bind Qualifier
3 = Default Qualifier , 4 = CURRENT SCHEMA)
Default Qualifier ===> MYDBID
Type “X” against the desired package
File-AID for DB2 ------------ Package Selection ----------- Row 61 to 63 of 65
COMMAND ===> SCROLL ===> CSR
SSID: DBXT
Line Commands:
S - Select Package for more detail
X - Explain entire Package
Cmd
X Package Name : TESTPGM
Bind Timestamp : 2011-12-14-00.18.57.728515
Collection ID : CTESTOTD
Version : 2011-09-02-13.52.39.549462
_ Package Name : TESTPGM
Bind Timestamp : 2011-12-14-00.17.30.499010
Collection ID : CTESTOTD
Version : 2011-11-23-03.14.50.057038
_ Package Name : TESTPGM
Bind Timestamp : 2011-10-15-15.15.36.566333
Collection ID : CTESTOUD
Version : 2011-09-02-13.52.39.549462
Select “S” for access path analysis in the below screen.
File-AID for DB2 ------------ Package Selection ----------- Row 61 to 63 of 65
C Essssssssssssssssssss Batch Plan Analysis Reports ssssssssssssssssssssN CSR
e e DBXT
e Batch SQL Analysis will always produce the following reports: e
L e - Batch SQL Analysis Summary e
e - Explain Detail Summary (Includes Index Information) e
e e
e Use 'S' to select additional reports e
C e S Access Path Analysis e
e _ Predicate Analysis Summary e
e _ Predicate Analysis Detail (Rule descriptions) e
e e
DsssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssM
_ Package Name : TESTPGM
Bind Timestamp : 2011-12-14-00.17.30.499010
Collection ID : CTESTOTD
Version : 2011-11-23-03.14.50.057038
_ Package Name : TESTPGM
Bind Timestamp : 2011-10-15-15.15.36.566333
Collection ID : CTESTOUD
Version : 2011-09-02-13.52.39.549462
Type a new dataset name in the below screen, so the report can be stored in that dataset
File-AID for DB2 ----- SQL Analysis Batch Output Specification ----------------
COMMAND ===>
Select Printer Destination or Dataset then press ENTER
_ TO PRINTER
Printer Destination ===>
Sysout Class ===>
S TO DATASET
Report Dataset Name ===> 'TXUXXXX.TSTS.XRPT'
Volume Serial ===>
Submit the below job. Once the job is completed, you can see the report for access path.
EDIT ---- TXUXXXX.ISP01743.SPFTEMP1.CNTL --------------------- Columns 001 072
COMMAND ===> SCROLL ===> CSR
****** ***************************** Top of Data ******************************
000001 //SS301 JOB (1013DBDC),'I',
000002 // MSGCLASS=X,CLASS=3,
000003 // NOTIFY=SS301,
000004 // MSGLEVEL=(1,1)
000005 //*-------------------------------------------------------------------
000006 //COPYTL EXEC PGM=IEBCOPY
000007 //SYSPRINT DD SYSOUT=*
000008 //SYSOUT DD SYSOUT=*
000009 //INDD1 DD DSN= FADB2.DBXT.V6R2M0.ISPTLIB,
000010 // DISP=SHR
000011 //INDD2 DD DSN=SYS1.SISPTENU,
000012 // DISP=SHR
000013 //*
000014 //OUTDD DD DSN=&TBLIBS,
000015 // DISP=(NEW,PASS,DELETE),
000016 // SPACE=(TRK,(30,15,15),RLSE),
000017 // DSNTYPE=LIBRARY,LRECL=80,
000018 // UNIT=SYSDA
000019 //OUTDD2 DD DSN=&CEELIB,
000020 // DISP=(NEW,PASS,DELETE),
000021 // SPACE=(TRK,(30,15,15),RLSE),
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.