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

Start of change 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:End of change

Start of changeEnd of change

Syntax

Read syntax diagramSkip visual syntax diagram
                     .-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:
  1. 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
Start of changeIdentifies 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".

End of change
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:

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.