SQL Plan Analysis Specification

The SQL Plan Analysis Specification screen see the following figure) displays when you select option 1 from the SQL Analysis and Development menu. On this screen you can select an object type to analyze, specify batch or online processing mode, and specify the default qualifier used for unqualified tables and views.

SQL Plan Analysis Specification Screen

Select One Object Type to be Analyzed

Enter S next to the object type to be analyzed. Only one object type can be selected at a time.

Plan	Displays the SQL Plan Selection screen shown in SQL Plan Selection.
DBRM	Displays the DBRM Selection screen shown in DBRM Selection Screen.
Collection ID	Displays the Collection ID Selection screen shown in Collection ID Selection Screen.
Package	Displays the Package Selection screen shown in Package Selection Screen.

Specify Selection Criteria

Object Name	Enter the name of the object to be analyzed. An asterisk (*) can be entered. If a single asterisk is entered, all objects of the type selected are displayed. An asterisk can also be used in conjunction with a partial object name.
Use Filters	Enter YES to use a wild card character to specify a creator and filter the search. Filters Used to Select Object Window displays. This field is ignored if you have selected a type of Collection ID.

Specify Processing Option

Processing Mode

Select the mode in which the analysis is to be performed.

1	Performs the analysis in online mode. Requested information displays on the screen.
2	Performs the analysis in batch mode. JCL is generated and submitted to produce a report of the requested information.

Host Variable Substitution

Specify Type

This is where you specify the type of host variable substitution to use for an explain:

1	File-AID for Db2 substitutes host variables with literals which are generated by using data type information obtained from the Db2 catalog. Substitution may involve use of default lengths established by File-AID for Db2. LOB data types result in generation of the Db2 CAST function to cast a default value to the appropriate LOB data type.
2	Use parameter markers. A question mark will be substituted for a host variable where allowed by Db2, otherwise the product will generate a literal.

Example

You have a Plan or Package containing the following SQL statement, where IXNAME is defined as VARCHAR(18), and IXCREATOR is defined as CHAR(8):

SELECT COUNT ( * )
INTO :SQLINPAR
FROM SYSIBM . SYSINDEXPART
WHERE IXNAME = :SQLINAME
AND IXCREATOR = :SQLICREA

If you enter 1 (for Literal), then the following SQL is generated:

EXPLAIN PLAN SET QUERYNO = 3519 FOR SELECT COUNT(*) FROM
SYSIBM.SYSINDEXPART
WHERE IXNAME = ' ' AND IXCREATOR = ' '

If you enter 2 (for Parameter Marker), then the following SQL is generated:

EXPLAIN PLAN SET QUERYNO = 3519 FOR SELECT COUNT(*) FROM
SYSIBM.SYSINDEXPART
WHERE IXNAME = ? AND IXCREATOR = ?

Note

File-AID for Db2 strips off the INTO :hostvariable(list) portion of any SELECT INTO clause. This is necessary because of the Db2 restriction that dynamically prepared EXPLAIN statements cannot include host variables. As an option, you can turn on SQL logging to see the exact SQL statement that is explained.

Specify Object Qualifier Option

Qualify By

Specify which qualifier option to use for the specified table name.

1	(Default) CURRENT SQLID. Enter 1 to use the CURRENT SQLID as specified with the primary command SQLID or in the User-Parms-Menu.
2	Bind Qualifier. Enter 2 to use the qualifier that was specified when the plan was bound.
3	Default Qualifier. Enter 3 to use the qualifier that is shown in the Default Qualifier field.
4	CURRENT SCHEMA. Enter 4 to use the CURRENT SCHEMA as specified with the primary command SCHEMA or in the User-Parms-Menu.

Default Qualifier

This is where you specify the default qualifier for Option 3 in the Qualify By field.