Setting a commit point within a single update statement/bulk updates

Hopefully a dumb question, but is it possible to execute the command "update table tablename set col='val'" and have it commit every 200 rows or so ?

I know the statement is a single UOW and auto-commit will only take effect after the transaction has completed.

I want to update several million rows with the same value so if it fails I only want to roll-back to a sensible point and not right back to the beginning.

If there's a simpler method for doing this, that I've missed, I'd be grateful to know.


Thanks in advance

Alex_GouldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ghp7000Commented:
you will have to write a c program to do this:
do
{
for (int i=1; i<=200; i++)
update....
commit
}
while(not done)
however, you are wasting your time, a several million row  update on one column with a commit charge of 200 will take longer to complete than auto commit on and just run the update, providing your log size space is large enough. So, unless you are talking about hundreds of millions of rows, I don't think you have anything to worry about.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alex_GouldAuthor Commented:
Hi,

Thanks for the clarification.

I figured as much.

It was just so if a process failed overnight (for non-DB2 reasons) then the process would a) not have rollback right to the start, thereby taking more server time and b) leave a portion left to update the following day.

It's a pretty much one-off process we're going through, I was just curious if there was a CLP way that I'd never seen before.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.