Performance Tuning for Content Manager

Chapter 8. Tuning DB2 for Content Manager 179

Other document routing indexes that can improve performance if appropriate are:

db2 create index icmux002040018x on icmut00204001(

notifytime asc);

db2 create index icmux002040019x on icmut00204001(

suspendflag asc,

resumetime asc);

8.2.7 Additional configuration and tuning suggestions

You might want to consider other additional configuration and tuning suggestions.

Some of them (using a striped file system and separating indexes from base

tables) are especially helpful if you need to improve performance on a

high-volume production system. We recommend the following actions:

 Avoid Unicode.

 Use same code pages.

 Use a striped file system for database table spaces.

 Assign indexes to separate table spaces from the base tables.

 Use DB2 multi-page file allocation.

Avoid Unicode

There is a substantial performance penalty to using Unicode for the Library

Server database. Avoid using this feature unless you need the functionality.

Use same code pages

Use the same code page for both Library Server and Resource Manager

databases if possible. Data conversion might be required to map data between

application and database code pages when your application and database do not

use the same code page. This overhead negatively affects performance.

The code page can be specified in the DDL files described earlier.

Use a striped file system for database table spaces

If your data is placed on Redundant Array of Independent Disks (RAID) devices,

use the striped file systems for the database table spaces. If possible, use four to

six or more physical disks. For each table space that uses a RAID device, you

should:

 Define a single container for the table space (using the RAID device).

 Make the EXTENTSIZE of the table space equal to, or a multiple of, the RAID

stripe size.

180 Performance Tuning for Content Manager

 Ensure that the PREFETCHSIZE of the table space is:

– The RAID stripe size multiplied by the number of RAID parallel devices

– A multiple of EXTENTSIZE

 Use the DB2_PARALLEL_IO registry variable to enable parallel I/O for the

table space (DB2 must be stopped and started afterwards):

db2set DB2_PARALLEL_IO=*

Assign indexes to separate table spaces from the base tables

Assign indexes to separate table spaces from the base tables. This can allow for

more efficient use of disk storage by reducing the movement of read/write heads

during index access. You can also create index table spaces on faster physical

devices. Having separate index table spaces gives you the option of assigning

them to a different buffer pool than that being used by the data table spaces. This

might keep the index pages in the buffer longer because they do not compete

with the table data pages.

Use DB2 multi-page file allocation

With the SMS table spaces, when you are inserting or updating a table in the

table space, the file associated with the SMS container is extended one page at a

time, which can lead to a performance degradation if you are inserting large

numbers of rows.

For SMS databases, consider the use of DB2 multi-page file allocation for

improved performance. This is done by running the db2empfa tool. With

multi-page file allocation enabled, disk space is allocated one extent at a time

rather than one page at a time.

This parameter has no effect on DMS table spaces because space is

pre-allocated. Nor does it have any effect on temporary table because all of the

required pages are allocated when the temporary table space is created.

Prior to Version 8.2 of DB2, the default for the MULTIPAGE_ALLOC database

configuration parameter was NO, which caused one page to be allocated at a

time. In Version 8.2, the default setting is YES, which means that a full extent is

allocated at a time by default.

Attention: When you set this parameter to YES for a particular database, you

cannot set it back to NO.