Introduction

There could be many situations when a non-partitioned database is not an appropriate choice. Many organizations today have high performance demands from their database systems and want to gain more value from their hardware and software.

IBM DB2 EE comes with database partitioning as an alternative option to implement a very scalable and high performance system. Database partitioning is suitable for both Online Transaction Processing (OLTP) and Decision Support Systems (DSS) systems.

You may partition an existent database on a single system, in a shared-everything configuration, or you can use multiple standalone systems in a shared-nothing configuration. For a large SMP system, the first method is the recommended one, because the inter-partition communication can be conducted through shared memory, which is obviously one of the fastest communication methods on the planet.

A shared-nothing configuration is recommended in cases where we have many servers with fewer hardware resources and want to group or distribute tables by different processing type (OLTP or DSS), on these systems. Another benefit of multipartitioning is data segregation by database partition groups; here, we could enumerate: table collocation (tables are collocated when they are stored in the same database partition group with compatible distribution keys), table partitioning combined with database partitioning, and so on.

Another major benefit is the inter-partition parallelism that can effectively boost considerably, the performance of operations such as large batch processing, sorting operations, hash joins, parallel index creations, or rebuilds. However, database partitioning, like many complex systems, needs careful design and testing.

In the following recipes, we will cover the main administrative and functional aspects of database partitioning.