SET CURRENT DEGREE

The SET CURRENT DEGREE statement assigns a value to the CURRENT DEGREE special register.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared. It must not be specified in REXX.

Authorization

Start of changeThe privileges held by the authorization ID of the statement must include *JOBCTL special authority or be authorized to the SQL Administrator function of IBM® i through Application Administration in System i® Navigator. The Change Function Usage Information (CHGFCNUSG) command, with a function ID of QIBM_DB_SQLADM, can also be used to change the list of authorized users.End of change

Start of change If a global variable is referenced in the statement, the privileges held by the authorization ID of the statement must include at least one of the following:End of change

Start of change
  • For the global variable identified in the statement,
    • The READ privilege on the global variable, and
    • The system authority *EXECUTE on the library containing the global variable
  • Administrative authority
End of change

Syntax

Read syntax diagramSkip visual syntax diagram
                        .- = -.                        
>>-SET--CURRENT DEGREE--+-----+--+-string-constant-+-----------><
                                 +-variable--------+   
                                 '-DEFAULT---------'   

Description

The value of CURRENT DEGREE is replaced by the value of the string constant or variable.

string-constant
Specifies a character string constant. The content is not folded to uppercase.

The length of the string-constant must not exceed 5 after trimming any leading and trailing blanks.

variable
Specifies a variable that contains the value for CURRENT DEGREE.

The variable:

  • Must be a CHAR, VARCHAR, Unicode GRAPHIC, or Unicode VARGRAPHIC variable. The actual length of the contents of the variable must not be greater than 5 after trimming any leading and trailing blanks.
  • Must not be the null value.
  • Must have contents in uppercase characters. All characters are case-sensitive and are not converted to uppercase characters.
DEFAULT
If the PARALLEL_DEGREE parameter in a current query options file (QAQQINI) is specified, the CURRENT DEGREE will be reset to the PARALLEL_DEGREE. Otherwise, the CURRENT DEGREE will be reset from the degree specified by the QQRYDEGREE system value.

The value of the string constant or variable must be one of the following:

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.

Notes

Transaction considerations: The SET CURRENT DEGREE statement is not a committable operation. ROLLBACK has no effect on CURRENT DEGREE.

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

Parallelism degree precedence: The parallelism degree can be controlled in several ways. The actual parallelism degree used is determined as follows:

  • If a SET CURRENT DEGREE statement or a CHGQRYA CL command with a DEGREE keyword has been executed, the parallelism degree specified by the most recent of either is the value of CURRENT DEGREE.
  • If neither a SET CURRENT DEGREE statement nor a CHGQRYA CL command with a DEGREE keyword has been executed,
    • If a current query options file (QAQQINI) with a PARALLEL_DEGREE parameter has been specified, the parallelism degree specified by the QAQQINI file is the value of CURRENT DEGREE.
    • Otherwise, the parallelism degree specified by the QQRYDEGREE system value is the value of CURRENT DEGREE.

For more information, see Database Performance and Query Optimization topic collection.

Current degree scope: The scope of CURRENT DEGREE is the job.

Parallel limitations: If the DB2® Symmetric Multiprocessing feature is not installed, a warning is returned and parallelism is not used.

Some SQL statements cannot use parallelism.

Example

Example 1: The following statement sets the CURRENT DEGREE to inhibit parallelism.

  SET CURRENT DEGREE = '1'

Example 2: The following statement sets the CURRENT DEGREE to allow parallelism.

  SET CURRENT DEGREE = 'ANY'
Parent topic: Statements