Collecting data for access path performance problems
For performance problems that are related to access path issues, you can collect a description of the problem symptoms, the output of the EXPLAIN statement for the query, related data definition statements, and catalog statistics.
Before you begin
- Ensure that the data is well organized and that complete, accurate, and current statistics are available for relevant database objects. For more information, see Maintaining data organization and statistics.
- Try to resolve the access path problem by using the statistics advisor feature of free tools such as Data Server Manager or your own analysis of the statistics, as described in Investigating access path problems.
- To collect data to diagnose performance problems, you need the appropriate Db2 administrative authority.
- Ensure that the latest Db2 maintenance is applied.
About this task
Most access path performance issues and access path performance regressions can be resolved by ensuring that a complete, current, and accurate set of statistics from the RUNSTATS utility is available to Db2. Include the basic statistics that are needed for all database objects, and selectivity statistics that support the particular SQL statement.
If you contact IBM® Support, you can provide information to help diagnose your access path problems.
Tip: Enhanced query tuning capabilities that can help you with this task are available in IBM Db2 Query Workload Tuner for z/OS® and IBM Db2 Administration Foundation for z/OS.
Procedure
To collect access path diagnostic data to send to IBM Support: