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, identify the areas that will yield the largest performance increases over the widest variety of situations and focus your analysis on those areas.
Many of the examples within this publication illustrate a query written through either an SQL or an OPNQRYF query interface. The interface chosen for a particular example does not indicate an operation exclusive to that query interface, unless explicitly noted. It is only an illustration of one possible query interface. Most examples can be easily rewritten into whatever query interface that you prefer.
Read the Code license and disclaimer information for important legal information.
- What's new for V5R4
The following information was added or updated in this release of the information:
- Printable PDF
Use this to view and print a PDF of this information.
- Query Engine Overview
DB2® UDB for iSeries™ provides two query engines to process queries: the Classic Query Engine (CQE) and the SQL Query Engine (SQE).
- Data access on DB2 UDB for iSeries: data access paths and methods
Data access methods are used to process queries and access data.
- Processing queries: Overview
This overview of the query optimizer provides guidelines for designing queries that will perform and will use server resources more efficiently.
- Optimizing query performance using query optimization tools
Query optimization is an iterative process. You can gather performance information about your queries and control the processing of your queries.
- Creating an index strategy
DB2 Universal Database™ for iSeries provides two basic means for accessing tables: a table scan and an index-based retrieval. Index-based retrieval is typically more efficient than table scan when less than 20% of the table rows are selected.
- Application design tips for database performance
There are some design tips that you can apply when designing SQL applications to maximize your database performance.
- Programming techniques for database performance
By changing the coding of your queries, you can improve their performance.
- General DB2 UDB for iSeries performance considerations
As you code your applications, there are some general tips that can help you optimize performance.
- Database monitor: Formats
This section contains the formats used to create the database monitor SQL tables and views.
- Memory Resident Database Monitor: DDS
The following DDS statements are used to create the memory resident database monitor physical and logical files.
- Query optimizer messages reference
See the following for query optimizer message reference: