Oracle® Migration Workbench Reference Guide for Informix Dynamic Server Migrations
Release 10.1.0 for Microsoft Windows 98/2000/NT/XP and Linux x86
Part No. B16022-01
This chapter describes how Informix Dynamic Server and Oracle handled remote objects. It also lists the application development tools available. It contains the following sections:
A distributed environment is chosen for various applications where:
The data is generated at various geographical locations and needs to be available locally most of the time.
The data and software processing is distributed to reduce the impact of any particular site or hardware failure.
When a relational database management system (RDBMS) allows data to be distributed while providing the user with a single logical view of data, it supports location transparency. Location transparency eliminates the need to know the actual physical location of the data. Location transparency thus helps make the development of the application easier. Depending on the needs of the application, the database administrator (DBA) can hide the location of the relevant data.
To access a remote object, the local server must establish a connection with the remote server. Each server requires unique names for the remote objects. The methods used to establish the connection with the remote server, and the naming conventions for the remote objects, differ from database to database.
Oracle allows remote objects (such as tables, views, and procedures) throughout a distributed database to be referenced in SQL statements using global object names. In Oracle, the global name of a schema object comprises the name of the schema that contains the object, the object name, followed by an at sign (@), and a database name. For example, the following query selects information from the table named scott.emp in the SALES database that resides on a remote server:
SELECT * FROM email@example.com
A distributed database system can be configured so that each database within the system has a unique database name, thereby providing effective global object names.
Furthermore, by defining synonyms for remote object names, you can eliminate references to the name of the remote database. The synonym is an object in the local database that refers to a remote database object. Synonyms shift the responsibility of distributing data from the application developer to the DBA. Synonyms allow the DBA to move the objects as desired without impacting the application.
The synonym can be defined as follows:
CREATE PUBLIC SYNONYM emp FOR firstname.lastname@example.org;
Using this synonym, the SQL statement outlined above can be changed to the following:
SELECT * FROM emp;
Informix Dynamic Server requires schema objects throughout a distributed database to be referenced in SQL statements by fully qualifying the object names. The complete name of a schema object has the following format:
server_name is the name of a remote server. The
database_name is the name of a remote database on the remote server.
Informix Dynamic Server does not allow you to create a database link, but does allow you to create a synonym. So a remote object can be referred to by a synonym. A remote object is specified by the database name followed by the at sign (@), the remote server name, and then the name of the schema and the object. For example, the following synonym (
myemp) is created for the object
scott.emp that is in the
sales database on the
CREATE SYNOYM myemp FOR sales@boston:scott:emp
You can use this synonym to reference a remote object:
SELECT * FROM myemp
You can make a common name refer to remote objects that can work for both Oracle databases and Informix Dynamic Server databases.
Several application development tools that are currently available use specific features of one of the various database servers; you may have to invest significant effort to port these products to other database servers. With critical applications, it is sometimes best to develop and maintain a different set of application development tools that work best with the underlying database, as ODBC support is not adequate in such cases.
The majority of applications are written in Informix Dynamic Server 4GL.
If a Visual Basic application is written with ODBC as the connection protocol to access Informix Dynamic Server, it is possible to modify and fix the Visual Basic application to work with an Oracle back-end.
The Informix 4GL application development environment does not provide connectivity to other databases, including Oracle. To overcome this limitation in Informix 4GL, you must convert or migrate your code. Oracle recommends one of our partners in converting or migrating your Informix 4GL code: