You are here

Home

DB2 UDB

Database Tuning Tips for EIM - DB2 UDB

Use DB2 load replace option when loading EIM tables and turn off table logging if possible.Use separate tablespaces for EIM tables and the base tables.Use large page sizes for the EIM and the larger base tables. A page size of 16KB or 32KB has been found to give good performance in practice. The larger page sizes allow more data to be fitted on a single page and also reduce the number of levels in the index B-tree structures.Similarly, use large extent sizes for both the EIM and the large base tables.

Consider using DMS containers for all Siebel tablespaces. Using raw devices or volumes will further help in improving performance.Ensure that the tablespace containers are equitably distributed across the logical and physical disks and the 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.Reorg tables if data on disk is fragmented. Use reorgchk utility with current statistics to find the fragmented table/index.

Periodically ensure that table and index statistics are collected. Do not use “RUNSTATS” with the “DETAILED” option.Use DB2 snapshot monitors to ensure performance is optimal and to detect and resolve any performance bottlenecks.Log retain can be turned "OFF" during the initial load. But remember to turn it back on before going live.For the EIM and the base tables involved, alter the table to set them to VOLATILE. This will ensure that indexes are preferred over table scans.

 
Consider the following setting for DB2 registry values
 
  • DB2_CORRELATED_PREDICATES = YES
  • DB2_HASH_JOIN = NO
  • DB2_RR_TO_RS = YES
  • DB2_PARALLEL_IO = “*”
  • DB2_STRIPPED_CONTAINERS = ON (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 setting the following setting 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 (for initial EIM loads only, thereafter, during production run set it to between 64 and 256)
  • STAT_HEAP_SZ = 8000
Subscribe to DB2 UDB