SET SCHEMA

The SET SCHEMA statement changes the value of the CURRENT SCHEMA special register.

Invocation

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

Authorization

Start of change If a global variable is referenced in the statement, the privileges held by the authorization ID of the statement must include at least one of the following:End of change

Start of change
  • For the global variable identified in the statement,
    • The READ privilege on the global variable, and
    • The system authority *EXECUTE on the library containing the global variable
  • Administrative authority
End of change

Syntax

Read syntax diagramSkip visual syntax diagram
          .-CURRENT-.            .- = -.   
>>-SET--+-+---------+--SCHEMA-+--+-----+------------------------>
        '-CURRENT_SCHEMA------'            

>----+-schema-name------+--------------------------------------><
     +-+-SESSION_USER-+-+     
     | '-USER---------' |     
     +-SYSTEM_USER------+     
     +-variable---------+     
     +-string-constant--+     
     '-DEFAULT----------'     

Description

schema-name
Start of changeIdentifies a schema. If the specified schema name is a system schema name, no validation that the schema exists is made at the time the current schema is set. If the specified schema name is not a system schema name, the schema must exist at the time the current schema is set.

If the value specified does not conform to the rules for a schema-name, an error is returned.

End of change
SESSION_USER or USER
This value is the SESSION_USER special register.
SYSTEM_USER
This value is the SYSTEM_USER special register.
variable
Specifies a variable which contains a schema name. Start of changeIt can be a global variable if it is qualified with schema name. End of changeThe content is not folded to uppercase.

The variable:

  • Must be a character-string or Unicode graphic variable. The actual length of the contents of the variable after trimming any trailing blanks must not exceed the length of a schema name. See SQL limits.
  • Must not be followed by an indicator variable.
  • Must not be the null value.
  • Must conform to the rules for forming an ordinary or delimited identifier.
  • Must be padded on the right with blanks if the variable is fixed length.
  • Must not be the keyword SESSION_USER, SYSTEM_USER, or USER.
string-constant
A character constant with a schema name.

The string constant:

  • Must have a length after trimming any trailing blanks that does not exceed the maximum length of a schema name
  • Must include a schema name that is left justified and conforms to the rules for forming an ordinary or delimited identifier.
  • Must not be the keyword SESSION_USER, SYSTEM_USER, or USER.
DEFAULT
The CURRENT SCHEMA is set to its initial value. The initial value for SQL naming is USER. The initial value for system naming is *LIBL.

Notes

Considerations for keywords: There is a difference between specifying a single keyword (such as USER) 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. If USER is specified as a delimited identifier instead (for example, "USER"), it is interpreted as a schema name of that value ("USER").

Transaction considerations: The SET SCHEMA statement is not a committable operation. ROLLBACK has no effect on the CURRENT SCHEMA.

Impact on other special registers: 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 SCHEMA: The value of the CURRENT SCHEMA special register is used as the qualifier for some unqualified names in all dynamic SQL statements except in programs where the DYNDFTCOL has been specified. If DYNDFTCOL is specified in a program, its schema name is used instead of the CURRENT SCHEMA schema name. For information about qualification of names, see Qualification of unqualified object names.

For SQL naming, the initial value of the CURRENT SCHEMA special register is equivalent to USER. For system naming, the initial value of the CURRENT SCHEMA special register is '*LIBL'.

Syntax alternatives: 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.

SET SCHEMA is equivalent to calling the QSQCHGDC API.

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.

Parent topic: Statements