db2expln - SQL and XQuery Explain command
- 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
The options can be specified in any order.
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
- The name of the tenant in the database that contains the packages to be explained. If not specified, the default SYSTEM tenant is assumed.
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.
- The db2expln output is directed to the terminal.
For backward compatibility, you can use -t instead of -terminal.
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:
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 -package CALC% ....
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.
- 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.
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.
- 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:
These statements make it possible to alter the plan chosen for subsequent dynamic query statements processed by db2expln.
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
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.
These options determine what additional information is provided in the explained plans.
- 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.
- 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.
- 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.
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
activitystmtlogical 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.
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.
- 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
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
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
- Dynamic DECLARE CURSOR
- EXECUTE IMMEDIATE
- OPEN cursor
- SQL control statements
Each sub-statement within a compound SQL statement might have its own section, which can be explained by db2expln.
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.
db2expln -database SAMPLE -schema % -package % -output my.exp
To explain these statements, enter the following command:
SET PATH=SYSIBM, SYSFUN, DEPT01, DEPT93@ SELECT EMPNO, TITLE(JOBID) FROM EMPLOYEE@
db2expln -database DEPTDATA -stmtfile statements.db2 -terminator @ -terminal
The following command:
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
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
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