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

 

What Rows are sent to DM?

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

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Sat Mar 27, 2010 12:33 am    Post subject: What Rows are sent to DM?
Reply with quote

Hi All,

The following is the process of sql execution -
SQL query is given to RDs, which optimises the query and gives it to DM. Then DM will send it to BM. And B<M in turn will give the pages from Buffer or raise a request for read from table. and then the data goes to DM. WHich applies Stage 1 predicates on it.

The question is, when stage 1 predicate is applied after sending the pages to DM, that means when a call is made to BM, the where clause has not been evaluated so what pages are to be sent to DM, how that is determined, How BM will know what pages to be sent.
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sat Mar 27, 2010 1:53 am    Post subject:
Reply with quote

Tushar,

I think filtering is applied at both times during requesting & sending

Requesting Part
RDS(optimises the sql) --> DM(tries to decrease the amount of data to be accessed) --> BM(get the data)

Sending Part
BM(gets the data) --> DM(tries to decrease the amount of data received from BM by applying STAGE 1) --> RDS(Stage 2 predicates applied)

Sushanth
Back to top
View user's profile Send private message
saurabh39
Warnings : 1

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Sat Mar 27, 2010 4:02 am    Post subject: Reply to: What Rows are sent to DM?
Reply with quote

Hi Sushant,

Quote:
Requesting Part
RDS(optimises the sql) --> DM(tries to decrease the amount of data to be accessed) --> BM(get the data)


If DM tries to decrease the amount of data, then on what basis does it do it, obviously it cannot be the where clause, because that is applied after BM sends the data.

Also, on what basis or criteria BM gets the data. Again where clause is not being used here.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sat Mar 27, 2010 6:52 pm    Post subject:
Reply with quote

Tushar,

DM tries to narrow down its search using indexes & statistics.

Tell me how there can be a search without using WHERE clause, the search without using where clause will be a tablespace scan. Indexes are created to avoid tablespace scan. So, WHERE clause will be considered by DM definately.

Sushanth
Back to top
View user's profile Send private message
saurabh39
Warnings : 1

Active User


Joined: 11 Apr 2008
Posts: 144
Location: Jamshedpur

PostPosted: Sat Mar 27, 2010 11:08 pm    Post subject: Reply to: What Rows are sent to DM?
Reply with quote

Hi Sushant,

If DM applies the stats and index before sending it to BM, does it mean STAGE 1 predicate is applied at DM before sending it to BM?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Sun Mar 28, 2010 4:02 am    Post subject:
Reply with quote

Saurabh,

DM uses the available statistics, indexes and sends that info to BM to retreive rows. And later after BM gets the rows, sends it to DM, this is where Stage 1 is applied.

Sushanth
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 To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Row-Numbers of distinct rows? Auryn DB2 1 Thu Oct 20, 2016 4:38 pm


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