Specifying a Database Object in an External Database

You can specify a database object in either an external database on the local database server or in an external database on a remote database server.

Specifying a Database Object in a Cross-Database Query

To specify an object in another database of the local database server, you must qualify the identifier of the object with the name of the database (and of the owner, if the external database is ANSI compliant), as in this example:

corp_db:hrdirector.executives

In this example, the name of the external database is corp_db. The name of the owner of the table is hrdirector. The name of the table is executives. Here the colon ( : ) separator is required after the database qualifier.

In Dynamic Server, queries and other data manipulation language (DML) operations on other databases of the local database server can access the built-in opaque data types BOOLEAN, BLOB, CLOB, and LVARCHAR. DML operations can also access user-defined data types (UDTs) that can be cast to built-in types, as well as DISTINCT types that are based on built-in types, if each DISTINCT types and UDT is explicitly cast to a built-in type, and if all the DISTINCT types, UDTs, and casts are defined in all of the participating databases. The same data-type restrictions also apply to the arguments and to the returned values of a user-defined routine (UDR) that accesses other databases of the local Dynamic Server instance, if the UDR is defined in all of the participating databases.

Specifying a Database Object in a Cross-Server Query

To specify an object in a database of a remote database server, you must use a fully-qualified identifier that specifies the database, database server, and owner (if the external database is ANSI compliant) in addition to the database object name. For example, hr_db@remoteoffice:hrmanager.employees is a fully-qualified table name.

Here the database is hr_db, the database server is remoteoffice, the table owner is hrmanager, and the table name is employees. The at ( @ ) separator, with no blank spaces, is required between the database and database server qualifiers. Cross-server queries can only access columns of built-in data types that are not opaque data types. (You cannot access UDTs, nor opaque, complex, or other extended data types in cross-server operations.)

In Dynamic Server, if a UDR exists on a remote database server, you must specify a fully-qualified identifier for the UDR. Like cross-server DML operations, a remote UDR is limited to built-in non-opaque data types for its arguments, parameters, and returned values.

You can refer to a remote database object in the following statements only. For information on the support in these statements across databases of the local server, or across database servers, refer to the IBM Informix Guide to SQL: Tutorial.

3If the name of a database server is a delimited identifier 3or if it includes uppercase letters, that database server cannot participate 3in distributed DML operations. To avoid this restriction, use only undelimited 3names that include no uppercase letters when you declare the name or the alias 3of a database server.