DB2 10.5 for Linux, UNIX, and Windows

SET CURRENT OPTIMIZATION PROFILE statement

The SET CURRENT OPTIMIZATION PROFILE statement assigns a value to the CURRENT OPTIMIZATION PROFILE special register. The value specifies the optimization profile the optimizer should use when preparing dynamic DML statements.

This statement is not under transaction control.

When the statement is evaluated, the name of the optimization profile is checked for validity, but the profile is not processed until the optimizer encounters a dynamic DML statement.

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 diagram
                                     .-=-.                                  
>>-SET CURRENT OPTIMIZATION PROFILE--+---+--+-optimization-profile-name-+-><
                                            +-host-variable-------------+   
                                            +-string-constant-----------+   
                                            '-NULL----------------------'   

Description

optimization-profile-name
The two-part name of the optimization profile. The name can be specified with a literal, host variable, or special register. The name specified is the name entered into the CURRENT OPTIMIZATION PROFILE special register.

If the specified optimization-profile-name is unqualified, the value of the CURRENT DEFAULT SCHEMA register is used as the implicit qualifier. The default value of the special register is null.

host-variable
A variable of type CHAR or VARCHAR that includes the name of the optimization profile. A host variable that includes a null indicator indicates that the value of the OPTPROFILE bind option is to be used if that value is specified for the current package. A host variable of zero length, or of white space only, indicates that no optimization profile is to be used.

The host variable must meet the following characteristics:

  • The content of the string is a single or two-part identifier (separated by a period), with no leading blanks.
  • The identifier or identifiers can be delimited or non-delimited.
  • The content of the string is not folded to upper case.
  • Lower case and special characters cannot be used in non-delimited strings.
  • If the first character is a double quotation mark, a closing double quotation mark must either precede a period or be the last non-blank character in the string.
  • If the first character following a period is a double quotation mark, then a double quotation mark must be the last non-blank character in the string.
  • If the identifier is delimited, then to include double quotation marks in the identifier, specify the character twice.
  • Any period that is not inside a delimited identifier is treated as a separator, and only one period separator can exist in the string.
string-constant
Specifies a constant as a character string that is the name of the optimization profile. The content of a string constant must meet the same characteristics as a host variable.
NULL
Sets the CURRENT OPTIMIZATION PROFILE register to null.

Table 1 provides examples of string literals and identifiers that might be used to assign the register as per the optimization profile naming rules. The value in the SCHEMA and NAME column represent an optimization profile name as it might appear in the OPT_PROFILE table. The valid string literals column shows string literals that match the optimization profile named by the corresponding SCHEMA and NAME column values. The valid identifiers column shows identifiers that would identify that same optimization profile.

Table 1. Examples of string literals and identifiers
SCHEMA NAME Valid string literals Valid identifiers
SIMMEN BIG_PROF 'BIG_PROF'

'SIMMEN.BIG_PROF'

'"BIG_PROF"'

'"SIMMEN"."BIG_PROF"'

BIG_PROF

SIMMEN.BIG_PROF

"BIG_PROF"

"SIMMEN"."BIG_PROF"

SIMMEN low_profile '"low_profile"'

'SIMMEN."low_profile"'

'"SIMMEN"."low_profile"'

"low_profile"

SIMMEN."low_profile"

"SIMMEN"."low_profile"

eliaz DBA3 'DBA3'

'"DBA3"'

'"eliaz".DBA3'

'"eliaz"."DBA3"'

DBA3

"eliaz".DBA3

"eliaz"."DBA3"

SNOW PROFILE1.0 '"PROFILE1.0"'

'SNOW."PROFILE1.0"'

'"SNOW"."PROFILE1.0"'

"PROFILE1.0"

SNOW."PROFILE1.0"

"SNOW"."PROFILE1.0"

Notes

  • If the value of the register specifies the name of an existing optimization profile, the specified optimization profile is used when preparing subsequent dynamic DML statements.
  • If the value of the register is null, the optimization profile specified by the OPTPROFILE bind option, if any, is used when preparing subsequent dynamic DML statements.
  • If the value of the register is null, and the OPTPROFILE bind option is not set, no optimization profile is used when preparing subsequent dynamic DML statements.
  • If the value of the register is the empty string, then no optimization profile is used when preparing subsequent dynamic DML statements, regardless of whether the OPTPROFILE bind option is set.
  • Subsequent changes to CURRENT DEFAULT SCHEMA do not have any effect on the optimization profile. The CURRENT OPTIMIZATION PROFILE register value is set with the two part name that is in effect at the time SET CURRENT OPTIMIZATION PROFILE statement is evaluated. Only another SET CURRENT OPTIMIZATION PROFILE statement can change the optimization profile that is used.

Examples