db2expln - SQL and XQuery Explain command

The db2expln tool describes the access plan selected for SQL and XQuery statements. Use the tool to obtain a quick explanation of the chosen access plan when explain data was not captured. For static SQL and XQuery statements, db2expln examines the packages stored in the system catalog tables. For dynamic SQL and XQuery statements, db2expln examines the query cache sections.

Authorization

DBADM or one of the following authorizations or privileges:
  • For static statements, SELECT privilege on the catalog tables
  • For dynamic statements, SELECT privilege on the catalog tables plus one of the following authorizations or privileges:
    • Sufficient privileges to compile the statement
    • EXPLAIN authority
    • SQLADM authority

Command syntax

Read syntax diagramSkip visual syntax diagramdb2explnconnection-optionsoutput-optionspackage-optionsdynamic-optionsexplain-optionsevent-monitor-options-help
connection-options
Read syntax diagramSkip visual syntax diagram-databasedatabase-name-useruser-idpassword-tn-tenanttenantname
output-options
Read syntax diagramSkip visual syntax diagram -outputoutput-file -terminal
package-options
Read syntax diagramSkip visual syntax diagram -schemaschema-name-package package-name-versionversion-identifier-escapeescape-character-noupper-sectionsection-number
dynamic-options
Read syntax diagramSkip visual syntax diagram-cacheanchIDstmtUIDenvIDvarID-statementquery-statement-stmtfilequery-statement-file-terminatortermination-character-noenv
explain-options
Read syntax diagramSkip visual syntax diagram-graph-opids-setupsetup-file
event-monitor-options
Read syntax diagramSkip visual syntax diagram-actevmevent-monitor-name -appidapplication_id-uowiduow_id-actidactivity_id-actid20-actid2activity_secondary_id

Command parameters

The options can be specified in any order.

connection-options:

These options specify the database to connect to and any options necessary to make the connection. The connection options are required except when the -help option is specified.

-database database-name
The name of the database that contains the packages to be explained.

For backward compatibility, you can use -d instead of -database.

-user user-id password
The authorization ID and password to use when establishing the database connection. Both user-id and password must be valid according to Db2® naming conventions and must be recognized by the database.

For backward compatibility, you can use -u instead of -user.

-tn | tenant tenantname
The name of the tenant in the database that contains the packages to be explained. If not specified, the default SYSTEM tenant is assumed.
output-options:

These options specify where the db2expln output should be directed. Except when the -help option is specified, you must specify at least one output option. If you specify both options, output is sent to a file as well as to the terminal.

-output output-file
The output of db2expln is written to the file that you specify.

For backward compatibility, you can use -o instead of -output.

-terminal
The db2expln output is directed to the terminal.

For backward compatibility, you can use -t instead of -terminal.

package-options:

These options specify one or more packages and sections to be explained. Only static queries in the packages and sections are explained.

As in a LIKE predicate, you can use the pattern matching characters, which are percent sign (%) and underscore (_), to specify the schema-name, package-name, and version-identifier.

-schema schema-name
The SQL schema of the package or packages to be explained.

For backward compatibility, you can use -c instead of -schema.

-package package-name
The name of the package or packages to be explained.

For backward compatibility, you can use -p instead of -package.

-version version-identifier
The version identifier of the package or packages to be explained. The default version is the empty string.
-escape escape-character
The character, escape-character to be used as the escape character for pattern matching in the schema-name, package-name, and version-identifier.
For example, the db2expln command to explain the package TESTID.CALC% is as follows:
db2expln -schema TESTID -package CALC% .... 
However, this command would also explain any other plans that start with CALC. To explain only the TESTID.CALC% package, you must use an escape character. If you specify the exclamation point (!) as the escape character, you can change the command to read: db2expln -schema TESTID -escape ! -package CALC!% ... . Then the ! character is used as an escape character and thus !% is interpreted as the % character and not as the "match anything" pattern. There is no default escape character.

For backward compatibility, you can use -e instead of -escape.

To avoid problems, do not specify the operating system escape character as the db2expln escape character.

-noupper
Specifies that the schema-name, package-name, and version-identifier, should not be converted to uppercase before searching for matching packages.

By default, these variables are converted to uppercase before searching for packages. This option indicates that these values should be used exactly as typed.

For backward compatibility, you can use -l, which is a lowercase L and not the number 1, instead of -noupper.

-section section-number
The section number to explain within the selected package or packages.

To explain all the sections in each package, use the number zero (0). This is the default behavior. If you do not specify this option, or if schema-name, package-name, or version-identifier contain a pattern-matching character, all sections are displayed.

To find section numbers, query the system catalog view SYSCAT.STATEMENTS. Refer to the SQL Reference for a description of the system catalog views.

For backward compatibility, you can use -s instead of -section.

dynamic-options:

These options specify one or more dynamic query statements to be explained.

-cache anchID, stmtUID, envID, varID
Specifies the dynamic SQL cache from which the statement, identified by the given identifiers (IDs), is retrieved. The IDs can be obtained using the db2pd command with the -dynamic option.
-statement query-statement
An SQL or XQuery query statement to be dynamically prepared and explained. To explain more than one statement, either use the -stmtfile option to provide a file containing the query statements to explain, or use the -terminator option to define a termination character that can be used to separate statements in the -statement option.
-stmtfile query-statement-file
A file that contains one or more query statements to be dynamically prepared and explained. By default, each line of the file is assumed to be a distinct query statement. If statements must span lines, use the -terminator option to specify the character that marks the end of an query statement.
-terminator termination-character
The character that indicates the end of dynamic query statements. By default, the -statement option provides a single query statement and each line of the file in the -stmtfile is treated as a separate query statement. The termination character that you specify can be used to provide multiple query statements with -statement or to have statements span lines in the -stmtfile file.
-noenv
Specifies that dynamic statements that alter the compilation environment should not be executed after they have been explained.
By default, db2expln will execute any of the following statements after they have been explained:
	  SET CURRENT DEFAULT TRANSFORM GROUP
	  SET CURRENT DEGREE
	  SET CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
	  SET CURRENT QUERY OPTIMIZATION
	  SET CURRENT REFRESH AGE
	  SET PATH
	  SET SCHEMA
These statements make it possible to alter the plan chosen for subsequent dynamic query statements processed by db2expln.

If you specify -noenv, then these statement are explained, but not executed.

It is necessary to specify either -statement or -stmtfile to explain dynamic query. Both options can be specified in a single invocation of db2expln.

explain-options:

These options determine what additional information is provided in the explained plans.

-graph
Show optimizer plan graphs. Each section is examined, and then the original optimizer plan graph is constructed. It is possible for the optimizer graph to show some gaps, based on the information contained within the section plan.

For backward compatibility, you can specify -g instead of -graph.

-opids
Display operator ID numbers in the explained plan.

The operator ID numbers allow the output from db2expln to be matched to the output from the explain facility. Not all operators have an ID number and that some ID numbers that appear in the explain facility output do not appear in the db2expln output.

For backward compatibility, you can specify -i instead of -opids

.
-help
Shows the help text for db2expln. If this option is specified no packages are explained.
Most of the command line is processed in the db2exsrv stored procedure. To get help on all the available options, it is necessary to provide connection-options along with -help. For example, use:
    db2expln -help -database SAMPLE
    

For backward compatibility, you can specify -h or -?.

-setup setup-file
A file that contains one or more statements needed to setup the environment for dynamic statements or for static statements that need to be recompiled (such as a static statement that references a declared temporary table). Each statement in the file will be executed and any errors or warnings will be reported. The statements in the file are not explained.
event-monitor-options:

These options specify one or more section environments from an activities event monitor to be explained.

-actevm event-monitor-name
Specifies the name of the activities event monitor whose activitystmt logical grouping contains the section environments (in the section_env monitor element) to be explained.
-appid application-id
Specifies the application identifier (appl_id monitor element) uniquely identifying the application that issued the activities whose section environments are to be explained. -actevm must be specified if -appid is specified.
-uowid uow-id
Specifies the unit of work ID (uow_id monitor element) whose section environments are to be explained. The unit of work ID is unique only within a given application. -actevm must be specified if -uowid is specified.
-actid activity-id
Specifies the activity ID (activity_id monitor element) whose section environments are to be explained. The activity ID is only unique within a given unit of work. -actevm must be specified if -actid is specified.
-actid2 activity-secondary-id
Specifies the activity secondary ID (activity_secondary_id monitor element) whose section environments are to be explained. This defaults to zero if not specified. -actevm must be specified if -actid2 is specified.

Usage notes

Unless you specify the -help option, you must specify either package-options or dynamic-options. You can explain both packages and dynamic SQL with a single invocation of db2expln.

Some of the option flags listed previously might have special meaning to your operating system and, as a result, might not be interpreted correctly in the db2expln command line. However, you might be able to enter these characters by preceding them with an operating system escape character. For more information, see your operating system documentation. Make sure that you do not inadvertently specify the operating system escape character as the db2expln escape character.

Help and initial status messages, produced by db2expln, are written to standard output. All prompts and other status messages produced by the explain tool are written to standard error. Explain text is written to standard output or to a file depending on the output option chosen.

The following messages can be returned by db2expln:
  • No packages found for database package pattern: "<creator>".<package> with version "<version>"

    This message will appear in the output if no packages were found in the database that matched the specified pattern.

  • Bind messages can be found in db2expln.msg

    This message will appear in the output if the bind of db2expln.bnd was not successful. Further information aboutthe problems encountered will be found in the db2expln.msg file in the current directory.

  • Section number overridden to 0 (all sections) for potential multiple packages.

    This message will appear in the output if multiple packages might be encountered by db2expln. This action will be taken if one of the pattern matching characters is used in the package or creator input arguments.

  • Bind messages for <bind file> can be found in <message file>

    This message will appear if the bind of the specified bind file was not successful. Further information aboutthe problems encountered will be found in the specified message file on the database server.

  • No static sections qualify from package.

    This message will appear in the output if the specified package only contains dynamic query statements, which means that there are no static sections.

  • Package "<creator>"."<package>", "<version>", is not valid. Rebind the package and then rerun db2expln.

    This message will appear in the output if the specified package is currently not valid. Reissue the BIND or REBIND command for the plan to re-create a valid package in the database, and then rerun db2expln.

The following statements will not be explained:

  • BEGIN/END COMPOUND
  • BEGIN/END DECLARE SECTION
  • CLOSE cursor
  • COMMIT and ROLLBACK
  • CONNECT
  • DESCRIBE
  • Dynamic DECLARE CURSOR
  • EXECUTE
  • EXECUTE IMMEDIATE
  • FETCH
  • INCLUDE
  • OPEN cursor
  • PREPARE
  • SQL control statements
  • WHENEVER

Each sub-statement within a compound SQL statement might have its own section, which can be explained by db2expln.

Note: The db2expln command does not exclude any XQuery statements.

Examples

To explain multiple plans with one invocation of db2expln, use the -package, -schema, and -version option and specify string constants for packages and creators with LIKE patterns. That is, the underscore (_) can be used to represent a single character, and the percent sign (%) can be used to represent the occurrence of zero or more characters.

To explain all sections for all packages in a database named SAMPLE, with the results being written to the file my.exp, enter
  db2expln -database SAMPLE -schema % -package %  -output my.exp
As another example, suppose a user has a CLP script file called "statements.db2" and wants to explain the statements in the file. The file contains the following statements:
  SET PATH=SYSIBM, SYSFUN, DEPT01, DEPT93@
  SELECT EMPNO, TITLE(JOBID) FROM EMPLOYEE@
To explain these statements, enter the following command:
  db2expln -database DEPTDATA -stmtfile statements.db2 -terminator @ -terminal
Explain the following statement:
  SELECT e.lastname, e.job, d.deptname, d.location, p.projname
    FROM employee AS e, department AS d, project AS p
    WHERE e.workdept = d.deptno AND e.workdept = p.deptno
The following command:
  db2expln -database SAMPLE
    -statement "SELECT e.lastname, e.job,
      d.deptname, d.location, p.projname
      FROM employee AS e, department AS d, project AS p
      WHERE e.workdept = d.deptno AND e.workdept = p.deptno"
    -terminal
returns:
Db2 Enterprise Server Edition n.n, nnnn-nnn (c) Copyright IBM Corp. 1991, yyyy
Licensed Material - Program Property of IBM
IBM Db2 Database SQL and XQUERY Explain Tool

******************** DYNAMIC ***************************************

==================== STATEMENT ==========================================

        Isolation Level          = Cursor Stability
        Blocking                 = Block Unambiguous Cursors
        Query Optimization Class = 5

        Partition Parallel       = No
        Intra-Partition Parallel = No

        SQL Path                 = "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
                                   "SDINIRO"


Statement:

  SELECT e.lastname, e.job, d.deptname, d.location, p.projname
  FROM employee AS e, department AS d, project AS p
  WHERE e.workdept =d.deptno AND e.workdept =p.deptno


Section Code Page = 1208

Estimated Cost = 22.802252
Estimated Cardinality = 105.000000

Access Table Name = SDINIRO.PROJECT  ID = 2,10
|  #Columns = 2
|  Skip Inserted Rows
|  Avoid Locking Committed Data
|  Currently Committed for Cursor Stability
|  Relation Scan
|  |  Prefetch: Eligible
|  Lock Intents
|  |  Table: Intent Share
|  |  Row  : Next Key Share
|  Sargable Predicate(s)
|  |  Process Build Table for Hash Join
Hash Join
|  Estimated Build Size: 4000
|  Estimated Probe Size: 4000
|  Access Table Name = SDINIRO.DEPARTMENT  ID = 2,6
|  |  #Columns = 3
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  Process Probe Table for Hash Join
Hash Join
|  Estimated Build Size: 4000
|  Estimated Probe Size: 4000
|  Access Table Name = SDINIRO.EMPLOYEE  ID = 2,7
|  |  #Columns = 3
|  |  Skip Inserted Rows
|  |  Avoid Locking Committed Data
|  |  Currently Committed for Cursor Stability
|  |  Relation Scan
|  |  |  Prefetch: Eligible
|  |  Lock Intents
|  |  |  Table: Intent Share
|  |  |  Row  : Next Key Share
|  |  Sargable Predicate(s)
|  |  |  Process Probe Table for Hash Join
Return Data to Application
|  #Columns = 5

End of section