Data Types in Distributed INSERT Operations

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

An INSERT 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 INSERT 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.

Cross-server DML operations cannot reference a column or expression of a complex, large-object, nor user-defined data type (UDT), nor of an unsupported DISTINCT or built-in opaque type. For additional information about the data types that Informix® supports in cross-server DML operations, see Data Types in Cross-Server Transactions.

Distributed operations that access other databases of the local Informix® instance, however, can access the cross-server data types that are listed above, 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 line above
  • DISTINCT of any of the data types that are listed in either of the two lines above
  • Opaque user-defined data types (UDTs) that can be cast explicitly to built-in data types.
Cross-database INSERT operations can support these DISTINCT 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 participating database.

Distributed INSERT 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.