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. 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 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.
Job: B,I Pgm: B,I REXX: B,I Exec

    (1)           (2)
>>--------CHGQRYA----------------------------------------------->
 
>----+---------------------------------------------------------+>
     |       .-*--------------------------------------------.  |
     '-JOB(--+-+--------------+--user-name/---job-name---)--+--'
               '-job-number/--'
 
>----+------------------------------+--------------------------->
     |             .-*SAME---.      |
     '-QRYTIMLMT(--+-*NOMAX--+--)---'
                   +-*SYSVAL-+
                   '-seconds-'
 
>----+------------------------------------------------+--------->
     |           .-*SAME-----------------------.      |
     '-DEGREE (--+-*NONE-----------------------+--)---'
                 +-*IO-------------------------+
                 +-*OPTIMIZE-------------------+
                 +-*MAX------------------------+
                 +-*SYSVAL---------------------+
                 +-*ANY------------------------+
                 '-*NBRTASKS--number-of-tasks--'
 
>-----+---------------------------+----------------------------->
      |           .-*SAME--.      |
      '-ASYNCJ (--+-*LOCAL-+--)---'
                  +-*DIST--+
                  +-*NONE--+
                  '-*ANY---'
 
>-----+---------------------------+----------------------------><
      |           .-*SAME--.      |
      '-APYRMT (--+-*YES---+--)---'
                  '-*NO----'
 

Notes:

1. Value *ANY is equivalent to value *IO.

2. All parameters preceding this point can be specified in positional form.

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 system, 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 system 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 system 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.

*ANY

Parameter value *ANY has the same meaning as *IO. The *ANY value is maintained for compatibility with prior releases.

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