SQL Reference

SET PATH

The SET PATH statement changes the value of the CURRENT PATH special register. It is not under transaction control.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

No authorization is required to execute this statement.

Syntax

                   .-FUNCTION-.
        .-CURRENT--+----------+---.          .-=-.
>>-SET--+-------------------------+---PATH---+---+-------------->
 
      .-,-----------------------------------.
      V                                     |
>-------+-schema-name--------------------+--+------------------><
        +-SYSTEM PATH--------------------+
        +-USER---------------------------+
        |          .-FUNCTION--.         |
        +-CURRENT--+-----------+---PATH--+
        +-host-variable------------------+
        '-string-constant----------------'
 

Description

schema-name

This one-part name identifies a schema that exists at the application server. No validation that the schema exists is made at the time that the path is set. If a schema-name is, for example, misspelled, it will not be caught, and it could affect the way subsequent SQL operates.

SYSTEM PATH

This value is the same as specifying the schema names "SYSIBM","SYSFUN".

USER

The value in the USER special register.

CURRENT PATH

The value of the CURRENT PATH before the execution of this statement. CURRENT FUNCTION PATH may also be specified.

host-variable

A variable of type CHAR or VARCHAR. The length of the contents of the host-variable must not exceed 30 bytes (SQLSTATE 42815). It cannot be set to null. If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 42815).

The characters of the host-variable must be left justified. When specifying the schema-name with a host-variable, all characters must be specified in the exact case intended as there is no conversion to uppercase characters.

string-constant

A character string constant with a maximum length of 8.

Rules

• A schema name cannot appear more than once in the function path (SQLSTATE 42732).
• 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, doubling quotes within the schema name as necessary, and then separating each schema name by a comma. The length of the resulting string cannot exceed 254 bytes (SQLSTATE 42907).

Notes

• The initial value of the CURRENT PATH special register is "SYSIBM","SYSFUN","X" where X is the value of the USER special register.
• The schema SYSIBM does not need to be specified. If it is not included in the SQL path, it is implicitly assumed as the first schema (in this case, it is not included in the CURRENT PATH special register).
• The CURRENT PATH special register specifies the SQL path used to resolve user-defined data types, procedures and functions in dynamic SQL statements. The FUNCPATH bind option specifies the SQL path to be used for resolving user-defined data types and functions in static SQL statements. See the Command Reference for further information on the use of FUNCPATH option in BIND command.

Example

Example 1:  The following statement sets the CURRENT FUNCTION PATH special register.

   SET PATH = FERMAT, "McDrw #8", SYSIBM

Example 2:  The following example retrieves the current value of the CURRENT PATH special register into the host variable called CURPATH.

   EXEC SQL VALUES (CURRENT PATH) INTO :CURPATH;

The value would be "FERMAT","McDrw #8","SYSIBM" if set by the previous example.