Data Types in Distributed UPDATE Operations

Distributed UPDATE operations across tables in databases of different server instances can return only a subset of the data types that distributed UPDATE operations can return from tables that are all in databases of the same Informix® instance.

The UPDATE statement (or any other SQL data-manipulation language statement) that accesses a database of another Informix instance can reference only the following data types:

Built-in data types that are not opaque or complex
BOOLEAN
BSON
JSON
LVARCHAR
DISTINCT of built-in types that are not opaque
DISTINCT of BOOLEAN
DISTINCT of BSON
DISTINCT of JSON
DISTINCT of LVARCHAR
DISTINCT of the DISTINCT types in this list.

Cross-server distributed UPDATE operations can support these DISTINCT types only if the DISTINCT types are cast explicitly to built-in types, and all of the DISTINCT types, their data type hierarchies, and their casts are defined exactly the same way in each participating database. For additional information about the data types that Informix supports in cross-server DML operations, see Data Types in Cross-Server Transactions.

Cross-database distributed UPDATE operations that access other databases of the local Informix instance, however, can access the cross-server data types in the preceding list, and also the following data types:

Most of the built-in opaque data types, as listed in Data Types in Cross-Database Transactions
DISTINCT of the built-in types that are referenced in the preceding line
DISTINCT of any of the data types that are listed in either of the two preceding lines
Opaque user-defined data types (UDTs) that are explicitly cast to built-in data types.

Cross-database UPDATE operations can support these DISTINCT types and opaque UDTs only if all the opaque UDTs and DISTINCT types are cast explicitly to built-in types, and all of the opaque UDTs, DISTINCT types, data type hierarchies, and casts are defined exactly the same way in each of the participating databases.

Distributed UPDATE transactions cannot access the database of another Informix instance unless both servers define TCP/IP or IPCSTR connections in their DBSERVERNAME or DBSERVERALIASES configuration parameters and in the sqlhosts file or SQLHOSTS registry subkey. The requirement, that both participating servers support the same type of connection (either TCP/IP or else IPCSTR), applies to any communication between Informix instances, even if both reside on the same computer.