SET PATH

The SET 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


        .-CURRENT-.        .-=-.   
>>-SET--+---------+--PATH--+---+-------------------------------->

   .-,-----------------------------.   
   V  (1)                          |   
>---------+-schema-name----------+-+---------------------------><
          +-SYSTEM PATH----------+     
          +-+-SESSION_USER-+-----+     
          | '-USER---------'     |     
          | .-CURRENT-.          |     
          +-+---------+--PATH----+     
          +-CURRENT PACKAGE PATH-+     
          +-host-variable--------+     
          '-string-constant------'

Notes:

SYSTEM PATH, SESSION_USER or USER, and CURRENT PATH can be specified only once each.

Description

The value of 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".

SESSION_USER or USER

Specifies the value of the SESSION_USER (USER) special register.

PATH

Specifies the value of the CURRENT PATH special register before the execution of this statement.

CURRENT PACKAGE PATH

Specifies the value of the CURRENT PACKAGE PATH special register.

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.

Notes

Restrictions on SET PATH:

These restrictions apply to the SET PATH statement:

If the same schema name appears more than one time 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. The special register string is built by taking each schema name that is specified and removing trailing blanks, delimiting with double quotes, changing each double quote character to two double quote characters within the schema name as necessary, and then separating each schema name with a comma. The length of the resulting string cannot exceed 2048 bytes.

Specifying "SYSIBM", "SYSFUN", "SYSPROC":

Schemas "SYSIBM", "SYSFUN", "SYSPROC" do not need to be specified in the special register. If these schemas are not explicitly specified in the CURRENT PATH special register, each schema is implicitly assumed at the front of the SQL path; if any of these schemas are not specified, they are assumed in the order of "SYSIBM", "SYSFUN", "SYSPROC" (see SQL path for an example). Only the schemas that are explicitly specified in the CURRENT PATH register are included in the 2048 byte limit.

To avoid having "SYSIBM", "SYSFUN", "SYSPROC" implicitly added to the front of the SQL path, explicitly specify them in the path when setting the value of the register. If you specify them at the end of the path, DB2 will check all the other schemas in the path first.

Specifying keywords versus delimited identifiers:

There is a difference between specifying a keyword and specifying a delimited identifier. For example, specifying SESSION_USER with and without escape characters. To indicate that the value of the SESSION_USER special register should be used in the SQL path, specify the keyword SESSION_USER. If you specify SESSION_USER is as a delimited identifier instead (for example, "SESSION_USER"), it is interpreted as a schema name of 'SESSION_USER'. For example, assume that the current value of the SESSION_USER special register is SMITH and that the following statement is issued:

SET PATH = SYSIBM, SYSPROC, SESSION_USER, "SESSION_USER"

The result is that the value of the SQL path is set to: "SYSIBM","SYSPROC","SMITH","SESSION_USER".

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 PATH=name statement is a variable or a schema-name:

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 PATH.
If name is not the same as a parameter or SQL variable in the SQL procedure, DB2 uses name as a schema-name and assigns the value name to PATH.

The use of the path to resolve object names:

For information on when the SQL path is used to resolve unqualified data type, function, and procedure names and when the CURRENT PATH special register provides the SQL path, see SQL path.

DRDA classification:

The SET PATH statement is executed by the database server and, therefore, is classified as a non-local SET statement in DRDA.

Alternative syntax and synonyms:

For compatibility with previous releases of DB2 or other products in the DB2 family, DB2 supports CURRENT FUNCTION PATH or CURRENT_PATH as a synonym for CURRENT PATH. CURRENT_PATH is consistent with the SQL standard name of the special register.

Examples

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

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

When the SQL path specified in the special register is used for name resolution the system schemas which were not explicitly specified in the special register are implicitly assumed at the front of the SQL path, making the effective value of the path:

   SYSFUN, SYSPROC, SCHEMA1, SCHEMA#2, SYSIBM

Example 2: Add schema SMITH and SYSPROC to the value of the CURRENT PATH special register that was set in Example 1.

   SET PATH = CURRENT PATH, SMITH, SYSPROC;

The effective value of the SQL path specified by the special register becomes:

   SYSFUN, SCHEMA1, SCHEMA#2, SYSIBM, SMITH, SYSPROC