Data Types in Cross-Database Transactions

A query that accesses tables in more than one database of the local server instance can return only a subset of the SQL data types that a local query can return from tables in the local database. Similarly, a cross-server distributed query on tables in databases of more than one server instance has additional restrictions on returned data types.

Distributed queries (and other distributed DML operations or function calls) that access only databases of the local Informix® instance can access data types of the following categories:
  • The built-in data types that are not opaque, including these:
    • BIGINT
    • BIGSERIAL
    • BYTE
    • CHAR
    • DATE
    • DATETIME
    • DECIMAL
    • FLOAT
    • INT
    • INTERVAL
    • INT8
    • MONEY
    • NCHAR
    • NVARCHAR
    • SERIAL
    • SERIAL8
    • SMALLFLOAT
    • SMALLINT
    • TEXT
    • VARCHAR
  • Most built-in opaque data types, including these:
    • BLOB
    • BSON
    • BOOLEAN
    • CLIENTBINVAL
    • CLOB
    • IFX_LO_SPEC
    • IFX_LO_STAT
    • INDEXKEYARRAY
    • JSON
    • LVARCHAR
    • POINTER
    • RTNPARAMTYPES,
    • SELFUNCARGS
    • STAT
    • XID
  • User-defined types (UDTs) that are cast explicitly to any of the built-in types that are listed above
  • DISTINCT of any of the built-in types in the preceding list.

Distributed operations across databases of the local Informix® instance can return UDTs and DISTINCT types based on built-in data types only if all the UDTs and DISTINCT types are cast explicitly to built-in data types.

All the opaque UDTs, DISTINCT types, data type hierarchies, and casts must have exactly the same definitions in each database that participates in the distributed query. For queries or other DML operations in cross-database UDRs that use the data types listed above as parameters or as returned data types, the UDR must also have the same definition in each participating database.

A cross-database distributed query (or any other cross-database DML operation) fails with an error if it references a table, view, or synonym in another database of the local Informix® instance that includes a column of any of the following data types:
  • LOLIST
  • IMPEXP
  • IMPEXPBIN
  • SENDRECV
  • DISTINCT of any of the built-in opaque data types that are listed above.
  • Complex types (named or unnamed ROW, COLLECTION, LIST, MULTISET, or SET)
  • DISTINCT of ROW.