Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: When does DB2 empty the database logs

    Hi,

    I am testing database logs with DB2 UDB v7.2 on my local Windows 2000 machine.
    I have circular loggin enabled (the default setting).

    According to the documentation DB2 empty the "log buffer" every time the commit is executed. Commit writes the data from "log buffer" to "log files".

    I am just curious when does DB2 empty the "log files"? When does DB2 writes the contents of logs to database? Does commit of each transaction do writting? Is there some check interval (time period) witch triggers writting from logs to database?

    How can I determine "log files" writting to database? Is there some parameter to see that?

    Thanks,
    Grofaty

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: When does DB2 empty the database logs

    My understanding of what happens :

    Any change to the database is first written to the buffer pools and the log buffers … These log buffers are flushed to the disk(ie logs ) when a commit occurs … This is called Write Ahead Logging(WAL) …. This WAL is to ensure that every committed transaction is written to the disk.

    Simultaneously, iocleaners are also active … These processes write the changed pages from the bufferpools to the table pages ….

    Hence it is possible that uncommitted data is written to the tablespace pages … If those uncommitted changes get into the table data page, a subsequent process will rollback the change …. This is where the concept of locking will help in ensuring that uncommitted data is not ‘visible’ to an application ….

    Here, WAL ensures that in case of a database crash, every change that was committed by the database is available to the database or any uncommitted change that was written to the database page is rolled back ….

    I’m sure you will have another question …..


    Originally posted by grofaty
    Hi,

    I am testing database logs with DB2 UDB v7.2 on my local Windows 2000 machine.
    I have circular loggin enabled (the default setting).

    According to the documentation DB2 empty the "log buffer" every time the commit is executed. Commit writes the data from "log buffer" to "log files".

    I am just curious when does DB2 empty the "log files"? When does DB2 writes the contents of logs to database? Does commit of each transaction do writting? Is there some check interval (time period) witch triggers writting from logs to database?

    How can I determine "log files" writting to database? Is there some parameter to see that?

    Thanks,
    Grofaty

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The following is a somewhat “simplified” explanation of some of the issues under discussion.

    DB2 uses the concept of “asynchronous” writes to disk to increase performance. Disk I/O for updated data is kept to a minimum until it is necessary or convenient to do so.

    All data is read by programs from the buffer pools, and if the required page (data or index) is not in a buffer pool, then DB2 puts it in the buffer pool first. In some cases (pre-fetch) DB2 puts data in the buffer pools that is not even asked for yet, if DB2 thinks there is a high probability a process will ask for it very soon.

    When data is moved to the buffer pools, and the buffer pool is already full with existing data, then DB2 uses complex algorithms to determine who gets kicked out to make room for the new pages. If updated pages need to be kicked out, and the updated pages have not previously been written to disk, they are scheduled for write to disk. These updated pages that have not yet been written to disk are called “dirty” pages.

    As mentioned by sathyaram, “iocleaner” processes are frequently checking the buffer pools and may write “dirty” pages to disk when there is a “lull” in the action (other database activity is low), when a certain threshold is reached (the number of total buffer pool pages that are “dirty” reaches a certain percentage), or based on other complex algorithms. Any “dirty” page that is flushed out of the buffer pool to make room for new pages is written to disk. In some cases, a “dirty” page is written to disk, but still remains in the buffer pool (in which case it is no longer ‘dirty”).

    The whole point is to minimize “synchronous” updates to disk while the SQL statement or the commit is processed. Obviously, the write of the log buffer to the log file on disk (for changes to data) at commit time must be ensured, and this disk I/O is synchronous with the commit. But all other disk I/O is usually deferred to increase performance, while at the same time maintaining data integrity.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One point that I forget to mention about the original question. With cricular log files, the log file can be re-used if a commit point has been taken prior to that point in the log. If the log files fill up without any commit, then DB2 can no longer process any more updates.

Posting Permissions

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