SET PATH

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.

Authorization

If a global variable is referenced in the statement, the privileges held by the authorization ID of the statement must include at least one of the following:

For each global variable identified in the statement,
- The READ privilege on the global variable, and
- The system authority *EXECUTE on the library containing the global variable
Database administrator authority

Syntax

Notes:

¹ SYSTEM PATH, SESSION_USER, USER, CURRENT_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.

CURRENT USER

Specifies the value of the CURRENT 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.
If the SET statement is specified in an SQL routine, SQL trigger, or compound statement, variable cannot be a global variable with an unqualified name.

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.

*LIBL

The path is set to the library list of the current thread.

Notes

Transaction considerations: The SET PATH statement is not a committable 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 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.