IBM PureData System for Analytics, Version 7.1

Cross-database usage examples in single-schema systems

In the following examples, the IBM® Netezza® system has two databases, PROD and DEV, and each database has a table that is named EMP. While connected to the DEV database, you can retrieve all rows from the table EMP using the following command.

DEV.DBUSER(DBUSER)=> SELECT * FROM emp;

Note: Each database has a default schema that is named for the user that created the database, such as the user DBUSER in this example. These examples show that the DBUSER created both the DEV and PROD databases.

While connected to DEV, if you want to select rows from the EMP table in PROD, you can use the database.schema.object name format to identify the table, for example:

DEV.DBUSER(DBUSER)=>SELECT * FROM prod.dbuser.emp;

Note: As a best practice, use fully qualified object names such as PROD.DBUSER.EMP to refer to objects. In a single-schema IBM Netezza environment, you could also use the "dot dot" notation such as PROD..EMP and the database uses the default and only schema of the database. However, you should avoid the "dot dot" notation as a best practice. In a single-schema IBM Netezza environment, the value you specify for schema is ignored. If you specify an invalid schema, the system uses the default schema and does not display an error.

You cannot use a cross-database INSERT, UPDATE, or DELETE statements to change a table in a different, remote, database. If you attempt a cross-database write operation, the system displays an error message. For example:

DEV.DBUSER(DBUSER)=>INSERT INTO prod..emp SELECT * FROM newusers;
Cross Database Access not supported for this type of command.

For this type of query, you can change the query to a cross-database SELECT statement by using the SET CATALOG command to change to the PROD database. For example:

DEV.DBUSER(DBUSER)=>SET CATALOG PROD;
SET CATALOG
PROD.DBUSER(DBUSER)=>INSERT INTO emp SELECT * FROM dev.dbuser.newusers;

You cannot use CREATE, ALTER, or DROP commands to change objects in a database that is not your current database. If you attempt to do so, the system displays an error message. For example:

DEV.DBUSER(DBUSER)=>CREATE SEQUENCE prod.dbuser.seq1;
Cross Database Access not supported for this type of command.