IBM SQL Performance Analyzer
By Tom Glaser
SQL PA; Explaining Dynamic Statement Cache
Date: October, 2026
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: We are always looking for ways to make SQL PA a better product. Several companies have expressed interest in being able to explain SQL statements that are stored in the Db2 Dynamic Cache (DSC). This article provides an overview of the DSC and explains how to analyze the SQL statements stored within it. It also reviews how these panels can be used to stabilize or free dynamic SQL statements and queries.
What is Dynamic Statement Cache (DSC): The Dynamic Statement Cache is a memory area where Db2 stores the internal control structures of prepared dynamic SQL statements so they can be reused rather than re-prepared. Instead of repeatedly performing the expensive PREPARE and optimization process, Db2 can reuse the cached version. This speeds of the processing and saves on CPU.
Dynamic SQL is prepared at runtime.
Examples:
- JDBC applications
- ODBC applications
- DRDA workloads
- QMF queries
- SPUFI
- Many web applications
- Stored procedures generating SQL dynamically
Without DSC:
- PREPARE
- EXECUTE
- COMMIT
- PREPARE
- EXECUTE
- COMMIT
Db2 would optimize the same statement repeatedly.
With DSC enabled:
- PREPARE
- EXECUTE
- COMMIT
-
- PREPARE --> Cache Hit
- EXECUTE
Db2 reuses the existing prepared statement and avoids much of the optimization overhead. By sharing these control structures, applications can avoid unnecessary preparation processes and thus improve performance. We are to look at what’s in the DSC by using Omegamon AI for Db2; here’s a screen shot of the SQL statements.
Updating SQL PA:
Once APAR (PH70224) / PTF (UO07482) has been applied to SQL PA, simply go to option 1.10 of SQL PA. Option 10 is the new option for explaining SQL statements in the DSC. A panel similar to the following is displayed:
At the top of the panel, a timestamp shows when the DSN_STATEMENT_CACHE_TABLE was last populated. This indicates the moment when the STMTCACHE data was captured and written to the user’s DSC table. This table is dynamically created in the SQL PA database name that was given when the product was installed. If the table is empty, you need to copy the live Dynamic Statement Cache to your own cache table. To do this, enter the letter E on the TSO command line. The data will be instantly copied into your table with all the SQL statements from the Db2 Dynamic Statement Cache. Here’s my example:
The data is automatically sorted by package name. Notice the numeric values on the lines with the asterisks, such as “***2****”? You can sort the column by typing on the TSO command line:
===> SORT 2 D (sort column 2 in descending order)
Notice the line above the asterisk fields Program, Execb and S, are columns you can filter out data. For example, if you only wanted to see programs/packages starting with ADB, you would enter:
Let’s review the columns on this panel:
FIELDS:
The list area displays the following columns:
L - Line command column. Enter a line command to process the query.
STMT_ID - Identifier of the dynamic statement in the statement cache.
Program - Package name.
Execb - Execution count indicating how many times the statement has been executed.
CPU Time - Total CPU time accumulated for executions of the statement.
Elapsed - Total elapsed time accumulated for executions of the statement.
S - Stabilization indicator.
SQL_Text – The text of the SQL statement. To see more of the SQL statement, you can place your cursor on the text and hit PF11 to scroll to the right. Another option is to enter I for Info, to see the text.
FILTERS:
To limit displayed queries by Program, enter a name such as ABC or ABC% in the Program column.
To limit displayed queries by Execb, enter a numeric value such as:
Program - Package name.
Execb - Execution count indicating how many times the statement has been executed.
S - Stabilization indicator.
To limit displayed queries by the Stabilized field, enter Y, N, %, or a blank in the S column. Queries matching the entered condition will be displayed. If % or a blank is entered, both Y and N will be included.
You can also provide filters to limit which dynamic queries are displayed.
REFRESHING the data:
At the top of the panel, a timestamp shows when the DSN_STATEMENT_CACHE_TABLE was last populated. This indicates the moment when the STMTCACHE data was captured and written to the table. To invoke a command, place the cursor under the command name or type the command on the command line and press Enter. Commands can be abbreviated to the first 1-2 characters.
EXPLAIN_ALL: Performs the command EXPLAIN STMTCACHE ALL, which populates the DSN_STATEMENT_CACHE_TABLE table. You can invoke this command with: E, EX, or EXPLAIN_ALL. This command takes the live SQL statements in the Db2 Dynamic Statement Cache and copies it into the user’s DSN_STATEMENT_CACHE_TABLE table.
DELETE_ALL: Deletes all records from DSN_STATEMENT_CACHE_TABLE table. You can invoke this command with: D, DE, or DELETE_ALL.
Note: If you want to refresh this data, cursor select DELETE_ALL, followed by EXPLAIN_ALL.
LINE COMMANDS: There are 4 line commands available on this panel. To invoke a line command, enter its one-character abbreviation in the column next to the SQL statement and press Enter.
I - Info
Displays a report with additional important fields of the selected query from the statement cache.
S - Stabilize
Stabilizes the query into the appropriate stabilization group.
F - Free
Removes the query from the list of dynamic queries.
E - Explain.
Examples:
INFO:
Stabilize
What does it mean to stabilize an SQL statement? Similar to static SQL statements that are bound in packages and saved in the persistent Db2 catalog and directory, Db2 saves the prepared runtime structures of dynamic SQL statements and their corresponding EXPLAIN and dependency (object and authorization) information, in the persistent new catalog tables, SYSIBM.SYSDYNQRY and SYSIBM.SYSDYNQRYDEP. The source statements in the DSC can be saved in those catalog tables through the new START DYNQRYCAPTURE command.
During subsequent prepared request of the same dynamic statement, Db2 looks in the DSC. If the statement is not found in the DSC (cache miss), Db2 will look up the SYSDYNQRY for a stabilized statement using the same criteria for DSC look up. If a match is found on the catalog table, a running copy of the statement is made along with its dependency information to insert into the DSC and to be executed by the running thread. This process is called loading a stabilized dynamic statement into the DSC, which bypasses the full PREPARE
process altogether.
When the function level is activated to 500 or above, START DYNQUERYCAPTURE (STA DYNQRY for short) can be used to capture qualified statements in the DSC into the catalog tables. The DSNT758 message is issued when attempting to issue the commands before new function activation.
-START DYNQUERYCAPTURE
-STOP DYNQUERYCAPTURE
Looking at the screen above, you’ll notice SQL PA is asking for a group name. This stabilization group name is a user-defined label that logically groups stabilized dynamic SQL statements together. When I press enter from the screen above, a group called SQLPAGRP will be created and the query will be assigned a number. Once you hit enter, you’ll see a message on the top right-hand side showing the group name was created.
You can verify the group name was created and the SQL statement assigned an SQL statement number but backing out of this panel and going to option 1.9 of SQL PA. Enter the stabilized group name and put your cursor the SQL statement word:
Press Enter:
This will show you the SQL statement and statement number in the group:
Free
If you would like to remove this query from being a stabilized query, enter the letter “f” for free:
You will see this message:
Explain
This displays the panel with available explain options to explain the query.
When the user hits enter, you’ll see:
If you select option 1, it refreshes the query in your DSC table. If you enter option 2, it will perform a dynamic explain as you normal would, from option 1.3. Let’s enter option 2:
We are now at the normal option 1.3 single SQL statement explain function. Select option EXPLAIN or type it in, and the query will be explained.
In Summary: This article provided an overview of the new feature, explaining SQL statements from the Db2 Dynamic Statement Cache (DSC). This was added because an individual added this to enhancements and companies voted on it. If you have ideas on ways to make SQL Performance Analyzer better, you can submit an Aha!