Migration tuning guidelines

Before you upgrade to a new version of TADDM, you can complete the following guidelines for better performance.

  1. Do not limit the number of physical disk drives available to your database based on storage capacity alone.
  2. Place the following components on separate disk drives:
    • Application data
    • Database logs
    • Database temporary space for sort and join operations
    For the optimal number of disk drives, see Table 4.
  3. Use the fastest disks available for your database operations and log files.
  4. Enable write caching on disk volumes with the database and log files.

    Enable write caching only if the cache is nonvolatile and can stand unexpected power outages and other failures. It is useful to gauge performance of a database volume by measuring the IOPS (I/Os per second) rate. Measuring the IOPS rate works best with only one database volume allocated per a physical volume. A typical Fibre Channel attached disk manages roughly 150 IOPS before it starts queuing. When a physical disk approaches the mark of 150 IOPS, you can add additional disk and database volumes to the database configuration. Operating system tools, such as iostat or filemon, can measure the IOPS rate for physical volumes. Advanced storage adapters or subsystems that use solid-state drive (SSD) technologies can improve TADDM server database performance.

    The SSD devices are suited for random IOPS workloads. Some subsystems can dynamically manage the IOPS workload for better performance of the SSD devices and the physical disk drives. It is better to have many small capacity physical disks than few large capacity ones with the same rotation speed.

  5. Enable asynchronous I/O at the operating system level.
  6. The following DB2® settings provide good results in the lab environment for enterprise size database that consists of 10,000,000 CIs:
    DB2 9.7, and 9.8
    db2 update dbm config using UTIL_IMPACT_LIM 95

    db2 update db config using SORTHEAP 8000
    db2 update db config using DBHEAP 8000
    db2 update db config using APPLHEAPSZ 2000
    db2 update db config using UTIL_HEAP_SZ 8000
    Additionally, you can change the buffer pools to the following values:
    db2 alter bufferpool IBMDEFAULTBP size 240000
    db2 alter bufferpool BUF8K size 40000
    db2 alter bufferpool BUF32K size 8000
    DB2 10.1, and later:
    db2 update dbm cfg using UTIL_IMPACT_LIM 95
    db2 update dbm cfg using SHEAPTHRES 0

    db2 update db config using SORTHEAP AUTOMATIC
    db2 update db config using SHEAPTHRES_SHR AUTOMATIC
    db2 update db config using DBHEAP AUTOMATIC
    db2 update db config using APPLHEAPSZ AUTOMATIC
    db2 update db config using UTIL_HEAP_SZ AUTOMATIC
    Additionally, you can change the buffer pools to the following values:
    db2 alter bufferpool IBMDEFAULTBP size AUTOMATIC
    db2 alter bufferpool BUF8K size AUTOMATIC
    db2 alter bufferpool BUF32K size AUTOMATIC