DB2 performance optimization
DB2 has a number of performance optimization capabilities that give you the insight and ability to optimize workload execution. These capabilities can save you money and lower your risks by helping you to do more work with your existing hardware, ensure Service Level Agreements (SLAs) are met or exceeded and increase DBA productivity.
Table partitioning - Enables easy roll-in and roll-out of table data, flexible index placement and better query processing. Table-level administration is more flexible, allowing administrative tasks, such as backing up and restoring individual data partitions, to be performed on individual data partitions.
Database partitioning - Database partitioning enables massively parallel processing. DB2 transparently splits the database across multiple partitions and uses the power of multiple servers to satisfy requests for large amounts of information.
Massively Parallel Processing (MPP) - MPP is recognized as the only real scalable architecture for data warehousing. MPP allows the system to use multiple servers and CPUs to process data in parallel, resulting in a performance improvement through a “divide and conquer” approach. With reduced I/O requirements, both hardware and infrastructure support costs are reduced.
Continuous Ingest - Continuous Ingest allows you to add data to a warehouse through two mechanisms: Pipes and from files. Using files to add data to the warehouse allows for external sources to seamlessly ingest data in the warehouse efficiently and effectively, giving the warehouse the ability to continuously receive data from operational external data sources in a more timely and active fashion. This is seamless due to the fact that it does not make a difference if the files were created by an external application or by DB2. For operational data warehouse environments, it provides for a nearly real-time responsiveness to the changing and dynamic business environment.
Multidimensional Clustering (MDC) - MDC provides a flexible method to continuously and automatically cluster table data in multiple dimensions. This improves query performance by reducing I/O requirements.
Aggregates - It is not unusual for a business user to make the same query time and time again. Materialized query tables (MQTs) improve the performance of such queries by caching the results of these types of queries. When you submit the query again, the database engine can return the data from the MQT.
Multi-Temperature Data Management - Allows for grouping of hot, warm, and cold data on varying storage systems. You can use different storage technologies to help optimize performance at the lowest possible cost. Storage can be allocated depending on data temperature, such as placing hot data on the fastest and most expensive Solid State Disks (SSDs), while other data can reside on less expensive storage systems. Multi-Temperature Data Management is integrated into the DB2 Workload Manager to more easily prioritize queries, not only on "who" submitted the work or "what" type of work it is, but also on "where" the data resides. For faster performance, queries touching only hot data can be assigned higher prioritization than queries touching cold data.
Mixed Workload Management - DB2 is optimized for mixed workloads, or random I/O, which is common in an enterprise data warehouse. The Workload Manager allows administrators to prioritize queries coming from different users and applications, and control the number of underlying resources dedicated to those processes.
Control over Workloads
DB2 performance optimization capabilities give you low overhead, fine grained control over the execution environment, and the ability to execute workloads according to different business priorities.
This level of control can reduce the risk of Service Level Agreement (SLA) failure by prioritizing execution of business-critical workloads and throttle low priority work until it can execute without impacting the high priority work. DB2 pureScale feature provides efficient, highly reliable capabilities that can easily scale as workloads demand.