Tools for improving query performance

IBM Data Studio or IBM Data Server Manager

IBM Data Studio and IBM Data Server Manager are sets of tools that help you to perform a wide range of database management activities, including query performance tuning.

Db2 Query Workload Tuner for z/OS

Db2 Query Workload Tuner for z/OS helps database administrators and SQL developers optimize the performance of SQL statements in applications that query Db2 for z/OS subsystems.

IBM OMEGAMON for Db2 Performance Expert on z/OS

IBM IBM OMEGAMON for Db2 Performance Expert on z/OS is a performance analysis, monitoring, and tuning tool for Db2 for z/OS environments.

EXPLAIN statement and EXPLAIN tables

In Db2 for z/OS, EXPLAIN statements produces the following information:

• A plan, package, or SQL statement when it is bound. The output appears in a table that you create, called a plan table.
• The estimated cost of executing a SELECT, INSERT, UPDATE, or DELETE statement. The output appears in a table that you create, called a statement table.
• User-defined functions that are referred to in an SQL statement, including the specific function name and schema. The output appears in a table that you create, called a function table.

Db2 SQL Performance Analyzer for z/OS

Db2 SQL Performance Analyzer for z/OS provides you with an extensive analysis of SQL queries without executing them. This analysis aids you in tuning your queries to achieve maximum performance. Db2 SQL Performance Analyzer helps you reduce the escalating costs of database queries by estimating their cost before execution.

Using Db2 SQL Performance Analyzer for z/OS helps you:

• Estimate how long queries are likely to take
• Prevent queries from running too long
• Analyze new access paths
• Code queries efficiently using hints and tips that the tool provides