Cross-database examples in multiple schema systems

If the system is configured to support multiple schemas within a database, the system still follows the rules and restrictions for cross-database access. Within a database that contains multiple schemas, there are some important guidelines for object names and also cross-schema operations.

For example, assume that the Netezza Performance Server system has two databases, DEV and PROD. The DEV database has schemas named SCH1 (the default schema) and SCH2. In the SCH1 schema there is a table named TEST1. In SCH2 there is a table named TEST2. The PROD database has a schema named ENG (the default schema) and SALES. The SALES schema has a table named NE_SALES.

While connected to the DEV.SCH1 database, you can reference objects in SCH1 using only the object name. For example, to retrieve all rows from TEST1, you can use the following command:

DEV.SCH1(ADMIN)=> SELECT * FROM test1;

From DEV.SCH1, you cannot access objects in SCH2 using only the object name. For example:

DEV.SCH1(ADMIN)=> SELECT * FROM test2;
ERROR: relation does not exist DEV.SCH1.TEST2

You must use the schema.object format or a fully qualified name to select from a table that is in a different schema in the same database, for example:

DEV.SCH1(ADMIN)=> SELECT * FROM sch2.test2;

When connected to the DEV database, you can access objects in the PROD database using a fully qualified object name, for example:

DEV.SCH2(ADMIN)=> SELECT * FROM prod.sales.ne_sales;

From the DEV database, if you wanted to access a table in the ENG schema of the PROD database, you can specify ENG in the object name or use “..” because ENG is the default schema for the database, for example:

DEV.SCH2(ADMIN)=> SELECT * FROM prod.eng.tbl1;

Netezza Performance Server supports cross-schema write actions for schemas that reside in the same database and across other databases. Several examples follow:

DEV.SCH1(ADMIN)=> CREATE SEQUENCE sch2.seq1;
DEV.SCH1(ADMIN)=> CREATE TABLE sales_old AS SELECT * FROM sch2.sales;
DEV.SCH1(ADMIN)=> TRUNCATE TABLE sch2.sales;
DEV.SCH1(ADMIN)=> INSERT INTO sch2.sales SELECT * FROM sales_old;