Data Types in Cross-Server Transactions

A distributed query (or any other distributed DML operation or function call) across databases of two or more Informix® instances cannot return complex or large-object data types, nor most user-defined data types (UDTs) or opaque data types.

A distributed query (or any other distributed DML operation or function call) across databases of two or more Informix® instances cannot return complex or large-object data types, nor most user-defined data types (UDTs) or opaque data types. Cross-server distributed queries, DML operations, and function calls can return only the following atomic data types, where atomic excludes the complex data types:
  • Any non-opaque atomic built-in data type
  • BOOLEAN
  • BSON
  • JSON
  • LVARCHAR
  • DISTINCT of non-opaque built-in types
  • DISTINCT of BOOLEAN
  • DISTINCT of BSON
  • DISTINCT of JSON
  • DISTINCT of LVARCHAR
  • DISTINCT of any of the DISTINCT types that appear above in this list.
A cross-server distributed query can support DISTINCT data types only if both of the following conditions are true for every DISTINCT type in the query:
  • The DISTINCT data type is explicitly cast to one of the atomic built-in types above,
  • The DISTINCT type, its data type hierarchy, and its casts are defined exactly the same way in each database that participates in the distributed query.
For queries or other DML operations in cross-server UDRs (user-defined routines) that use the data types in the preceding list as parameters or as returned data types, the UDR must also have the same definition in every participating database.

Cross-server queries of protected tables

The built-in DISTINCT data type IDSSECURITYLABEL, which stores security label objects, can be accessed in cross-server and cross-database operations on protected data by users who hold sufficient security credentials for the same label-based access control (LBAC) security policy. Like local operations on protected data, distributed queries that access remote tables protected by a security policy can return only the qualifying rows that IDSLBACRULES allow, after the database server has compared the security label that secures the data with the security credentials of the user who issues the query.

For additional information about the data types that Informix® supports in cross-server DML operations, see Data Types in Distributed Queries. For information about the table hierarchies of the DISTINCT data types that are valid in cross-server operations, see DISTINCT Types in Distributed Operations.

Built-in data types not valid in cross-server queries

A 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 data types:
  • BLOB
  • BYTE
  • CLIENTBINVAL
  • CLOB
  • IFX_LO_SPEC
  • IFX_LO_STAT
  • INDEXKEYARRAY
  • POINTER
  • RTNPARAMTYPES
  • SELFUNCARGS
  • STAT
  • TEXT
  • XID
  • User-defined OPAQUE types
  • Complex types (named or unnamed ROW, COLLECTION, LIST, MULTISET, or SET)
  • DISTINCT of any of the opaque or complex data types that are listed above.

Requirements for participating database servers and databases

Cross-server operations require that all participating database server instances support the SQL syntax that specifies the operation.

Cross-server queries cannot access the database of another Informix® instance unless both servers define TCP/IP or IPCSTR connections in their DBSERVERNAME or DSERVERALIASES configuration parameters and in the sqlhosts information. 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.

For the SQL syntax to reference objects in databases of remote server instances that are not part of an Informix® grid, see Specifying a Database Object in a Cross-Server Query. For information about grid queries, see GRID clause.