Siebel Enterprise Integration Manager Administration Guide > EIM Performance Tuning > Database Guidelines for Optimizing EIM >
IBM DB2 UDB
- Use the IBM DB2 load replace option when loading EIM tables and, if possible, turn off table logging.
- Use separate tablespaces for EIM tables and the base tables.
- Use large page sizes for EIM and the larger base tables. Previous experience has determined that a page size of 16 KB or 32 KB provides good performance. The larger page sizes allow more data to be fitted on a single page and also reduces the number of levels in the index B-tree structures.
- Similarly, use large extent sizes for both EIM and the large base tables.
- Consider using DMS containers for all Siebel tablespaces. Using raw devices or volumes will further help to improve performance.
- Make sure that the tablespace containers are equitably distributed across the logical and physical disks and across the input/output (I/O) controllers of the database server.
- Use separate bufferpools for EIM tables and the target base tables. Since initial EIM loads are quite large and there are usually no online users, it is recommended to allocate a significant amount of memory to the EIM and the base table bufferpools.
- Reorganize the tables if data on disk is fragmented. Use the reorgchk utility with current statistics to find the fragmented tables or indexes.
- Periodically make sure that table and index statistics are collected. Do not use RUNSTATS with the DETAILED option.
- Use IBM DB2 snapshot monitors to make sure performance is optimal and to detect and resolve any performance bottlenecks.
- Log retain can be turned OFF during the initial load. However, you should turn it back on before moving into a production environment.
- For the EIM tables and the base tables involved, alter the tables to set them to VOLATILE. This makes sure that indexes are preferred over table scans.
- Consider the following settings for DB2 registry values:
|
|
DB2_CORRELATED_PREDICATES = |
YES |
DB2_HASH_JOIN = |
NO |
DB2_RR_TO_RS = |
YES |
DB2_PARALLEL_IO = |
"*" |
DB2_STRIPPED_CONTAINERS = |
When using RAID devices for tablespace containers |
- Consider the following settings for the DB2 database manager configuration parameters:
|
|
INTRA_PARALLEL = |
NO (may be used during large index creation) |
MAX_QUERYDEGREE = |
1 (may be increased during large index creation) |
SHEAPTHRES = |
100,000 (depends upon available memory, SORTHEAP setting, and other factors) |
- Consider the following settings for the database parameters:
|
|
CATALOGCACHE_SZ = |
6400 |
DFT_QUERYOPT = |
3 |
LOCKLIST = |
5000 |
LOCKTIMEOUT = |
120 (between 30 and 120) |
LOGBUFSZ = |
512 |
LOGFILESZ = |
8000 or higher |
LOGPRIMARY = |
20 or higher |
LOGRETAIN = |
NO (only during initial EIM loads) |
MAXLOCKS = |
30 |
MINCOMMIT = |
1 |
NUM_IOCLEANERS = |
Number of CPUs in the database server |
NUM_IOSERVERS = |
Number of disks containing DB2 containers |
SORTHEAP = |
10240 (This setting is only for initial EIM loads. During production, set it to between 64 and 256.) |
STAT_HEAP_SZ = |
8000 |
|