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