Tuning Subsystems › Buffer Pools › I/O Requests and Externalization

I/O Requests and Externalization

Synchronous reads are physical pages that are read in one page per I/O. Synchronous writes are pages written one page per I/O. Minimize synchronous read and writes to only what is necessary. If you do not, you will begin to see buffer pool stress. DB2 will begin to use synchronous writes if the IWTH threshold is reached or if two system checkpoints pass without a page being written that has been updated and not yet committed.

Asynchronous reads are several pages that are read per I/O for prefetch operations such as the sequential prefetch, dynamic prefetch, or list prefetch. Asynchronous writes are several pages per I/O for such operations as deferred writes.

Pages are externalized to disk when one of the following occurs:

• DWQT threshold reached
• VDWQT threshold reached
• Data set is physically closed or switched from R/W to R/O
• DB2 takes a checkpoint (LOGLOAD or CHKFREQ is reached)
• QUIESCE (WRITE YES) utility is executed
• If a page is at the top of LRU chain and another update is required of the same page by another process

You can control page externalization by using your DWQT and VDWQT thresholds for best performance and can avoid surges in I/O. You do not want page externalization to be controlled by DB2 system checkpoints because too many pages would be written to disk at one time causing I/O queuing delays, increased response time, and I/O spikes. During a checkpoint, all updated pages in the buffer pools are externalized to disk and the checkpoint is recorded in the log except for the work files.