SET CURRENT PATH

The SET CURRENT PATH statement assigns a value to the CURRENT PATH 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.

Authorization

None required.

Syntax

SET {[CURRENT [FUNCTION]] PATH | CURRENT_PATH} [=]
{   schema-name 
    | SYSTEM PATH 
    | USER 
    | {[CURRENT [FUNCTION]] PATH | CURRENT_PATH} 
    | CURRENT PACKAGE PATH
    | host-variable 
    | string-constant
} [,...]

Description

The value of CURRENT PATH is replaced by the values specified.

schema-name

Identifies a schema. DB2 does not verify that the schema exists. For example, a schema name that is misspelled is not detected, which could affect the way subsequent SQL operates.

SYSTEM PATH

Specifies the schema names "SYSIBM", "SYSFUN" , "SYSPROC". SYSTEM PATH can be specified only once.

USER

Specifies the value of the USER special register. USER can be specified only once.

CURRENT PATH

Specifies the value of the CURRENT PATH special register before the execution of this statement. CURRENT PATH can be specified only once.

host-variable

A variable with a data type of CHAR or VARCHAR. The value of host-variable must not be null and must represent a valid schema name.

The schema name must:

Be left justified within the host variable
Be padded on the right with blanks if its length is less than that of the host variable

string-constant

A character string constant that represents a valid schema name.

If the schema name specified in string-constant will also be specified in other SQL statements and the schema name does not conform to the rules for ordinary identifiers, the schema name must be specified as a delimited identifier in the other SQL statements.

Restrictions on SET CURRENT PATH

These restrictions apply to the SET CURRENT PATH statement:

If the same schema name appears more than once in the path, the first occurrence of the name is used and a warning is issued.
The length of the CURRENT PATH special register limits the number of schema names that can be specified. DB2 builds the string for the special register by taking each schema name specified and removing any trailing blanks from it, adding two delimiters around it, and adding one comma after each schema name except the last one. The length of the resulting string cannot exceed 254 bytes.

Specifying SYSIBM and SYSPROC

Schemas SYSIBM and SYSPROC do not need to be specified in the special register. If either of these schemas is not explicitly specified in the CURRENT PATH special register, the schema is implicitly assumed at the front of the SQL path; if both are not specified, they are assumed in the order of SYSIBM, SYSPROC.

Specifying USER versus "USER"

There is a difference between specifying USER with and without escape characters. To indicate that the value of the USER special register should be used in the SQL path, specify the keyword USER. If you specify USER as a delimited identifier instead (for example, "USER"), it is interpreted as a schema name of "USER". For example, assuming that the current value of the USER special register is SMITH, SET CURRENT PATH = SYSIBM, SYSPROC, USER, "USER" results in SYSIBM, SYSPROC, SMITH, USER being used in the SQL path.

Specifying a schema name in an SQL procedure

Because a host variable (SQL variable) in an SQL procedure does not begin with a colon, DB2 uses the following rules to determine whether a value that is specified in a SET CURRENT PATH=name statement is a variable or a string constant:

If name is the same as a parameter or SQL variable in the SQL procedure, DB2 uses name as a parameter or SQL variable and assigns the value in name to CURRENT PATH.
If name is not the same as a parameter or SQL variable in the SQL procedure, DB2 uses name as a string constant and assigns the value name to CURRENT PATH.

Examples:

Set the CURRENT PATH special register to the list of schemas: "SCHEMA1", "SCHEMA#2", "SYSIBM".

SET CURRENT PATH = SCHEMA1,"SCHEMA#2", SYSIBM;

If the special register provides the SQL path, then SYSPROC, which was not explicitly specified in the special register, is implicitly assumed at the front of the SQL path, making the effective value of the path:

SYSPROC, SCHEMA1, SCHEMA#2, SYSIBM