My Oracle Support Banner

Options for Connecting to Foreign Data Stores and Non-Oracle Databases - For Example - DB2, SQL*Server, Sybase, Informix, Teradata, MySQL (Doc ID 233876.1)

Last updated on JULY 09, 2019

Applies to:

Oracle Database Gateway for DB2 - Version 10.1.0.3 to 10.2.0.3 [Release 10.1 to 10.2]
Oracle Database Gateway for WebSphere MQ - Version 11.1.0.6 to 12.1.0.1 [Release 11.1 to 12.1]
Oracle Database Gateway for Teradata - Version 11.1.0.6 to 12.1.0.1 [Release 11.1 to 12.1]
Oracle Database Gateway for SQL Server - Version 11.1.0.6 to 12.1.0.1 [Release 11.1 to 12.1]
Oracle Database Gateway for Sybase - Version 11.1.0.6 to 12.1.0.1 [Release 11.1 to 12.1]
Information in this document applies to any platform.

Details

PURPOSE
-------

To outline options for connecting to foreign data stores and non-Oracle databases from Oracle.

SCOPE & APPLICATION


All users that need to access non-Oracle data from Oracle.


Connecting to Foreign Data Stores

There are 3 main options to connect to foreign data stores from Oracle :-

- Database and Procedural gateways
- Generic Connectivity
- ODBC direct connectivity using Oracle Call Interface (OCI) or JDBC

Connecting From Foreign Data Stores

 
It is also possible to connect to Oracle from some foreign data stores using Access Managers.
Another alternative is to migrate data from the foreign data store into Oracle.

Actions

1. Database Gateways

Oracle Database Gateways provide the ability to transparently access data residing in a non-Oracle system from an Oracle environment. This transparency eliminates the need for application developers to customize their applications to access data from different non-Oracle systems, thus decreasing development efforts
and increasing the mobility of the application.

All versions of the following Transparent Gateways are now desupported and have been replaced by Database Gateways -

Oracle Transparent Gateway for Sybase
Oracle Transparent Gateway for Informix
Oracle Transparent Gateway for Teradata
Oracle Transparent Gateway for Microsoft SQL Server
Oracle Transparent Gateway for Rdb
Oracle Transparent Gateway for RMS
Oracle Transparent Gateway for TG4DB2400

See the following notes -

<Document 353723.1> Oracle Transparent Gateway for iWay
<Document 353728.1> Oracle Transparent Gateway for INGRES
<Document 353725.1> Oracle Transparent Gateway for Rdb
<Document 353729.1> Oracle Transparent Gateway for RMS
<Document 417250.1> Oracle Transparent Gateway for INFORMIX
<Document 417253.1> Oracle Transparent Gateway for SYBASE
<Document 417254.1> Oracle Transparent Gateway for MS SQL Server
<Document 417251.1> Oracle Transparent Gateway for TERADATA
<Document 420436.1> Oracle Transparent Gateway for DRDA - SNA Support desupport
<Document 559948.1> Oracle Transparent Gateway for DB2/400

Note that for the Gateway for Rdb there is a replacement supplied by Oracle - OCI Services which is a component of SQL/Services - see this note -

<Document 133602.1> SQL/Services, OCI Services for Rdb (SQL*Net), ORDT & JDBC for Rdb Documentation Index

Further information can be found in this note -

<Document 549796.1> Desupport of Oracle Transparent Gateways

Information on Oracle's Lifetime Support Policy can be found on My Oracle Support (MOS) -

http://support.oracle.com

Then -
- choose 'Certifications'
- then click on 'Lifetime Support' under 'Quick Links'
- then from the link -
For additional information or support timeframes for currently supported products refer to the Oracle Lifetime Support brochures and FAQ: http://www.oracle.com/support/lifetime-support-policy.html
- choose -
Lifetime Support Policy: Software Brochures
- then choose
 Lifetime Support Policy: Oracle Technology Products (PDF)
- and search for ''Oracle Gateway Release's" and information is shown for each version.

11g Database Gateways are the replacements for Transparent gateway. See the following for further details -
<Document 1083703.1> Master Note for Oracle Gateway Products

http://www.oracle.com/technetwork/database/gateways/index.html

Database gateways are available for the following foreign datastores :-

Microsoft SQLServer
Sybase
Informix
Teradata
ODBC compliant databases
IBM DRDA compliant databases - DB2 z/OS, DB2 UDB and DB2/400
IBM APPC - CICS/TS, IDMS-DC,IMS/TM and APPC
IBM WebSphere MQ

All gateways require a separate license to enable them to be used. They are not included in the license for the RDBMS except the Database Gateway for ODBC (DG4ODBC) which is included in the RDBMS license.
See the following note for more information about licensing -

<Document 232482.1> Gateway and Generic Connectivity Licensing Considerations


Further information about each gateway can be found at My Oracle Support (MOS) :-

http://support.oracle.com

- click 'Knowledge' tab then 'Oracle Database Products' then 'Oracle Database' then 'Gateways' and choose the gateway for which you require information.
And also from -

http://www.oracle.com/technetwork/database/gateways/index.html

Documentation for each gateway is available from :-

http://www.oracle.com/technetwork/indexes/documentation/index.html

You will need to register if you have not already done so, but this is free.
Gateway documentation can be found under the 'Information Integration' tab for each version.

2. Procedural Gateways


Procedural gateways are available for APPC and IBM MQSeries. In Oracle 11g they
have been replaced by Database gateways.

The Oracle Database Gateway for APPC allows users to initiate transaction program execution on remote online transaction processors (OLTPs). The Oracle Database Gateway for APPC provides Oracle applications seamless access to IBM mainframe data and services through Remote Procedural Call (RPC) processing.

The Oracle Database Gateway for WebSphere MQ allows Oracle applications to integrate with other MQSeries applications. Oracle applications can send messages to other MQSeries applications or receive messages from them. With the gateway, Oracle applications access IBM MQSeries message queues through remote procedure call (RPC) processing.

Both these products require a separate license in the same way as the Database Gateways.
Further information and documentation is available from the same sites as for Database Gateways.

3. Generic Connectivity

Generic connectivity allows access to any ODBC data store for which a third party ODBC driver is available, and which meets the standards required by the Oracle software.
It uses Heterogeneous Services (HS) which is part of the RDBMS kernel to do most of the processing.
Generic connectivity has some limitations and has limited functionality, especially compared to the Database Gateways.
Generic connectivity does not need a separate license as it is included in the RDBMS license. All that is needed is a third party ODBC driver, as Oracle does not provide these.

With Oracle 11g Generic Connectivity is performed using the Database Gateway for ODBC - DG4ODBC.

DG4ODBC is supported on the following platforms -

Linux x86 & x86_64 RH 4, 5, OEL 4, 5, SLSE-10
Windows x86 32 bit 2000/XP/2003/Vista
Windows x86 64 bit XP/2003/Vista/2008, Windows 7  - 11.2 only
Solaris SPARC Solaris 9 (update 6), 10
AIX 5.3 Maint. Level 02
HP-UX
PA-RISC & Itanium - 11i v2 (11.23),11i v3 (11.31)

The ODBC driver required for DG4ODBC depends on the platform -   for 32bit Oracle installation it requires a 32bit ODBC driver and for 64bit Oracle installations it requires a 64bit ODBC driver.

Starting with Oracle 11g HSOLEDB is no longer supported.

Further information can be found at :-

http://support.oracle.com

- click 'Knowledge' tab then choose 'Oracle Database Products' then 'Oracle Database' then 'Gateways' to show the list of gateways.
Also at -

http://www.oracle.com/technology/products/gateways/index.html

Documentation for generic connectivity is available from the gateway documentation link referred to earlier.

The relevant manuals are :-

Oracle Database Gateway for ODBC User Guide 11g Release 1 (11.1)
Oracle Database Gateway for ODBC User Guide 11g Release 2 (11.2)

4. OCI and JDBC

4.1 Oracle Call Interface

Oracle Call Interface (OCI) is a comprehensive, commercially-available interface  addressing all requirements of high performance, secure, scalable middle-tier mission-critical solutions that integrate well with an Oracle server.
The OCI provides a library of standard database access and retrieval functions in the form of a dynamic runtime library, that can be linked in by the application.
Further information can be found on My Oracle Support.

4.2 JDBC

JDBC can be used to access foreign datastores.
Further information can be found on My Oracle Support.

However, please note that :-

1) Although Oracle JDBC 2.0 compliant Thin drivers can be used to connect, this is
not supported since Oracle does not certify its drivers with third party RDBMS servers.

2) Although third party JDBC drivers can be loaded into the Oracle server to connect to
the non-Oracle RDBMS, this configuration is not supported since Oracle does not have access
to the third party driver code.

5. Access Managers

Access Managers allow access to Oracle databases from some foreign datastores.
They are available for the IBM AS400 and for CICS on IBM zOS (OS390) mainframes.

Oracle Access Manager for AS/400 (AM4AS400) provides AS/400 users with access to data that
is residing on any supported Oracle platform via DB2/400 SQL or stored procedures. Oracle Access Manager for AS/400 connects to your Oracle environment through Oracle Net. The connection from your AS/400 to an Oracle server uses TCP/IP.
However, please be aware that the Access Manager for AS400 is being desupported. See the desupport notice available in My Oracle Support -

<Document 559947.1> Oracle Access Manager for AS/400

The Access Manager for CICS (AM4CICS) allows CICS applications to issue EXEC SQL statements
to an Oracle server, using the precompiler API.

Documentation is available from the links referred to earlier.

Oracle Access Manager Installation and User's Guide for IBM AS/400
- available for 1.3.0 and 9.2 versions.
Oracle Access Manager for AS/400 Installation and User's Guide
- available for 10.1 and 10.2
- for AM4CICS information see the following -
Oracle Database System Administration Guide 10g Release 2 (10.2) for IBM z/OS (OS/390)


6. Migrating to Oracle

There are several options for migrating information in foreign data stores to an Oracle database.

6.1 Oracle SQL*Developer

Oracle SQL*Developer is free to download and use. Information is available here -


http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html

and information on using it for migration to Oracle from here -

http://www.oracle.com/technetwork/database/migration/index-084442.html

Supported non-Oracle databases for migration are -

Microsoft SQL Server 7.0, 2000, 2005, 2008 R1 and 2008 R2
Microsoft Access 97, 2000, 2002, 2003, 2007
MySQL 3.x, 4.x, 5.x
Sybase Adaptive Server 12, Sybase 15
IBM DB2 LUW 8.x, 9.x
Teradata 12, 13

but review the SQL*Developer migration site for up to date information as further products and versions are added.

6.2 Oracle Migration Workbench 10.1.0.4

OMWB 10.1.0.4 has been replaced by SQL*Developer and now only supports the migration of Informix 7.3, 9.1, 9.2, 9.3, 9.4 and IBM DB2/400 V4R3 & V4R5.
The product information may say it also supports DB2 on other platforms but this should be ignored and SQL*Developer used for DB2 migration on Linux, Unix and Windows. 
Further information and the download can be found at -

http://www.oracle.com/technetwork/topics/index-095746.html


6.3 Migration Toolkits

Migration toolkits are no longer available.

6.4 Other Options

If SQL*Developer or OMWB cannot be used for the migration then other options include a manual
migration. This could be done using a Database Gateway or generic connectivity to copy tables, or by loading data into flat files and loading into the Oracle  database using SQL*Loader.
Oracle Consulting can help you to do this.

7. 12c Features

In Oracle 12c there will be additional features to help in accessing non-Oracle databases and writing applications. 


One new feature is the 12c Oracle Database Driver for MySQL

From the feature description:
Oracle Database Driver for MySQL Applications This Oracle Database driver is a drop-in replacement for the client  library for MySQL 5.5. It enables applications and tools built on  languages based on MySQL C library (PHP, Ruby, Perl, Python, and so on)  to run against Oracle Database using a new library which translates  MySQL API calls to Oracle Call Interface (OCI) calls. All functions in  the client interface are supported with the same semantics. SQL  statements will be translated with some restrictions. Supported or  unsupported MySQL errors, options, data types and their conversions are  documented. Key benefits are the reuse of MySQL applications against both MySQL  and Oracle and the reduction in the costs and complexities of migrating  MySQL applications to Oracle.

And here from the 12c migration documentation:


http://st-doc.us.oracle.com/12/121/gateways.121/e22508/migr_tools_feat.htm#DRDAA107

and -

http://docs.oracle.com/cd/E16655_01/gateways.121/e22508/migr_tools_feat.htm#DRDAA29127

Support for MySQL Applications

Oracle Database driver for MySQL eases migration of applications initially developed to work with MySQL database. This feature has two key benefits:

    it enables the enterprise to reuse the same application to use data stored in both MySQL and Oracle Database

    it reduces the costs and complexities of migrating MySQL applications to Oracle

Oracle supports all MySQL functions in the client interface with the same semantics. SQL statements are translated, with some restrictions.

8. Replicating Data Using GoldenGate

It is possible to replicate data between Oracle and non-Oracle databases using Oracle GoldenGate.
Further information is available from -

http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

and the GoldenGate master note -

<Document 1298817.1> Master Note - Oracle GoldenGate

 

Contacts

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.