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

Posted on 2003-02-19
Medium Priority
Last Modified: 2012-08-13
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

Question by:Alex_Gould
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 13

Accepted Solution

ghp7000 earned 300 total points
ID: 7987285
you will have to write a c program to do this:
for (int i=1; i<=200; i++)
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.

Author Comment

ID: 7988872

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.

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month9 days, 11 hours left to enroll

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question