Bookshelf Home | Contents | Index | Search | PDF

Siebel Enterprise Integration Manager Administration Guide > EIM Performance Tuning > Database Optimization Tips for 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:

  Registry Value	Setting
  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:

  Registry Value	Setting
  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:

  Registry Value	Setting
  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

Bookshelf Home | Contents | Index | Search | PDF