Evaluating SQL Performance

Db2ZAI evaluates the performance your SQL statements in order to determine whether the performance can be improved or to safeguard against degradation.

Before you begin

You should update the SQL optimization settings (if needed) before evaluating SQL performance. If you find when evaluating SQL performance that the behavior of Db2ZAI needs to be adjusted, you may need to update the SQL optimization settings to get the behavior you want. See Updating the SQL optimization settings.

About this task

After you enable Db2ZAI, Db2® will start collecting query execution history. When an appropriate number of SQL execution records has been collected, Db2ZAI will trigger model training and deployment.

To see a comprehensive list of metrics for both static and dynamic SQL, including the packages and statements that are using Db2ZAI models, packages that are candidates for rebinding, the SQL statements that have been improved by Db2ZAI models, the SQL statements that have regressed, and current status of packages and statements, go to the SQL optimization dashboard.

It's important to keep in mind that the statistics that Db2ZAI displays are cumulative, and thus will change over time.

Assuming that your SQL optimization settings allow it, when Db2ZAI detects that an access path regression has occurred, it automatically reverts to the previously used access path. The previous access path is locked and becomes the new current access path.

Note:

For static SQL, the automatic reversion to the prior good access path requires the activation of Db2 function level V12R1M505. FL505 delivers phase-in REBIND support in Db2. If your Db2 has an activated function level that is less than FL505, Db2ZAI will display a recommendation on the SQL optimization dashboard to manually REBIND the package after a regression is detected.

Db2ZAI will also display a recommendation to manually REBIND the package if function level 505 is activated, but you've chosen to disable automatic rebind for your connection.

After models have been trained and deployed, you will be able to see the model counts on the SQL optimization dashboard.

Procedure

Open a browser window and navigate to https://<hostname>:<port>/, where <hostname> is your Db2ZAI address and <port> is the port you have assigned to Db2ZAI.
Sign in to IBM Db2 AI for z/OS®.
From the list of available connections, locate the one for which you want to evaluate SQL performance, and click the options menu (︙), and click SQL Optimization.
The SQL optimization dashboard is displayed. The default view shows the available data for the top CPU consumers for static SQL.
Use the drop-down at the top left of the page to select the range of the displayed data. You can choose to view the data for:
- All CPU consumers
- Top CPU consumers (the default view)
You can also toggle between viewing data for Static statements or Dynamic statements.

Note:
For static SQL, some statement metrics will be displayed once the packages containing the statements are rebound and executed.

For dynamic SQL, the statement metrics will be updated as they come in and are executed.
If the dashboard indicates that there are rebinds that failed to resolve access path regressions, you can choose to drill down further, to investigate why the rebind failed. Click the arrow (↗) next to the number of rebinds to scroll down to the Static packages table, then click the Analyze rebind output link in the Recommendations column. The rebind output for the package is displayed.

The rebind timestamp, rebind command issued, and the rebind output are shown. You can use this information to investigate possible reasons for the rebind failure.
For static packages, you can drill-down to view the performance data for individual SQL statements inside the package. In the Static packages table, click the package name.

The statements for which data are available are displayed in a following table, along with the information described in the Package statement list in the topic SQL optimization data elements.
You can view detailed data for both static and dynamic statements.

To view the details of a particular static statement, click the STMT_HASHID2 in the package statement table. The Statement details page opens.

To view the details of a particular dynamic statement, click the STMT_HASHID2 in the Dynamic statements table. The Statement details page opens.

To learn more about the various statement details, see SQL optimization data elements.
The Access path performance graph and following Access path metrics table on the Statement details page provide a visual indication of the relative performance of current and previous access paths, in terms of average elapsed time per row and average CPU time per row.

You'll notice that the rows of the access path metrics table are color-coordinated with the Access path performance graph. This is to make it easier to associate the data from the table row with the data shown in the graph.

You can choose to view a complete list of access path history for the statement, or to view an aggregated list. Click Aggregate AP list to view the aggregated list. Click List all AP history to view the full list.

To change the time range of the access path comparison, enter a new start time or date or a new end time or date and click Apply time range. Depending upon the time frame you choose, there may not be data available.
When an access path changes, you can choose to drill down to review the comparison details. In the AP performance comparison column of the Access path metrics table, click Details. The Access path performance comparison details table is displayed.

As with the Access path performance graph and the Access path metrics table, the Access path performance comparison details table carries through the color-coding of the previous and current access paths.

For a description of the data shown by the Access path performance comparison details table, see SQL optimization data elements.
To display the details for an access path (or paths, to view a comparison), in the Access path metrics table, click the checkbox next to the access path, then click Display AP details. The plan details are displayed following the SQL text.
You can choose to lock or unlock an access path for a particular dynamic or static SQL statement, or, for static SQL, all access paths in a package. You might, for example, want to do this when you are satisfied with the performance of the current access paths in a package and therefore do not want them to change.
- To lock all access paths at the package level (static SQL):
  1. In the Static packages table on the SQL optimization dashboard, find the package whose statements you want to lock and click the Lock statements icon at the end of the table row. The Lock access paths dialog is displayed.
  2. Click Confirm to lock the access paths.
- To unlock all access paths at the package level (static SQL):
  1. In the Static packages table on the SQL optimization dashboard, find the package whose statements you want to unlock and click the Unlock statements icon at the end of the row. The Unlock access paths dialog is displayed.
  2. Click Confirm to unlock the access paths.
- To lock an access path for a particular static SQL statement:
  1. In the Static packages table on the SQL optimization dashboard, click the package name that contains the SQL statement you want to lock. The available SQL statements are displayed in the table following the Static packages table.
  2. Click the STMT_HASHID2 for the statement. The Statement details page opens.
  3. In the Access path metrics table, find the access path that you want to lock for the statement, and click the Action menu icon (︙) and select Lock. The Lock access path dialog opens.
  4. Click Confirm to lock the access path.
- To unlock an access path for a particular static SQL statement:
  1. In the Static packages table on the SQL optimization dashboard, click the package name that contains the SQL statement you want to unlock. The available SQL statements are displayed in the table following the Static packages table.
  2. Click the STMT_HASHID2 for the statement. The Statement details page opens.
  3. In the Access path metrics table, find the access path to be unlocked, click the Action menu icon (︙) and select Unlock. The Unlock access path dialog opens.
  4. Click Confirm to unlock the access path.
- To lock an access path for a dynamic SQL statement:
  1. In the Dynamic statements table on the SQL optimization dashboard, find the statement and click the STMT_HASHID2. The Statement details page opens.
  2. In the Access path metrics table, find the access path to be locked, click the Action menu icon (︙) and select Lock. The Lock access path dialog opens.
  3. Click Confirm to lock the access path.
- To unlock an access path for a dynamic SQL statement:
  1. In the Dynamic statements table on the SQL optimization dashboard, find the statement and click the STMT_HASHID2. The Statement details page opens.
  2. In the Access path metrics table, find the access path to be unlocked, click the Action menu icon (︙) and select Unlock. The Unlock access path dialog opens.
  3. Click Confirm to unlock the access path.
You can choose to exclude a static SQL package from Db2ZAI processing, or to include a package that had previously been excluded.
- To exclude a package:
  1. In the Static packages table on the SQL optimization dashboard, find the package that you want to exclude.
  2. Click the Exclude package icon at the end of the table row. The Exclude package dialog is displayed.
  3. Click Confirm to exclude the package.
- To include a package:
  1. In the Static packages table on the SQL optimization dashboard, find the package that you want to include.
  2. Click the Include package icon at the end of the table row. The Include package dialog is displayed.
  3. Click Confirm to include the package.

What to do next

If Db2ZAI has determined that an existing access path can be improved, the package containing the static SQL statement must be rebound.