Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

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

0
Alex_Gould
Asked:
Alex_Gould
1 Solution
 
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
 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now