Consultor Eletrônico

Status: Verified

GOAL:

SQL: How to join two SYNONYM objects from two separate databases?

GOAL:

How to create two SYNONYM objects from two separate databases without qualifying their names with their database names?

GOAL:

How to create PUBLIC SYNONYM objects for the Primary and the Auxiliary databases of a Multi-Database Connection?

GOAL:

Sample SQL code to create PUBLIC SYNONYM objects for the Primary and the Auxiliary databases.

GOAL:

Sample SQL query to join SYNONYM objects from multiple databases of a Multi-Database Connection.

GOAL:

Sample Primary database Multi-Database Connection Properties File to enable Multi-Database Connection.

GOAL:

Sample Primary database and Auxiliary database DSN definitions used in a Multi-Database ODBC Connection.

FACT(s) (Environment):

All Supported Operating Systems
OpenEdge 10.1x
OpenEdge 10.2x

FIX:

To join two SYNONYM objects from two separate databases without having to qualify the SYNONYM names with their respective database names is to create them as PUBLIC SYNONYM objects.
The following is a step by step procedure to demonstrates how to do that using the sports2000 demo database. Although a Windows machine and an ODBC driver and client were used to test this procedure, a UNIX machine and a JDBC driver and client may also be used to perform the same task with minimal variations in the steps of this procedure:
1. Login to a Windows machine that has OpenEdge 10.2x installed.
2. Copy the Multi-Database Connection Properties File DATABASE1.oesql.properties, CreatePrimarySynonym.sql, CreateAuxiliarySynonym.sql, JoinQualifiedSynonyms.sql and the JoinUnQualifiedSynonyms.sql files from the note section of this solution into your working OpenEdge 10.2x directory.
3. Start a Proenv session:
Start > Programs > OpenEdge > Proenv
4. Create the primary and the auxiliary databases DATABASE1 and DATABASE2 as copies of the sports2000 database:
prodb DATABASE1 sports2000
prodb DATABASE2 sports2000
5. Serve ( start )