|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