By Tom Glaser

SQL PA; Ability to return to the previous “What-If” analysis after PF3

Date: November 2023

Summary: 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 purpose of this blog: This blog is providing a description of an enhancement to SQL PA on the “What-If” analysis feature.  What-If is a really cool feature that allows the user to create a virtual index and/or modify the query.  Once the changes are in place, SQL PA will compare it to the base line.  The access path of the new change is compared to the original access path so that you can visually see the difference.  For additional details on What-If, see the following blog:

What-If analysis using ANLCSPA

What’s new with PTF UI93952 - APAR PH56606 is the ability to go back and view the What-If Analysis from a previous report.  Without this change, once you get out of the What-If panel, you lose the What-If analysis report.  Let’s go through an example.

SQL PA provides many options to explain an SQL Statement.  Let’s explain a list of SQL statements that come with SQL PA found in the hlq.SANLSQL(ANLSTEST) PDS member.

By placing your cursor under the EXPAIN field or typing EXPLAIN on the command line, hit enter:

SQL PA has explained all the queries found in our list and sorted by QUNITS (Query Units).  If you had never run the What-If Analysis report and you attempt to access the LWIR (Last What-If Report), you will see this message:

Let’s enter a W next to the first query to create our first What-If report:

Press enter…

Selecting option 3 will allow us to create a virtual index.  This is not a real index and does not exist in SYSIBM.SYSINDEXES, but SQL PA will analyze the query using this index. 

Backing out (PF3) will compare the original query to the updated query with the new virtual index:

With the enhancement, you can click on the LWIR field or type LWIR on the command line and hit enter:

This will take you directly to the latest What-If Analysis Report:

The last What-If report will be available until you leave SQL PA or perform one from the primary commands SQL or TABLES.

In Summary: As you can see, using the “what-if” feature can save you a lot of time in testing out new indexes.   And with this enhancement, you can go back in time to review the previous What-If Analysis Report.  This enhancement came about from a user requesting this feature.  IBM and Rocket Software are committed to making SQL PA a better product.  If you have ideas on ways to make SQL Performance Analyzer better, you can submit an Aha!