Enabling inter-partition query parallelism

Inter-partition parallelism occurs automatically based on the number of database partitions and the distribution of data across these database partitions.

• To enable parallelism when loading data:
  The load utility automatically makes use of parallelism, or you can use the following parameters on the LOAD command:

  • CPU_PARALLELISM
  • DISK_PARALLELISM

  In a partitioned database environment, inter-partition parallelism for data loading occurs automatically when the target table is defined on multiple database partitions. Inter-partition parallelism for data loading can be overridden by specifying OUTPUT_DBPARTNUMS. The load utility also intelligently enables database partitioning parallelism depending on the size of the target database partitions. MAX_NUM_PART_AGENTS can be used to control the maximum degree of parallelism selected by the load utility. Database partitioning parallelism can be overridden by specifying PARTITIONING_DBPARTNUMS when ANYORDER is also specified.

• To enable parallelism when creating an index:
  • The table must be large enough to benefit from parallelism
  • Multiple processors must be enabled on an SMP computer.
• To enable I/O parallelism when backing up a database or table space:
  • Use more than one target media.
  • Configure table spaces for parallel I/O by defining multiple containers, or use a single container with multiple disks, and the appropriate use of the DB2_PARALLEL_IO registry variable. If you want to take advantage of parallel I/O, you must consider the implications of what must be done before you define any containers. This cannot be done whenever you see a need; it must be planned for before you reach the point where you need to backup your database or table space.
  • Use the PARALLELISM parameter on the BACKUP command to specify the degree of parallelism.
  • Use the WITH num-buffers BUFFERS parameter on the BACKUP command to ensure that enough buffers are available to accommodate the degree of parallelism. The number of buffers should equal the number of target media you have plus the degree of parallelism selected plus a few extra.
    Also, use a backup buffer size that is:

    • As large as feasible. 4 MB or 8 MB (1024 or 2048 pages) is a good rule of thumb.
    • At least as large as the largest (extent size * number of containers) product of the table spaces being backed up.
• To enable I/O parallelism when restoring a database or table space:
  • Use more than one source media.
  • Configure table spaces for parallel I/O. You must decide to use this option before you define your containers. This cannot be done whenever you see a need; it must be planned for before you reach the point where you need to restore your database or table space.
  • Use the PARALLELISM parameter on the RESTORE command to specify the degree of parallelism.
  • Use the WITH num-buffers BUFFERS parameter on the RESTORE command to ensure that enough buffers are available to accommodate the degree of parallelism. The number of buffers should equal the number of target media you have plus the degree of parallelism selected plus a few extra.
    Also, use a restore buffer size that is:

    • As large as feasible. 4 MB or 8 MB (1024 or 2048 pages) is a good rule of thumb.
    • At least as large as the largest (extent size * number of containers) product of the table spaces being restored.
    • The same as, or an even multiple of, the backup buffer size.