Explaining Objects in Batch
The SQL Plan Analysis can be processed in Batch mode if you specify Batch processing on the SQL Plan Analysis Specification screen (SQL Plan Analysis Specification Screen). (Enter 2 in the Processing Mode field.) The Batch Plan Analysis Reports window (see the following figure) displays when an X is entered in the CMD field on the SQL Plan Selection screen (SQL Plan Selection). You can select more than one plan.
When you press Enter, the SQL Analysis Batch Output Specification screen displays (SQL Analysis Batch Output Specification Screen).
Batch Plan Analysis Reports Window
Enter an S next to the reports you want generated.
Access Path Analysis
See Access Path Analysis for more information.
Predicate Analysis Summary & Detail (Rule Descriptions)
See Predicate-Analysis for more information.
The following is a list of the reports with their abbreviated names:
- ES = Explain Summary Report
- CI = Statement Tables and Indexes Report
- AP = Access Path Analysis Report
- PA = Predicate Analysis Summary Report
- PD = Predicate Analysis Detail Report
The abbreviated name will be displayed at the beginning of the report. Once a report is generated, use the abbreviated name with the FIND command to search for a specific report.
Batch SQL Analysis Summary Report
The Batch SQL Analysis Summary Report is always generated for objects processed in batch. See the following figure for a sample of this report.
Sample of Batch SQL Analysis Summary Report
In the Batch SQL Analysis Summary Report, each SQL statement is assigned a unique identification (ID) number. The ID number displays on each line associated with that statement number. The ID number makes it easier to search for information, to know which lines of the report are associated with each SQL statement, and to know where you are within the report.
Use the FIND command to navigate from report to report for a particular SQL statement.
SQL Analysis Batch Output Specification
On the SQL Analysis Batch Output Specification screen you can route the selected reports to a printer or a file. You cannot elect to send the report to both destinations. Use the S line command to select the destination and enter the required fields for the appropriate destination. Set the default print options and job statement by selecting option 0, Defaults and User Parameters, from the Primary Option Menu. See Print-Options and Option 3, Batch Options .
SQL Analysis Batch Output Specification Screen
After you enter the appropriate information, File-AID for Db2 generates a temporary data set containing the JCL. Use the SUBMIT primary command to submit the job. If you want to save the data set, use the ISPF/PDF CREATE or COPY command before exiting the screen. Once submitted, the SQL Analysis report is routed to the selected destination.
Explaining Objects in Batch via a Source Listing File
If you created a source file listing with Code Debug or Abend-AID, you can explain the SQL in the file. Your program must be processed with the postprocessor.
Create the JCL below and provide the appropriate data set names and parameters. Typically, the explain will be the last step in a job that performs a compile, link-edit, and bind.
JCL to Explain via SLS (1 of 2)
JCL to Explain via SLS (2 of 2)
DD Statements
STEPLIB
Step library
ISPPLIB
Panel library
ISPMLIB
Message library
ISPSLIB
Skeleton library
ISPTLIB
Table library
SLSF00 n
Where n is a value between 1 and 8. A maximum of eight source list files can be specified.
ISPPROF
ISPF profile data set definition for the batch TSO session
ISPCTL1
Temporary data set used by ISPF
SYSTSPRT
Required DD statement
SYSTERM
Required DD statement
SYSPRINT
Required DD statement
SYSUDUMP
Required DD statement
F2SYSOUT
Printer information. Required if print-option is Y.
SYSTSIN
Required DD statement
JCL Input
PGM
Specify the File-AID for Db2 program F2ANLYZ
NEWAPPL
Specify the File-AID for Db2 application identification
Parameters
Subsystem
Subsystem name
Plan Name
Plan name of File-AID for Db2
Creator
Userid of the creator
Qualifier
Qualifier used for unqualified tables
SOURCE
Required keyword. Specifies that the SQL is in the source listing file.
Member
Specify the member in the source listing file containing the program to be explained.
LISTEND
Required keyword
ALL
Required keyword
LISTEND
Required keyword
DSN
Output dataset name. Specify a dataset set name in single and double quotes.
Volume
Volume of the dataset name specified for the DSN parameter. Enclose the volume in double quotes. If writing to a printer, specify double quotes.
Print-option
Y | Writes the report to the printer specified in the DD F2SYSOUT |
N | Writes the report to the data set specified in the DSN parameter |
ALL
Required keyword. Performs an Explain for all statements.
Analysis Options
Specifies which report you want printed. The available reports are listed below. Enter a Y or N for each report. Do not separate with blanks.
- Access Path Analysis
- Predicate Analysis Summary & Predicate Analysis Detail (Rule descriptions)
Rule Dataset Name
Identifies the name of the data set containing the rules for predicate analysis.
Object Qualifier Option
Specify which qualifier option to use (see also Qualify By).
1 | Current SQLID |
2 | Bind Qualifier |
3 | Default Qualifier |
Trace
Specify which logging option to use
1 | Write to ISPF log. |
2 | Write to SYSPRINT. |
3 | Write to ISPF log and SYSPRINT. |
4 | No logging. |
Plan Table Creator
Specify the Creator ID for the plan table you want to use.
Sample Explain JCL
The following JCL will perform an explain on the SQL in member SQLDEMO. The output will be written to ’TSOID01.XP.OUTPUT’. The following reports will be created: Access Path Analysis and Predicate Analysis Summary & Predicate Analysis Detail. The rule data set hlq. SXVJSENU will be used as well as the current SQLID for the object qualifier, logging to SYSPRINT, and the plan table of creator TSOID01.
Sample JCL to Explain with SLS
Related topics