Data Types in Distributed DELETE Operations

Distributed DELETE operations across tables in databases of different server instances can return only a subset of the data types that distributed DELETE operations can return from tables that are all in databases of the same HCL OneDB™ instance.

A DELETE 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 DELETE 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 opaque data type (UDT), nor of an unsupported DISTINCT type or built-in opaque type. For additional information about the data types that HCL OneDB supports in cross-server DML operations, see Data Types in Cross-Server Transactions.

Distributed operations that access other databases of the local HCL OneDB instance, however, can access the same data types that are listed above for cross-server operations, and also the following additional 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 DELETE operations can support these DISTINCT and opaque UDTs only if all the opaque and DISTINCT UDTs 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 DELETE statements cannot access a database of another HCL OneDB instance unless both server instances support either a TCP/IP or an IPCSTR connection, as defined in their DBSERVERNAME or DBSERVERALIASES configuration parameters and in the sqlhosts information. This connection-type requirement applies to any communication between HCL OneDB instances, even if both database servers reside on the same computer.