Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009

    Unanswered: update 10 million records

    I need to update 10 million records in Stored Procedure:

    UPDATE table
    SET feild = feild*(CONSTANT_VALUE);

    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)


  2. #2
    Join Date
    Aug 2008
    Toronto, Canada
    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.

  3. #3
    Join Date
    Aug 2008
    Toronto, Canada
    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:

    So, consider taking a backup before and after using the not logged initially option if this is an important table.
    Last edited by db2girl; 07-26-09 at 14:26.

  4. #4
    Join Date
    May 2003
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Sep 2008

    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,

    Quote Originally Posted by assafp76
    2. Is there a way to do a commit every n record? (without a cursor)

    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.

    Last edited by DBFinder; 07-27-09 at 09:11.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts