Netteza Cross-Database Access and its Restrictions

In this article, we will explore Netezza's cross-database access feature and its restrictions. Understanding these concepts is crucial for efficient data management in a multi-database environment.

What is Cross-Database Access?

Cross-database access allows you to access and manipulate data across multiple databases within your Netezza system. This feature enables you to create unified views of your data, perform complex queries that span multiple databases, and improve overall data integration and management.

Benefits of Cross-Database Access

Improved Data Integration: By allowing access to data across multiple databases, cross-database access simplifies data integration and reduces the complexity of managing multiple databases.
Faster Query Performance: With cross-database access, you can perform complex queries that span multiple databases, which can lead to significant performance improvements compared to querying individual databases separately.

Restrictions on Cross-Database Access

While cross-database access offers many benefits, it is not without its restrictions. The following are some of the key limitations:

Restriction	Description
Database Hierarchy	The databases must be part of a hierarchy, with one database serving as the "root" or parent database.
Data Type Compatibility	The data types used in the query must be compatible across all participating databases.
Access Control	You must have the necessary permissions to access the participating databases and perform cross-database queries.

Code Sample: Performing Cross-Database Queries with Netezza


SELECT *
FROM sales_db.my_table
JOIN orders_db.your_table ON my_table.customer_id = your_table.customer_id;

This code sample demonstrates a simple cross-database query that joins two tables from different databases. The query assumes that the participating databases are part of a hierarchy, with `sales_db` serving as the root or parent database.

Netezza cross-database access enables you to query objects such as tables, views, and synonyms available on the same Netezza server. You can insert, update, or delete data from your current database by referring to objects in other databases on the same server. This feature is useful when you need to join data from another source or look up data using a SELECT clause on a different database.

For instance, if you have test and dev databases on the same server, you could insert certain test data into dev using an INSERT statement:

insert into [dev table] select * from [test table]

To 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 or catalog name, the schema (the name of the database owner), and the object (the name of the table, view, or synonym).

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 either 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.
Scheme.object: You can use this notation when referring to the current database

However, you cannot use cross-database INSERT, UPDATE, or DELETE statements. If you attempt to do so, the system will display an error message. For example:

dev(admin)=>INSERT INTO PROD..EMP SELECT * FROM EMP;

Cross Database Access is not supported for this type of command. Instead, consider changing the query to a cross-database SELECT statement (which is supported) while logged in to the target database.

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 will display an error message. For example:

dev(admin)=>CREATE PROD..PAYROLL ;

Cross Database Access is not supported for this type of command.

Netezza Cross Database Access Restrictions

The following are some restrictions when referring to objects from other databases:

You can access only objects that reside on the same server. You cannot access tables on another server.
You cannot specify a cross-reference object in the SELECT portion of a CREATE MATERIALIZED VIEW statement.
You cannot insert data into a table that does not reside in the current database.
You cannot create an object in a different database

Conclusion

Cross-database access in Netezza offers a powerful way to integrate and manage data across multiple databases. While there are restrictions on this feature, understanding these limitations is crucial for designing effective cross-database queries and ensuring optimal performance.