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 Xpediter 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

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).

Trace

Specify which logging option to use

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