Planning for partitioning with DB2® Multisystem

In most cases, you should plan ahead to determine how you want to use partitioning and partitioning keys.

How should you systematically divide the data for placement on other systems? What data do you frequently want to join in a query? What is a meaningful choice when doing selections? What is the most efficient way to set up the partitioning key to get the data you need?

When planning the partitioning, you should set it up so that the fastest systems receive the most data. You need to consider which systems take advantage of symmetric multiprocessing (SMP) parallelism to improve database performance. Note that when the query optimizer builds its distributed access plan, the optimizer counts the number of records on the requesting node and multiplies that number by the total number of nodes. Although putting most of the records on the SMP systems has advantages, the optimizer can offset some of those advantages because it uses an equal number of records on each node for its calculations.

If you want to influence the partitioning, you can do so. For example, in your business, you have regional sales departments that use certain systems to complete their work. Using partitioning, you can force local data from each region to be stored on the appropriate system for that region. Therefore, the system that your employees in the Northwest United States region use contains the data for the Northwest Region.

To set the partitioning, you can use the PTNFILE and PTNMBR parameters of the CRTPF command. Use the Change Node Group Attributes (CHGNODGRPA) command to redistribute an already partitioned file.

Performance improvements are best for queries that are made across large files. Files that are in high use for transaction processing but seldom used for queries might not be the best candidates for partitioning and should be left as local files.

For join processing, if you often join two files on a specific field, you should make that field the partitioning key for both files. You should also ensure that the fields are of the same data type.