SQL Reference

SET SCHEMA

The SET SCHEMA statement changes the value of the CURRENT SCHEMA special register. It is not under transaction control. If the package is bound with DYNAMICRULES BIND option, this statement has no effect.

Invocation

The statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

No authorization is required to execute this statement.

Syntax

        .-CURRENT-.          .-=-.
>>-SET--+---------+--SCHEMA--+---+----+-schema-name-----+------><
                                      +-USER------------+
                                      +-host-variable---+
                                      '-string-constant-'
 

Description

schema-name

This one-part name identifies a schema that exists at the application server. The length must not exceed 30 bytes (SQLSTATE 42815). No validation that the schema exists is made at the time that the schema is set. If a schema-name is misspelled, it will not be caught, and it could affect the way subsequent SQL operates.

USER

The value in the USER special register.

host-variable

A variable of type CHAR or VARCHAR. The length of the contents of the host-variable must not exceed 30 (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 justified. 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 30.

Rules

• If the value specified does not conform to the rules for a schema-name, an error (SQLSTATE 3F000) is raised.
• The value of the CURRENT SCHEMA special register is used as the schema name in all dynamic SQL statements, with the exception of the CREATE SCHEMA statement, where an unqualified reference to a database object exists.
• The QUALIFIER bind option specifies the schema name for use as the qualifier for unqualified database object names in static SQL statements (see the Command Reference for further information on use of the QUALIFIER option).

Notes

• The initial value of the CURRENT SCHEMA special register is equivalent to USER.
• Setting the CURRENT SCHEMA special register does not effect the CURRENT PATH special register. Hence, the CURRENT SCHEMA will not be included in the SQL path and functions, procedures and user-defined type resolution may not find these objects. 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.
• CURRENT SQLID is accepted as a synonym for CURRENT SCHEMA and the effect of a SET CURRENT SQLID statement will be identical to that of a SET CURRENT SCHEMA statement. No other effects, such as statement authorization changes, will occur.

Examples

Example 1:  The following statement sets the CURRENT SCHEMA special register.

  SET SCHEMA RICK

Example 2:  The following example retrieves the current value of the CURRENT SCHEMA special register into the host variable called CURSCHEMA.

  EXEC SQL VALUES (CURRENT SCHEMA) INTO :CURSCHEMA;

The value would be RICK, set by the previous example.