Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2011
    Posts
    29

    Unanswered: Archive Logs vs Current Logs DB2 V9 LUW

    I'm having a difficult time understanding the difference between the 2.

    I understand that during crash recovery, active logs are replayed.

    For Eg. The following is contained in the active log:

    UOW on Jan 3:
    * update t1 set salary = 500 where ID = 3
    * update t1 set salary = 300 where ID = 4
    *update t1 set salary = 400 where id = 5
    commit
    update t1 set salary = 300 where id = 3

    When the database goes into crash recovery it will:
    -redo all 4 statements commit 3 of them (the ones with a * beside them) and undo the last one.

    ARCHIVE LOGGING:

    For arguements sake, We have a full backup on Jan 1 and wanted to perform a restore and rollforward to point in time of Jan 3 (Same UOW as above). Today's date is Jan 5.

    I was under the assumption that Archived logs only contain commited transactions, so when I perform the rollforward, why would DB2 still perform the last transcation (update t1 set salary = 300 where id = 3) and then rollback? In other words, why does Archive Logs contain transactions that will not be externalized to the database as Archived logs should only contain transactions that will be externalized to the database unlike the statement.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The logs are written continuously. That means, they contain log records from all transactions - whether committed or rolled back or still in flight. The reason is simply that you wouldn't want DB2 to read all the log records and write them again. Since rolling back transactions is rather rare, DB2 simply processes those log records as usual and then undos the changes.

    Another aspect is that the data pages on disk may already contain changes for a transaction which was rolled back. If a page is evicted from the buffer pool, you don't know if that page contains dirty data. If it does, and the transaction that made the changes did not complete, DB2 has to be able to undo those changes again. For that it needs the log records of the aborted/rolled back transaction.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Quote Originally Posted by sanchez786 View Post
    why does Archive Logs contain transactions that will not be externalized to the database as Archived logs should only contain transactions that will be externalized to the database unlike the statement.
    DB2 archives the log when it's full, not when all transactions in that log have been committed.

  4. #4
    Join Date
    Jul 2011
    Posts
    29
    Quote Originally Posted by db2girl View Post
    DB2 archives the log when it's full, not when all transactions in that log have been committed.
    If that's the case then how can the transaction log ever return a "transaction full"?

    For ex. if you have 8 primary and 2 secondary.

    Then as soon as each primary log (current log) is filled, it is moved to the archive log.

    So not sure how the transaction log can ever be filled?

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by sanchez786 View Post
    If that's the case then how can the transaction log ever return a "transaction full"?

    For ex. if you have 8 primary and 2 secondary.

    Then as soon as each primary log (current log) is filled, it is moved to the archive log.

    So not sure how the transaction log can ever be filled?
    One way it can be "filled" is if the logs cannot be archived for some reason, such as out of space on log archive path, bad path, etc.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Jul 2011
    Posts
    29
    I was under the assumption that the only way your primary and secondary logs can be filled to give you the "Transaction log Full" condition is because all your primary and secondary logs are filled without a commit, therefore, it wasn't able to move them to the archive log.

    DB2GIRL states that once the active log (primary log) is full, it moves the log to archive right away once it's full.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sanchez786 View Post

    DB2GIRL states that once the active log (primary log) is full, it moves the log to archive right away once it's full.
    That's right. However, a log file that contains open transactions cannot be reused, because it may be needed for crash recovery. That's how you run out of log space.

    Manuals are made publicly available for a reason...

    Log file management through log archiving - IBM DB2 9.7 for Linux, UNIX, and Windows

Posting Permissions

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