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, the database server instance, and the 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 name is hr_db,
  • the @ separator (ASCII 64 ), with no blank spaces, is required between the database and database server qualifiers,
  • the database server name is remoteoffice,
  • the : separator (ASCII 58 ), with no blank spaces, is required between the database server and owner qualifiers,
  • the authorization identifier of the table owner is hrmanager,
  • the . separator (ASCII 46 ), with no blank spaces, is required between the owner and table qualifiers,
  • and the table name is employees.

Cross-server queries can access columns of built-in data types that are not opaque data types, but they cannot access UDTs nor complex data types. Among built-in opaque types, only BOOLEAN, BSON, JSON, VARCHAR, and DISTINCT types based on those base types, can be accessed or returned by DML statements or by UDRs in the databases of remote server instances. (For more information about the DISTINCT and built-in OPAQUE data types that Informix® supports in cross-server operations, see Data Types in Cross-Server Transactions.)

If a UDR exists on a remote database server instance, you must specify a fully-qualified identifier for the UDR. The following SQL statement invokes a routine in the same remote database as the previous example, where the authorization identifier of the owner of the routine is johan, and the identifier of the UDR is suggestion_box:
EXECUTE FUNCTION hr_db@remoteoffice:johan.suggestion_box(0):
You can refer to a remote database object only in the following SQL statements:
  • CREATE DATABASE
  • CREATE SYNONYM
  • CREATE VIEW
  • DATABASE
  • DELETE
  • EXECUTE FUNCTION
  • EXECUTE PROCEDURE
  • INFO
  • INSERT
  • LOAD
  • LOCK TABLE
  • MERGE
  • SELECT
  • UNLOAD
  • UNLOCK TABLE
  • UPDATE

In distributed MERGE operations, source tables can be in the database of a remote server instance, but the target table must be in a database of the local Informix instance.

For information on the support in these statements across databases of the local server, or across database servers, refer to the Informix® Guide to SQL: Tutorial.

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

Restrictions on remote user-defined routines

The EXECUTE FUNCTION or EXECUTE PROCEDURE statement can invoke a routine in the database of a remote server instance in most contexts where a UDR is valid in the local database, but every participating database must have the same logging mode.

Cross-server operations require exactly one coordinator to connect to one or more database server instances as subordinate participants that process branches of the operation that reads or modifies objects in their databases.

Only the database server instance that initiated the cross-server operation as the coordinator can establish a connection with another database server. A distributed operation that invokes UDRs on one or more remote database servers can have a logical topology of connections resembling a star schema, but not a snowflake schema. In the one-to-many relationship between the coordinator and participants, only the coordinator can be the calling context for invoking remote UDRs.

If the EXECUTE FUNCTION or EXECUTE PROCEDURE statement calls a remote routine that in turn attempts to execute a remote routine on a third database server instance, the distributed operation fails with error -556. A UDR called from a participant server cannot invoke a UDR on a third server, and cannot reference any object on a third server.