Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2007

    Unanswered: Bulk update vs Individual update


    If I have readings in a table and want to divide the values by some number, is it true that iterating through the range ( which can be 1 day, 1 week, 1 month, 6 months.. anything) and using the update command for each individual row is a better idea than a bulk update for the whole range? Would it put less of a 'burden' on the server if I go the individual update route?

    here is an example of the command --
    update <tablename> set readingval = readingval / <number> where .....


  2. #2
    Join Date
    Dec 2007
    Richmond, VA
    Provided Answers: 5
    The problems with doing something like this in its entirety is the locking and logging. Can your application handle not being to access a large block of pages in this table (depending on the range you are updating) without your users crying foul? Are your logs large enough to handle all of the logging (again, depending on the size of the range you are dealing with)?
    By running a cursor, then updating each row returned, you can add commit after n updates and restart logic. By doing this you are allowing other users access to the information you are updating either after/before you get to it. The restart logic would allow you to pick back up where you left off if the process were to abort or you had to cancel it for some reason.

  3. #3
    Join Date
    Nov 2007
    thanks for the response.. I forgot to mention that this is being done in a stored procedure and the table that is being accessed is a global temp table. I think the cursor method with commit is probably the best way too.. how can I check if the logs are large enough to handle the logging? will something like this work --

    --update statement
    SET updateStatement = 'UPDATE <table> SET val = val / ? WHERE Id = ? AND
    datetime > ? and datetime <= ?';

    PREPARE s1 FROM updateStatement;

    WHILE (startInterval <= endTime) DO
    EXECUTE s1 USING param_val, param_id, param_start, param_end;

    SET startInterval = --- new start interval
    SET endInterval = ---- new end interval


  4. #4
    Join Date
    Jan 2007
    Jena, Germany
    If it is a temp table, you don't have to care about logging because operations on such tables are not logged.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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