Updating the SQL optimization settings

The SQL optimization settings influence the selection of packages and dynamic statements for optimization. The settings also affect how Db2ZAI compares the performance of different access paths. You can keep the default settings or tailor them to achieve the behavior you want.

Before you begin

IFCID 318 data collection must be active in order for dynamic statements to be eligible for automated scoping.
Note: Db2ZAI automatically starts this trace when SQL optimization is started on the target connection.

For an example of updating SQL optimization settings, see Getting started with Db2ZAI SQL optimization.

About this task

The default behavior of Db2ZAI is to automatically selectively enable static packages and dynamic statements to be in processing scope, based on criteria that indicate that those static packages and dynamic statements are good candidates for Db2ZAI. You can, however, choose to manually selectively enable or disable particular packages for Db2ZAI processing. In addition, you can choose which dynamic queries that you want Db2ZAI to optimize by specifying the client user IDs or application names (or both) under which the queries are executed.

Regarding the automated selection of static packages:
  • It is initiated after the -START ML command is issued and then every 24 hours afterward.
  • Each time it runs, the process finds packages of interest to Db2ZAI and puts the first 50 of them in a ranked list of packages that are pending processing.
  • Package ranking is based upon several factors including the number of getpage requests from the table space or index space and the types of queries involved.
  • Only static statements within the package are included in scope and the criteria above only apply to those static statements. Any dynamic statements that execute under the package are put into scope using the process described below.
The automated selection of dynamic statements involves:
  • Monitoring of IFCID 318 data to determine the dynamic statements with the highest CPU consumption.
  • Selection of the top 5 dynamic statements in terms of highest CPU consumption in a monitoring cycle.
  • Once a dynamic statement is selected for processing, it remains in scope unless you explicitly exclude it.
Note: The following restrictions apply to the selection of dynamic statements:
  • Queries that use the 'CONCENTRATE STATEMENTS WITH LITERALS' option
  • REOPT(AUTO)
Note: The following restrictions apply to the selection of both static and dynamic statements:
  • Queries that involve temporal tables
  • Constant literal in a predicate on a high cardinality column
  • Simple query on a single table using fully-matched unique index

In determining the right mix of CPU vs. elapsed time weighting for your environment, an important factor to consider is the nature of your application. While a 50/50 balance of CPU vs. elapsed time weighting would work when the environment contains a broad mix of online and batch processes, if your environment contains a predominance of one or the other, you might want to change the setting. If the response time of your queries is important, as, for example, with an application that relies upon user responses (as opposed to a batch program), you may tend to give greater weight to elapsed time. In cases where you are running a batch process, where it might be more important to reduce CPU usage, you might want to weight CPU more heavily.

To reduce the possibility of unintended results, avoid setting either CPU or elapsed time weight to 100.

Procedure

To update the SQL optimization settings:

  1. 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.
  2. Sign in to Db2® AI for z/OS®.
  3. From the set of available connections, locate the one for which you want to improve SQL performance, and click .
  4. In the upper right corner of the SQL optimization dashboard, click SQL optimization settings. The SQL optimization settings page opens.

Static settings

  1. Click Static to display the static SQL settings.
    1. To automatically select packages for processing by Db2ZAI, set Automatically select static packages to Enabled. This setting is the default.
    2. You can choose to customize the relative performance contribution of CPU and elapsed time in the evaluation of access paths. In the Performance comparison settings for static packages section, move the slider to one side or the other to increase the relative contribution of CPU or elapsed time. You can also use the ➕ and ➖ controls to make fine adjustments.
      Note: The weighted formula indicates the relative performance contribution of CPU versus elapsed time in the comparison of the access paths. It is composed of two percentages that add up to 100 percent. For example, a setting of 50% CPU and 50% elapsed time indicates equal weighting between the two.
    3. You can choose to enable Confidence level. When enabled, Db2ZAI will determine the degree of confidence in the result of access path comparisons. Confidence level is enabled by default.
    4. You can specify the Confidence level threshold %, which is the threshold value below which actions should not be taken by Db2ZAI. The default threshold value is 90%. This means that an access path comparison that determines that a regression occurred should not cause Db2ZAI to take action to resolve the regression unless the confidence level is at least 90% that an access path regression has actually occurred.
    5. You can specify the Improvement performance threshold %, which is the smallest reduction in the weighted cost of the query that will still be considered an improvement. The default value is -10%, meaning that if the reduction in the weighted cost of the query is less than 10% no action should be taken by Db2ZAI.
    6. You can specify the Regression performance threshold %, which is the smallest increase in the weighted cost of a query that will be considered a regression. The default value is 10%, meaning that if the reduction in the weighted cost of the query is less than 10% no action should be taken by Db2ZAI.
    7. In the Access path regression detection settings section, set Automatically rebind static packages to resolve access path regressions to Enabled to rebind packages automatically when a rebind is needed in order to resolve a regression.
      Note: This requires Db2 12 with FL505 (or higher) enabled or Db2 13.
    8. For the APREUSE option for the rebind command, you can choose how much access path change is acceptable when resolving access path regressions. Click ERROR to ensure that only the access path changes needed to resolve a regression occur. Click WARN to allow regressions to be resolved in cases where one or more access paths cannot be reused.
      Note: If ERROR is selected, Db2ZAI will be unable to resolve the regression if one or more access paths in the package cannot be reused.
  2. Click Apply to save your settings.
  3. Alternatively, or in addition, you can enable static packages individually. Select Show static packages to display a table containing the static packages that are available.
    Find the Collection ID that contains the package you want to enable and click the ˅ to the left of the Collection ID. A table showing the individual packages is displayed. Find the package you want to enable and set the Enabled toggle switch. You can also choose to enable or disable all packages in a particular collection using by clicking the Enabled toggle switch in the Enabled column. When you choose to set the entire collection to Enabled, any packages that are added to the collection in the future are also enabled.
    Note: In order to add a stored procedure package (or any package where the SYSIBM.SYSPACKAGE.TYPE value is 'F', 'N', or '1') into processing scope, a REBIND of that package must be done after the procedure is either a) created, or b) dropped and recreated, or c) regenerated.
    Note: If you enable packages at the Collection ID level, you cannot selectively enable or disable individual packages within that Collection ID. To selectively enable or disable individual packages you must first disable at the Collection ID level and then selectively enable or disable individual packages.
    Tip: You can use the search feature to find the collection ID you're looking for. You can search by collection ID or by package name (or both).

Dynamic settings

  1. Click Dynamic to display the dynamic SQL settings.
    1. To automatically select dynamic statements for processing by Db2ZAI, set Automatic dynamic statement selection to Enabled. This setting is the default.
    2. You can specify the minimum number of times the statement has to run before Db2ZAI enables automated scoping. In the Number of execution threshold field, enter the execution threshold. You can also use the ➕ and ➖ controls to adjust the execution threshold. The default value is 100.
    3. You can choose to specify how often Db2ZAI considers selecting more statements. In the Time interval (sec) field, enter the time interval. You can also use the ➕ and ➖ controls to adjust the time interval. The default interval is 7200 seconds.
    4. You can choose to customize the relative performance contribution of CPU and elapsed time in the evaluation of access paths. In the Performance comparison settings for dynamic packages section, move the slider to one side or the other to increase the relative contribution of CPU or elapsed time. You can also use the ➕ and ➖ controls to make fine adjustments.
      Note: The weighted formula indicates the relative performance contribution of CPU versus elapsed time in the comparison of the access paths. It is composed of two percentages that add up to 100 percent. For example, a setting of 50% CPU and 50% elapsed time indicates equal weighting between the two.
    5. You can choose to enable Confidence level. When enabled, Db2ZAI will determine the degree of confidence in the result of access path comparisons. Confidence level is enabled by default.
    6. You can specify the Confidence level threshold %, which is the threshold value below which actions should not be taken by Db2ZAI. The default threshold value is 90%. This means that an access path comparison that determines that a regression occurred should not cause Db2ZAI to take action to resolve the regression unless the confidence level is at least 90% that an access path regression has actually occurred.
    7. You can specify the Improvement performance threshold %, which is the smallest reduction in the weighted cost of the query that will still be considered an improvement. The default value is -10%, meaning that if the reduction in the weighted cost of the query is less than 10% no action should be taken by Db2ZAI.
    8. You can specify the Regression performance threshold %, which is the smallest increase in the weighted cost of a query that will be considered a regression. The default value is 10%, meaning that if the reduction in the weighted cost of the query is less than 10% no action should be taken by Db2ZAI.
  2. Click Apply to save your settings.
  3. You can selectively disable dynamic statements that were automatically selected. Select Show dynamic statements to display a table containing the available dynamic statements.
    Find the statement you want to disable and set Enabled to the off position.
  4. You can choose which dynamic queries that you want Db2ZAI to optimize by specifying the client user IDs or application names (or both) under which the queries are executed. Select Show user IDs and application names to display a table containing user IDs and applications.

    To enable optimization for dynamic queries for a particular user ID or application, enter the client user ID or application name, and click Add.

    Note: If you are manually selecting which dynamic queries to put into scope, you may want to disable the Automatic selection of dynamic statements. This is so that you retain control over which queries are considered for processing. If you don't disable Automatic selection of dynamic statements, other statements that you haven't manually selected will be put into scope.
    Note: When adding a client user ID or client application name, be aware that the values are case sensitive, such that a lower case client user ID is not considered the same as an upper case client user ID. For example, client user ID user01 is not the same as client user ID USER01. Be sure to match the case of the client user ID value that is actually used to connect to Db2.