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.

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


          .-CURRENT-.            .-=-.                         
>>-SET--+-+---------+--SCHEMA-+--+---+--+-schema-name------+---><
        '-CURRENT_SCHEMA------'         +-+-SESSION_USER-+-+   
                                        | '-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.

SESSION_USER or USER

Specifies the value of the SESSION_USER (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 SESSION_USER, 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 SESSION_USER, 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. For information about the initial value of CURRENT SCHEMA, see CURRENT SCHEMA.

Notes

Considerations for keywords:: There is a difference between specifying a single keyword (such as SESSION_USER or DEFAULT) as a single keyword or as a delimited identifier. To indicate that the current value of the SESSION_USER special register should be used for setting the current schema, specify SESSION_USER as a keyword. To indicate that the special register should be set to its default value, specify DEFAULT as a keyword. If SESSION_USER or DEFAULT is specified as a delimited identifier instead (for example, "SESSION_USER"), it is interpreted as a schema name of that value ("SESSION_USER").
Transaction considerations:: The SET SCHEMA statement is not a committable operation. ROLLBACK has no effect on CURRENT SCHEMA.
Usage of the assigned value:: 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.
Impact on other special registers:: 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.

Examples

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

   EXEC SQL 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 SELECT CURRENT SCHEMA INTO :CURSCHEMA
      FROM SYSIBM.SYSDUMMY1;

The value of the host variable is RICK.

Example 3: Assume that the following statements are issued:

SET CURRENT SQLID = 'USRT001';
SET CURRENT SCHEMA = 'USRT002';

At this point, the two special registers contain different values. Any subsequent CREATE statements will use USRT002 as the implicit qualifier, but the owner of the newly created objects is USRT001.

Example 4: Assume that the value of CURRENT SCHEMA is 'Jane' and that the default value of the PATH special register was established using that value (that is, the value of PATH is "SYSIBM", "SYSFUN", "SYSPROC", "JANE"). Change the value of the CURRENT SCHEMA special register to 'John'.

   SET CURRENT SCHEMA = 'JOHN';

To change the SQL path to use the updated CURRENT SCHEMA value of "JOHN", issue a SET PATH statement to change the value of the PATH special register to specify "JOHN" as the first schema to check:

SET PATH = 'JOHN', CURRENT PATH;

Alternatively, a commit would cause PATH to be re-initialized. Otherwise, the path remains "SYSIBM", "SYSFUN", "SYSPROC", "JANE"), which might cause unqualified object names to resolve to "JANE" when you want them to resolve to "JOHN".