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
 
Db2 fetching again records which are updated

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

New User


Joined: 16 Jul 2008
Posts: 22
Location: Bangalore

PostPosted: Wed Dec 24, 2008 8:54 am    Post subject: Db2 fetching again records which are updated
Reply with quote

Hi,

I have a DECLARE cursor on a tble like below.
NAME MARKS VERSION_NO
--------------------------------
AA 45 1
BB 42 2
CC 43 1

I am fetching these records, and updating the marks field. I also increment the VERSION_NO field after each update.

Problem: After I fetch the first record (AA) and updates the marks, the updated record is fetched again. But strangley this happens only once. I mean the third fetch is my actual 2nd record (BB).


Regards
JK
Back to top
View user's profile Send private message

ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Dec 24, 2008 3:36 pm    Post subject:
Reply with quote

What is your cursor declaration?
Back to top
View user's profile Send private message
harijax

New User


Joined: 16 Jul 2008
Posts: 22
Location: Bangalore

PostPosted: Mon Jan 05, 2009 11:15 am    Post subject:
Reply with quote

I have a join query as

DECLARE CURSOR
SELECT NAME, MARKS
FROM MARKS A, STUDENT B
WHERE A.NAME = B.NAME
AND A.MARKS > 40.


I have an update as below.
UPDATE MARKS SET MARKS = :NEW-marks,
VERSON_NO = VERSIOn_NO + 1
WHERE NAME = :ws-Name
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Mon Jan 05, 2009 11:45 am    Post subject:
Reply with quote

Quote:
I mean the third fetch is my actual 2nd record (BB)
Do you have an ORDER BY NAME clause in your DECLARE CURSOR statement?
Back to top
View user's profile Send private message
harijax

New User


Joined: 16 Jul 2008
Posts: 22
Location: Bangalore

PostPosted: Mon Jan 05, 2009 12:19 pm    Post subject:
Reply with quote

Yes.. i have an ORDER BY NAME.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jan 06, 2009 3:33 pm    Post subject:
Reply with quote

are you sure there is 2nd occurence of the same name ? try using FOR UPDATE cursor....
Back to top
View user's profile Send private message
harijax

New User


Joined: 16 Jul 2008
Posts: 22
Location: Bangalore

PostPosted: Tue Jan 06, 2009 3:42 pm    Post subject:
Reply with quote

Hi Ashimer,

It is a join query first of all. I think FOR UPDATE wont work there.

It is picking.

I followed the below method.

ORDER BY NAME, VERSION_NO DESC.

So after I updated VERSIOn_NO, the updated record was not fetched again.

But my clients ay it is not a good method.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Jan 06, 2009 3:48 pm    Post subject:
Reply with quote

Yes you are right .... i overlooked that part ....
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Tue Jan 06, 2009 3:53 pm    Post subject:
Reply with quote

why do you need the student table in the join?
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 sort records based on length exceeds ... maxsubrat DFSORT/ICETOOL 7 Wed Oct 04, 2017 4:48 pm
No new posts Addition of two records on 2 fields amar143 SYNCSORT 8 Tue Oct 03, 2017 11:14 am
No new posts Join records from 2 files with No Dup... Poha Eater DFSORT/ICETOOL 22 Sun Aug 27, 2017 10:35 pm
This topic is locked: you cannot edit posts or make replies. Fetching data from BAI File arunsoods JCL & VSAM 1 Wed Jul 19, 2017 4:28 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm

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