SET PATH statement
The SET PATH statement assigns a value to the CURRENT PATH special register.
Invocation for SET PATH
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
Authorization for SET PATH
None required.
Syntax for SET PATH
Description for SET PATH
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", "SYSIBMADM".
- 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. The schema name must be left justified within the string constant.
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 for SET PATH
- 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.
- The schema name SYSPUBLIC cannot be specified in the SQL path, even if you specify the value as a delimited identifier.
- Specifying "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM":
- Schemas "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM" 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", "SYSIBMADM" (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", "SYSIBMADM" 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:
The result is that the value of the SQL path is set to: "SYSIBM","SYSPROC","SMITH","SESSION_USER".SET PATH = SYSIBM, SYSPROC, SESSION_USER, "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.
Examples for SET PATH
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, SYSIBMADM, SCHEMA1, SCHEMA#2, SYSIBM
SET PATH = CURRENT PATH, SMITH, SYSPROC;
The effective value
of the SQL path specified by the special
register becomes:
SYSFUN, SYSIBMADM, SCHEMA1, SCHEMA#2, SYSIBM, SMITH, SYSPROC