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 HCL OneDB™ instance.

The UPDATE statement (or any other SQL data-manipulation language statement) that accesses a database of another HCL OneDB 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 HCL OneDB 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 HCL OneDB 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 HCL OneDB 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 HCL OneDB instances, even if both reside on the same computer.