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,

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

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

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.

The program issuing a COMMIT will wait until the write resume is issued.

Andy

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.

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.

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."

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>

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.

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?

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.