Guidelines for tuning DB2 databases

Be sure to review these guidelines before tuning DB2® databases including the DB2 Universal Database (UDB) multi-user version of DB2.

• Ensure that you have enough disks (6–10 per CPU is a good start). The container for each table space should span all available disks. Some table spaces, such as SYSCATSPACE, and those with a small number of tables do not need to be spread across all disks, while those with large user or temporary tables should.
• Buffer pools should use about 75% (OLTP) or 50% (OLAP) of available memory.
• Perform runstats on all tables, including system catalog tables.
• To configure the database manager, use the Configuration Advisor.
• To restrict logging to a separate high-speed disk, specify this disk by using the NEWLOGPATH database configuration parameter.
• To avoid sort overflows, increase the value specified for the SORTHEAP parameter.
• Table space type should be SMS for the system catalog table space and temporary table spaces and DMS raw (device) or file for the rest. Run db2empfa to enable multi-page file allocation for the SMS table spaces; this will allow SMS table spaces to grow an extent at a time (instead of a page), which can speed up heavy insert operations and sorts which spill to disk.
• Set the database transaction per Minute (tpm) parameter to a value closer to your system's events/second figure. At minimum, this value should be set to 3000.
• To reduce database locks, set the isolation level to Uncommitted Read.
• To address performance considerations, set the DB2 statement concentrator (STMT_CONC) setting to OFF.