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
 

 

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 BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts How to update a portion of text in a ... Bill Woodger DFSORT/ICETOOL 25 Wed Nov 09, 2016 9:41 pm
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
This topic is locked: you cannot edit posts or make replies. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am


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