Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    22

    Question Unanswered: Questions of buffer pool

    Prefetcher loads pages from disk to buffer pool. All these pages are clean before they are modified. When a page is modified, but this change has not been committed by transaction, this page becomes a dirty page. This dirty page is still in buffer pool and page cleaner writes this dirty page into log. If DB2 use write-ahead to record all changes into log, this page was written into log before it was changed. (Write to log before and after changes??) If the transaction is committed, this dirty page is written into the table which it comes from. If the transaction is rollback, where will the dirty page go? I dont' think it will be written into the table space where it comes from. Do I misunderstand the whole process? I would really appreciate if someone can show me a whole process.


    When database crash, where will the dirty page go if this dirty has not been committed? How does DB2 rebuild buffer pool? I think DB2 will write all buffer pool into a temporary file on hard disk when database crash. Is my thought right? I would really appreciate if someone give a correct point.


    Thanks in advance!

    James
    **************************************
    Make progress everyday
    Mail: hoo.jimmy@gmail.com
    **************************************

  2. #2
    Join Date
    Sep 2003
    Posts
    237
    What gets written to the log is 'before and after images' of the changed record (not a whole page) along with a lot of control information ;transactionid ,page#etc. If a transaction rolls back, db2 goes to the logs gathers all changes belonging to the transaction ; by applying before images the effects of the transaction is reversed; samething happens during crash recovery; db2 goes to the first active log ; gathers all the info; if transactions are committed you can check the relevant pages; each change has a LSN(log sequence#?)and each page carries the latest LSN applied.In oracle where the log pool is limited, before images are stored in rollback or undo tablespace.In db2 if you have a long running transaction and if primary and secondary logs fillup, system stops and asks you to force the longest running transaction.
    mota

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by huyuhui
    Prefetcher loads pages from disk to buffer pool. All these pages are clean before they are modified. When a page is modified, but this change has not been committed by transaction, this page becomes a dirty page. This dirty page is still in buffer pool and page cleaner writes this dirty page into log. If DB2 use write-ahead to record all changes into log, this page was written into log before it was changed. (Write to log before and after changes??) If the transaction is committed, this dirty page is written into the table which it comes from. If the transaction is rollback, where will the dirty page go? I dont' think it will be written into the table space where it comes from. Do I misunderstand the whole process? I would really appreciate if someone can show me a whole process.
    A dirty page is one that has rows that have been changed but not written to the tablespace on disk. It doesn't matter whether or not it has been commited.

    Quote Originally Posted by huyuhui
    When database crash, where will the dirty page go if this dirty has not been committed? How does DB2 rebuild buffer pool? I think DB2 will write all buffer pool into a temporary file on hard disk when database crash. Is my thought right? I would really appreciate if someone give a correct point.
    No this does not happen (write all buffer pool into a temporary file on hard disk). DB2 can rebuild the tablespace pages to correctly reflect all commited transactions (and back-out uncommited transactions) when DB2 does a crash recovery (which happens when you restart DB2 after a crash). DB2 uses the transaction log to do this (which has all commited transactions written to disk on the log). DB2 does not rebuild the entire buffer pool to exactly the way it was just before a crash, but it does correctly rebuild the tablespaces (on disk or in the bufferpool).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Something else to remember: if you simply pull the plug from your machine, DB2 doesn't even have a chance to write something to disk. It just crashes right away.

    The buffer pool just contains copies of the data pages and, therefore, loosing it doesn't loose any important information.
    Last edited by stolze; 05-26-07 at 06:19.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    The buffer pool just contains copies of the data pages and, therefore, loosing it doesn't loose any important information.
    The bufferpool could contain data that has not been written to the tablespace. However, the tablespace can be reconstructed correctly using the transaction log on disk when crash recovery takes place.

    The point to remember is that upon crash recovery, DB2 rebuilds the tablespace, not necessarily the bufferpool, to the way it should be reflecting commited transactions, and backing out any uncommited transactions.

    DB2 uses the bufferpool to increase performance (reduce synchronous disk I/O) and not to maintain integrity of the data.
    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
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You are correct, Marcus, of course. However, I think that the user doesn't have to worry about those DBMS-internal optimizations to speed up COMMIT operations by implementing a NO-FORCE write-back strategy.
    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
  •