IBM SQL Performance Analyzer Blog
By Tom Glaser
SQL PA; Explain Table Management
Date: November, 2024
What is SQL Performance Analyzer
The IBM Db2 SQL Performance Analyzer (SQL PA) is a tool for the Db2 application programmers and DBAs to access the resource usage information and costs associated with SQL queries without executing them in Db2. This analysis helps in tuning the queries to achieve the maximum performance.
Using Db2 SQL Performance Analyzer you can verify the execution time for queries before processing them. This helps prevent problems related with the long running queries even before the resources are consumed or query is terminated by a Governor. Db2 SQL Performance Analyzer calculates the cost of executing a query using the IMS, CICS, batch, TSO, SPUFI, and QMF facilities. The cost of a query is calculated based on the factors like CPU time, I/O count, elapsed time, and as QUNITS (a single number that represents the overall cost). Data is provided in easy-to-read reports.
The Plan Table report provides information on how Db2 accesses data for a given SQL statement. The Plan Table report is produced in both TSO and batch. You can scan the programming language data sets to extract SQL for analysis.
Blog Purpose: The latest release of SQL PA is V5.2 and the biggest enhancement is a connection directly into the IBM Db2 Admin Tool. There may be times you need to manage the Db2 explain tables. With the link to the Admin Tool, “Explain” table management is now possible.
When you want to explain a query or a package, SQL PA will use the following explain tables:
- PLAN_TABLE
- DSN_STATEMNT_TABLE
- DSN_FUNCTION_TABLE
- DSN_DETCOST_TABLE
- DSN_PREDICAT_TABLE
If these tables do not exist, SQL PA will create them. If you are using an older version of plan tables, as an example, SQL PA will automatically update the plan table to the correct number of columns. For Db2 V13, this is 68 columns. What if you want to do other things with the plan tables, like clean them up?
To access this new panel, go to panel 2 (Advanced DB2 SQL PA Processing), then option 6 (Manage EXPLAIN tables & aliases):
You are now in the Admin Tool and from this panel, you can do so many things…
Followed by MNT command:
On the Manage EXPLAIN Tables & Aliases panel, you can create, upgrade, and drop EXPLAIN tables and associated aliases. The panel uses the stored procedure ADMIN_EXPLAIN_MAINT to perform this EXPLAIN table maintenance. The description of the ADMIN_EXPLAIN_MAINT stored procedure is documented in
the Db2 for z/OS SQL Reference manual.
https://www.ibm.com/docs/en/db2-for-zos
What can you do on the MNT panel? Everything…notice it automatically pulls up the tables associated with your ID?
Recommended panel operation flow:
1. Enter the input for the stored procedure before using the primary or line
commands so that EXPLAIN table status is updated based on this input.
2. If the action is CREATE or UPGRADE, select or unselect EXPLAIN tables by
using the relevant primary and line commands.
3. Review messages by using the M line command when the W column has I, W or E.
4. If the action is CREATE or UPGRADE, optionally enter parameters for the
stored procedure by using the OPTIONS, or O primary command.
5. Optionally, preview the specified action by using the Preview, or P primary
command.
6. Execute the stored procedure for the specified action by using the Run, or R
primary command.
Panel field processing priority and rules:
When any values are changed in the input field for the stored procedure
ADMIN_EXPLAIN_MAINT, the panel resets any specified primary commands and the
line commands without processing those commands. The panel also fetches the
current EXPLAIN table status from the Db2 catalog by using the newly input
field value. When line commands and primary commands are specified together,
the panel processes line commands first, then it processes the primary command.
Input for ADMIN_EXPLAIN_MAINT stor
Action Possible values are CREATE, UPGRADE and DROPALL.
CREATE:
Sets the action parameter of the stored procedure to CREATE.
Selected EXPLAIN tables will be created. EXPLAIN tables can
be selected by using the S line command. When the Manage
alias field is set to YES, aliases well be created by using
CREATE ALIAS statements. The stored procedure is not used to
create aliases.
This value can be abbreviated as C.
UPGRADE:
Sets the action parameter of the stored procedure to
STANDARDIZE_AND_CREATE. All existing EXPLAIN tables are
upgraded to the format for the current Db2 version. Also,
selected non-existing EXPLAIN tables will be created. When
the Manage alias field is set to YES, an alias for
PLAN_TABLE will be created by using CREATE ALIAS statement
if an alias does not exist. Aliases of other EXPLAIN tables
are created by the stored procedure. The store procedure
creates multiple aliases for each EXPLAIN table when
PLAN_TABLE has multiple aliases even though the Schema alias
field is specified. You can review the multiple aliases per
EXPLAIN table by setting blank to the Schema alias field.
This value can be abbreviated as U.
DROPALL:
Sets the action parameter of the stored procedure to DROP.
All EXPLAIN tables qualified by the schema field will be
dropped. When the Manage alias field is set to YES, aliases
are dropped by using the DROP ALIAS statements. You should
set the Schema alias field to blank so that you can review
the all aliases that will be dropped.
This value can be abbreviated as D.
Schema The schema name of the EXPLAIN table on which the specified
action is to be taken. The leading and trailing blanks are
action is to be taken. The leading and trailing blanks are
removed. Enter the question mark (?) in this field to look up
existing schema names for PLAN_TABLE.
Manage alias Specifies whether to create and drop aliases for the selected
EXPLAIN tables. Possible values are YES or NO.
When this field is set to YES:
- If the action is CREATE, the specified alias is created for
any selected EXPLAIN tables (S column has 'S').
- If the action is UPGRADE, the specified alias is created
for any selected EXPLAIN tables (S column has 'S') and
existing EXPLAIN tables even though those existing EXPLAIN
tables are not explicitly selected (S column has 'U').
If multiple aliases are already created for PLAN_TABLE,
the same set of multiple aliases will be created for each
EXPLAIN table that will be created (S column has 'S') or
already exists (S column has 'U').
- If the action is DROPALL, the aliases are dropped for all
existing EXPLAIN tables.
This field sets the manage-alias parameter of the stored
procedure.
Schema alias The schema name for the alias of the EXPLAIN table. This
field is visible when the Manage alias field is set to YES.
field is visible when the Manage alias field is set to YES.
The leading and trailing blanks are removed. Enter the
question mark (?) in this field to look up existing schema
alias names for PLAN_TABLE.
The selected EXPLAIN tables are used to set the table-set parameter of
the stored procedure. The primary commands Run and Preview set the mode
parameter of the stored procedure. Other stored procedure parameters can be
entered from the options (ADB2EMO) panel by entering the primary command
OPTIONS, or O.
The panel Columns are:
LC Input field where you can enter one of the listed line
commands.
S Whether the EXPLAIN table in that row is selected.
Possible values are:
S - The table is selected for the action CREATE or UPGRADE.
U - The table is selected for the action UPGRADE.
U indicates that the table was not explicitly selected by
the user, while S indicates that the table was explicitly
specified by the user. If the action is DROPALL, the column
is blank.
W Pending messages for the EXPLAIN table in that row.
Possible values are:
I - Informational message
W - Warning message
E - Error message
If messages of multiple severities exist, the highest
severity is reported. If this field is blank, no pending
messages exist.
Schema Schema name of the EXPLAIN table.
Owner Owner of the EXPLAIN table.
Schema Alias The name of the alias for the EXPLAIN table. This field is
visible when the Manage alias field is set to YES.
This column data is filtered by the Schema alias input field
value. When the Schema alias input field is blank, the
filter value for this column is the existing aliases for
PLAN_TABLE. In this case each EXPLAIN table can have
multiple rows, each with a different alias in this column.
Line commands:
All line commands are applied to the EXPLAIN table on the row.
Line commands S and U are available when the action field has CREATE or
UPGRADE. Line commands A, CA, and DA are available when the manage alias
UPGRADE. Line commands A, CA, and DA are available when the manage alias
field has YES.
A Show alias of the EXPLAIN table qualified by the schema alias
in the Aliases for Tables and Views (ADB21A) panel.
CA Create alias for the EXPLAIN table qualified by the specified
schema alias value.
DA Drop alias of the EXPLAIN table qualified by the schema
alias.
DT Drop the EXPLAIN table qualified by the schema.
M Show messages for the EXPLAIN table.
S Select the EXPLAIN table for the action CREATE or UPGRADE.
After the selection, the S column has S.
T Show the EXPLAIN table qualified by the schema on the Tables,
Views, and Aliases (ADB21T) panel.
U Unselect the EXPLAIN table for the action CREATE or UPGRADE.
After clearing the selection, the column is blank unless the
action is UPGRADE and the EXPLAIN table exists. In this case,
the column is changed to U.
Primary commands:
DropallAlias Drop all aliases qualified by the Schema alias field. This
operation includes those aliases that do not reference the
operation includes those aliases that do not reference the
EXPLAIN table qualified by the Schema field. This command is
available when the Manage alias field is set to YES.
This command can be abbreviated as DA.
Options Change additional options for creating EXPLAIN tables.
This command can be abbreviated as O.
Preview Invoke stored procedure ADMIN_EXPLAIN_MAINT in PREVIEW mode.
The result set of the stored procedure shows SQL statements
that will be used by the stored procedure in RUN mode.
When the Manage alias field is set to YES, the panel
processes the creation and drop of aliases in these certain
cases. Therefore not all CREATE and DROP ALIAS statements
are in the result set of the stored procedure.
This command can be abbreviated as P.
Run Invoke stored procedure ADMIN_EXPLAIN_MAINT in RUN mode. The
action CREATE is performed against the selected EXPLAIN
tables. The action UPGRADE is performed against all existing
EXPLAIN tables and selected EXPLAIN tables. The action
DROPALL is performed against all existing EXPLAIN tables.
This command can be abbreviated as R.
SelAll Select all EXPLAIN tables. All rows in the S column will have
S. This primary command is available when the action is
Run Invoke stored procedure ADMIN_EXPLAIN_MAINT in RUN mode. The
action CREATE is performed against the selected EXPLAIN
tables. The action UPGRADE is performed against all existing
EXPLAIN tables and selected EXPLAIN tables. The action
DROPALL is performed against all existing EXPLAIN tables.
This command can be abbreviated as R.
SelAll Select all EXPLAIN tables. All rows in the S column will have
S. This primary command is available when the action is
CREATE or UPGRADE.
This command can be abbreviated as SA.
SelDiag Select the diagnostic EXPLAIN tables. These tables are listed
in DIAGNOSTICS value explanation for the table-set parameter
of the stored procedure ADMIN_EXPLAIN_MAINT. This command is
available when the action is CREATE or UPGRADE.
This command can be abbreviated as SD.
UnselAll Unselect all EXPLAIN tables. The S column will be cleared for
all rows unless the action is UPGRADE and the EXPLAIN table
exists. In this case, the column for that row is changed to
U. This command is available when the action is CREATE or
UPGRADE.
This command can be abbreviated as UA.
In Summary: This article explains with V5.2 of SQL PA, you now have the connection into the Admin Tool and along with the Admin Tool is the ability to manage all of your explain tables. If you have ideas on ways to make SQL Performance Analyzer better, you can submit an Aha!