IBM SQL Performance Analyzer
By Tom Glaser
SQL PA; Compare SQL Enhancement
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.
The COMPARE SQL enhancement
A key feature of SQL PA is comparing the SQL statements in a package (Options 7 & 8). Using this features you can compare explain table access path information for two instances of a package. The Version ID and Collection ID fields determine which instances are compared.
To compare the packages, provide the Package name and current Collection ID. If the previous Collection ID is not provided, the current Collection ID is used. The Package name, current, and the previous collection IDs may contain the wild card characters. If wild cards are used for the Collection ID, both current and previous Collection IDs must be identical.
The default value of the current version is zero and previous version is -1. These are relative versions based on the order of explain data in the explain tables determined by the explain time. You can also code the version as found in the DBRM at bind or explain time. The version text can be 122 bytes in length. Make sure the version text is specified inside the quotes.
The Statement Matching parameter:
SQL PA provides many options to precisely compare SQL statements to achieve your objectives. The Statement Matching parameter indicates the method used to match the statements between package versions. This command is optional. If no value is specified then the default value:1 is used. This is same as the STMTMATCH command in batch.
As per an enhancement request, a new parameter value ‘Statement Matching 2’ was added. It matches by table, query type, and statement text.
The valid values of Statement Matching parameter are as follows:
· - 1 matches by table and query type.
· - 2 matches by table, query type, and statement text. ( New enhancement )
· - Q matches by query number.
· - S matches by the sequence of the statements.
Statement Matching 1:
Matches SQL statements based on the SQL characteristics rather than the statement number or physical sequence in the program. MATCHSQL1 is mutually exclusive with MATCHSQL2, MATCHQUERYNO, and MATCHSEQUENCE.
The Statement Matching 1 is used as the default value while comparing the SQL statements.
Statement Matching 2:
The MATCHSQL2 processing option matches the statement text in the packages rather than statement number or physical sequence in the package. Because SQL PA compares the SQL statement text in the catalog, this option works only for the following commands:
· COMPARE PACKAGE
· TEST AS PACKAGE
· TEST FROM CATALOG
When you use MATCHSQL2, make sure that the SQL statements for the previous bind of the package are also available in the catalog otherwise the report shows that no statements match and flags all the statements as changed.
MATCHSQL2 flags changes even if the exact SQL statements are placed in different sequence in the packages.
Note: Statement Matching 2 executes in two cycles. In the first cycle it matches Table-name, Table-number, and Query Type. In the second cycle it matches the statement text. It matches exactly like MATCHSQL1 and then also matches the SQL statement source in SYSIBM.SYSPACKSTMT.
Statement Matching Q:
Matches SQL statements by QUERYNO value. MATCHQUERYNO is mutually exclusive with MATCHSQL1, MATCHSQL2, and MATCHSEQUENCE.
Statement Matching S:
Matches SQL statements sequentially. For example first statement is matched with first, second with second and so on. MATCHSEQUENCE is mutually exclusive with MATCHSQL1, MATCHSQL2, and MATCHQUERYNO. If you specify multiple options, SQL PA uses only the last one.
Let’s take a closer look at the compare feature. Based on the ‘Process COMPARE of packages’ panel above, you have the following options:
Statement matching . . . . . . . . . . . . 1 (1, 2, Q, S)
- See Above
Package cost filter . . . . . . . . . . . . N (N,Y,-,+)
- The Package Cost Filter processes packages with a different total cost (all
statements in the package). Valid values are:"N" (no filtering), "Y"
(TOTAL_COST has changed), "-" (TOTAL_COST has decreased), "+" (TOTAL_COST
has increased). The default is "N".
Statement cost filter . . . . . . . . . . . Y (N,Y,-,+)
- The Statement Cost Filter processes statements with a different total cost
Valid values are: "N" (no filtering), "Y" (TOTAL_COST has changed), "-"
(TOTAL_COST has decreased), "+" (TOTAL_COST has increased). The default is
"N".
Minimum percent cost change to display . . 0.00
- Minimum package/statement total cost the compare will process. If both
Package and Statement cost filters are specified, the threshold value will
first be used for Package total cost and then for statements inside the
package. If both cost filters are set to N, this option has no effect.
Show invalid packages . . . . . . . . . . . N (N/Y)
- The Show Invalid Packages field determines whether a warning message will b
shown for packages flagged as INVALID by Db2. The default is "N" (no show).
Show percent change . . . . . . . . . . . . Y (N/Y)
- This option displays percent of changes in all numeric fields
(ACCESS_DEGREE, JOIN_DEGREE, MATCHCOLS, PROCMS, PROCSU and TOTAL_COST). The
default is N (no display).
Show only packages bound before . . . . . . ____________
- The "Show only packages bound before" field restricts the packages selected
for comparison and testing to those bound before the specified
datetimestamp. The format for the datetimestamp field, must: 1) Be numeric;
2) Contain 8 - 16 digits; 3) Begin with a four-digit year followed by a
two-digit month, and a two-digit day; 4) For a 16-digit format, continue on
with two digits for each of the following: the number of minutes, seconds,
and fractions of a second.
Show details of Inserted/Deleted statemets. Y (N/Y)
- Show details of inserted/deleted statements. Valid values are "N", "Y". The
default is "N" (do not show details).
In this example, the value of the field ‘Statement Matching’ is set to 2 (the new enhancement). This tells SQL PA to match SQL statements based on the SQL characteristics followed by the SQL statement text rather than by statement number or physical sequence in the program. You may also notice it takes a bit longer to perform the compare. SQL PA requires a double loop because it matches by an aggregate of Table-name, Table-number, and Query Type. It matches exactly like MATCHSQL1 and then also matches the SQL statement source in SYSIBM.SYSPACKSTMT.
Let’s look at the COBOL example. In the following panel, the Query number 111 has been changed.
The following report shows the statement 111 as old as well as new with different costs. It also shows the accounting details.
--- TS4430.ANLSS1.ANL510.DLT---------------------------------------------------------------------------
===>
*******************************************************************************************************
PACKAGE = CLASS
COLLECTION = CLASS
|OLD |NEW
--------------------+------------------------------------------------------+---------------------------
VERSION |V4 |V3
BIND_TIME |2023-07-24-16.31.33.358628 |2023-08-14-10.38.45.766909
EXPLAIN_TIME |2023-07-24-16.31.33.358628 |2023-08-14-10.38.45.766909
NO CHANGED EXPLAIN OUTPUT
NEW STATEMENTS:
--------------------+------------------------------------------------------+---------------------------
<QUERYNO> | |111
PROCMS | |1
PROCSU | |11
TOTAL_COST | |2.2910
| |
<QBLOCKNO> | |1
<PLANNO> | |1
COMPCOST | |1.0933
| |
<QBLOCKNO> | |1
<PLANNO> | |2
COMPCOST | |2.2910
| |
| |
| |
DELETED STATEMENTS:
--------------------+------------------------------------------------------+---------------------------
<QUERYNO> |111 |
PROCMS |1 |
PROCSU |17 |
TOTAL_COST |6.9798 |
| |
<QBLOCKNO> |1 |
<PLANNO> |1 |
COMPCOST |1.0933 |
| |
<QBLOCKNO> |1 |
<PLANNO> |2 |
COMPCOST |2.2910 |
| |
<QBLOCKNO> |1 |
<PLANNO> |3 |
COMPCOST |6.9798 |
Statements Compared : 4
Statements Filtered : 3
Statements Changed : 0
Statements Added : 1
Statements Deleted : 1
Packages Compared : 1
If the value of the field ‘Statement Matching’ is set to 1, the report would look like the following:
The Old and New statements are compared on the same line? Matches SQL statements based on the SQL characteristics rather than by statement number.
Using the option 1.8 of SQL PA, you can test the package to verify if the explain information will change if the package is rebound. This could be an option if you don’t want to bind the package.
In Summary: This article explains the advantages of the ability to compare before and after binding a package. By comparing, you can not only find out if the new changes are better for performance reasons, but also understand how the new changes will be in the production. If you have ideas on ways to make SQL Performance Analyzer better, you can submit an Aha!
#IBMChampion