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

IBM DB2 UDB

This topic describes EIM tuning tips for the IBM DB2 UDB database platform.

In addition to the information available here and elsewhere on Siebel Bookshelf, IBM have published a Redbook, Siebel 7.8 with IBM DB2 UDB V8.2 Handbook, which provides additional information about Siebel Business Applications on the IBM DB2 UDB database platform. See http://www.redbooks.ibm.com for more information.

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 table(s) instantly.

• Use separate tablespaces for EIM tables and the base tables.
• For large EIM loads or where many EIM tasks execute in parallel, place individual EIM tables in separate tablespaces.
• 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.
• 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.
• After you load new data, reorganize the tables if the data on a disk is out of cluster. Use the REORGCHK command if the results of executing the RUNSTATS command indicate that clustering has deteriorated (clustering index < 80% clustered) and that a reorganization of tables is required. For more information about using the REORGCHK command, see FAQ 2072 on Siebel SupportWeb.

  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 performance is optimal and to detect and resolve any performance bottlenecks.
• You can turn off logretain during the initial load. However, you should 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 EIM processes in parallel will cause deadlock and timeout on IBM DB2 UDB databases if multiple EIM processes attempt to update the same catalog table simultaneously. To avoid this, set the UPDATE STATISTICS parameter to FALSE in the EIM configuration file (.IFB file).
• Executing UPDATE STATISTICS in each EIM process consumes significant database server resources. It is recommended that the database administrator updates statistics outside of the EIM process using the RUNSTATS command.
• Consider the following settings for DB2 registry values:

  Registry Value	Setting
  DB2_CORRELATED_PREDICATES =	YES
  DB2_HASH_JOIN =	NO
  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: