OpenEdge SQL operations in a multi-database environment are limited to data retrieval from an auxiliary database. In other words, you cannot use any statement that writes to an auxiliary database. In a multi-database environment, the following limitations exist:

  • Catalog names cannot be used with the following statements:
    • GRANT
    • CREATE INDEX
    • CREATE PROCEDURE
    • CREATE SYNONYM
    • CREATE TABLE
    • CREATE TRIGGER
    • CREATE VIE
    • UPDATE STATISTICS
  • JTA transactions are not permitted when an auxiliary database connection exists.
  • Catalog names cannot be used with sequences, including functions, such as CURRVAL and NEXTVAL.
  • The databases involved must have the same code-page for the multi-database connection to succeed.
  • The databases involved must have the same collation settings for the multi-database connection to succeed.
  • The user name and the password used for the primary database must be same for all database connections in a multi-database configuration.
  • Although it is not possible to update data in an auxiliary database, you may use data from auxiliary databases to update data in the primary database. The following example uses the SELECT statement on an auxiliary database to enable an update in the primary database:
    DELETE FROM pub.Order
WHERE OrderNum in (SELECT OrderNum FROM auxCatalog.pub.NewOrders WHERE OrderDate = SYSDATE);
  • In a multi-database environment, the maximum number of databases to which an SQL client can connect is 16. That is, in addition to the primary database, an SQL client can connect to up to 15 auxiliary (secondary) databases.