OverviewRecovery and ReplicationApplication Management

Performance tuning a DB2 UDB database is in many way an art rather than science. It takes a great deal of experience to recognise where a performance problem might lie and to know how to deal with it effectively. Many performance problems can be anticipated at the development stages of a project and it will often pay dividends to put resources into this area before a database design is finalised and the project goes live.

DB2 performance problems can be caused by a variety of reasons. Contention on database hotspots can cause lock waits or deadlocks. Badly skewed data can cause transactions to take varying amounts of time to complete depending on the data values being processed.

Poorly designed SQL can take hours to run rather than minutes if it had been written properly in the first place. The creation of an index can stop a tablespace scan which might take hours if millions of rows are involved.

The DB2 bufferpools should also be tuned to provide the maximum system performance possible. This would include separating the randomly accessed datasets from the sequentially accessed datasets and the isolation of critical pages within their own pool.