Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in


What are the internal processes occurs while executing Query

Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message

New User

Joined: 28 Aug 2006
Posts: 9
Location: pune

PostPosted: Sat Sep 09, 2006 10:47 pm    Post subject: What are the internal processes occurs while executing Query
Reply with quote

what are the internal processes that occurs while executing a qury?
how is the internal organisation in db2.
Back to top
View user's profile Send private message


Active Member

Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Sun Sep 10, 2006 8:47 pm    Post subject: Re: What are the internal processes occurs while executing Q
Reply with quote


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.

Back to top
View user's profile Send private message

Global Moderator

Joined: 27 Dec 2005
Posts: 2360
Location: Israel

PostPosted: Mon Sep 11, 2006 12:11 am    Post subject:
Reply with quote

I believe you are looking for IBM's DB2 introduction?

Back to top
View user's profile Send private message

New User

Joined: 01 Jun 2006
Posts: 23
Location: Sinagpore

PostPosted: Wed Sep 13, 2006 2:33 pm    Post subject:
Reply with quote

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 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).


Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1


Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Easytrieve - Report writing - Only 1 ... Abhi Nature CA Products 2 Wed Sep 14, 2016 11:40 am
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am
No new posts Require help in DB2 Query Shruti Takkar DB2 3 Wed Aug 03, 2016 10:57 pm

Back to Top
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us