How the GBPCACHE option affects write operations

You can use several options in a CREATE TABLESPACE statement to tell Db2 how you want data to be handled through the group buffer pool.

GBPCACHE CHANGED or ALL

GBPCACHE CHANGED is the default in Db2. For both ALL and CHANGED, the write operations are the same for changed pages: changed pages are written to the group buffer pool before being written to disk.

Recommendation: For LOB table spaces, use the GBPCACHE CHANGED option. Due to the usage patterns of LOBs, the use of GBPCACHE CHANGED can help avoid excessive and synchronous writes to disk and the group buffer pool.

GBPCACHE SYSTEM

This option is allowed only for LOBs. For GBPCACHE SYSTEM page sets, the only pages that are written to the group buffer pool are LOB space map pages. All other data pages are written directly to disk, similar to GBPCACHE NONE page sets.

GBPCACHE NONE

For GBPCACHE NONE page sets, or for page sets defined in a group buffer pool as GBPCACHE(NO), no pages are written to the group buffer pool. The group buffer pool is used only for the purpose of buffer cross-invalidation. At every COMMIT, any pages that were updated by the transaction and have not yet been written are synchronously written to disk during commit processing. This can have a severe impact on performance for most types of transactions.

One potential advantage of not caching pages in the group buffer pool is that data does not have to be recovered from the log if the coupling facility fails. However, because Db2 still depends on the cross-invalidation information that is stored in the group buffer pool, a coupling facility failure still means some data might not be available. That is why specifying an alternate coupling facility in the CFRM policy is recommended. If you are looking for a high availability option, consider duplexing the group buffer pool rather than suffering the performance hit of writing directly to disk at every COMMIT.

Advantage of specifying GBPCACHE(NO) for group buffer pools: You can specify GBPCACHE(NO) on the group buffer pool level instead of on the page set level. Changing the group buffer pool attribute is usually less disruptive than changing the page set attribute. Because the GBPCACHE(NO) attribute takes precedence over the GBPCACHE option on the page set, you can plan to use different types of processing at different times of day. For example, assume that you want to run transactions or queries during the day, but you want to do batch updates at night. Assume also that your batch updates would benefit from no group buffer pool data caching. You can do the following:
  1. Define the table space as GBPCACHE CHANGED and put it into its own buffer pool.
  2. Define the corresponding group buffer pool as GBPCACHE(YES) for daytime processing.
  3. At night, use the ALTER GROUPBUFFERPOOL command to change the group buffer pool to GBPCACHE(NO).
  4. Set the SETXCF START,REBUILD,STRNAME=strname command to enable the new attribute.
  5. In the morning, use the ALTER GROUPBUFFERPOOL command to change the group buffer pool back to GBPCACHE(YES).
  6. Issue the SETXCF START,REBUILD,STRNAME=strname command to enable the new attribute.

Reasons not to cache: A performance benefit is possible for applications in which an updated page is rarely, if ever, referenced again, such as a batch job that sequentially updates a large table. By not caching, you save the costs of transferring data to the group buffer pool and casting out from the group buffer pool. Again, this benefit is at the cost of synchronous disk I/O at COMMIT. To reduce the amount of synchronous disk I/O at COMMIT, you can lower the deferred write thresholds so that Db2 writes more pages asynchronously prior to COMMIT.

To determine if a particular group buffer pool is a candidate for GBPCACHE(NO), look at the group buffer pool statistics. If the ratio of READS, DATA RETURNED / PAGES WRITTEN is less than 1%, this page set might be a good candidate for GBPCACHE(NO), or the page sets using this group buffer pool might be a good candidate for GBPCACHE NONE. You receive the following benefits:
  • Reduced coupling facility costs and faster coupling facility response time
  • Reduced processor time on the host system
  • Better transaction throughput at a small possible cost in higher transaction response time

If you use GBPCACHE NONE, enable DASD Fast Write and set the vertical deferred write threshold (VDWQT) to 0. By setting this threshold to 0, you let deferred writes happen continuously before the COMMIT, thus avoiding a large surge of write activity at the COMMIT.