You use SET SCHEMA to define the default schema name for the unqualified name of integrity constraints, indexes and tables that occur in statements subsequently prepared with PREPARE or EXECUTE IMMEDIATE. The default schema name set with the precompiler option continues to be used to qualify the names of integrity constraints, indexes and tables for all other statements. Until the time that the first SET SCHEMA statement is executed, the schema name set with the precompiler option is used as the default schema name for all statements.
The defined default determined by SET SCHEMA is revoked when the immediately following transaction - the current UTM transaction in the case of openUTM - is rolled back. This is also true if the transaction immediately following SET SCHEMA only contains CALL DML statements.
Otherwise the default schema name you set with SET SCHEMA is valid until a new schema name is set with SET SCHEMA or until the end of the SQL session.
You will find information on the general rules for implicit database and schema names in section "Qualified names".
The SET SCHEMA statement does not initiate a transaction.
SET SCHEMA default_schema
default_schema ::= { alphanumeric_literal | :host_variable }
default_schema
Name of the default schema for the current SQL session. You can qualify the unqualified schema name with a database name.
If you qualify the schema name with a database name, this database name is used as the default database name as if it has been set with SET CATALOG.
alphanumeric_literal
The schema name is specified as an alphanumeric literal (not in the hexadecimal format).
host_variable
The schema name is specified as an alphanumeric host variable of the type CHAR or VARCHAR. The host variable cannot be a vector and cannot have an associated indicator variable.
Examples
Example from the sample database:
SET SCHEMA 'ordercust.orderproc'
Example from the dynamic SQL:
The host variable SOURCESTMT contains the following statement:
CREATE TABLE ordstat (order_stat_num INTEGER, order_stat_text CHAR(15))
The following statements execute a CREATE TABLE statement for the table ORDSTAT in the schema ORDERPROC of the database ORDERCUST:
SET SCHEMA 'ordercust.orderproc'
EXECUTE IMMEDIATE :SOURCESTMT
See also
SET CATALOG