Predicates in SQL statements are classified. These classifications dictate how DB2 processes the predicates and how much data is filtered during the process. These classifications are as follows:
The DB2 stage 1 engine understands your indexes and tables and can use an index for efficient access to your data. Only a stage 1 predicate can limit the range of data accessed on a disk.
The stage 2 engine processes functions and expressions. But, it is unable to access data in indexes and tables directly. Data from stage 1 is passed to stage 2 for further processing. So, stage 1 predicates are more efficient than stage 2 predicates. Stage 2 predicates cannot use an index, and thus cannot limit the range of data retrieved from a disk.
A stage 3 predicate is processed in the application layer. Filtering is performed after the data is retrieved from DB2 and processed in the application. Stage 3 predicates are the least efficient.
The IBM DB2 Performance Monitoring and Tuning Guide contains lists of the various predicate forms, and whether they are stage 1 indexable, stage 1, or stage 2.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|