as400 Query performance and query optimization

The goal of database performance tuning is to minimize the response time of your queries and to make the best use of your server's resources by minimizing network traffic, disk I/O, and CPU time. This goal can only be achieved by understanding the logical and physical structure of your data, understanding the applications used on your server, and understanding how the many conflicting uses of your database may impact database performance.

The best way to avoid performance problems is to ensure that performance issues are part of your ongoing development activities. Many of the most significant performance improvements are realized through careful design at the beginning of the database development cycle. To most effectively optimize performance, you must identify the areas that will yield the largest performance increases over the widest variety of situations and focus your analysis on those areas.

In this topic, you will find the following information

What's new for V5R3
This describes the new topics in V5R3

Print this topic
This describes how to print

Query Engine Overview
This describes query engine overview

Data access on DB2 UDB for iSeries: data access paths and methods
Find out how the server determines the most efficient access method and what factors determine their selection by the server.

Processing queries: Overview
Describes how to design queries that leverage the query optimizer's cost estimation and decision-making rules.

Optimizing query performance using query optimization tools
Describes how you can use query optimization tools to improve data retrieval times by gathering statistics about your queries or controlling the processing of your queries. With the results that these tools provide, you can then change the data access method chosen by the server or create the correct indexes and use them effectively.

Creating an index strategy
Describes the index-based retrieval method for accessing tables and how to create effective indexes by avoiding such things as numeric conversions, arithmetic expressions, character string padding, and the use of like patterns.

Application design tips for database performance
Describes how the correct design of user applications can improve performance. Application design considerations include parameter passing techniques, using live data, reducing the number of open operations, and retaining cursor positions.

Programming techniques for database performance
Describes how the correct programming techniques can improve performance. Among the techniques covered are: using the OPTIMIZE clause, using FETCH n ROWS, using INSERT n ROWS, controlling the database manager blocking, optimizing the number of columns selected with SELECT statements, eliminating redundant validation, and paging interactively displayed data.

General DB2 UDB for iSeries performance considerations
Describes some general server considerations and how they affect the performance of your queries.

Database Monitor DDS
Reference information about database monitor DDS

Query optimizer messages reference
Reference information about query optimizer messages

You can also find more information about the V5R2 query engine in the Preparing for and Tuning the V5R2 SQL Query Engine on DB2 Universal Database™ for iSeries™.