Optimizing query performance using query optimization tools

You can use query optimization tools to improve data retrieval time. Use the results of the tools to:

• Change the data access method chosen by the system. See Data access methods: overview.
• Create the correct indexes and use them effectively. See Using indexes to speed access to large tables.

Query optimization is an iterative process. Do the following as needed to optimize your queries.

Gather statistics about your queries:

• Verify the performance of SQL applications with CL commands
• Examine query optimizer debug messages in the job log
• Gather information about embedded SQL statements with the PRTSQLINF command
• Gather statistics about your queries with the database monitor
• Gather statistics about your queries with memory resident database monitor APIs
• View the effectiveness of your queries with Visual Explain
• Use the Operations Navigator SQL Performance monitor.

Control the processing of your queries:

• Change the attributes of your queries with the Change Query Attributes (CHGQRYA) command
• Control queries dynamically with the query options file QAQQINI
• Control long-running queries with the DB2 UDB for AS/400 Predictive Query Governor
• Control parallel processing for queries

Comparing the different tools:

You may want to check out the Query optimization tools: comparison table to learn:

• What information each tool can yield about your query
• When in the process a specific tool can analyze your query
• The tasks each tool can perform to improve your query

For additional tips and techniques:

If you are experienced with query optimization, you may want to refer to a list of General query optimization tips.

Also, the following topics provide programming tips and techniques for optimizing your applications for query performance:

• Application design tips for database performance
• Programming techniques for database performance
• General DB2 UDB for AS/400 performance considerations