DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
The DB2® product supports parallel environments, specifically on symmetric multiprocessor (SMP) machines.
In SMP machines, more than one processor can access the database, allowing the execution of complex SQL requests to be divided among the processors. This intrapartition parallelism is the subdivision of a single database operation (for example, index creation) into multiple parts, which are then executed in parallel within a single database partition.
To specify the degree of parallelism when you compile an application, use the CURRENT DEGREE special register, or the DEGREE bind option. Degree refers to the number of query parts that can execute concurrently. There is no strict relationship between the number of processors and the value that you select for the degree of parallelism. You can specify a value that is more or less than the number of processors on the machine. Even for uniprocessor machines, you can set the degree to be higher than one to improve performance. Note, however, that each degree of parallelism adds to the system memory and processor overhead.
You can also specify the degree of parallelism for workloads using the MAXIMUM DEGREE workload attribute. In the affected workload, values set using MAXIMUM DEGREE will override values assigned by the CURRENT DEGREE special register, or the DEGREE bind option.
Some configuration parameters must be modified to optimize performance when you use parallel execution of queries. In an environment with a high degree of parallelism, you should review and modify configuration parameters that control the amount of shared memory and prefetching.
To enable or disable intrapartition parallelism from within a database application, you can call the ADMIN_SET_INTRA_PARALLEL procedure. Setting ADMIN_SET_INTRA_PARALLEL will apply intrapartition parallelism only to your application. For your application, this value will override the intra_parallel database manager configuration parameter.
To enable or disable intrapartition parallelism from within a workload, you can set the MAXIMUM DEGREE workload attribute. This will apply intrapartition parallelism only to your workload. This value will override both the the intra_parallel database manager configuration parameter and any values assigned by the ADMIN_SET_INTRA_PARALLEL procedure.
If a query is compiled with DEGREE = ANY, the database manager chooses the degree of intrapartition parallelism on the basis of a number of factors, including the number of processors and the characteristics of the query. The actual degree used at run time might be lower than the number of processors, depending on these factors and the amount of activity on the system. The degree of parallelism might be reduced before query execution if the system is busy.
Use the DB2 explain facility to display information about the degree of parallelism chosen by the optimizer. Use the database system monitor to display information about the degree of parallelism actually being used at run time.
You can specify a degree of parallelism without having an SMP machine. For example, I/O-bound queries on a uniprocessor machine might benefit from declaring a degree of 2 or more. In this case, the processor might not have to wait for I/O tasks to complete before starting to process a new query. Utilities such as load can control I/O parallelism independently.