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
 

 

Difference between Queries

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

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Fri Jan 22, 2010 3:52 am    Post subject: Difference between Queries
Reply with quote

Hello,

I have a query as follows.

Code:
SELECT T1ID
FROM TABLE1 F
   , TABLE2 R
   , TABLE3 P
WHERE
     T1_CD = :H
 AND T1_ID  = T2_ID
 AND T3_ID  = T1_ID
 AND T3_NB  = T2_NB
 AND EXISTS (SELECT 1
             FROM  TABLE2 B
             WHERE B.T2_ID = R.T2_ID
               AND B.T2_NB = R.T2_NB
               AND T2_CD = :H)


I am planning to rewrite the above query as follows. But I am just worried if there could be any difference between the output of these two queries. Could anyone comment if you can find differences in the way these queries might behave?

Code:
SELECT T1ID
FROM TABLE1 F
   , TABLE2 R
   , TABLE3 P
WHERE
     T1_CD = :H
 AND T1_ID  = T2_ID
 AND T3_ID  = T1_ID
 AND T3_NB  = T2_NB
 AND T2_CD = :H
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 22, 2010 4:18 am    Post subject:
Reply with quote

Hello,

Seems like running a little test in spufi would give the answer immediately. . .

If something unexpected happens, someone here may be able to clarify. . .
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Fri Jan 22, 2010 4:23 am    Post subject:
Reply with quote

Thanks for the reply d.sch.

Indeed i ran a sample run and it gave same results, but as it involved host variables, it might not be the only case. I just wanted to confirm with thoughts from experts as well.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 22, 2010 9:53 am    Post subject:
Reply with quote

Hello,

Quote:
but as it involved host variables, it might not be the only case
I don't understand the concern here. . .

Using host variables should not change the outcome of the query, unless there is something about your question that i misunderstand.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Fri Jan 22, 2010 10:28 am    Post subject:
Reply with quote

I am sorry for not being clear on what I said.

What I meant to say was, I just randomly picked one value for the host variable, and I got same outputs. I was just afraid, if the test might fail for a different value for the host variable. I could not consolidate all possible host variable values to perform an extensive testing in a short time. That is why I preferred to post it here.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Fri Jan 22, 2010 10:40 am    Post subject:
Reply with quote

Hello,

Quote:
I am sorry for not being clear on what I said.
Not to worry - it may take a couple of iterations, but eventually, we usually "get there" icon_smile.gif

While not every specific value can be tested, different conditions can be tested.

I'd make sure that testing was done with values that will have multiple rows returned. . .
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Fri Jan 22, 2010 10:48 am    Post subject:
Reply with quote

yeah.. Thanks d.sch.. I'll try to test maximum cases possible in the short span of time..
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Fri Jan 22, 2010 5:19 pm    Post subject:
Reply with quote

both queries have the same host variable.

the first query probably executes the EXIST against the table for every row queried by the outside select.

the second query just incorporates the conditional check against each row.

did you bother to run an explain?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Jan 22, 2010 5:30 pm    Post subject:
Reply with quote

On the first glance, your first query searches for T2_CD in TABLE2 where there is no dependency on T2_ID and T2_NB.
In second query T2_CD will depend on T2_ID and T2_NB's values which in turn depends on T1_ID and T3_NB.

As dick mentioned use test cases where you get multiple rows; to confirm.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Fri Jan 22, 2010 9:16 pm    Post subject:
Reply with quote

it looks like there could be a difference :
SQL 2 gives the rows of T2 where (T1_ID = T2_ID AND T3_NB = T2_NB) and T2_CD = :H
SQL 1 gives the rows of T2 where (T1_ID = T2_ID AND T3_NB = T2_NB) as long as there is 1 where T2_CD = :H

thus
If T2_ID, T2_NB is unique then the output is the same.
If not then SQL1 can return more rows than SQL2.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Fri Jan 22, 2010 9:29 pm    Post subject:
Reply with quote

Thank you all for your replies..

@Dick Brenholtz - I indeed ran EXPLAIN and got different access path for each of them. I thought it was obvious because we are eliminating a subquery altogether. Hence did not wanted to just depend on it.

@GuyC - You are pretty much correct. This query might depend on the uniqueness of T2_ID and T2_NB and unfortunately in my case they are not unique. So I think it wont work out.

Thank you all once again.
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 Need help with below queries ashek15 DB2 3 Thu Mar 23, 2017 1:38 am
No new posts Difference between TWALENG and TWASIZE Arunkumar Chandrasekaran CICS 3 Tue Jan 03, 2017 12:57 pm
No new posts Sticky: difference between ... DUMMY ... and ... enrico-sorichetti JCL & VSAM 0 Mon Oct 17, 2016 4:31 pm
No new posts Difference space showed for TS and it... autobox DB2 1 Thu Apr 14, 2016 1:07 pm
No new posts Difference Between CICS WebSupport an... sachinji84 CICS 1 Mon Mar 07, 2016 7:25 pm


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