Optimizing SQL performance
IBM® Z Database Assistant provides SQL analysis and tuning features to help you optimize the performance of SQL applications that work with Db2® for z/OS®.
These features evaluate the following aspects of SQL performance and provide recommendations for improvement:
- Access path efficiency (for existing queries only)
- Index efficiency
- How efficiently an SQL query is written
There are two primary use cases for optimizing SQL queries:
- Optimizing new queries
- You can evaluate the performance of new queries as you are creating them so that you can ensure they perform optimally before you deploy them on your system. Recommendations are provided for indexes and for improving the structure and syntax by rewriting the query. Estimated performance improvements are provided for each recommendation.
The tools for tuning new queries are part of the SQL editor. See Optimizing new SQL queries for instructions.
- Optimizing existing queries
- You can automatically monitor the performance of existing queries to identify the ones that use the most CPU resources. Recommendations are generated for improving the access paths and indexes that are used by these queries and for rewriting these queries to improve their performance. Estimated performance improvements are provided for each recommendation.
The process of optimizing existing queries consists of the following tasks:
Description Instructions Update the SQL tuning settings that control how often tuning-related performance data is collected and the number of queries to evaluate. You can also specify whether to collect data about static SQL, dynamic SQL, or both. Additionally, you can configure how IBM Z Database Assistant compares the performance of different access paths and resolves the regressed access paths. This step is optional. You can keep the default settings or tailor them to achieve the behavior you want.
Updating SQL tuning settings Create schedules for automatically exploring SQL queries to generate recommendations for improving their performance, for deploying recommendations, and for resolving access path regressions. This step is optional. If you do not create schedules for these tasks, you must perform them manually on an ad-hoc basis.
Scheduling SQL optimization tasks Evaluate the performance of SQL query performance and identify the query or queries that are most in need of improvement. Evaluating SQL performance Implement the recommendations.