Netezza cross database access does allows you to execute the objects such as tables, view, synonyms that are available on the same Netezza server. You can can INSERT, UPDATE or DELETE data from current database by referring objects in other database on same server.
For example,
TRAINING1.ADMIN(ADMIN)=>SELECT * FROM TRAINING1..TEST1;
Read:
- Access Netezza Database, Tools and Examples
- Netezza nzsql Command and its Usage
- Netezza Synonym Best Practices and Examples
Referencing Database Object from other Database
To access objects in other databases on the same Netezza system, you must use three-level naming, which consists of the following components:
- Database: Name of database to which you are connecting
- Schema: Which is the name of the database owner
- Object: The name of the object, table, view or synonym.
You specify objects in the FROM clause of SQL statements in the three-level form. You can use the standard form or the shorthand notation.
database.schema.object-name: This is the fully qualified form.
For example,
TRAINING1.ADMIN(ADMIN)=>SELECT * FROM TRAINING1.admin.TEST1;
database..object-name: Another way of specifying fully qualified format.
For example,
TRAINING1.ADMIN(ADMIN)=>SELECT * FROM TRAINING1..TEST1;
Schema.object: You can use this notation when referring current database
For example,
TRAINING1.ADMIN(ADMIN)=>SELECT * FROM admin.TEST1;
Netezza Cross Database Access Examples
Let’s consider we have two data bases TRAINING1 and TRAINING2:
Select rows from table TEST1 present in TAINING1:
TRAINING1.ADMIN(ADMIN)=>SELECT * FROM TRAINING1..TEST1;
Select rows from table TEST1 present in TAINING2:
TRAINING1.ADMIN(ADMIN)=>SELECT * FROM TRAINING1..TEST1;
Join TEST1 table present in both TRAINING1 and TRAINIGN2:
TRAINING1.ADMIN(ADMIN)=>SELECT tab1.col1, tab2.col2 FROM TRAINING1..TEST1 tab1 join TRAINING2..TEST1 tab2 on(tab1.col1 = tab2.col1) ;
Netezza Cross Database Access Restrictions
Following are some of the restrictions when referring objects from other databases:
- One can access the objects resides on same server, you cannot access the tables on other server.
- You cannot specify a cross-reference object in the SELECT portion of a CREATE MATERIALIZED VIEW statement.
- One cannot insert data into a table that does not reside in the current database.
- You cannot create object in the different database