Chapter 5. Performance and monitoring 171
Multidimensional clustering (MDC) tables in SMS table spaces
If you plan to store MDC tables in an SMS table space, we strongly recommend
that you use multipage file allocation. See 5.1.7, “Multipage file allocation on
SMS table spaces” on page 167.
The reason for this recommendation is that MDC tables are always extended by
whole extents, and it is important that all pages in these extents are physically
consecutive. Therefore, there is no space advantage to disabling multipage file
allocation; and furthermore, enabling it will significantly increase the chances that
the pages in each extent are physically consecutive.
Improving the performance of the load utility
Increase the UTIL_HEAP_SZ of the database parameter. The load algorithm will
perform significantly better when loading MDC tables because this will reduce
disk I/O during the clustering of data that is performed during the load phase.
Also choose bigger DATA BUFFER sizes when using this option. Because of
extended logging requirements, you should also increase the LOGBUFSZ
database configuration parameter.
5.3 Improvement of the RUNSTATS utility
The RUNSTATS utility has been improved for performance with a sampling
feature that uses only a subset of its data and with the possibility to throttle its
5.3.1 Improved RUNSTATS performance through sampling
Table statistics are used by the query optimizer in selecting the best access plan
for any given query, so it is important that statistics remain current to accurately
reflect the state of a table at any given time. As the activity against a table
increases, so should the frequency of statistics collection. With the increasing
size of databases, it is becoming more important to find efficient ways to collect
Note: The usefulness of block index during query processing depends on the
order of its key parts. The key part order is determined by the order of the
columns encountered by the parser when parsing the dimensions specified in
the ORGANIZE BY clause of the CREATE TABLE statement. Refer to “Block
index considerations for MDC tables” for more information.
Note: Multipage file allocation is the default for newly created databases in
Version 8.2 and later.

Get DB2 UDB V8.2 on the Windows Environment now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.