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
 
Performance in checking the existance of a record

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

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Thu Jul 10, 2008 5:21 pm    Post subject: Performance in checking the existance of a record
Reply with quote

Hi All

I have a program where there are quite a few cursors and in some places before an operation, i need to check the existance of that row.
for doing that, i have used the following select query:

SELECT 1
INTO :WS-COUNT
FROM SYSIBM.SYSDUMMY1
WHERE EXISTS
(SELECT 1
FROM abcd B
WHERE
B.col1 = :col1
AND B.col2 = :col2

and then i am checking for this variable WS-COUNT..
however, looks like using sysibm.sysdummy1 leads to random scan that is affecting the performance. Can anyone suggest me an alternative??
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jul 10, 2008 5:43 pm    Post subject:
Reply with quote

If you want to chk the existence of a row then a normal select query on your base table shud be sufficient right ??? if return sqlcode is 100 , it means the row is not present ...
Back to top
View user's profile Send private message
darakhshan

New User


Joined: 03 Mar 2008
Posts: 32
Location: India

PostPosted: Thu Jul 10, 2008 6:13 pm    Post subject:
Reply with quote

that is not getting compiled.. it says 'into' clause required.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Thu Jul 10, 2008 6:15 pm    Post subject:
Reply with quote

Just do a normal select ...

Code:


SELECT col1
INTO ws-col
FROM abcd B
WHERE
B.col1 = :col1
AND B.col2 = :col2

Evaluate sqlcode
when 0
when -811
  row exists ..
when 100
  no row

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 How to select record x+7 or x+2 based... JoAnn Kulcyk SYNCSORT 2 Tue Jan 16, 2018 10:49 pm
No new posts query to fetch record which has only ... maxsubrat DB2 12 Mon Dec 11, 2017 5:03 pm
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 6 Thu Nov 23, 2017 3:32 pm
No new posts Extract Record using range of Data scorp_rahul23 DFSORT/ICETOOL 3 Wed Nov 15, 2017 11:54 pm
No new posts Squeeze record left so that zeroes ar... Vignesh Sid SYNCSORT 5 Fri Nov 10, 2017 1:40 pm

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