>>-db2expln--+------------------------+-------------------------> '-| connection-options |-' >--+--------------------+--+---------------------+--------------> '-| output-options |-' '-| package-options |-' >--+---------------------+--+---------------------+-------------> '-| dynamic-options |-' '-| explain-options |-' >--+---------------------------+--+--------+------------------->< '-| event-monitor-options |-' '- -help-' connection-options |-- -database--database-name--+---------------------------+-----| '- -user--user-id--password-' output-options |--+-----------------------+--+------------+--------------------| '- -output--output-file-' '- -terminal-' package-options |-- -schema--schema-name-- -package--package-name---------------> >--+-------------------------------+----------------------------> '- -version--version-identifier-' >--+----------------------------+--+-----------+----------------> '- -escape--escape-character-' '- -noupper-' >--+---------------------------+--------------------------------| '- -section--section-number-' dynamic-options |--+----------------------------------------+-------------------> '- -cache--anchID--stmtUID--envID--varID-' >--+------------------------------+-----------------------------> '- -statement--query-statement-' >--+----------------------------------+-------------------------> '- -stmtfile--query-statement-file-' >--+-------------------------------------+--+---------+---------| '- -terminator--termination-character-' '- -noenv-' explain-options |--+---------+--+---------+--+---------------------+------------| '- -graph-' '- -opids-' '- -setup--setup-file-' event-monitor-options |-- -actevm--event-monitor-name--+-------------------------+----> '- -appid--application_id-' >--+-----------------+--+----------------------+----------------> '- -uowid--uow_id-' '- -actid--activity_id-' .- -actid2--0---------------------. >--+---------------------------------+--------------------------| '- -actid2--activity_secondary_id-'
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.
For backward compatibility, you can use -d instead of -database.
For backward compatibility, you can use -u instead of -user.
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.
For backward compatibility, you can use -o instead of -output.
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.
For backward compatibility, you can use -c instead of -schema.
For backward compatibility, you can use -p instead of -package.
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.
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.
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.
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.
These options determine what additional information is provided in the explained plans.
For backward compatibility, you can specify -g instead of -graph.
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
. db2expln -help -database SAMPLE
For backward compatibility, you can specify -h or -?.
These options specify one or more section environments from an activities event monitor to be explained.
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.
This message will appear in the output if no packages were found in the database that matched the specified pattern.
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.
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.
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.
This message will appear in the output if the specified package only contains dynamic query statements, which means that there are no static sections.
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:
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
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
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