Cross-database usage examples in single-schema systems

In the following examples, the 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 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 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 can use cross-database INSERT, UPDATE, or DELETE statements to change a table in a different, remote, database. For example:

DEV.DBUSER(DBUSER)=>INSERT INTO prod..emp SELECT * FROM newusers;