Limitations of the OpenEdge SQL multi-database environment

SQL Development

Performing Multi-database Queries : Multi-database query overview : Working with catalogs in multi-database queries : Limitations of the OpenEdge SQL multi-database environment

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.

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);