SQL statements that access more than one database

Only the data manipulation language (DML) statements of SQL, and a subset of the data definition language (DDL) statements of SQL can reference database objects outside the local database from which the statement is issued, or in databases of server instances that are not the local HCL OneDB™ server instance.

You can run the following SQL statements across databases and across database server instances:
  • CREATE DATABASE
  • CREATE SYNONYM
  • CREATE VIEW
  • DATABASE
  • DELETE
  • DROP DATABASE
  • EXECUTE FUNCTION
  • EXECUTE PROCEDURE
  • INFO
  • INSERT
  • LOAD
  • LOCK TABLE
  • MERGE
  • SELECT
  • UNLOAD
  • UNLOCK TABLE
  • UPDATE
Restriction:

To run each of these SQL statements successfully across databases or across database servers, the local database and the external databases must all have the same logging mode. For example, if the local database from which you issue a distributed query was created as MODE ANSI, any other database that the query accesses cannot be unlogged, and cannot use explicit transactions.

Return data types in cross-database operations

Distributed operations that use SQL statements or UDRs to access other databases of the local HCL OneDB database server instance can return values of these data types:
  • Any built-in atomic data type that is not opaque
  • The built-in opaque types BLOB, BOOLEAN, BSON, CLOB, JSON, and LVARCHAR
  • DISTINCT types based on a non-opaque built-in atomic type, or on a built-in opaque type listed above
  • User-defined data types (UDTs) that can be cast to built-in types.
The DISTINCT or UDT values above must all be explicitly cast to built-in data types, and all the DISTINCT types, UDTs, and casts must be defined identically in each of the participating databases.

These data types can be returned by SPL, C, and Java-language UDRs as parameters or as return values, if the UDRs are defined in all of the participating databases. The DISTINCT data types must have exactly the same data type hierarchy defined in all databases that participate in the distributed query.

A cross-database distributed query or other cross-database DML operation that accesses another database of the local HCL OneDB database server will fail with an error if it references a table, view, or synonym that includes a column of any of the following data types:
  • IMPEXP
  • IMPEXPBIN
  • LOLIST
  • SENDRECV
  • DISTINCT of any of the built-in opaque data types in this list
  • Complex types, including COLLECTION, LIST, MULTISET, or SET, and named or unnamed ROW types.

This restriction against cross-database distributed operations that access tables with these built-in opaque or complex data types also applies to operations that access databases of two or more database server instances, which the next section describes.

Return data types in cross-server operations

A distributed query (or any other distributed DML operation or function call) across databases of two or more HCL OneDB instances cannot return complex or large-object data types, nor most UDTs or opaque data types. Cross-server distributed queries, DML operations, and function calls can return only the following data types:
  • Any non-opaque built-in data type
  • BOOLEAN
  • BSON
  • JSON
  • LVARCHAR
  • DISTINCT of non-opaque built-in types
  • DISTINCT of BOOLEAN or LVARCHAR
  • DISTINCT of BSON or JSON
  • DISTINCT of any of the DISTINCT types in this list.
The same cross-database DDL requirements, that all UDRs, casts, and DISTINCT data types have identical definitions in every participating database, also apply to distributed SQL operations across the databases of two or more HCL OneDB database-server instances.
A cross-server DML operation that accesses a database of another HCL OneDB instance will fail with an error. however, if it references a table object that includes a column of any of the following data types:
  • BLOB
  • BYTE
  • CLIENTBINVAL
  • CLOB
  • IFX_LO_SPEC
  • IFX_LO_STAT
  • IMPEXP
  • IMPEXPBIN
  • INDEXKEYARRAY
  • LOLIST
  • POINTER
  • RTNPARAMTYPES
  • SELFUNCARGS
  • SENDRECV
  • STAT
  • TEXT
  • XID
  • User-defined OPAQUE type
  • Complex types, including COLLECTION, LIST, MULTISET, or SET, and named or unnamed ROW types.
  • DISTINCT of any of the opaque or complex data types in this list.