The DB2 UDB for AS/400 query optimizer: overview

This chapter provides guidelines for designing queries that will perform and will use system resources more efficiently. This chapter covers queries that are optimized by the query optimizer and includes interfaces such as SQL, OPNQRYF, APIs (QQQQRY), ODBC, and Query/400 queries. Whether or not you apply the guidelines, the query results will still be correct.

Note:

The information in this chapter is complex. You might find it helpful to experiment with an AS/400 system as you read this chapter to gain a better understanding of the concepts.

If one understands how DB2 UDB for AS/400 processes queries, it is easier to understand the performance impacts of the guidelines discussed in this chapter. There are two major components of DB2 UDB for AS/400 query processing:

• How the system accesses data. See Data access: data access methods.

  These methods are the algorithms that are used to retrieve data from the disk. The methods include index usage and row selection techniques. In addition, parallel access methods are available with the DB2 UDB Symmetric Multiprocessing operating system feature.

• Query optimizer. See How the query optimizer makes your queries more efficient.

  The query optimizer identifies the valid techniques which could be used to implement the query and selects the most efficient technique.