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
The 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.
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:
- 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
Syntax
.- = -. >>-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'