By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,234 Members | 1,519 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,234 IT Pros & Developers. It's quick & easy.

begin atomic for call in while fetching rows from cursor

P: n/a
Hello,
I've written a procedure that extracts records from the source table
and migrates them into several other tables. For each source record
several records in destination tables have to be created and updated,
so this operations need to be done in one UoW. Furthermore, the
possible errors should be logged but they shouldn't stop the whole
migration process. So if the migration fails for one source record, the
migration should proceed for next records.
The procedure core looks like this:

declare c cursor for select ... --source records;
fetch from c into ...

while (SQLSTATE = '00000') do
t: begin atomic
call migration_logic(...)
end;
fetch from c into ...

end while;

However when I run the procedure after first rollback, all the
proceeding calls of migration_logic(...) are rolled back with the same
sqlexception. I have no idea what is wrong. I tried to use savepoint
and rollback to savepoint but with the same result. I will appreciate
any help.
Best regards,
Mike

Sep 8 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
You need to have a CONTINUE handler for those failures in the procedure
you want to tolerate.
IMHO, the best place for it would be inside the BEGIN ATOMIC.. END block.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Sep 8 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.