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.
Redpoint Data Management v9.6.1 introduces a next-generation set of database providers that were rewritten to be more robust and performant. Refer to RDBMS database providers for a table that delineates next-generation data provider availability. The RDBMS schemas vary between these next-generation data providers and the legacy providers. Refer to the appropriate section below.
Next-generation data providers
NEW IN 9.6.1
The next-generation data providers introduced in RPDM v9.6 and later implement the following schema discovery heuristics:
If the tablespace is explicitly chosen in the UI, RPDM always uses it; for example when reading a table it will use
SELECT * FROM table. It is entirely up to the RDBMS and/or its driver how to interpret the default tablespace/schema/catalog.If the tablespace is not specified AND the RDBMS doesn’t fill in the default (i.e., anywhere RPDM is naming the table in an API call instead of SQL statements), then RPDM will run a database-specific query to ask for the default tablespace.
Legacy data providers
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.