Results 1 to 7 of 7

Thread: Commit count

  1. #1
    Join Date
    Nov 2011
    Posts
    87

    Unanswered: Commit count

    db2 9.7 fp 4 / windows

    I am going to delete millions of rows from 10 tables. Each tabe size is around 20GB. I beleive we will delete 50% of the data from 10 tables.

    This will be done using a stored proc. In your experience, what is the commit count value is suitable for this deletion?

    I am thinking to go with commit count 5000.

    current logsize is 10MB (primary 80 + seconday 40 )
    logbufsiz - 30MB.

    please advise.

    Thanks,

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Normally I would not base it on number of rows, but how long it takes. I would commit just enough rows so that the time does not exceed the lock-wait-timoout. This is assuming that you have enough log space.

    But since you are deleting 50%, and are on V9.7, I would consider copying the rows you want to keep, then TRUNCATE the table, then copy the rows back.

    Andy

  3. #3
    Join Date
    Nov 2011
    Posts
    87

    Thanks, need more infor.

    Thanks Andy.

    It is not possible to do as you suggested. Because there is a big business rules behind the delete calculation.
    We are expecting that the 50% of the rows will be removed. But nobody knows exactly.

    Can you explain me or send me a link to findout how commit count works.
    and how logbuffer/logs handles..and what is the benefit/non benefit doing commit cout very offen.

    Thanks.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    When using a Stored Procedure to do the commit, you will be looping and issuing a commit every x (e.g. 1000) rows using a loop counter.

    The reason you do this is to limit the amount of time the locks are held and the amount of log space needed. How much time will be consumed for each delete depends on the data, triggers, FK constraints, etc. The same for the log space needed. As they say, your mileage will vary. You should test it in a test/development system to see what the optimal number is.

    Andy

  5. #5
    Join Date
    Nov 2011
    Posts
    87

    Big Thank you.

    Thanks Andy. really helpful.

  6. #6
    Join Date
    Nov 2011
    Posts
    334
    Why do you set logbuffer size 3 times of the logfile size?
    When db2 try to flush the log buffer to logfile, it need to switch the file.
    maybe it will degrade the performance。
    and for performance consideration i think the commit count is the bigger the better if you have enough log space。 and you can lock the table in exclusive mode before you delete data to avoid getting lock row by row。

  7. #7
    Join Date
    Nov 2011
    Posts
    87

    good suggesion

    Thanks you fengsun2.
    Someone in the past, suggested to have logbuffer 3 time bigger than logfilesize.

    you are correct, it may effect the performance. I may reduce the size.

    I will check inforcenter for this.

    anyway, thanks for your suggestion.

Posting Permissions

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