The SET PATH statement changes the value of 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.
Syntax

.-FUNCTION-.
.-CURRENT--+----------+-. .- = -.
>>-SET--+-+-----------------------+--PATH-+--+-----+------------>
'-CURRENT_PATH--------------------'
.-,---------------------------------------.
V | (1)
>--+---+-schema-name-------------------------+-+-+-------------><
| +-SYSTEM PATH-------------------------+ |
| +-+-SESSION_USER-+--------------------+ |
| | '-USER---------' | |
| +-SYSTEM_USER-------------------------+ |
| | .-FUNCTION-. | |
| | .-CURRENT--+----------+-. | |
| +-+-+-----------------------+--PATH-+-+ |
| | '-CURRENT_PATH--------------------' | |
| +-variable----------------------------+ |
| '-string-constant---------------------' |
'-*LIBL---------------------------------------'
Notes:
- SYSTEM PATH, SESSION_USER, USER, SYSTEM_USER, and CURRENT
PATH may each be specified at most once on the right side of the statement.
Description
- schema-name
Identifies a schema. If a specified schema name
is a system schema name, no validation that the schema exists is made
at the time the PATH is set. For example, if a schema-name is
misspelled, it could affect the way subsequent SQL operates. If the
specified schema name is not a system schema name, the schema must
exist at the time the PATH is set. Although not recommended, PATH
can be specified as a schema-name if it
is specified as "PATH".

- SYSTEM PATH
- Specifies the schema names for
the system path. This value is the same as specifying the schema names
"QSYS","QSYS2","SYSPROC","SYSIBMADM".
- SESSION_USER or USER
- Specifies the value of the SESSION_USER special register.
- SYSTEM_USER
- Specifies the value of the SYSTEM_USER special register.
- CURRENT PATH
- Specifies
the value of the CURRENT PATH special register before the execution
of this statement. CURRENT PATH is not allowed if the current path
is *LIBL.
- variable
- Specifies a variable that contains one or more schema names that
are separated by commas. It can be a global variable if it is qualified
with schema name.
The variable:
- Must be a CHAR, VARCHAR, Unicode GRAPHIC, or Unicode VARGRAPHIC
variable. The actual length of the contents of the variable must
not exceed the maximum length of a path.
- Must not be followed by an indicator variable.
- Must not be the null value.
- Each schema name must conform to the rules for forming an ordinary
or delimited identifier.
- Each schema name must not contain lowercase letters or characters
that cannot be specified in an ordinary identifier.
- Must be padded on the right with blanks if the variable is fixed
length character.
- string-constant
- A character constant with one or more schema names that are separated
by commas.
The string constant:
- Each schema name must conform to the rules for forming an ordinary
or delimited identifier.
- Each schema name must not contain lowercase letters or characters
that cannot be specified in an ordinary identifier.
Notes
Transaction
considerations: The SET PATH statement is not a commitable operation.
ROLLBACK has no effect on the CURRENT PATH.
Rules for the content of the SQL path:
- A schema name must not appear more than once in the path.
- The number of schemas that can be specified is limited by the
total length of the CURRENT PATH special register. The special register
string is built by taking each schema name specified and removing
trailing blanks, delimiting with double quotes, and separating each
schema name by a comma. An error is returned if the length of the
resulting string exceeds 3483 bytes. A maximum of 268 schema names
can be represented in the path.
- There is a difference between specifying a single keyword (such
as USER, or PATH, or CURRENT_PATH) as a single keyword, or as a delimited
identifier. To indicate that the current value of a special register
specified as a single keyword should be used in the SQL path, specify
the name of the special register as a keyword. If the name of the
special register is specified as a delimited identifier instead (for
example, "USER"), it is interpreted as a schema name of that value
('USER'). For example, assuming that the current value of the USER
special register is SMITH, then SET PATH = SYSIBM, USER, "USER" results
in a CURRENT PATH value of "SYSIBM","SMITH","USER".
- The following rules are used to determine whether a value specified
in a SET PATH statement is a variable or a schema-name:
- If name is the same as a parameter or
SQL variable in the SQL procedure, name is
interpreted as a parameter or SQL variable, and the value in name is
assigned to PATH.
- If name is not the same as a parameter
or SQL variable in the SQL procedure, name is
interpreted as schema-name, and the value name is
assigned to PATH.
The system path: SYSTEM PATH refers to the system
path for a platform. The schemas QSYS, QSYS2, SYSPROC, and SYSIBMADM
do not need to be specified. If not included in the path, they are
implicitly assumed as the last schemas (in this case, it is not included
in the CURRENT PATH special register).
The initial value of the CURRENT PATH special register
is *LIBL if system naming was used for the first SQL statement run
in the activation group. The initial value is "QSYS","QSYS2","SYSPROC","SYSIBMADM","X"
(where X is the value of the USER special register) if SQL naming
was used for the first SQL statement.
Using the SQL path: The CURRENT PATH special register
is used to resolve user-defined distinct types, functions, and procedures
in dynamic SQL statements. For more information see SQL path.
Example
The following statement sets the
CURRENT PATH special register.
SET PATH = FERMAT, "McDuff", SYSIBM
The following statement retrieves the current value of
the SQL path special register into the host variable called CURPATH.
EXEC SQL VALUES (CURRENT PATH) INTO :CURPATH;
The value would be "FERMAT","McDuff","SYSIBM" if set by
the previous example.