SET PATH statement

This statement 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

None required.

Syntax

          .-CURRENT-.          .-=-.   
>>-SET--+-+---------+--PATH-+--+---+---------------------------->
        '-CURRENT_PATH------'          

   .-,------------------------.   
   V                          |   
>----+-schema-name----------+-+--------------------------------><
     +-SYSTEM PATH----------+     
     +-USER-----------------+     
     +-+-CURRENT PATH-+-----+     
     | '-CURRENT_PATH-'     |     
     +-CURRENT PACKAGE 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, the error 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","SYSPROC","SYSIBMADM".

USER

The value of the USER special register.

CURRENT PATH

The value of the CURRENT PATH special register before this statement executes.

CURRENT PACKAGE PATH

The value of the CURRENT PACKAGE PATH special register.

host-variable

A variable of type CHAR or VARCHAR. The length of the contents of the host-variable must not exceed 128 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-aligned. 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 128 bytes.

Rules

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

Notes

• The initial value of the CURRENT PATH special register is "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","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 function names, procedure names, data type names, global variable names, and module object names in dynamic SQL statements. The FUNCPATH bind option specifies the SQL path to be used for resolving function names, procedure names, data type names, global variable names, and module object names in static SQL statements.
• Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of DB2® and with other database products. These alternatives are non-standard and should not be used.
  • CURRENT FUNCTION PATH can be specified in place of CURRENT PATH

Examples

• Example 1:  The following statement sets the CURRENT 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.