MVSFORUMS.com

prasad.selvam · Beginner Joined: 11 Feb 2008 Posts: 2 Topics: 1

We need to implement mass update capablity within our application in online process. Almost all of our updates are done in stored procedures and the commits are handled by online.

Just wanted to ask the group what would be the best way to go about it.

Lets say that we need to update 100000 rows. Will it be better to make intermdiate commits every X number of updates and return back to the UI so that it will recall SP untill all rows are updated.

Or do all updates in one shot without commiting do a sigle commit at the end.

I think option 1 would be better, but we have a requirement that even if a sigle update fails the entire update process must not happen, I wasnt sure how to handle this without impacting the performance.

prasad

jsharon1248 · Posted: Wed Jan 14, 2009 9:53 am Post subject:

100,000 rows seems like a lot to update in one LUW. It will seem like a lot more when it fails due to contention and you're sitting there waiting for DB2 to ROLLBACK the changes. Your success depends on how much concurrent access is required for the table involved and how long the update will take. If there's no concurrent access, you'll be fine. If there's a high volume of access, you're asking for trouble. One other option would be to LOCK the table in SHARE mode. I'd question the requirement to update 100,000 rows in one LUW in an online app. I don't know the business justification, but that seems like an unreasonable request to me. That sounds like a batch process.

dbzTHEdinosauer · Posted: Wed Jan 14, 2009 10:11 am Post subject:

what kind of 'mass' update are you talking about. how is it triggered?

a mass update is all at the same time. so, if one did not work, none would work. no problem with the backout/commit problem.

withut knowing why you are updating specific rows, can not give you good advice.
_________________
Dick Brenholtz
American living in Varel, Germany

prasad.selvam · Beginner Joined: 11 Feb 2008 Posts: 2 Topics: 1

jim haire · Beginner Joined: 30 Dec 2002 Posts: 140 Topics: 40

I think you answered your own question when you said that a requirement was that if a single update fails the entire update process must not happen.

If you make intermediate commits and then an update fails, you can't rollback. You will have done a partial update.

I agree that 100,000 updates is large for a unit of work...especially for an online process.