Results 1 to 5 of 5
  1. #1
    Join Date
    May 2012
    Posts
    155

    Unanswered: why ROLLBACK TO SAVEPOINT by deleting

    Hallo everyone,

    as in the title:
    I tried to delete couple of millions of rows on my table via DELETE FROM statement. At a point, my db monitoring showed me that the transaction in the state ROLLBACK TO SAVEPOINT. Does the DELETE statement always do ROLLBACK TO SAVEPOINT?

    The problem was, as the transaction in this state, my mirrorlog was exploding to 98%. I had to cancell the transaction to get the mirrorlog down.

    Can I solve this problem with NOT LOGGED INITIALLY?

    Thank you.

    Regards,

    Ratna

  2. #2
    Join Date
    May 2012
    Posts
    155
    I dont understand, why db2 did ROLLBACK TO SAVEPOINT, because my DELETE statement was all okay? That s why I asked, does DELETE statement always do ROLLBACK TO SAVEPOINT, not caring if everything goes fine or wrong?

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by ratnalein View Post
    Does the DELETE statement always do ROLLBACK TO SAVEPOINT?
    No. The DELETE statement deletes records. But if it fails, e.g. runs out of log space, the database manager will terminate it and perform a rollback.
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    May 2012
    Posts
    155
    Hallo n_i,

    thanks for the answer, n_i.

    But if it fails, e.g. runs out of log space, the database manager will terminate it and perform a rollback
    So, ROLLBACK TO SAVEPOINT has actually nothing to do with NOT LOGGED INITIALLY option in a table (NOT LOGGED in a temporary table)? If I have NOT LOGGED INITIALLY option activated in the table, DELETE statement will also do ROLLBACK TO SAVEPOINT if it comes to running out of log space?

    Perhaps I might have had no problem, if I would have tried to delete partially, so that it doesnt come to running out of log space?

    Thank you.

    Regards,
    Ratna

  5. #5
    Join Date
    Feb 2012
    Posts
    23
    Please not that NOT LOGGED INITIALLY is non recoverable. So you can´t rollfoward in case of a serious error and you have to restore and rollforward to a point in time.

    When you run out of log space, you should get the message: DIA3609C Log file was full.
    Besides that you should see in the db2diag log that a rollback is issued

    Try deleting partially.

    Kind regards,
    Bert

Posting Permissions

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