SET statements

SET CATALOG 'catalog';

Sets the name of the catalog from which schemas, tables, and other database objects will be resolved for the current session. The default catalog is called “LOCALDB”, and this is where you should create application schemas. You can override the catalog by using fully-qualified object names:

SELECT * FROM LOCALDB.SALES.Emps

SQLstream s-Server has several catalogs predefined (SYS_BOOT, SYS_MOF, SYS_CWM, SYS_FEM, and LOCALDB). Additional catalogs can be created implicitly via the CREATE SERVER command. The SYS_BOOT catalog contains internal tables for metadata storage, along with things like views for implementing JDBC metadata. The SYS_MOF catalog is a virtual catalog exposing the highest-level metamodel. The SYS_CWM catalog is a virtual catalog exposing the contents of the CWM-based repository. SYS_FEM is similar, but for Farrago extensions to CWM.

Catalog Description
SYS_BOOT Contains internal tables for metadata storage, along with things like views for implementing JDBC metadata.
SYS_MOF Virtual catalog exposing the highest-level metamodel.
SYS_CWM Virtual catalog exposing the contents of the CWM-based repository.
SYS_FEM Similar to SYS_CWM, but for Farrago extensions to CWM.
LOCALDB Stores user metadata about both local and foreign tables.

SET PATH

SET PATH 'path_element [, path_element...]';

Defines the lookup scope for references to user-defined routines. Note that according to the SQL standard the PATH does not have to be the same as the SCHEMA. For example:

SET PATH 'CommonProcs';

Here is a more complex example, using the CURRENT_PATH operator to build a path of three elements then print it:

SET PATH 'sys_boot.jdbc_metadata, sales';
SET PATH current_path | | ', sys_cwm."Relational"';
VALUES current_path;
+----------------------------------------------------------------------+
| CURRENT_PATH |
+----------------------------------------------------------------------+
| "SYS_BOOT"."JDBC_METADATA","LOCALDB"."SALES","SYS_CWM"."Relational" |
+----------------------------------------------------------------------+

See the topic CREATE FUNCTION in this guide for examples of how paths are used to resolve routine definitions.

SET SCHEMA

SET SCHEMA 'schema_name';

Sets or changes the default schema within the current catalog. This enables you to reference objects in the schema without fully qualifying the name. For example:

SET SCHEMA 'SALES';
SELECT * FROM EMPS;
-- has the same effect as
SELECT * FROM SALES.EMPS;

User schemas are created in the LOCALDB catalog. SET SCHEMA changes the default schema within the current catalog. The SYS_CWM catalog defines one schema per CWM package (e.g. “Core”, “Relational”, or “KeysIndexes”); the tables in the CWM schemas correspond to classes (e.g., schema SYS_CWM.“Core” contains “ModelElement” and “Feature”; schema SYS_CWM.“Relational” contains “Table” and “View”). Likewise for SYS_FEM.

The behavior of the SET SCHEMA command is dictated by the SQL standard, which specifies that the string between the single-quotes is an expression that is to be evaluated whenever a default schema is required. Therefore, identifiers that contain lower-case characters, spaces or other punctuation must be enclosed in double-quotes, as follows:

SET SCHEMA '"Mixed-case schema"';
SELECT * FROM UpperCaseTable;
-- has the same effect as
SELECT * FROM "Mixed-case schema".UPPERCASETABLE;