|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 or 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