Access other databases

You can run queries that reference tables, views, and synonyms in other databases on the same system. This means that you can use these references in the SELECT statement FROM clauses in queries that can include INSERT, DELETE, UPDATE, CREATE TABLE AS, joins, set operations, aggregations, subselects, view definitions, and similar contexts.

Netezza Performance Server allows some SQL statements to modify data in a different database, provided that the user has the necessary permissions on the other database and on the object being modified:
  • Create Table
  • Create View
  • Insert
  • Delete
  • Update
  • Merge
  • Truncate Table
  • Groom Table
Other statements, including, but not limited to, the following, cannot modify data in other databases - an error will be returned if that is attempted:
  • Generate Statistics
  • Alter Table
    • Add Column
    • Drop Column
    • Rename Column
    • Modify Column
  • Create Materialized View
  • Alter View Materialize
  • Grant
  • Revoke
When you are specifying reference objects, keep in mind the following guidelines:
  • You must specify reference objects that are on the same Netezza Performance Server system.
  • You cannot specify reference objects that are under control of third-party applications.
  • You cannot specify a cross-reference object in the SELECT portion of a CREATE MATERIALIZED VIEW statement.