Use the SET SCHEMA statement to set your default schema.


SET SCHEMA {DEFAULT | schema-name}


When the System Administrator creates a user identity for you, they either give you your own schema or allocate you to an existing schema. Subsequently, this schema is taken as your “default” schema, and any submission against a specified table searches the default schema. You can change the default schema for the current session with the SET SCHEMA command.

Before using the SET SCHEMA command, you can refer to tables in your own “default” schema without using a schema prefix:


But when you refer to tables in the schema you intend to set as default, you need to include the schema name:


After using the SET SCHEMA command:

SET SCHEMA otherschema

You can refer to the tables in the new default schema without a schema prefix:


But now you must add the schema prefix when referring to tables in your own schema:


The new schema remains as the default until

  • The session is disconnected, or

  • You issue another SET SCHEMA statement.

Re-allocating the default schema doesn’t automatically give access to tables in that schema—the privilege constraints still apply.

It isn’t necessary to specify the default schema name, as this is allocated at the time of user installation, and is automatically restored.

See “Example: Creating and Dropping Schemas” on page 14 for additional examples of SET SCHEMA use.