CURRENT PATH

CURRENT PATH specifies the SQL path used to resolve unqualified data type names and function names in dynamically prepared SQL statements. It is also used to resolve unqualified procedure names that are specified as host variables in SQL CALL statements (CALL host-variable).

The data type is VARCHAR(2048).

The CURRENT PATH special register contains a list of one or more schema names, where each schema name is enclosed in delimiters and separated from the following schema by a comma (any delimiters within the string are repeated as they are in any delimited identifier). The delimiters and commas are included in the 2048 character length.

For information on when the SQL path is used to resolve unqualified names in both dynamic and static SQL statements and the effect of its value, see SQL path.

The initial value of the CURRENT PATH special register is determined from the first of one of the following sets of conditions that is met:

  • If the CURRENT PATH special register was assigned a value (by a SET PATH statement) in the scope of the invoking environment, the special register inherits the value from the invoking environment.
  • If the connection is trusted, and both the role as object owner and qualifier options in effect, the special register is set to:"SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "role-name" where role-name is the role that is associated with the user in the trusted context.
  • Otherwise, the special register is set to: "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "current-sqlid-value".

The initial value of the CURRENT PATH special register in an SQL routine or trigger is determined from the first of one of the following sets of conditions that is met:

  • In an SQL routine, the special register inherits the value from the invoking environment, if the special register was assigned a value (by a SET PATH statement) in the scope of the invoking environment.
  • In a trigger, the special register inherits the value from the invoking environment, if the special register was assigned a value (by a SET PATH statement) in the scope of the invoking environment.
  • In an SQL routine or trigger, the special register is set to the value specified for the SQL PATH routine or trigger option, if specified.
  • In an SQL routine or trigger, the special register is set to the value specified for the SQL PATH routine or trigger option, if specified.
  • If the connection is trusted, and both the role as object owner and qualifier options in effect, the special register is set to:"SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "role-name" where role-name is the role that is associated with the user in the trusted context.
  • Otherwise, the special register is set to: "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM", "current-sqlid-value".

If the value of the CURRENT SQLID special register changes after the initial value of PATH special register is established, the value of the PATH special register is unaffected when the CURRENT SQLID is updated. However, if a commit later occurs and a SET PATH statement has not been processed, the value of PATH special register is reinitialized taking into consideration the current value of the CURRENT SQLID special register.

You can change the value of the register by executing the statement SET PATH. For portability across the platforms, it is recommended that a SET PATH statement be issued at the beginning of an application.

Example: Set the special register so that schema SMITH is searched before the system schemas:
   SET PATH = SMITH, SYSTEM PATH;