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

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).

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:

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:

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.