Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2004
    Posts
    306

    Unanswered: Write Suspend and commit

    Hey guys,

    I'm wondering if anyone has much experience with the Set Write Suspend command?

    I'm wondering how likely delays are in an OLTP environment and if an application issues a commit during the suspend window will it always have to wait or can commits sucessfully take place in memory with the tuning of Bufferpools, the Log Buffer and Mincommit?

    The documentation seems pretty vague on the subject :/

    Cheers,

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    When a database is put in write suspend, it is still very much accessible to all operations, as all write operations are done in memory. Even if the bufferpools are very large, it is still a good idea to have the window of write suspend as small as possible. As long as there is enough space in the bufferpools, you will not see any delays with write operations. All write suspend really does is make sure that what is written to disk is in a consistent state. Normal writes are done asynchronously (page cleaners). Putting a database in write suspend just holds up the page cleaning for a while once the disk is in a steady state. Then when write suspend is lifted, the page cleaning is free to continue as usual.

    Andy

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Since a commit involves flushing of log buffers to log files on disk, I think the original question is does the write suspend prevent this from happening?
    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 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by Marcus_A
    Since a commit involves flushing of log buffers to log files on disk, I think the original question is does the write suspend prevent this from happening?
    Yes. All writes to the disk are suspended. This is why it is good to keep the write suspend window as small as possible.

    Andy

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by ARWinner
    Yes. All writes to the disk are suspended. This is why it is good to keep the write suspend window as small as possible.

    Andy
    So, does the calling program get a zero return code on the COMMIT, or does it just hang until it can write the log buffer to disk? Normally if a zero return code is returned for a COMMIT, DB2 guarantees that the logs have been written to disk.
    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 2003
    Posts
    4,292
    Provided Answers: 5
    The program issuing a COMMIT will wait until the write resume is issued.

    Andy

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    from http://www.ibm.com/developerworks/da...204quazi.html:

    ...The suspended I/O feature ensures the prevention of any partial page write by suspending all write operations on the source database. While the database is in write suspend mode, all of the tablespace states change to a new state, SUSPEND_WRITE, and all operations function normally. However, some transactions may wait if they require disk I/O such as flushing dirty pages from the buffer pool or flushing logs from the log buffer. These transactions will proceed normally once the write operations on the database are resumed.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    So in answer to the original question, commits will be suspended and will not be affected at all by tuning of bufferpools or log buffer size. Not sure about Mincommit, but I doubt it would help much (or maybe not all) to tune that either to allow updates to take place during a write suspend.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My answer would be the opposite: Write suspend only affects page writes. Log records are not page writes, so logs can still be written. Thus, a COMMIT operation can succeed because log writing is not impacted at all.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by stolze
    My answer would be the opposite: Write suspend only affects page writes. Log records are not page writes, so logs can still be written. Thus, a COMMIT operation can succeed because log writing is not impacted at all.
    That would be great news. Are you sure about that?

    According to db2girl:

    "However, some transactions may wait if they require disk I/O such as flushing dirty pages from the buffer pool or flushing logs from the log buffer."
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    I tested it and my appl was "hanging" (appl status = "Commit Active") until I issued set write resume. So, flashing of the log records during commit will be suspended.

    db2 +c "insert into test values (1,2)"
    DB20000I The SQL command completed successfully.

    db2 commit
    <it's waiting here>

  12. #12
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by Marcus_A
    That would be great news. Are you sure about that?
    No, not at all. I was merely interpreting the text, which talked about pages. Apparently, that text is not complete (and, hence, mi interpretation) because logs are also affected.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  13. #13
    Join Date
    Jul 2004
    Posts
    306
    Thanks for the feedback guys.... So I'm thinking this make write suspend unsuitable for OLTP applications (which is odd because none of the doco indicates it's not suitable for OLTP) unless you're only going to suspend for a second or so?

  14. #14
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by meehange
    suspend for a second or so?
    Yes, that's the idea. It is intendeded to give you the oppertunity to take a snapshot of you LUN's .

    So when you're not on a SAN/NAS with all your db2 objects or you've never heared of snapshots, this options is useless for you.

  15. #15
    Join Date
    Jul 2004
    Posts
    306
    Quote Originally Posted by dr_te_z
    Yes, that's the idea. It is intendeded to give you the oppertunity to take a snapshot of you LUN's .

    So when you're not on a SAN/NAS with all your db2 objects or you've never heared of snapshots, this options is useless for you.
    That's actually what we're doing, but we're doing a flash copy of several LPARs... so we need several minutes to get it all done.

Posting Permissions

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