SET SCHEMA

The SET SCHEMA statement assigns a value to the CURRENT SCHEMA special register. If the package is bound with the DYNAMICRULES BIND option, this statement does not affect the qualifier that is used for unqualified database object references.
Restriction: This topic applies to Windows environments only.
Restriction: This command is only supported syntactically by the XDB Server.

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

Bold text indicates clauses or options that are supported only syntactically. 

SET {SCHEMA | CURRENT SCHEMA | CURRENT_SCHEMA} [=] 
     {schema-name | 
      USER | 
      host-variable | 
      string-constant | 
      DEFAULT}

Description

schema-name
Identifies a schema. No validation that the schema exists is made at the time the CURRENT SCHEMA is set. For example, if a schema name is misspelled, it could affect the way subsequent SQL operates.
USER
Specifies the value of the USER special register.
host-variable
Specifies a host variable that contains a schema name. The content is not folded to uppercase. The host variable must:
  • Be a CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC variable. The actual length of the contents of the host-variable must not exceed the length of a schema name.
  • Not be set to null. If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value.
  • Include a schema name that is left justified and conforms to the rules for forming an ordinary identifier or delimited identifier. If the identifier is delimited, it must not be empty or contain only blanks.
  • Be padded on the right with blanks if the host variable is fixed length.
  • Not contain USER or DEFAULT.
string-constant
Specifies a string constant that contains a schema name. The content is not folded to uppercase. The string constant must:
  • Have a length that does not exceed the maximum length of a schema name.
  • Include a schema name that is left justified and conforms to the rules for forming an ordinary identifier or delimited identifier. If the identifier is delimited, it must not be empty or contain only blanks.
  • Not contain USER or DEFAULT.
DEFAULT
Specifies that CURRENT SCHEMA is to be set to its initial value, as if it had never been explicitly set during the application process.
Note:
  • There is a difference between specifying a single keyword (such as USER or DEFAULT) as a single keyword or as a delimited identifier. To indicate that the current value of the USER special register should be used for setting the current schema, specify USER as a keyword. To indicate that the special register should be set to its default value, specify DEFAULT as a keyword. If USER or DEFAULT is specified as a delimited identifier instead (for example, "USER"), it is interpreted as a schema name of that value ("USER").
  • The SET SCHEMA statement is not a commitable operation. ROLLBACK has no effect on CURRENT SCHEMA.
  • The value of the CURRENT SCHEMA special register, as set by this statement, is used as the schema name in all dynamic SQL statements. The QUALIFIER bind option specifies the schema name for use as the qualifier for unqualified database object names in static SQL statements.
  • Setting the CURRENT SCHEMA special register does not affect any other special register. Therefore, the CURRENT SCHEMA is not be included in the SQL path that is used to resolve the schema name for unqualified references to function, procedures and user-defined types in dynamic SQL statements. To include the current schema value in the SQL path, whenever the SET SCHEMA statement is issued, also issue the SET PATH statement including the schema name from the SET SCHEMA statement.
Parent topic: SQL Statements