482 DB2 UDB ESE V8 Performance Guide for High Performance OLTP and BI
pages are split can have a direct impact on the amount of space used by the
index for any given table. The default 50/50 split is most effective for random table
insertion patterns. The new clauses on the CREATE INDEX statement allow the
index split method to be configured to match more defined insertion patterns via
the PAGE SPLIT SYMMETRIC (default), PAGE SPLIT HIGH and PAGE SPLIT LOW
clauses.
A.4.5 Buffer pool memory allocation
Starting in Version 8.1.4, the size for buffer pool memory allocations can be set
using the DB2_ALLOCATION_SIZE registry variable. Setting this variable to a higher
value results in fewer allocations needed to reach the desired amount of memory
allocated to a buffer pool. The potential cost of setting a higher value for this
registry variable is that memory can be wasted if the buffer pool is altered by a
non-multiple of the allocation size. For example, if the value for
DB2_ALLOCATION_SIZE is 8 MB and a buffer pool is reduced by 4 MB, this 4 MB will
be wasted because an entire 8 MB segment cannot be freed.
A.4.6 Page cleaning enhancements
An alternative method of configuring the page cleaning system is supported,
which differs from the default behavior in that page cleaners behave more
proactively in choosing which dirty pages get written out at any given point in
time. This new method of page cleaning differs from the default page cleaning in
two major ways:
1. Page cleaners are not triggered by the chngpgs_thresh database manager
configuration parameter.
2. Page cleaners are not triggered by LSN gap triggers issued by the logger.
To use this new method of cleaning, the DB2_USE_ALTERNATE_PAGE_CLEANING
registry variable must be set to ON. When this variable is set to ON, DB2 uses a
proactive method of page cleaning, writing changed pages to disk, keeping
ahead of LSN gap, and proactively finding victims. Doing this allows the page
cleaners to better utilize available disk I/O bandwidth.
A.4.7 Lock deferral
To improve concurrency, DB2 now permits the deferral of row locks of CS or RS
isolation scans in some situations until a record is known to satisfy the predicates
of a query. By default, when row-locking is performed during a table or index
scan, DB2 locks each row that is scanned before determining whether the row
qualifies for the query. To improve concurrency of scans, it may be possible to
defer row locking until after it is determined that a row qualifies for a query.

Get DB2 UDB ESE V8 non-DPF Performance Guide for High Performance OLTP and BI now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.