ISQL : EXPLAIN options for EXPLAIN command

Products

• Database Management for DB2 for z/OS - Administration Suite
• Plan Analyzer for DB2 for z/OS
• SQL-Ease for DB2 for z/OS

Issue/Introduction

Interactive SQL(ISQL) has an EXPLAIN command which is available from the "SQL Editor" screen only when SQL is being edited. When an EXPLAIN command is issued where are the EXPLAIN options set?

 

 

 

 

 

Environment

Release: R20
Component: ISQL

Resolution

The EXPLOPTS command is available when in the "SQL Editor" screen. The EXPLOPTS command allows for explain options to be specified before executing the EXPLAIN command on your SQL.

The Options available include "Target DB2 SSID" which identifies SSID the explain should run on, "PLAN_TABLE Option" to commit or rollback updates to the userid.Plan_table, "Primary Authid" and "Secondary Authid" to be used, the "SQL Qualifier Id" to be used for unqualified table name references, "Path Schemas" to be used or not, "Parallelism Degree" and "Isolation" to be used.

Sample screen:

Target DB2 SSID     : ssid        PLAN_TABLE Option=> C
Primary Authid      : authid1     Secondary Authid => authid2
SQL Qualifier Id   => authid1   > Path Schemas     => N
Parallelism Degree => 1           Isolation        => CS

This EXPLOPTS command can easily be missed when using the EXPLAIN command causing inappropriate explain options to be used could produce misleading results.

Caveat : Before using the EXPLAIN command be sure to identify a PLAN_TABLE that will be used as the default for your explain data.

NOTE: The EXPOPTS command is only available if your site owns a license for SQL-Ease.  In addition, EXPLOPTS is only available when the SQL Editor is invoked from one of the following products:
- ISQL     (Interactive SQL facility)
- SQL-Ease (Programmer productivity tool)
- PPA      (Plan Analyzer)

NOTE: The EXPLAIN command is only available if your site owns a license for SQL-Ease.  In addition, EXPLAIN is only available when the SQL Editor is invoked from one of the following products:
- ISQL     (Interactive SQL facility)
- SQL-Ease (Programmer productivity tool)
- PPA      (Plan Analyzer)

 

Additional Information

SQL Editor Commands

For detailed information about the EXPLOPTS command , when in the SQL Editor screen editing some SQL that has been input, place a "?" question mark in the command field :

COMMAND ===> ?

A list of valid commands appears. Search down to the EXPLOPTS command and enter a "?" there too.

? EXPLOPTS      - Set explain options and explain the sql statement.

It has a detailed help text that the user can <enter> through a number of screens to detail how the explain works. 

Also, In the event that this error is encountered from the EXPLAIN see this knowledgebase article : SQLCODE = -440 ERROR NO AUTHORIZED PROCEDURE NAMED executing Plan Analyzer explain