Results 1 to 8 of 8

Thread: DB2 log buffer

  1. #1
    Join Date
    Jan 2014
    Posts
    13

    Unanswered: DB2 log buffer

    hi, i read in document that db2 first log transactions in log buffer and when it becomes full , move log record to log files. but in db2 10.5 when i insert or update in table, and see db2pd -d <dbname> -logs , Current LSN increase by 1 after one update or insert. why these log records are written to log files immediately? how i can see log buffer status?

    thanks

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,525
    Provided Answers: 11
    log buffer can also be flushed to disk after commit

    according the doc
    All changes to regular data and index pages are written to the log buffer before being written to disk by the logger process. SQL statement processing must wait for log data to be written to disk:

    On COMMIT
    Until the corresponding data pages are written to disk, because the DB2 server uses write-ahead logging, in which not all of the changed data and index pages need to be written to disk when a transaction completes with a COMMIT statement
    Until changes (mostly resulting from the execution of data definition language statements) are made to the metadata
    When the log buffer is full
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Jan 2014
    Posts
    13
    but Current LSN are increased for uncommited insert or update also. why????? my db have very insert and update and i dont want to write these log record in log files immediately because db I/o's is increased. how can configure it to flush log buffer to disk when it become full, how can see usage percent of logbuffer in db2?
    thanks

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by zohre View Post
    but Current LSN are increased for uncommited insert or update also. why????? my db have very insert and update and i dont want to write these log record in log files immediately because db I/o's is increased. how can configure it to flush log buffer to disk when it become full, how can see usage percent of logbuffer in db2?
    thanks
    You should probably not be worrying about this, and just let DB2 handle it. It is not as big of a deal as you think.

    You could look at the SOFTMAX parm in the database cfg, but I would not recommend that you change it unless you are absolutely sure you know what you are doing. If DB2 does not flush the log buffer to disk at commit time, then there is no guarantee of recovery to the proper unit of work.

    A better solution would be to get the applications to buffer the inserts using one statement that inserts multiple rows, or turn off autocommit and manually force a commit from the application side at the appropriate interval.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2014
    Posts
    13
    i have low tps in DB2 because of direct disk logging , please give me some hint to solve this problem

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by zohre View Post
    i have low tps in DB2 because of direct disk logging
    How did you determine that?
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    zohre,

    I thought that you should seriously investigate the recommendation...
    Quote Originally Posted by Marcus_A View Post
    ...

    A better solution would be to get the applications to buffer the inserts using one statement that inserts multiple rows, or turn off autocommit and manually force a commit from the application side at the appropriate interval.

  8. #8
    Join Date
    Jan 2014
    Posts
    13
    when i inserted 100000 records in table, it finished after 400 seconds but in informix it finished after 50 second. why????? i must commit after any insert or update
    Last edited by zohre; 06-09-14 at 00:49.

Posting Permissions

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