RDBMS schemas

Overview

Some databases (notably Oracle, SQL Server, DB2, and PostgreSQL) organize tables into schemas, which are logical top-level groupings. Each login has a default schema, so a user normally can access only those tables associated with the login's default schema. However, users with the required RDBMS permissions can access tables from other schemas.

In an SQL query tool, an RDBMS user would normally specify the schema as part of the table name, for example someschema.sometable. In Data Management, users can optionally specify a schema in the RDBMS Input and Output tools. When a schema is specified, Data Management treats every table selected in the interface as a member of that schema. This is true of even temporary tables used to assist updates, deletes, and dynamic queries. However, tables embedded within the text of an SQL query are not included as schema members.

On some databases  (notably MySQL), the schema is really the table owner. However it is described, referencing table "T" in schema "S" is always equivalent to specifying "T.S" in an SQL script.

Default schemas and search paths

Some database management systems support the notion of a per-user default schema, and a few (PostgreSQL, DB2) support a per-user search path specifying a hierarchy of schemas. If a schema is not specified in the RDBMS Input tool, Data Management will attempt to determine the default schema for the user specified in the data connection or the project, and use that. Data Management will query the search path and use the first schema in the search path as the default schema, ignoring any additional entries.

When in doubt, specify the schema explicitly. If you want to reconfigure an RDBMS Input tool from Direct table read to SQL query, and you have previously specified a schema for the direct table read, you should delete that schema specification and explicitly commit the change before configuring the SQL query. The schema specification is no longer visible when the tool is reconfigured for SQL query, but it is retained internally until the change is committed, and can be troublesome.