Siebel Performance Tuning Guide > Tuning Siebel EIM > Database Guidelines for Optimizing Siebel EIM >

IBM DB2 and Siebel EIM

This topic is part of Database Guidelines for Optimizing Siebel EIM. It describes Siebel EIM tuning tips for IBM DB2.

Review the following list of tuning tips for Siebel EIM:

• Use the IBM DB2 load replace option when loading EIM tables.

  NOTE:  You can also use the IBM DB2 load option to purge EIM tables. To do this, run the load option with an empty (null) input file in LOAD REPLACE mode. This purges the specified EIM tables instantly.

• Use separate tablespaces for EIM tables and the base tables.
• For large Siebel EIM loads or where many Siebel EIM tasks execute in parallel, place individual EIM tables in separate tablespaces.
• Use large page sizes for EIM tables and for 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 tables and the large base tables.
• 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 Siebel 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 table and base table bufferpools.
• After you load new data, reorganize the tables if the data on a disk is out of cluster. If the results of executing the RUNSTATS command indicate that clustering has deteriorated (clustering index is less than 80% clustered) and that a reorganization of tables is required, then check the system catalog to see if tables need to be reorganized. See also 477378.1 (Article ID) on My Oracle Support. This article contains sample SQL that you can use to determine which tables are out of cluster and need reorganization.

  NOTE:  Allocate time to conversion schedules to allow for the reorganization of tables and the gathering of statistics prior to allowing end users access a system containing new data.

• Use IBM DB2 snapshot monitors to make sure that performance is optimal and to detect and resolve any performance bottlenecks.
• You can turn off logretain during the initial load. However, you must turn it back on before moving into a production environment.

  NOTE:  When logretain is enabled, you must make a full cold backup of the database.

• 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.
• Executing Siebel EIM processes in parallel will cause deadlock and timeout on IBM DB2 databases if multiple Siebel EIM processes attempt to update the same catalog table simultaneously. To avoid this, set the UPDATE STATISTICS parameter to FALSE in the Siebel EIM configuration file (.IFB file).
• Executing UPDATE STATISTICS in each Siebel EIM process consumes significant database server resources. It is recommended that the database administrator updates statistics outside of the Siebel EIM process using the RUNSTATS command.
• Consider the settings for IBM DB2 registry values in Table 7.

  Table 7. IBM DB2 Registry Settings

  Registry Value	Setting
  DB2_CORRELATED_PREDICATES =	YES
  DB2_HASH_JOIN =	NO
  DB2_PARALLEL_IO =	"*"
  DB2_STRIPPED_CONTAINERS =	When using RAID devices for table space containers

• Consider the settings for the IBM DB2 database manager configuration parameters in Table 8:

  Table 8. IBM DB2 Database Manager Configuration Parameter Settings

  Configuration Parameter	Setting
  INTRA_PARALLEL =	NO (can be used during large index creation)
  MAX_QUERYDEGREE =	1 (can be increased during large index creation)
  SHEAPTHRES =	100,000 (depends upon available memory, SORTHEAP setting, and other factors)

• Consider the settings for the IBM DB2 database parameters in Table 9:

  Table 9. IBM DB2 Database Parameter Settings

  Database Parameter	Setting
  CATALOGCACHE_SZ =	AUTOMATIC
  DFT_QUERYOPT =	3
  LOCKLIST =	5000
  LOCKTIMEOUT =	120 (between 30 and 120)
  LOGBUFSZ =	512
  LOGFILESZ =	8000 or higher
  LOGPRIMARY =	20 or higher
  MAXLOCKS =	30
  MINCOMMIT =	1
  NUM_IOCLEANERS =	AUTOMATIC
  NUM_IOSERVERS =	AUTOMATIC
  SORTHEAP =	10240 (This setting is only for initial Siebel EIM loads. During production, set it to between 64 and 256.) The value that you specify for SORTHEAP impacts the result of changing the value for SHEAPTHRES. For example, if SORTHEAP = 10000, then you can execute no more than 9 Siebel EIM batches if you set SHEAPTHRES = 100000. If executing concurrent Siebel EIM batches, then make sure to allocate sufficient physical memory so that memory swapping or memory paging do not occur.
  STAT_HEAP_SZ =	8000