Optimizing SQL access paths

This topic presents an overview of setting up Db2ZAI to optimize SQL access paths.

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.
Improving query performance
Use the following procedure to improve the performance of particular SQL statements.
Evaluating query performance
Db2ZAI evaluates the performance your SQL statements in order to determine whether the performance can be improved or to safeguard against degradation.
Deploying recommended access paths
Use the following procedure to implement access paths that have been recommended by Db2ZAI.
SQL optimization data elements
This topic describes the various data elements presented on the Db2ZAI SQL optimization dashboard.
Implementing new access paths for Db2ZAI
After Db2ZAI determines that there is an opportunity to possibly improve upon an existing access path, the package containing the static SQL statement must be rebound.
Replacing optimization hints
Complete these optional tasks in order to reduce or eliminate the use of optimization hints in your queries.
Canceling an SQL optimization exploration process
You can cancel the SQL optimization exploration process should the need arise.
Manually reverting to a previous access path
If the access path that you have isn't what you want, you can change it to another access path that you used previously.
Creating an exploration schedule
You can choose to create an exploration schedule to specify when Db2ZAI will automatically conduct SQL optimization explorations.
Creating a deployment schedule
You can choose to create a deployment schedule to specify when Db2ZAI will automatically implement the results of an exploration.
Generating the SQL optimization summary report
Db2ZAI provides a scalar user-defined function called ‘DSNML.DSN_ML_SQLOPT_SUMMARY_REPORT’, which produces a report that summarizes the activity related to the Db2ZAI SQL optimization feature.