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.