MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

Existence Check

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
sudharsan100
Beginner


Joined: 05 Dec 2006
Posts: 2
Topics: 1
PostPosted: Mon Apr 09, 2007 10:48 pm    Post subject: Existence Check Reply with quote
Hi ,

Could some tell me which of the following existence check query efficient in DB2 V.8 and why ?

(1).
Code:

EXEC SQL                                                   
       SELECT    'X'                                               
          INTO :WS-EXISTS                                     
         FROM SYSIBM.SYSDUMMY1 SYS                           
       WHERE EXISTS                                         
                 (SELECT 'X'                                     
                     FROM XXX
                   WHERE XXX_XX          = :XXX-XX         
                       AND SYS.IBMREQD = SYS.IBMREQD)             
END-EXEC.                                           

(2).
Code:

EXEC SQL                                                   
         SELECT 'X'                                     
             INTO :WS-EXISTS 
            FROM XXX
            WHERE XXX_XXP          = :XXX-XX
            FETCH FIRST 1 ROW ONLY
END-EXEC           

Thanks.
Back to top
View user's profile Send private message
vkphani
Intermediate


Joined: 05 Sep 2003
Posts: 483
Topics: 48
PostPosted: Tue Apr 10, 2007 1:16 am    Post subject: Reply with quote
Efficient in what factors? Performance or Time or something else?

Time to execute your second query is less than that of the first query. So, second query is more efficient if you are taking Time into consideration.
Back to top
View user's profile Send private message Send e-mail
sudharsan100
Beginner


Joined: 05 Dec 2006
Posts: 2
Topics: 1
PostPosted: Tue Apr 10, 2007 2:45 am    Post subject: Reply with quote
While considering into DB2 CPU and Cost factor. Please advise.
Back to top
View user's profile Send private message
bauer
Intermediate


Joined: 10 Oct 2003
Posts: 319
Topics: 51
Location: Germany
PostPosted: Tue Apr 10, 2007 3:55 am    Post subject: Reply with quote
sudharsan100,

did you try the explain command? What are the results of explain ?
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12399
Topics: 75
Location: San Jose
PostPosted: Tue Apr 10, 2007 5:57 am    Post subject: Reply with quote
sudharsan100,

Please SEARCH before posting. This has been discussed earlier. Check this link

http://www.mvsforums.com/helpboards/viewtopic.php?t=1499&highlight=fetch+first

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group