SQL path

The SQL path is an ordered list of schema names. DB2® uses the path to resolve the schema name for certain unqualified object names that appear in any context other than as the main object of an ALTER, CREATE, DROP, COMMENT, GRANT, RENAME, or REVOKE statement.

DB2 uses the path to resolve the schema name for the following object names:
  • data types (both built-in types and distinct types)
  • functions
  • stored procedures

Searching through the path from left to right, DB2 implicitly qualifies the object name with the first schema name in the SQL path that contains the same object with the same unqualified name for which the user has appropriate authorization. For functions, DB2 uses a process called function resolution in conjunction with the SQL path to determine which function to choose because several functions with the same name and number of parameters but different parameter data types might be defined in the same schema or other schemas in the SQL path. (For details, see Function resolution.) For procedures, DB2 selects a matching procedure name only if the number of parameters is also the same.

The SQL path does not apply to unqualified procedure names in ASSOCIATE LOCATOR and DESCRIBE PROCEDURE statements. For these statements, an implicit schema name is not generated.

For an example of how DB2 uses the SQL path to resolve the schema name, assume that the SQL path is SMITH, XGRAPHIC, SYSIBM, and that an unqualified distinct type name MYTYPE was specified. DB2 looks for MYTYPE first in schema SMITH, then XGRAPHIC, and then SYSIBM.

The PATH option establishes the SQL path that is used to resolve:

  • Unqualified data type and function names in static SQL statements
  • Unqualified procedure names in SQL CALL statements that specify the procedure name as an identifier token (CALL procedure-name)

If the PATH option was not specified when the plan or package was created or last rebound or when native SQL procedure was defined or last changed, the default value of the SQL path is: SYSIBM, SYSFUN, SYSPROC, plan or package qualifier.

The CURRENT PATH special register determines the SQL path used to resolve:

  • Unqualified data type and function names in dynamic SQL statements
  • Unqualified procedure names in SQL CALL statements that specify the procedure name in a host variable (CALL host-variable)

Generally, the initial value of the CURRENT PATH special register is one of the following:

  • The value of the PATH option
  • "SYSIBM", "SYSFUN", "SYSPROC", value of CURRENT SQLID special register if the PATH option was not specified.

If schema "SYSIBM", "SYSFUN", "SYSPROC" is not explicitly specified in the SQL path, the schema is implicitly assumed at the front of the path; if all are not specified, they are assumed in the order of "SYSIBM", "SYSFUN", "SYSPROC".

For example, assume that the SQL path is explicitly specified as SYSIBM, GEORGIA, SMITH. As implicitly assumed schemas, SYSFUN and SYSPROC are added to the beginning of the explicit path effectively making the path:
  • SYSFUN, SYSPROC, SYSIBM, GEORGIA, SMITH

For more information about the SQL path for dynamic SQL, see CURRENT PATH and SET PATH.