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

IBM DB2 for z/OS and Siebel EIM

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

Review the following list of tuning tips for Siebel EIM:

• Use the IBM DB2 for z/OS load replace option when loading EIM tables.

  NOTE:  You can also use the IBM DB2 for z/OS 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. Use the REORGCHK command 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. For more information about using the REORGCHK command, see 477378.1 (Article ID) on My Oracle Support. This document was previously published as Siebel FAQ 2072.

  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 for z/OS 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 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 for z/OS 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 for z/OS registry values in Table 8.

  Table 8. IBM DB2 for z/OS 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 for z/OS database manager configuration parameters in Table 9:

  Table 9. IBM DB2 for z/OS Database Manager Configuration Parameter Settings

  Registry Value	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 for z/OS database parameters in Table 10:

  Table 10. IBM DB2 for z/OS Database Parameter Settings

  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 Siebel 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 Siebel EIM loads. During production, set it to between 64 and 256.) The value 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