Netezza cross database access does allows querying on 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. This would be useful while we need to join data from another source or look up data using a select clause on a different database.
Example when test and dev database are on the same server, you could insert certain test data into dev using
insert into [dev table] select * from [test table]
Referencing Database Object from other Database
Refer to objects in other databases on the same Netezza system, you must use three-level naming, which consists of the following components:
- The database — The database or catalog name
- The schema — The schema, which is the name of the database owner.
- The 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.
database..object-name: Another way of specifying fully qualified format.
Schema.object: You can use this notation when referring current database
Note that you cannot use cross-database INSERT, UPDATE, or DELETE statements. If you attempt to do so, the system displays an error message. For example, if you attempt to insert data into a table that does not reside in the current database (the database you are logged in to), the system displays an error message:
dev(admin)=>INSERT INTO PROD..EMP SELECT * FROM EMP;
Cross Database Access not supported for this type of command.
For this type of query, consider changing the query to a cross-database SELECT statement (which is supported) while logged in to the target database.
For example:
prod(admin)=>INSERT INTO EMP SELECT * FROM DEV..EMP;
You cannot use CREATE, ALTER, or DROP commands to change objects in a database outside your current database. If you attempt to do so, the system displays an error message.
For example, if you attempt to create an object in a different database, the system displays an error message:
dev(admin)=>CREATE PROD..PAYROLL ;
Cross Database Access not supported for this type of command.
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
