Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2008
    Posts
    40

    Unanswered: intermediate commit

    Hi All,

    Currently we have a batch process or rather a shell script in which one of the step is to delete al the rows from the table its just a flat delete stmt with no conditions.
    How can i introduce intermediate commit in the existing script.

    Have a nicde day

    Regards
    Sandeep

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are deleting all the rows, you can load replace from /dev/null.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2008
    Posts
    40
    Quote Originally Posted by Marcus_A
    If you are deleting all the rows, you can load replace from /dev/null.
    Hey Marcus,
    Thanks for your reply.

    I did think of this but the reason i want to introduce intermediate comit is that this is causing lot of lock escalations on the table and not releasing locks ,and by commitoing the locks should be released .... I dont thinl load from / dev null will solve my problem ?
    Please comment

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Reason you are getting dead locks now is because delete statement is taking for ever. Do what Marcus said and you will be fine. Your table will be empty and ready to go within seconds. After that do your one time commit.

    Depending on how much data is in the table you can save your self hours of dayli operation.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    p.s. There is no way to issue commit on a middle of the massive delete statement.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Besides that LOAD/IMPORT from /dev/null is easier, you could batch your delete operation. Do a loop that deletes a batch of rows, then commits, then deletes the next batch.

    @cougar: where did the OP mention something about deadlocks?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Stolze, in post #3 he is mentioning locks.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I believe you also know that locks are not the same as deadlocks, of course.

    Typically, the issue with too much locking are:
    - you have many, many fine-granular locks, which means you need a big lock list and you have a negative performance impact just due to acquiring/releasing those tons of locks
    - you may run into lock escalations; which is an expensive operation (performance-wise) and it drastically reduces concurrency
    - holding many locks over a long period of time impacts concurrency in a negative way

    Yeah, deadlocks are another possibility, too...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    You are correct as always I think I have been assuming way too much lately. For some reason I thought that he meant deadlocks.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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