Controlling job level parallel processing for queries

 

You can also control query parallel processing at the job level using the DEGREE parameter of the Change Query Attributes (CHGQRYA) command or in the QAQQINI file, or using the SET_CURRENT_DEGREE SQL statement.

 

Using the Change Query Attributes (CHGQRYA) command

The parallel processing option allowed and, optionally, the number of tasks that can be used when running database queries in the job can be specified. You can prompt on the Change Query Attributes (CHGQRYA) command in an interactive job to display the current values of the DEGREE query attribute.

Changing the DEGREE query attribute does not affect queries that have already been started or queries using reusable ODPs.

The parameter values for the DEGREE keyword are:

*SAME

The parallel degree query attribute does not change.

*NONE

No parallel processing is allowed for database query processing.

*IO

Any number of tasks can be used when the database query optimizer chooses to use I/O parallel processing for queries. SMP parallel processing is not allowed.

*OPTIMIZE

The query optimizer can choose to use any number of tasks for either I/O or SMP parallel processing to process the query. SMP parallel processing can be used only if the DB2^® UDB Symmetric Multiprocessing feature is installed. Use of parallel processing and the number of tasks used is determined with respect to the number of processors available in the server, the job's share of the amount of active memory available in the pool in which the job is run, and whether the expected elapsed time for the query is limited by CPU processing or I/O resources. The query optimizer chooses an implementation that minimizes elapsed time based on the job's share of the memory in the pool.

*MAX

The query optimizer can choose to use either I/O or SMP parallel processing to process the query. SMP parallel processing can be used only if the DB2 UDB Symmetric Multiprocessing feature is installed. The choices made by the query optimizer are similar to those made for parameter value *OPTIMIZE except the optimizer assumes that all active memory in the pool can be used to process the query.

*NBRTASKS number-of-tasks

Specifies the number of tasks to be used when the query optimizer chooses to use SMP parallel processing to process a query. I/O parallelism is also allowed. SMP parallel processing can be used only if the DB2 UDB Symmetric Multiprocessing feature is installed.

Using a number of tasks less than the number of processors available on the server restricts the number of processors used simultaneously for running a given query. A larger number of tasks ensures that the query is allowed to use all of the processors available on the server to run the query. Too many tasks can degrade performance because of the over commitment of active memory and the overhead cost of managing all of the tasks.

*SYSVAL

Specifies that the processing option used should be set to the current value of the QQRYDEGREE system value.

The initial value of the DEGREE attribute for a job is *SYSVAL.

 

Using the SET CURRENT DEGREE SQL statement

You can use the SET CURRENT DEGREE SQL statement to change the value of the CURRENT_DEGREE special register. The possible values for the CURRENT_DEGREE special register are:

1

No parallel processing is allowed.

2 through 32767

Specifies the degree of parallelism that will be used.

ANY

Specifies that the database manager can choose to use any number of tasks for either I/O or SMP parallel processing. Use of parallel processing and the number of tasks used is determined based on the number of processors available in the system, this job's share of the amount of active memory available in the pool in which the job is run, and whether the expected elapsed time for the operation is limited by CPU processing or I/O resources. The database manager chooses an implementation that minimizes elapsed time based on the job's share of the memory in the pool.

NONE

No parallel processing is allowed.

MAX

The database manager can choose to use any number of tasks for either I/O or SMP parallel processing. MAX is similar to ANY except the database manager assumes that all active memory in the pool can be used.

IO

Any number of tasks can be used when the database manager chooses to use I/O parallel processing for queries. SMP is not allowed.

The value can be changed by invoking the SET CURRENT DEGREE statement.

The initial value of CURRENT DEGREE is determined by the current degree in effect from the CHGQRYA CL command, PARALLEL_DEGREE parameter in the current query options file (QAQQINI), or the QQRYDEGREE system value.

 

Parent topic:

Control parallel processing for queries

 

Related information

Set Current Degree statement
Change Query Attributes (CHGQRYA) command