A type of parallel environment supported by DB2 is one which requires symmetric multi-processor (SMP) machines. In this environment, more than one processor shares access to the database. This allows parallel execution of complex SQL requests which can be divided among the processors.
You can specify the degree of parallelism to implement when compiling your application by using the CURRENT DEGREE special register, or the DEGREE bind option. "Degree" simply refers to the number of concurrently executing parts of a query. There is no strict relation between the number of processors and the value selected for the degree of parallelism. The total number of processors available for use in your hardware platform need not be requested while running your applications; you can select more or less than this number.
Each degree of parallelism adds to the system memory and CPU overhead.
As a result of using a number of degrees of parallelism, some configuration parameters could be modified to use this parallelism more effectively. Configuration parameters controlling the amount of shared memory and prefetching should be reviewed and modified as necessary in an environment with a high degree of parallelism. See Parallel for a list of parameters related to parallel operations and partitioned database environments.
There is a database manager configuration parameter, intra_parallel, that enables or disables instance parallelism support. The default is "NO" for a uni-processor system and "YES" for SMP machines. An upper limit, or maximum, for the run time degree of parallelism is established in the database configuration parameter, max_querydegree. There is a database configuration parameter, dft_degree, to specify the default value for the CURRENT DEGREE special register and the DEGREE bind option.
For more information on the application use and implications from using more than one degree of parallelism, refer to the Application Development Guide manual.
If a query is run with DEGREE = ANY, the database manager chooses the degree of intra-partition parallelism based on a number of factors including the number of processors and the characteristics of the query. The actual degree used at runtime may be lower than the number of processors depending on these factors.
The degree of parallelism is determined by the SQL optimizer when the statement is compiled and may be adjusted before query execution depending on the database activity. The degree of parallelism may be lower than that chosen by the SQL optimizer if the system is heavily utilized. This occurs since intra-partition parallelism aggressively uses system resources to reduce the elapsed time of the query which may adversely affect the performance of other database users.
The degree of parallelism chosen by the SQL optimizer can be found by using the SQL Explain Facility to display the access plan. The degree of parallelism used at runtime can be found by using the database System Monitor. See Chapter 22, SQL Explain Facility and Appendix L, SQL Explain Tools for more information on the SQL Explain Facility and related tools. Refer to the System Monitor Guide and Reference for additional monitor information.
Note: | The "degree" of parallelism can be set independent of the hardware environment. This means that you can use a degree of parallelism without having an SMP machine. For example, "I/O-bound" queries on a uni-processor machine may benefit from declaring a degree of "2" or more. In this case, the uni-processor may not have to wait for input or output tasks to complete before working on a new query. Declaring a degree of "2" or more does not directly control I/O parallelism on a uni-processor machine. Utilities such as LOAD can control I/O parallelism independent from such a declaration. The keyword ANY can also be used when changing the dft_degree. The use of ANY means that the optimizer determines the degree of intra-partition parallelism. |
In many cases, database agents are used to coordinate parallel execution. See Database Agents for more information, and a list of the various database manager configuration parameters that affect database agents.