Db2 fetching again records which are updated

harijax · New User Joined: 16 Jul 2008 Posts: 22 Location: Bangalore

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

ashimer · Posted: Wed Dec 24, 2008 3:36 pm

What is your cursor declaration?

harijax · New User Joined: 16 Jul 2008 Posts: 22 Location: Bangalore

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

Arun Raj · Posted: Mon Jan 05, 2009 11:45 am

harijax · New User Joined: 16 Jul 2008 Posts: 22 Location: Bangalore

Yes.. i have an ORDER BY NAME.

ashimer · Posted: Tue Jan 06, 2009 3:33 pm

are you sure there is 2nd occurence of the same name ? try using FOR UPDATE cursor....

harijax · New User Joined: 16 Jul 2008 Posts: 22 Location: Bangalore

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.

ashimer · Posted: Tue Jan 06, 2009 3:48 pm

Yes you are right .... i overlooked that part ....

dbzTHEdinosauer · Posted: Tue Jan 06, 2009 3:53 pm

why do you need the student table in the join?