Distributed queries across databases of two or more Informix instances

Distributed queries that access tables in databases of two or more participating server instances can return built in-atomic data types that are not opaque, and can return some atomic built-in opaque data types. Cross-server queries can also return values of user-defined DISTINCT types of built-in atomic types, if those DISTINCT values are explicitly cast to built-in types, and if all participating databases use the same DISTINCT definitions and cast definitions.

Distributed operations across databases of two or more IBM® Informix® instances are subject to the following restrictions on returned data types:
  • A cross-server query, DML operation, or function call can return non-opaque atomic built-in data types, and the built-in opaque BOOLEAN data type, the BSON and JSON key-value pair types, and the LVARCHAR data type.
  • A cross-server query, DML operation, or function call can return DISTINCT data types that are explicitly cast to a built-in atomic data type, and whose base types are either non-opaque built-in data types, or BOOLEAN or LVARCHAR, or BSON or JSON key-value pair types.
  • Additionally, the base type can also be a DISTINCT data type whose base type is a non-opaque built-in type, or a BOOLEAN, LVARCHAR, or BSON or JSON key-value pair types, or whose base type is another DISTINCT data type that is based on one of these types.
  • Each participating database of the distributed operation must define these explicit casts, functions, and DISTINCT data types identically.
  • If any participating database servers are earlier versions that cannot support some data types in cross-server operations, those servers return only the data types that they support. A distributed operation fails if that operation specifies an unsupported data type.

Like distributed operations across databases of the same IBM Informix server instance, cross-server distributed operations require that all databases be of compatible transaction logging types, as described in Logging-type restrictions on distributed queries.

Data types that cross-server queries cannot return

The following built-in data types are not supported in distributed cross-server queries. The cross-server query (or any other cross-server DML operation) fails with an error if it references a table, view, or synonym in a database of another Informix instance that includes a column of any of the following large-object, opaque, or complex data types:
  • BLOB
  • CLOB
  • BYTE
  • TEXT
  • CLIENTBINVAL
  • IFX_LO_SPEC
  • IFX_LO_STAT
  • IMPEXP
  • IMPEXPBIN
  • INDEXKEYARRAY
  • LOLIST
  • POINTER
  • RTNPARAMTYPES
  • SELFUNCARGS
  • SENDRECV
  • STAT
  • STREAM
  • XID
  • User-defined OPAQUE types
  • Complex types (COLLECTION, LIST, MULTISET, SET, and named or unnamed ROW)
  • DISTINCT of any data type that is listed above.