Tuning Linux, UNIX, and Windows database objects
Database objects listed here are the most important for replication performance.
The following are the most important database objects for tuning:
- Bufferpools
- For high performance, you should define multiple bufferpools for replication and your application workload. Define as much bufferpool space as you can for optimal performance.
- Disk system
- Whenever possible, use multiple disk drives to allow for parallel I/O. Use disk controllers with fast-write cache capability.
- Logs
- Whenever possible, use multiple disk drives to allow for parallel I/O. For improved performance, use disk striping for the Db2® logs. Store the logs and the data on different sets of disk drives.
- Spill files
- The file system (the value of the apply_path startup parameter) for the Apply spill files should span across multiple disk drives to allow for parallel I/O. If possible, store the spill files on a set of disk drives that is separate from the data and logs.
- Statistics
- Ensure that database statistics have been set to allow the Db2 optimizer to use indexes for the
replication control tables by running the RUNSTATS utility for all tables. For the CD tables and UOW
table, run the RUNSTATS utility just once, when the CD table contains a large amount of data (that
is, when the cardinality is high enough to guarantee that the Db2 query optimizer will use the
CD-table index for queries, set aside sufficient space for sorts, if needed, from the sort heap, and
create an optimal execution plan). Although the CD tables and UOW table are created with the
VOLATILE keyword, statistics that reflect high cardinality are more likely to produce an optimal
execution plan. Save a good set of
mimic stats
information for possible future use by issuing the following command:db2look -d dbname -a -m -t tablename -o tablename.stat - Table spaces
- Tables that have extensive data changes (for example, CD tables) should use DMS raw-device table spaces for optimal performance. Whenever possible, define table space containers that span multiple disk drives to allow for parallel I/O.