Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6970
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
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am
No new posts Changeman get multiple "Browse C... elixir1986 Compuware & Other Tools 4 Fri Jul 14, 2017 1:48 am
No new posts Include/omit with "ALL" pa... mgl DFSORT/ICETOOL 6 Wed Jun 14, 2017 2:31 pm
No new posts READ A PACKED "NEGATIVE" FI... jdesouza CA Products 3 Tue May 02, 2017 11:43 pm
No new posts RC 20 for Address ISPEXEC "ISRED... pkmurali CLIST & REXX 3 Sun Apr 16, 2017 11:30 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us