Establishing a performance tuning strategy

The Design Advisor The DB2 Design Advisor is a tool that can help you significantly improve your workload performance. The task of selecting which indexes, materialized query tables (MQTs), clustering dimensions, or database partitions to create for a complex workload can be daunting. The Design Advisor identifies all of the objects that are needed to improve the performance of your workload. Given a set of SQL statements in a workload, the Design Advisor generates recommendations fo...

Read more

Self-tuning memory in partitioned database environments

There are few factors to determine the self tuning feature will tune the system appropriately in partitioned database environment. When self-tuning memory is enabled for partitioned databases, a single database partition is designated as the tuning partition, and all memory tuning decisions are based on the memory and workload characteristics of that database partition. After tuning decisions on that partition are made, the memory adjustments are distributed to the other database partitions to ensure that all database partitions maintain similar...

Read more

How Resource utilization will be done in DB2

Memory allocation Memory allocation and deallocation occurs at various times. Memory might be allocated to a particular memory area when a specific event occurs (for example, when an application connects), or it might be reallocated in response to a configuration change. Below figure shows the different memory areas that the database manager allocates for various uses and the configuration parameters that enable you to control the size of these memory areas. Note that in a partitioned database environment, each database partition has its...

Read more

Performance monitoring tools and methodology

Operational monitoring of system performance Operational monitoring refers to collecting key system performance metrics at periodic intervals over time. This information gives you critical data to refine that initial configuration to be more tailored to your requirements, and also prepares you to address new problems that might appear on their own or following software upgrades, increases in data or user volumes, or new application deployments. Operational monitoring considerations A DB2 database (“DB2”) system provides some excellent sources...

Read more