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:

Generate an EXPLAIN report of the query during a time period when the query performed poorly.
For example, issue the following EXPLAIN statement. Replace query-number with the PLAN_TABLE rows for the query, and replace problem-SQL-statement with the SQL statement.
```
EXPLAIN PLAN SET QUERYNO = query-number FOR

problem-SQL-statement;
```
You can issue the following SQL statement to create a report that describes the access path for the SQL statement:
```
SELECT *
FROM PLAN_TABLE
WHERE QUERYNO = query-number
ORDER BY TIMESTAMP, QUERYNO, QBLOCKNO, PLANNO, MIXOPSEQ;
```
Collect service SQL documentation that describes the Db2 environment for the SQL statement or workload, as described in Collecting service SQL documentation. If possible, collect the service SQL documentation for both before and after the access path problem occurred.