The Oracle Database Gateway for DRDA gives you access to your Oracle data and DB2 data with a single set of applications while you continue to use existing IBM applications to access your DB2 data. The gateway enables you to:

• Integrate heterogeneous database management systems so that they appear as a single homogeneous database system.

• Read and write data from Oracle applications to data in DB2 UDB for z/OS, DB2 Universal Database™ for iSeries™ (DB2 UDB for iSeries), and DB2 Universal Database.

Oracle Database 12c Release 2 (12.2) provides the foundation for the next generation of the Oracle Database Gateways, which delivers enhanced integration capabilities by exploiting Oracle Database Heterogeneous Services.

As an integrated component of the Oracle database, Heterogeneous Services can take advantage of the powerful SQL parsing and distributed optimization capabilities of the Oracle database. This integration also ensures that the gateway can immediately take advantage of any enhancements made to future releases of the Oracle database. For detailed information on Oracle Heterogeneous Services, refer to Oracle Database Heterogeneous Connectivity User's Guide.

The gateways are even more tightly integrated with Oracle Database 12c Release 2 (12.2) than previous versions, enabling improved performance and enhanced functionality while still providing transparent integration of Oracle and non-Oracle data. For example, connection initialization information is available in the local Oracle database, reducing the number of round trips and the amount of data sent over the network. SQL execution is also faster, because, statements issued by an application are parsed and translated once and can then be reused by multiple applications.

Oracle Database Gateway for DRDA enables you to integrate your heterogeneous system into a single, seamless environment. If data is moved from a DRDA database to an Oracle database, then no changes in application design or function are needed. The gateway handles all differences in both data types and SQL functions between the application and the database. As a result, end users and application programmers are not required to know either the physical location or the storage characteristics of the data.

This transparency not only enables you to integrate heterogeneous data seamlessly, it also simplifies your gateway implementation, application development, and maintenance. The gateway capabilities are as follows:

• Transparency at All Levels

• Extended Database Services

• Extended Advanced Networking_ Internet and Intranet Support

• Dynamic Dictionary Mapping

• SQL

• Data Definition Language

• Data Control Language

• Passthrough and Native DB2 SQL

• Stored Procedures

• Languages

• Oracle Database Technology and Tools

• SQL*Plus

• Two-Phase Commit and Multi-Site Transactions

• Site Autonomy

• Migration and Coexistence

• Security

• DRDA UDB Server Encryption support

The terms used in this guide do not necessarily conform to the IBM terminology. The following list presents several terms and their meanings as used within this guide:

DRDA data is, generically, any database data accessed through DRDA.

DRDA database is the collection of data that belongs to a DRDA server

DRDA server is a database server that can be accessed through DRDA. IBM terminology for a DRDA server is a DRDA Application Server, or AS.

DRDA server type is a specific database product or program that can act as a DRDA server.

Oracle database is any Oracle Database 12c Release 2 (12.2) instance that communicates with the Oracle Database Gateway for DRDA to distribute database access operations to a DRDA server. The Oracle database can also be used for non-gateway applications.

DB2 Universal Database is a generic name for all implementations of IBM's DB2 Database product. DB2/UDB is frequently used as an abbreviation for the DB2 Universal Database for Unix, Linux, and Windows product.

The Oracle Database Gateway for DRDA works with the Oracle database to shield most of the differences of the non-Oracle database from Oracle applications.

The architecture consists of the following main components:

• Client

  The client is an Oracle application or tool.

• Oracle database

  The gateway instance is accessed by an Oracle database with procedural and distributed options. Usually, the Oracle database is installed on the same host as the gateway, but this is not a requirement. The Oracle database and the gateway communicate in the normal Oracle database-to-server manner.

  If the Oracle database is not on the host where the gateway resides, then you must install the correct Oracle networking software on the platform where the server resides. For Oracle database, you must install Oracle Net on the Oracle database machine.

• Oracle Database Gateway for DRDA

  The gateway must be installed on hosts that are running the appropriate operating system.

  If the Oracle database is not on the same host, then you must also install Oracle Net so that the gateway and Oracle database can communicate.

• DRDA server

  The DRDA server must be on a system accessible to the host via a network.

Multiple Oracle databases can access the same gateway.

Figure 1-1 illustrates the gateway architecture.

When the gateway is installed on your host, it has some of the same components as an Oracle database instance on your host. The gateway has the following components:

• A base file directory, similar to the one associated with an Oracle instance ORACLE_HOME environment variable

• A gateway system identifier (SID), comparable to an Oracle instance ORACLE_SID

• Oracle Net to support communication between the Oracle database and the Oracle Database Gateway for DRDA

The gateway does not have:

• Control, redo log, or database files

• The full set of subdirectories and ancillary files that are associated with an installed Oracle database

Because the gateway does not have background processes and does not need a management utility, such as Oracle Enterprise Manager, you do not need to start the gateway product. Each Oracle database user session that accesses a particular gateway creates an independent process on the host. This process runs the gateway session and executes network operations to communicate with a DRDA server.

The gateway has no database functions of its own. Instead, it provides an interface by which an Oracle database can direct part or all of a SQL operation to a DRDA database.

The gateway that is supporting the DRDA server is identified to the Oracle database using a database link. The database link is the same construct that is used to identify other Oracle databases. Tables on the DRDA server are referenced in SQL as:

table_name@dblink_name

or

owner.table_name@dblink_name

If you create synonyms or views in the Oracle database, then you can refer to tables on the DRDA server by using simple names as though the table were local to the Oracle database.

When the Oracle database encounters a reference to a table that is on the DRDA server, the applicable portion of the SQL statement is sent to the gateway for processing. Any host variables that are associated with the SQL statement are bound to the gateway and, therefore, to the DRDA server.

The gateway is responsible for sending these SQL statements to the DRDA server for execution and for fielding and returning responses. The responses are either data or messages. Any conversions between Oracle data types and DRDA data types are performed by the gateway. Both the Oracle database and the application read and process only Oracle data types.

Use the Oracle Database Gateway to run applications, such as Oracle database tools, that read and write data that is stored in DRDA databases.

While the Oracle Database Gateway for DRDA provides no new application or development facilities, it extends the reach of existing Oracle database tools to include data in non-Oracle databases that support DRDA.

Using the Oracle Database Gateway for DRDA with other Oracle products can greatly extend the capabilities of the stand-alone gateway.

Following is a list of important features that characterize Oracle Database Gateway for DRDA:

• Heterogeneous Services Architecture

• Performance Enhancements

• Fetch Reblocking

• Oracle Database Passthrough Supported

• Retrieving Result Sets Through Passthrough

• Support for TCP/IP

• Native Semantics

• Columns Supported in a Result Set

• EXPLAIN_PLAN Improvement

• Heterogeneous Database Integration

• Minimum Impact on Existing Systems

• Application Portability

• Remote Data Access

• Support for Distributed Applications

• Application Development and End User Tools

• Password Encryption Utility

• Support for DB2 UDB for z/OS Stored Procedures

• IBM DB2 Universal Database Support

• DB2 z/OS ASCII and UNICODE Table Support

• Read-Only Support

• Support for Graphic and Multi-byte Data

• Support for DB2 Universal Database on Intel Hardware

• Data Dictionary Support for DB2 Universal Database

Oracle Database Gateway for DRDA utilizes the Oracle Heterogeneous Services component within the Oracle database. Heterogeneous Services is the building block for the next generation of Oracle database gateways.

For detailed information about heterogeneous services, refer to Oracle Database Heterogeneous Connectivity User's Guide.

Oracle Database Gateway for DRDA contains several internal performance enhancements. This product has shown major improvements in response time and CPU utilization for all relevant address spaces for a variety of workloads compared to version 10 gateways. The actual performance improvement at your site might vary, depending on your installation type and workload.

The array size of the application for SELECT is effective between the application and the Oracle database. However, the array blocksize and the block fetch between the Oracle database and the gateway are controlled by two Heterogeneous Services initialization parameters: HS_RPC_FETCH_SIZE and HS_RPC_FETCH_REBLOCKING. These parameters are specified in the gateway initialization file. Refer to Oracle Database Heterogeneous Connectivity User's Guide for more information.

You can use the Oracle database DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE method to pass commands or statements available in your DRDA server through the gateway.

Oracle Database Gateway for DRDA provides a facility to retrieve result sets from a SELECT statement issued with passthrough. Refer to "Retrieving Results Sets Through Passthrough" for additional information.

This release of the gateway only supports the TCP/IP communication protocol between the gateway and the DRDA server. Refer to Oracle Database Gateway Installation and Configuration Guide for IBM AIX on POWER Systems (64-Bit), Linux x86-64, Oracle Solaris on SPARC (64-Bit), Oracle Solaris on x86-64 (64-Bit) and HP-UX Itanium, or Oracle Database Gateway Installation and Configuration Guide for Microsoft Windows depending on your platform.

This release of the gateway supports the ability to selectively enable or disable post-processing of various SQL functions by the DRDA server. Refer to "Native Semantics" for further information.

Oracle Database Gateway for DRDA supports up to 1000 columns in a result set.

The EXPLAIN_PLAN table contains the actual SQL statements passed to the DRDA server from the Oracle database through the gateway.

The gateway support for ANSI-standard SQL enables read/write access to DRDA databases. Even if your data exists on different platforms in different applications, new applications can use all data, regardless of location.

The gateway does not require installation of additional Oracle software on your OS/390 (MVS), AS/400, UNIX based, or Microsoft Windows target system. The database interface that it uses is provided by IBM and is built into the DRDA database products and network facilities that already exist on these platforms.

Configuring an IBM system for DRDA access typically consists of defining the network resources involved and establishing access security definitions specific to the target database.

The gateway's ability to interface with heterogeneous databases makes it possible to develop a single set of portable applications that can be used against both Oracle and IBM databases, and any other databases for which Oracle provides gateways.

Location flexibility is maximized because the gateway architecture permits network connections between each of the components. The application can use the Oracle client-server capability to connect to a remote Oracle database through Oracle Net. The Oracle database can connect to a remote gateway using a database link. The gateway connects to a DRDA server through network facilities.

The benefits of remote access are:

• Provides a means to allocate the appropriate resource to a given task

  You can, for example, move application development off expensive processors and onto cost-efficient workstations or microcomputers.

• Expands the number of available data sources

  Without remote access, you are limited to the data available in the local environment. With remote access, only your networks limit your data sources.

• Provides a means to tailor an application environment to a given user

  For example, some users prefer a block-mode terminal environment, while others prefer a bit-mapped, graphics driven terminal environment. Remote access can satisfy both because you are not constrained by the interface environment imposed by the location of your data.

Because the gateway gives your application direct access to DRDA data, you eliminate the need to upload and download large quantities of database data to other processors. Instead, you can access data where it is, when you want it, without having to move the data between machines and risk unsynchronized and inconsistent data. Avoiding massive data replication can also reduce aggregate disk storage requirements over all your systems.

However, if your system design requires moving data among the machines in a network, SQL*Plus and the gateway can simplify the data transfer. With a single SQL*Plus command, you can move entire sets of data from one node of the network to another and from one database to another.

You can pass commands and statements specific to your DRDA database through the gateway to be executed by the DRDA database. For example, you can pass native DB2 SQL through the gateway for DB2 to execute. You can also execute stored procedures defined in non-Oracle databases.

Through the gateway, Oracle extends the range of application development and end-user tools you can use to access your IBM databases. These tools increase application development and user productivity by reducing prototype, development, and maintenance time. Current Oracle database users do not have to learn a new set of tools to access data stored in DRDA databases. Instead, they can access Oracle database and DRDA data with a single set of tools.

With the gateway and the application development tools available from Oracle you can develop a single set of applications to access Oracle database and DRDA data. Users can use the decision support tools available from Oracle to access Oracle database and DRDA data. These tools can run on remote machines connected through Oracle Net to the Oracle database.

When designing applications, keep in mind that the gateway is designed for retrieval and relatively light transaction loads. The gateway is not currently designed to be a heavy transaction processing system.

Oracle Database Gateway for DRDA includes a utility to support encryption of plain-text passwords in the Gateway Initialization File. Refer to Chapter 15, "Security Considerations" in Oracle Database Gateway Installation and Configuration Guide for IBM AIX on POWER Systems (64-Bit), Linux x86-64, Oracle Solaris on SPARC (64-Bit), Oracle Solaris on x86-64 (64-Bit) and HP-UX Itanium or Oracle Database Gateway Installation and Configuration Guide for Microsoft Windows for details.

Oracle Database Gateway for DRDA supports the native stored procedures.

Oracle Database Gateway for DRDA supports IBM DB2 Universal Database.

Oracle Database Gateway for DRDA supports EBCDIC, ASCII, and UNICODE table for DB2 z/OS. The character set selection is defined during table creation.

The current release enables the gateway to be configured as a read-only gateway. In this mode, the user will not be able to modify data or call remote procedures at the DRDA database.

The current release of the gateway adds support for DB2 GRAPHIC and VARGRAPHIC data types. Refer to Developing Applications for more information.

The current release of the gateway adds support for DRDA servers running on Microsoft Windows and Linux on Intel hardware.

The current release of the gateway also provides Oracle data dictionary support for DB2 UDB.