The Licensed Internal Code and DB2 UDB for AS/400 share the work on access methods. The Licensed Internal Code does the low-level processing which includes selection, join functions, hashing, and index creation.
The query optimization process chooses the most efficient access method for each query and keeps this information in the access plan. The type of access is dependent on the number of rows, the expected number of page faults 1, and other criteria.
You can use the tools and tips that are described later in this book to influence the way in which the query optimizer processes your queries.
The optimizer uses any of the following methods to retrieve data. See Data access methods: overview for a summary of these methods:
Accessing data with the DB2 UDB Symmetric Multiprocessing methods
The DB2 UDB Symmetric Multiprocessing feature provides the optimizer with additional methods for retrieving data that include parallel processing.
Symmetrical multiprocessing (SMP) is a form of parallelism achieved on a single system where multiple processors (CPU and I/O processors) that share memory and disk resource work simultaneously towards achieving a single end result. This parallel processing means that the database manager can have more than one (or all) of the system processors working on a single query simultaneously. The performance of a CPU bound query can be significantly improved with this feature on multiple-processor systems by distributing the processor load across more than one processor on the system.
The following methods are available to the optimizer once the DB2 UDB Symmetric Multiprocessing feature has been installed on your system:
Additional considerations:
The following topics provide additional background information on the access methods: