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


                                     .-=-.                                  
>>-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

Example 1: The optimization profile RICK.FOO is used for statements 1, 2, and 3. TOM.FOO is used for statement 4.

   SET CURRENT SCHEMA = 'RICK'
   SET CURRENT OPTIMIZATION PROFILE = 'FOO'
      statement 1
      statement 2
   SET CURRENT SCHEMA = 'TOM'
      statement 3
   SET CURRENT OPTIMIZATION PROFILE = 'FOO'
      statement 4

Example 2: An application with the following statements was bound with the options OPTPROFILE("Foo") and QUALIFIER("John"). The optimization profile KAAREL.BAR is used for statement 1 and optimization profile "John"."Foo" is used for statement 2.
```
   SET CURRENT SCHEMA = 'KAAREL' 
   SET CURRENT OPTIMIZATION PROFILE = 'BAR' 
      statement 1 
   SET CURRENT SCHEMA = "Tom" 
   SET CURRENT OPTIMIZATION PROFILE NULL  
      statement 2 
```
Example 3: The empty string is a special value that indicates that no optimization profile is to be used. Optimization profile "Hamid"."Foo" is used for statement 1 and no optimization profile is used for statement 2.
```
   SET CURRENT OPTIMIZATION PROFILE = '"Hamid"."Foo"' 
      statement 1
   SET CURRENT OPTIMIZATION PROFILE = '' 
      statement 2
```