Unqualified object name resolution

Most object names are implicitly or explicitly qualified with a schema name. Synonyms are an exception.

A synonym has a single part name. When Db2 encounters an unqualified name, Db2 must determine which object to process. This process is called name resolution.

When Db2 encounters a single part name in a context where an alias, table, view, or synonym can be specified, Db2 first checks to see if the name refers to a synonym that is defined by the current user.

Unqualified object names, other than synonyms, are implicitly qualified. The rules for qualifying a name differ depending on the type of object that the name identifies.

Unqualified alias, index, JAR file, mask, permission, sequence, table, trigger, and view names

The default schema is determined as follows:

  • Start of changeFor static SQL statements, the default schema is the identifier specified in the QUALIFIER option of the BIND subcommand or the CREATE or ALTER statement for a SQL routine or trigger. If this option is not in effect for the plan, package, or native SQL procedure, the default schema is the authorization ID of the owner of the plan, package, or native SQL procedure.End of change
  • For dynamic SQL statements, the behavior as specified by the combination of the DYNAMICRULES option and the run time environment determines the default schema. (For a list of these behaviors and the DYNAMICRULES values that determine them, see Table 1).
    • If DYNAMICRULES run behavior applies, the default schema is the schema in the CURRENT SCHEMA special register. Run behavior is the default.
    • If bind behavior applies, the default schema is the identifier that is implicitly or explicitly specified in the QUALIFIER option, as explained for static SQL statements.
    • If define behavior applies, the default schema is the owner of the function or stored procedure (the owner is the definer).
    • If invoke behavior applies, the default schema is the authorization ID of the invoker of the function or stored procedure.
    Exception: For bind, define, and invoke behavior, the default schema of PLAN_TABLE, DSN_STATEMNT_TABLE, and DSN_FUNCTION_TABLE (output from the EXPLAIN statement) is always the value in special register CURRENT SQLID.

Unqualified type, function, procedure, global variable, and specific names

The qualification of unqualified type (built-in type, distinct type, or array type), function, stored procedure, global variable, and specific names depends on the SQL statement in which the unqualified name appears.

  • If an unqualified name is the main object of an ALTER, CREATE, COMMENT, DROP, GRANT, or REVOKE statement, the name is implicitly qualified with a schema name as follows:
    • Start of changeIn a static statement, the implicit schema name is the identifier specified in the QUALIFIER option of the BIND subcommand or the CREATE or ALTER statement for a SQL routine or trigger. If this option is not in effect for the package or procedure, the implicit qualifier is the authorization ID of the owner of the package or procedure.End of change
    • In a dynamic statement, the implicit schema name is the schema in the CURRENT SCHEMA special register.
  • Otherwise, the implicit schema name for the unqualified name is determined as follows:
    • For distinct type and array type names, Db2 searches the SQL path and selects the first schema in the path such that the data type exists in the schema and the user has authorization to use the type.
    • For global variable names, Db2 searches the SQL path and selects the first schema in the path such that the global variable exists in the schema and the user has authorization to use the global variable.
    • For function names, Db2 uses the SQL path in conjunction with function resolution, as described in Function resolution.
    • For stored procedure names in CALL statements, Db2 searches the SQL path and selects the first schema in the path such that the schema contains a procedure with the same name and number of parameters and the user has authorization to use the procedure.
    • For stored procedure names in ASSOCIATE LOCATORS and DESCRIBE PROCEDURE statements, Db2 does not use the SQL path because an implicit schema name is not generated for these statements.

    For information about the SQL path, see SQL path.