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 is "0<>0" means in DB2 SQL ?

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

New User


Joined: 03 Sep 2007
Posts: 16
Location: India

PostPosted: Mon Jul 05, 2010 4:18 pm    Post subject: what is "0<>0" means in DB2 SQL ?
Reply with quote

Can somebody please explain me the use of "0<>0" predicate in DB2 SQL. I found in my code.

Where .....
AND A.Copy = B.copy
OR 0<> 0

What does this mean ?

Thanks for help.
Back to top
View user's profile Send private message

Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Jul 05, 2010 6:37 pm    Post subject:
Reply with quote

That means only one thing, the condition is always wrong.
The right question here would be "Why do we use it?"

This is a tuning technique to make it a stage 2 predicate and to force an access path.
ex. if there are two indexes on the table and you are qualifying the sql with both the columns, the optimizer might choose any of these indexes. But, when you add OR 1=0 (or 0<>0 in this case) to one of the predicates, it will force the optimizer to take the other index.

Hope this helps.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Jul 05, 2010 7:02 pm    Post subject:
Reply with quote

Deepak Dash,

the easiest way to see the effect of the 0<>0 is to
take the query into spufi, run explain,
modify the query (removing the 0<>0), run another explain (different plan number),
and compare the two explain outputs.

since you omitted part of the where clause,
we really don't know the effect of the additional predicate (0<>0),
other than knowing we have a stage 2 predicate.

As Bharath Bhat said, the effect is to disable the use of the index by the optimizer
forcing either a table space scan or use of another index mentioned in the where clause (which you have omitted).
Back to top
View user's profile Send private message
Deepak Dash

New User


Joined: 03 Sep 2007
Posts: 16
Location: India

PostPosted: Tue Jul 06, 2010 12:56 pm    Post subject:
Reply with quote

Dear Bharath Bhat & dbzTHEdinosauer,

Thank you so much for education. Appreciate your help. Thanks.
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 Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm
No new posts hot to get details when "EXEC CI... Andi1982 CICS 11 Tue Sep 20, 2016 5:01 pm
No new posts Multi row fetch - "for read only... Nileshkul DB2 3 Sun Aug 14, 2016 12:52 am
No new posts "DFSRRC00 vs IKJEFT01" for ... arunsoods All Other Mainframe Topics 5 Fri Aug 12, 2016 9:54 pm
No new posts Null Indicator value -2 means in Embe... deepak_shrivastava DB2 2 Wed Jan 06, 2016 10:27 pm


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