Distributed queries across databases of one HCL OneDB instance

Cross-database queries that access tables in more than one database of the local server instance can return built in-atomic data types that are not opaque, and most built-in opaque data types, and user-defined atomic types (UDTs) that have the same definitions in all participating databases.

Data values returned from distributed operations across databases of the same HCL OneDB™ server instance are restricted, however, to the following returned data types:
  • The built-in atomic 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
    • BOOLEAN
    • BSON
    • CLIENTBINVAL
    • CLOB
    • IFX_LO_SPEC
    • IFX_LO_STAT
    • INDEXKEYARRAY
    • JSON
    • LVARCHAR
    • POINTER
    • RTNPARAMTYPES,
    • SELFUNCARGS
    • STAT
    • XID
  • For user-defined data types (UDTs), the query, DML operation, or function call can return DISTINCT or OPAQUE data types that are explicitly cast to a built-in atomic data type. Each of these DISTINCT and OPAQUE data types, and all of their explicit casts, must be defined identically in each participating database that stores or receives those data values.

Data types unsupported in cross-database queries

The cross-database distributed query fails, however, if it references a table, view, or synonym in another database of the local HCL OneDB instance that includes a column of any of the following built-in opaque or complex data types:
  • LOLIST
  • IMPEXP
  • IMPEXPBIN
  • SENDRECV
  • DISTINCT of any of these built-in opaque data types
  • DISTINCT of any of the DISTINCT opaque data types immediately above
  • Complex types, including COLLECTION, LIST, MULTISET, SET, and named or unnamed ROW.