DB2 Version 9.7 for Linux, UNIX, and Windows

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


                         .-=-.                        
>>-SET--CURRENT--DEGREE--+---+--+-string-constant-+------------><
                                '-host-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 intra-partition parallelism.

If the value of CURRENT DEGREE is a number when an SQL statement is dynamically prepared, the execution of that statement can involve intra-partition 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 intra-partition 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 intra-partition parallelism for static SQL statements can be controlled using the DEGREE option of the PREP or BIND command.

The actual runtime degree of intra-partition 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 intra-partition parallelism. If it is set to off, the value of this register will be ignored and the statement will not use intra-partition parallelism for the purpose of optimization (SQLSTATE 01623).

Some SQL statements cannot use intra-partition parallelism.

Example

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

   SET CURRENT DEGREE = '1'

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

   SET CURRENT DEGREE = 'ANY'