DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
pravinmaliye,
Your question is much to broad. The answer would take volumes to explain, and you probably would not understand it anyway.
If you ask specific questions, I think you will get specific answers here.
Dave |
|
venkatm99
New User
.jpg)
Joined: 01 Jun 2006
Posts: 23
Location: Sinagpore
|
|
|
|
The Two Stages of Processing
In SQL, the WHERE clause establishes the conditions that must be true for a row or rows to qualify and be returned to a program. Within DB2, those WHERE clause predicates are not applied by a single component. The volume of work that must be done in order to evaluate all possible predicate formats is simply far too much for a single program to handle. Also, applying simpler, lower-CPU, shorter path-length predicates before the more difficult predicates creates a performance advantage. More difficult, higher-CPU, longer path-length predicates, if they are executed second, will be executed fewer times because they will be applied to fewer rows: only those rows that are first prequalified by the simpler predicates.
During the parsing stage of the BIND step (whether static or dynamic), SQL is evaluated and the predicates are reordered. Easier, simpler predicates are assigned to a practical, DB2 workhorse component called the Data Manager. Harder, higher-CPU predicates are assigned to the mathematician inside DB2, the Relational Data System (RDS). At run time, during the first stage of processing, the Data Manager will apply the simpler (Stage 1) predicates and prequalify a row. Only those rows that prequalify according to the Data Manager's rules and decision-making ability will proceed to the next stage, the second stage, which will be done by the mathematically minded RDS component.
In short, predicates (WHERE clause conditions) are not applied in the order in which you code them. They are rearranged. And the first step of this rearranging is to separate the Stage 1, once known as sargable predicates, from the Stage 2, once resented and maligned as "nonsargable" predicates. At runtime, the Stage 1 predicates will be applied first, and only those rows that qualify will proceed to Stage 2 predicates will be applied to officially and completely qualify the row or to reject it.
The Data Manager
The Data Manager is the most diverse of the DB2 components. It is responsible for many, many DB2 features and functions, including formatting DB2 log records, requesting the acquisition and release of DB2 locks, enforcing DB2 referential integrity constraints, and many others. I'll focus on the fact that the Data Manager is responsible for most of the predicates that are normal and common in our SQL (such as simple predicates in which a column is compared to another column or a literal or host variable of the same data type and length).
The RDS
The RDS handles sort issues and optimizer duties, is responsible for applying the unusual, uncommon, and, at the risk of offending, "weird" predicates (such as concatenation, most scalar functions, data conversion, and arithmetic expressions).
cheers
venkat |
|
