This feature is only supported on the Sun Solaris Operating Environment.
Due to I/O overhead, prefetching pages from disk is an expensive operation. DB2's prefetching significantly improves throughput when processing can be overlapped with I/O. Most platforms provide high performance primitives to read contiguous pages from disk into discontiguous portions of memory. These primitives are usually called "scattered read" or "vectored I/O". On some platforms, the performance of these primitives cannot compete with doing I/O in large block sizes. By default, the buffer pools are page-based. That is, contiguous pages on disk are prefetched into discontiguous pages in memory. Prefetching performance can be further enhanced on these platforms if pages can be read from disk into contiguous pages in a buffer pool. A registry variable, DB2_BLOCK_BASED_BP, allows you to create a section in the buffer pool that holds sets of contiguous pages. These sets of contiguous pages are referred to as "blocks". By setting this registry variable, a sequential prefetch will read the pages from disk directly into these blocks instead of reading each page individually. This will improve I/O performance. For more information on this registry variable, see the 'Registry and Environment Variables' section of the Administration Guide.
Multiple table spaces of different extent sizes can be bound to a buffer pool of the same block size. There is a close relationship between extent sizes and block sizes even though they deal with separate concepts. An extent is the granularity at which table spaces are striped across multiple containers. A block is the only granularity at which I/O servers doing sequential prefetch requests will consider doing block-based I/O.
Individual sequential prefetch requests use extent-size pages. When such a prefetch request is received, the I/O server determines the cost and benefit of doing each request as a block-based I/O (if there is a block-based area in the buffer pool) instead of the page-based I/O using the scattered read method. The benefit of doing any I/O as block-based I/O is the performance benefit from reading from contiguous disk into contiguous memory. The cost is the amount of wasted buffer pool memory that can result from using this method.
Buffer pool memory can be wasted for two reasons when doing block-based I/O:
The I/O server allows for some wasted pages within each block in order to gain the benefit of doing block-based I/O. However, when too much of a block is wasted, the I/O server will revert to using page-based prefetching into the page area of the buffer pool. As a result, some of the I/O done during prefetching will not be block-based. This is not an optimal condition.
For optimal performance, you should have table spaces of the same extent size bound to a buffer pool of the same block size. Good performance can still be achieved if the extent size of some table spaces is greater than the block size of the buffer pool they are bound to. It is not advisable to bind table spaces to a buffer pool when the extent size is less than the block size.
Both AWE and block-based support cannot be setup for a buffer pool at the same time. If both the DB2_AWE and DB2_BLOCK_BASED_BP registry variables refer to the same buffer pool, precedence will be given to AWE. Block-based support will be disabled in this case and will only be re-enabled once AWE is disabled.
A buffer pool that is using extended storage does not support block-based I/O.
Before working with any of the examples, you will need to know the identifiers for the buffer pools on your system. The ID of the buffer pool can be seen in the BUFFERPOOLID column of the SYSCAT.BUFFERPOOLS system catalog view.
Scenario 1
You have a buffer pool with an ID of 4 that has 1000 pages. You wish to create a block area which is made up of 700 pages where each block contains 32 pages. You must run the following:
db2set DB2_BLOCK_BASED_BP=4,700,32
When the database is started, the buffer pool with ID 4 is created with a block area of 672 pages and a page area of 328 pages. In this example, 32 cannot be evenly divided into 700. This means that the block area size specified had to be reduced to the nearest block size boundary using the following formula:
((block area size))
FLOOR(-----------------) X block size
( (block size) )
( 700 )
= FLOOR(-----------------) X 32
( 32 )
= 21 x 32
= 672
Scenario 2
You have a buffer pool with an ID of 11 that has 3000 pages. You wish to create a block area which is made up of 2700 pages. You must run the following:
db2set DB2_BLOCK_BASED_BP=11,2700
When the database is started, the buffer pool with ID 11 is created with a block area of 2688 pages and a page area of 312 pages. With no value explicitly given for the block size, the default value of 32 is used. In this example, 32 cannot be evenly divided into 2700. This means that the block area size specified had to be reduced to the nearest block size boundary using the following formula:
((block area size))
FLOOR(-----------------) X block size
( (block size) )
( 2700 )
= FLOOR(-----------------) X 32
( 32 )
= 84 x 32
= 2688