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.
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.
update dbm cfg using intra_parallel yes;
get dbm cfg;
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.
connect to sample;
update db cfg using dft_degree -1;
get db cfg;
connect reset;
connect to prod;
prep demoapp.sqc bindfile;
bind demoapp.bnd degree 2;
...
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.
For example, suppose that bank_trans is a packaged application that mainly executes short OLTP transactions, and bank_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. While bank_trans is running, it is assigned to workload trans, which disables intrapartition parallelism. This OLTP application will run without any performance degradation associated with intrapartition parallelism overhead. While bank_report is running, it is assigned to workload bi, 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;
EXEC 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;
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 the demoapp application:connect to prod;
prep demoapp.sqc bindfile;
bind demoapp.bnd degree 2;
...