IBM SQL Performance Analyzer V5.2 Blog - Admin Tool Integration
By Tom Glaser
SQL PA V5.2; Admin Tool Integration
Date: November, 2024
What is SQL Performance Analyzer: The IBM Db2 SQL Performance Analyzer (SQL PA) tool provides Db2 application programmers and DBAs resource usage information and costs associated with SQL queries without having to run them in Db2. This analysis helps you to tune your queries to achieve maximum performance.
Db2 SQL Performance Analyzer prevents problems associated with long-running queries by calculating the cost of queries before processing them. Db2 SQL Performance Analyzer tells you how long queries will take before you run them, before resources are consumed, and before the query is terminated by a governor. Db2 SQL Performance Analyzer determines the cost of running a query under the attach facilities of IMS, CICS, batch, TSO, SPUFI, and QMF. The monetary cost of each query is presented and a cost estimate in familiar units: CPU time, I/O count, elapsed time, and as QUNITS (a single number that represents the overall cost). Data and content are presented in easily readable reports.
The Plan Table report provides you with information about how Db2 accesses data for a given SQL statement. The Plan Table report is produced in both TSO and batch. Programming language data sets can be scanned directly to extract SQL for analysis.
SQL PA V5.2 enhancements:
I’m sure there were times while in the SQL PA tool that you wish you could simply jump into the Admin Tool. For example, if you wanted to create a new index or maybe you wanted to bind a package. You had to get out of SQL PA, jump into the Admin Tool, make your changes and the navigate back to SQL PA. With V5.2 of SQL PA, you can now jump directly into the Admin Tool. We will look at the new layouts of the Query Limit and Query Block reports, review the new line commands, describe the integration of SQL PA with the IBM Db2 Admin Tool and look at these new screens. Sorting of the columns has also improved; I’ll show you what’s new.
What’s New:
There are many reports in SQL PA, but the most common and the one you will see right away is the Qlimits Report; otherwise known as the Query Limits Report. A few columns have been removed and a few new columns have been added.
For example, the cost category has been added:
https://www.ibm.com/docs/en/db2-for-zos/13?topic=statements-cost-categories
You will also get the collections, package and version on the same screen, something users have wanted.
The “query block” report has also changed.
User Parameters command is added to Explain process screens:
A new primary command USERPARM was added for the following screens:
⦁ Process packages from the DB2 system catalog
⦁ Process application DBRM
⦁ Process SQL from a sequential data set or PDS member
⦁ Process a query number from the plan table
⦁ Process a QMF statement
⦁ Process stabilized dynamic queries
Before if the user wanted to change user parameters it was required to go to option 3. 3 (Modify DB2 SQL PA Configuration -> Change the DB2 SQL PA parameters -> Edit SQL PA user parms). Now it’s possible to change the user parameters in the Process Panel screens
User Parameters Panel:
New option to include access path changes together with cost changes:
A new option to Always show access path changes was added. This option lets you see records that have changes in their access path, even if they do exceed cost limits. It's possible to choose "Y" (Yes) to see all changes, or "N" (No) to filter out changes that exceed cost limits. Default is "N" (No). This option is available for functions Compare(1.7) and Test (1.8)
Apart from this new option, we moved some options into ADVOPT panel.
Advanced option panel (ADVOPT primary command):
Some options were moved from the screens Process Compare and Test into Advanced option panel (ADVOPT primary command).
Add Return Code for Package Compare:
New option was added to allow to set return code for Batch job to 5 if the access path was changed. The default is "N" (No). This option is available for functions Compare(1.7) and Test (1.8)
Integration SQL Performance Analyzer with Db2 Administration Tool:
Integration with the Db2 Administration Tool
⦁ Migration of statistics: Utilizes ADB2GEN to collect statistics and ADBTEP2 to apply them. This approach facilitates easy restarts if a job abends and allows setting commit statements per database, tablespace, table, or for all objects.
⦁ Modification of statistics: Enables modification of statistics for tables, indexes, or tablespaces.
⦁ Management of Explain tables and aliases: Provides the ability to create any Explain table or alias on demand.
⦁ Catalog browsing: Allows calling the Db2 Administration Tool from various panels of the SQL Performance Analyzer to browse the catalog. From here, you can create new tables, rebind packages, etc.
New primary command ADMIN_TOOL was added to these different panels…
Image
Command ADMIN_TOOL in Process package will invoke Db2 Admin panel with information about collection and package:
It invokes Db2 Admin panel with information about collection and package:
Command ADMIN_TOOL in the following panels will invoke Db2 System Catalog:
⦁ Process application DBRM
⦁ Process SQL from a sequential data set or PDS member
⦁ Process a QMF statement
⦁ Process stabilized dynamic queries
Another option to access the Admin Tool is to simply type ADMIN on the command line:
Need the DDL for a table from the Query Block Report?
New line commands to show objects in Db2 Admin Tool – Command ‘X’ – Index info
New line commands to show objects in Db2 Admin Tool – Command ‘Y’ – Table info
Integration with Db2 Admin for Advanced Processing Options
Modify Table / Index / Tablespace statistics with Db2 Administration Tool
Migrate statistics by Db2 Admin Tool
Collect statistics by ADB2GEN program
Manage EXPLAIN tables & aliases
TCz
The Tools Customizer for z/OS has new options to connect to the Admin Tool:
In Summary: As you can see, V5.2 has some key functions that will help you analyze your SQL. Integration into the Admin Tool is my favorite.
If you have ideas on ways to make SQL Performance Analyzer better, you can submit an Aha!
https://ibm-data-and-ai.ideas.ibm.com/?project=DB2ZSQLPA
#IBMChampion