Accessing Other Databases

You can execute queries that reference tables, views, and synonyms in other databases on the same Netezza server. 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 so on.
When specifying reference objects, keep in mind the following:

􀁘 You must specify reference objects that reside on the same Netezza server.
􀁘 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.


Referencing Database Objects
To refer to objects in other databases on the 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. Note that in
Netezza release 3.0, there is only one schema per database.
􀁘 The object — The name of the object, table, view, or synonym.


Database Object Naming
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.

􀁘 The database-name.schema.object-name — The three-level or fully qualified form.
􀁘 The database-name..object-name — A convenient way of specifying a fully qualified
object name. The system supplies the schema name by internally inserting the current
schema name.
􀁘 The schema.object-name — The two-level form, which you can use only when referring
to the current database.


Cross-Database Usage Examples
In the following examples, the Netezza system has two databases: DEV and PROD. Both
databases have tables named EMP. A client program connected to DEV is able to reference tables in PROD in the FROM clause of SQL queries. This is referred to as a cross-database query.

Note: The following examples use the implicit schema represented by “..” between the
database name and the table or view name.

To retrieve all rows from the table EMP in connected-to database DEV, enter:
dev(admin)=>SELECT * FROM DEV..EMP;

To retrieve all rows from the table EMP in the database PROD, enter:
dev(admin)=>SELECT * FROM PROD..EMP;

To truncate the contents of DEV.EMP.
dev(admin)=>TRUNCATE TABLE EMP;

To insert the contents of PROD..EMP into DEV..EMP.
dev(admin)=>INSERT INTO EMP SELECT * FROM PROD..EMP;

To join tables from both PROD and DEV.
dev(admin)=>SELECT COUNT (*) FROM DEV..EMP DE, PROD..EMP PE WHERE
DE.ID = PE.ID;

To retrieve rows from PROD.EMP, enter:
dev(admin)=>SELECT * FROM PROD..EMP WHERE PROD..EMP.DEPTNO IN (SELECT
DE.DEPTNO FROM DEV..EMP DE WHERE DE.ID < 10);

To create a table from PROD.EMP and DEV.EMP, enter:
dev(admin)=>CREATE TABLE KEYEMPS AS SELECT * FROM PROD..EMP INTERSECT
SELECT * FROM DEV..EMP;

Common Error Messages
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.


Qualified Column Names
When a query involves multiple tables, it is sometimes difficult to know which column
belongs to which table especially if the tables have the same column names. To help distinguish among column names, SQL allows you to fully qualify column names by specifying the column as: database.schema.table.column.

When referring to column names in cross-database access, the system expects the qualified column name to be in the form of exposed-table-reference.column-name; where the exposed-table-reference is any of the acceptable tables references in the FROM clause.

For example, emp, admin.emp, dev.admin.emp and dev..emp are all equivalent forms for
the same table:

FROM emp WHERE dev.admin.emp.id = 10;
FROM dev.admin.emp WHERE emp.id = 10;
FROM emp WHERE admin.emp.id = 10;
FROM emp WHERE dev.admin.emp.id = 10;
FROM dev..emp WHERE admin.id = 10;


Table and Column Aliases
Aliases are like synonyms in that they are alternate names for tables or columns. Aliases differ from synonyms in that they exist only for the duration of the query.
Aliases can be single letters or words, but when using aliases in cross-database access, the system expects the column name to be in the form of the alias.column-name.

For example, the following are correct notations, where E is the alias:

dev(admin)=>FROM emp E WHERE E.id =10
dev(admin)=>FROM admin.emp E WHERE E.id =10

Using Synonyms

You can create SQL synonyms as an alternate way of referencing tables or views that reside in the current or other databases on the Netezza system. Synonyms allow you to create easily typed names for long table or view names. They also allow you to create a level of abstraction for the database objects and thereby enable you to swap the underlying objects without affecting the code that references these objects.

Synonyms share the same naming restrictions as tables and views, that is, they must be unique within a database and their names cannot be the same as global objects such as those of databases, users, or groups.

You can use the following synonym commands:
􀁘 CREATE SYNONYM — To create a synonym
􀁘 DROP SYNONYM — To drop/delete a synonym
􀁘 ALTER SYNONYM — To rename or change the owner of a synonym
􀁘 GRANT SYNONYM — To grant permission to create, alter, or drop a synonym to a user
or group
􀁘 REVOKE SYNONYM — To revoke permission to create, alter, or drop a synonym to a
user or group

2 comments:

  1. Hello Admin,

    Thanks for the blog. I could understand that very well as a beginner. I just started to work on netezza in my new project and i wish to have some pdf files or any reference books that i could go through to understand the complete picture of Netezza database, data modelling on netezza and tuning activities as well. Please help me get some . my mail id is madasamy.murugan@gmail.com

    ReplyDelete