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.
When exploiting parallelism, you should be aware that some configuration parameters require modification in order to optimize performance. 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 are 3 configuration parameters that you can use to control and manage parallelism. The first, the intra_parallel database manager configuration parameter, enables or disables instance parallelism support. The second, the max_querydegree database configuration parameter, sets an upper limit for the degree of parallelism for any query in the database. This value overides the CURRENT DEGREE special register and the DEGREE bind option. The third configuration paremeter is the dft_degree database configuration parameter. It sets 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 26, SQL Explain Facility and Appendix H, 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.