Hi,
I need to update 10 million records in Stored Procedure:

BEGIN
UPDATE table
SET feild = feild*(CONSTANT_VALUE);
END

The probles is performance.
Someone told me it's becuse of the spool file (or log) that get bigger and bigger .He said that only at the end of the update process commit is done.
So, my questions is:
1. Is there a way to prevent writting to the log?
(i tried to write "with none" at the end of the Stored Procedure- it's not working)
2. Is there a way to do a commit every n record? (without a cursor)

thanks,
Assaf

The following link has an example of how to delete with commitcount:
How to DELETE with COMMIT COUNT - bytes

This may give you some idea on how to update with commitcount.


Also, in DB2 LUW you temporary turn off logging for a UOW. Please check the following technote: IBM - How to temporarily turn logging off for operations modifying the table?

Not exactly sure whether this option works with stored proc, but I don't see why it wouldn't as long as the alter table and stored proc with update are executed as one UOW.

May be people who are more familiar with stored proc can offer other suggestions.

Just re-read the technote again and the notes section does mention about stored proc so this option should work for you. Please note that using this option can leave your table inaccessible (if some error occurs during execution or during rollforward). Please check the notes section in the technote and can also read here: http://publib.boulder.ibm.com/infoce.../c0006079.html

So, consider taking a backup before and after using the not logged initially option if this is an important table.

The proper way would be to open a cursor in the SP, and the fetch each row to be updated, and then update or delete the row using the PK of the row. You can do a commit every x number of updates (I would recommend no more than 1000). This allows maximum concurrency with other applications (depending on how often you commit) and logs all the data, although it will not fill up the active log files since you are doing commits (but make sure you have plenty of archive log space or you are using circular logging).

Using this method, you must define the cursor WITH HOLD, so it will not be closed when a commit is issued.

If you are only doing this once, and don't mind taking a backup before and after, then the NOT LOGGED INITIALLY method is acceptable. Unlike earlier versions of DB2, it is no longer required that that table be defined with NOT LOGGED INITIALLY in order to temporarily turn logging off (alter the table with NOT LOGGED INITIALLY). Don't forget to run the script auto-commit turned off, and then do an explicit commit when you are done, and then do a backup when you are finished.

Assaf,

If you are not comfortable with such an SP, I may be able to provide you a template of this SP ?( as Marcus said).
Let me know,

Well, I used a technique ( called chunking ) which is not for all situations. It was with additional sub-query having FETCH FIRST 20000 ROWS ONLY -- and of course repeating it in a for loop in batch file till all rows are done. This woks in some (few) situations. I used it once for update and once in delete. Few other situations it was not feasible because it was creating recursiveness.

I would say SP approach using cursor with hold is most reliable method , especially in production database.


DBFinder