Enabling intrapartition parallelism for queries
To enable intrapartition query parallelism, modify one or more database or database manager configuration parameters, precompile or bind options, or a special register. Alternatively, use the MAXIMUM DEGREE option on the CREATE or ALTER WORKLOAD statement, or the ADMIN_SET_INTRA_PARALLEL procedure to enable or disable intrapartition parallelism at the transaction level.
Before you begin
- CURRENT DEGREE special register (for dynamic SQL)
- DEGREE bind option (for static SQL)
- dft_degree database configuration parameter (provides the default value for the previous two parameters)
- max_querydegree database manager configuration parameter
- SET RUNTIME DEGREE command
- MAXIMUM DEGREE workload option
- MAXIMUM DEGREE service class option
- intra_parallel database manager configuration parameter
- ADMIN_SET_INTRA_PARALLEL stored procedure
- MAXIMUM DEGREE workload option
About this task
Use the GET DATABASE CONFIGURATION or the GET DATABASE MANAGER CONFIGURATION command to find the values of individual entries in a specific database or instance configuration file. To modify one or more of these entries, use the UPDATE DATABASE CONFIGURATION or the UPDATE DATABASE MANAGER CONFIGURATION command.
- intra_parallel
- Database manager configuration parameter that specifies whether
or not the database manager can use intrapartition parallelism. The
default is NO, which means that applications in this
instance are run without intrapartition parallelism. For example:
update dbm cfg using intra_parallel yes; get dbm cfg;
- max_querydegree
- Database manager configuration parameter that specifies the maximum
degree of intrapartition parallelism that is used for any SQL statement
running on this instance. An SQL statement does not use more than
this value when running parallel operations within a database partition.
The default is -1, which means that the system uses
the degree of intrapartition parallelism that is determined by the
optimizer, not the user-specified value. For example:
update dbm cfg using max_querydegree any; get dbm cfg;
The intra_parallel database manager configuration parameter must also be set to YES for the value of max_querydegree to be used.
- dft_degree
- Database configuration parameter that specifies the default value
for the DEGREE precompile or bind option and
the CURRENT DEGREE special register. The default is 1.
A value of -1 (or ANY) means that
the system uses the degree of intrapartition parallelism that is determined
by the optimizer. For example:
connect to sample; update db cfg using dft_degree -1; get db cfg; connect reset;
- DEGREE
- Precompile or bind option that specifies the degree of intrapartition
parallelism for the execution of static SQL statements on a symmetric
multiprocessing (SMP) system. For example:
connect to prod; prep demoapp.sqc bindfile; bind demoapp.bnd degree 2; ...
- CURRENT DEGREE
- Special register that specifies the degree of intrapartition parallelism
for the execution of dynamic SQL statements. Use the SET CURRENT DEGREE
statement to assign a value to the CURRENT DEGREE special register.
For example:
connect to sample; set current degree = '1'; connect reset;
The intra_parallel database manager configuration parameter must also be set to YES to use intrapartition parallelism. If it is set to NO, the value of this special register is ignored, and the statement will not use intrapartition parallelism. The value of the intra_parallel database manager configuration parameter and the CURRENT DEGREE special register can be overridden in a workload by setting the MAXIMUM DEGREE workload attribute.
- MAXIMUM DEGREE
- CREATE WORKLOAD or CREATE SERVICE CLASS statement (or ALTER WORKLOAD or ALTER SERVICE CLASS
statement) option that specifies the maximum runtime degree of parallelism for a workload or service
class.
For example, suppose that
bank_trans
is a packaged application that mainly executes short OLTP transactions, andbank_report
is another packaged application that runs complex queries to generate a business intelligence (BI) report. Neither application can be modified, and both are bound with degree 4 to the database. Whilebank_trans
is running, it is assigned to workloadtrans
, which disables intrapartition parallelism. This OLTP application will run without any performance degradation associated with intrapartition parallelism overhead. Whilebank_report
is running, it is assigned to workloadbi
, which enables intrapartition parallelism and specifies a maximum runtime degree of 8. Because the compilation degree for the package is 4, the static SQL statements in this application run with only a degree of 4. If this BI application contains dynamic SQL statements, and the CURRENT DEGREE special register is set to 16, these statements run with a degree of 8.connect to sample; create workload trans applname('bank_trans') maximum degree 1 enable; create workload bi applname('bank_report') maximum degree 8 enable; connect reset;
- ADMIN_SET_INTRA_PARALLEL
- Procedure that enables or disables intrapartition parallelism
for a database application. Although the procedure is called in the
current transaction, it takes effect starting with the next transaction.
For example, assume that the following code is part of the
demoapp
application, which uses the ADMIN_SET_INTRA_PARALLEL procedure with both static and dynamic SQL statements:
The degree of intrapartition parallelism for dynamic SQL statements is specified through the CURRENT DEGREE special register, and for static SQL statements, it is specified through the DEGREE bind option. The following commands are used to prepare and bind theEXEC SQL CONNECT TO prod; // Disable intrapartition parallelism: EXEC SQL CALL SYSPROC.ADMIN_SET_INTRA_PARALLEL('NO'); // Commit so that the effect of this call // starts in the next statement: EXEC SQL COMMIT; // All statements in the next two transactions run // without intrapartition parallelism: strcpy(stmt, "SELECT deptname FROM org"); EXEC SQL PREPARE rstmt FROM :stmt; EXEC SQL DECLARE c1 CURSOR FOR rstmt; EXEC SQL OPEN c1; EXEC SQL FETCH c1 INTO :deptname; EXEC SQL CLOSE c1; ... // New section for this static statement: EXEC SQL SELECT COUNT(*) INTO :numRecords FROM org; ... EXEC SQL COMMIT; // Enable intrapartition parallelism: EXEC SQL CALL SYSPROC.ADMIN_SET_INTRA_PARALLEL('YES'); // Commit so that the effect of this call // starts in the next statement: EXEC SQL COMMIT; strcpy(stmt, "SET CURRENT DEGREE='4'"); // Set the degree of parallelism to 4: EXEC SQL EXECUTE IMMEDIATE :stmt; // All dynamic statements in the next two transactions // run with intrapartition parallelism and degree 4: strcpy(stmt, "SELECT deptname FROM org"); EXEC SQL PREPARE rstmt FROM :stmt; EXEC SQL DECLARE c2 CURSOR FOR rstmt; EXEC SQL OPEN c2; EXEC SQL FETCH c2 INTO :deptname; EXEC SQL CLOSE c2; ... // All static statements in the next two transactions // run with intrapartition parallelism and degree 2: EXEC SQL SELECT COUNT(*) INTO :numRecords FROM org; ... EXEC SQL COMMIT;
demoapp
application:connect to prod; prep demoapp.sqc bindfile; bind demoapp.bnd degree 2; ...