SET CURRENT DEGREE statement

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

This statement is not under transaction control.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.

Authorization

None required.

Syntax

Read syntax diagramSkip visual syntax diagramSETCURRENTDEGREE=string-constanthost-variable

Description

The value of CURRENT DEGREE is replaced by the value of the string constant or host variable. The value must be a character string that is not longer than 5 bytes. The value must be the character string representation of an integer between 1 and 32 767 inclusive or 'ANY'.

If the value of CURRENT DEGREE represented as an integer is 1 when an SQL statement is dynamically prepared, the execution of that statement will not use intrapartition parallelism.

If the value of CURRENT DEGREE is a number when an SQL statement is dynamically prepared, the execution of that statement can involve intrapartition parallelism with the specified degree.

If the value of CURRENT DEGREE is 'ANY' when an SQL statement is dynamically prepared, the execution of that statement can involve intrapartition parallelism using a degree determined by the database manager.
host-variable
The host-variable must be of data type CHAR or VARCHAR and the length must not exceed 5. If a longer field is provided, an error will be returned (SQLSTATE 42815). If the actual value provided is larger than the replacement value specified, the input must be padded on the right with blanks. Leading blanks are not allowed (SQLSTATE 42815). All input values are treated as being case-insensitive. If a host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 42815).
string-constant
The string-constant length must not exceed 5.

Notes

  • The degree of intrapartition parallelism for static SQL statements can be controlled using the DEGREE option of the PREP or BIND command.
  • The actual runtime degree of intrapartition parallelism will be the lower of:
    • Maximum query degree (max_querydegree) configuration parameter
    • Application runtime degree
    • SQL statement compilation degree
  • The intra_parallel database manager configuration parameter must be on to use intrapartition parallelism. If it is set to off, the value of this register will be ignored and the statement will not use intrapartition parallelism for the purpose of optimization (SQLSTATE 01623).
  • The value in the CURRENT DEGREE special register and the intra_parallel setting can be overridden in a workload by setting the MAXIMUM DEGREE workload attribute.
  • If the DB2_WORKLOAD system environment variables is set to ANALYTICS and MAXIMUM DEGREE for the workload is set to DEFAULT, the value of the intra_parallel setting for the workload is overridden to ON.
  • Some SQL statements cannot use intrapartition parallelism.

Examples

  • Example 1: The following statement sets the CURRENT DEGREE to inhibit intrapartition parallelism. 
       SET CURRENT DEGREE = '1'
  • Example 2: The following statement sets the CURRENT DEGREE to allow intrapartition parallelism. 
       SET CURRENT DEGREE = 'ANY'