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 Copy 4 byte of data from the last rec... arunsoods DFSORT/ICETOOL 9 Fri Oct 06, 2017 12:15 pm
No new posts Updating the Trailer count in variabl... satheshbabur DFSORT/ICETOOL 6 Wed Aug 30, 2017 9:49 pm
No new posts Repeat part of record multiple times Learncoholic DFSORT/ICETOOL 4 Tue Aug 29, 2017 11:33 am
No new posts Read two inputs and write into output... murali.andaluri DFSORT/ICETOOL 6 Wed Jul 26, 2017 7:35 pm
No new posts CONTIG for performance? JPVRoff JCL & VSAM 8 Fri Jun 09, 2017 8:39 am

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