DB2: Any Workload, Any Data, All the Time

DB2 UDB ESE provides unparalleled performance and scalability to handle the most demanding workloads. Today, workloads are not strictly online transaction programming or data warehousing. More and more, customers are buying or building systems that combine both transaction processing and decision support workloads.

To satisfy the needs of your most complex databases and applications, IBM has extended the rich feature set of DB2 UDB to deliver unparalleled power and scalability to your entire enterprise. This has been accomplished through the features discussed below.

Intelligent Data Distribution

DB2 UDB supports parallel queries through intelligent database partitioning. When a DB2 UDB ESE database is partitioned, DB2 automatically distributes the data across the database partitions, or subsets of the database, which can reside on multiple servers or within a large SMP server. A unique partition map allows DB2 to manage the distribution and redistribution of the data as required.

DB2 UDB uses a shared-nothing architecture that has proven to provide superior scalability, maintenance, and optimization, compared with a shared-disk architecture. The shared-nothing architecture eliminates the overhead of distributed lock management and distributed views required by a shared-disk architecture.

Efficient Optimization

DB2's unrivaled cost-based SQL optimizer makes use of the database and system configuration information to evaluate the potential execution paths for an SQL query and choose the lowest -cost path for execution. DB2 UDB has an enhanced optimizer that supports SQL query rewrite, OLAP SQL extensions, Dynamic Bit Mapped Indexing, and star joins commonly used in data warehousing.

Parallel Everything

In DB2 UDB, access plans are automatically created for parallel execution with standard SQL, and no additional programming is needed. DB2's parallel execution applies to SELECT, INSERT, UPDATE, and DELETE functions. Data scans , joins, sorts, load balancing, table reorganization, data load, index creation, indexed access, backup, and restore can all be performed on all database partitions simultaneously .

Although DB2 can break a query into a number of pieces that can be executed in parallel, the scalability of DB2 with intra-partition parallelism only (i.e., using SMP-type parallelism with a single database image) can be limited or restricted by the underlying operating system or hardware.

Creating multiple database partitions within a larger SMP server (or across multiple servers) has proven to provide better scalability than intra-partition parallelism alone. As the number of CPUs grows, the scalability decreases when using intra-partition parallelism alone. By creating multiple database partitions within the SMP server, the scalability is able to remain almost linear.

Supreme Scalability

As you expand your data warehouse, accommodate more users, and move projects from pilot to production, you'll appreciate the predictably scalable performance of DB2 UDB. Its shared-nothing architecture allows parallel database queries with minimal data transfer between database partitions. Because the number of database partitions has little impact on inter-partition traffic, performance scales in a near-linear fashion when you add more servers to your cluster of servers or add SMP servers to an existing server cluster.

Multi-dimensional clustering (MDC) provides an elegant method of ensuring flexible, continuous, and automatic clustering of data based on multiple dimensions within a table. This can result in significant improvement in the performance of queries, as well as significant reduction ”or even elimination ”in the overhead of data maintenance operations such as table reorganization and index maintenance operations during INSERT, UPDATE, and DELETE operations.

In many of today's database servers, a process or thread is dedicated to each client that connects to a database. For a typical OLTP workload that handles large numbers of connected users who perform relatively short-lived transactions with some delay between subsequent transactions, this puts a heavy load on the database server because system resources are being tied up by client connections that are not performing any work. DB2 UDB Version 8.1 has implemented a connection multiplexing architecture called the Connection Concentrator that will allow users to move from a configuration where the number of connected users is constrained by the physical limitations of the underlying hardware to a scenario where the limiting factor will be based solely on the transaction load and the machine's ability to handle such a load.

Materialized query tables (previously ASTs) allow you to precompute some typical table joins, queries, aggregates, etc., that can be reused by other users. By reusing the result set and not having to rerun the statements each time, the response time is much faster and the resources required are drastically reduced.

Database Partitioning

The DB2 UDB Database Partitioning Feature (DPF) is required in order to partition your DB2 UDB ESE database, either within a single server or across multiple servers. The DPF is a license only and does not require any products additional to DB2 UDB ESE to be installed on your database server to support database partitioning.

In the past, database partitioning was provided by DB2 UDB Enterprise-Extended Edition (EEE), and to partition a database, this product needed to be installed. With DB2 UDB Version 8.1, if you already have DB2 UDB ESE installed and determine that it would be beneficial to partition the database, there is no need to remove or install any software. You need only purchase the DPF for the server(s) where you will create the database partitions.