1 Introduction

Protection of Current Investment

Oracle Transparent Gateway for DRDA gives your company the ability to develop its information systems without forfeiting its investments in current data and applications. The gateway gives you access to the Oracle and DB2 data with a single set of applications while you continue to use existing IBM applications to access your DB2 data. You can also use more productive database tools and move to a distributed database technology without giving up access to the current data.

If you choose to migrate to Oracle Database technology and productivity, then the gateway enables you to control the pace of your migration. As you transfer applications from your previous technology to the Oracle Database, you can use the gateway to move the DB2 data into Oracle databases.

Advantages of the Gateway

Oracle Transparent Gateway for DRDA enables Oracle applications to access the DRDA Application Servers, such as DB2 for OS/390 (MVS), through Structured Query Language (SQL). The gateway and the Oracle Database 10g server together create the appearance that all data resides on a local Oracle Database 10g server, though data might be widely distributed. If data is moved from a DRDA Application Server database to an Oracle Database server, 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.

Transparency at All Levels

The Oracle Transparent Gateway for DRDA gives you transparency at every level within your enterprise.

• Location transparency

  Users can access tables by name without needing to understand the physical location of the tables.

• Network transparency

  The gateways exploit Oracle Net technology to enable users to access data across multiple networks without concern for the network architecture or protocols. TCP/IP protocol is supported.

• Operating system transparency

  You can access data that is stored under multiple operating systems without being aware of the operating systems that hold the data.

• Data storage transparency

  Data can be accessed regardless of the database or file format.

• Access method transparency

  You can use a single dialect of SQL for any data store, eliminating the need to code for databaseEspecific access methods or SQL implementations.

Extended Database Services

Following are some of the more sophisticated Oracle Database 10g server services that are available through the gateway.

• SQL functions

  Your application can access all of your data using Oracle SQL, which is rich in features. Advanced Oracle Database 10g server functions, such as outer joins, are available even if the target data stores do not support them in a native environment. The method by which the gateways are integrated with the Oracle Database 10g server ensures that the newest features of each database release are always available immediately to the gateway.

• Distributed capabilities

  Heterogeneous data can be integrated seamlessly because Oracle Database distributed capabilities, such as JOIN and UNION, can be applied to nonEOracle data without any special programming or mapping.

• Distributed query optimization

  The Oracle Database 10g server can use its advanced query optimization techniques to ensure that SQL statements are run efficiently against any of your data. The data distribution and storage characteristics of local and remote data are equally considered.

• TwoEphase commit protection

  The Oracle server twoEphase commit mechanism provides consistency across data stores by ensuring that a transaction that spans data stores is still treated as a single unit of work. Changes are not committed (or permanently stored) in any data store unless the changes can be committed in all data stores that will be affected.

• Stored procedures and database triggers

  The same Oracle stored procedures and database triggers can be used to access all of the data, thereby ensuring uniform enforcement of business rules across the enterprise.

Extended Advanced Networking, Internet and Intranet Support

The gateway integration with the Oracle Database 10g server extends (to nonEOracle data) the benefits of the Oracle Internet software, and Oracle Net software and extends the benefits of the Oracle client/server and server/server connectivity software. These powerful features include:

• Application server support

  Any Internet or intranet application that can access data in Oracle database can also incorporate information from data stores that are accessible through the gateways. Web browsers can connect to Oracle database using any application server product that supports Oracle software.

• Implicit protocol conversion

  Oracle Database and Oracle Net can work together as a protocol converter, enabling applications to transparently access other data stores on platforms that do not support the network protocol of the client. An Oracle Database 10g server can use TCP/IP to communicate with the gateway and another data store.

• Advanced Security

  NonEOracle data can be protected from unauthorized access or tampering during transmission to the client. This is done by using the hardwareEindependent and protocolEindependent encryption and CHECKSUM services of Advanced Security.

• Wireless communication

  Oracle Mobile Agents, an industryEleading Oracle mobile technology, enables wireless communication to Oracle Database 10g servers or to any databases that are accessible through the gateways. This gives your field personnel direct access to enterprise data from mobile laptop computers.

Dynamic Dictionary Mapping

The simple setup of the gateway does not require any additional mapping. Before an application can access any information, the application must be told the structure of the data, such as the columns of a table and their lengths. Many products require administrators to manually define that information in a separate data dictionary stored in a hub. Applications then access the information using the hub dictionary instead of the native dictionaries of each database. This approach requires a great deal of manual configuration and maintenance on your part. As administrators, you must update the data dictionary in the hub whenever the structure of a remote table is changed.

Inefficient duplication is not necessary with Oracle Transparent Gateway for DRDA. The gateway uses the existing native dictionaries of each database. The applications access data using the dictionaries that are designed specifically for each database, which means that no redundant dictionary ever needs to be created or maintained.

SQL

Oracle Transparent Gateways ease application development and maintenance by enabling you to access any data using a uniform set of SQL queries. Changes to the location, storage characteristics, or table structure do not require any changes to the applications. ANSI and ISO standard SQL are supported, along with powerful Oracle extensions.

Data Definition Language

Oracle applications can create tables in target data stores by using native data definition language (DDL) statements.

Data Control Language

You can run native data control language (DCL) statements from an Oracle environment, enabling central administration of user privileges and access levels for heterogeneous data stores.

Passthrough and Native DB2 SQL

Running of native DB2 SQL can be passed through the gateway for processing directly against DB2. This enables applications to send statements, such as a DB2 CREATE TABLE, to the gateway for running on a target DB2 system.

Stored Procedures

The gateway enables you to exploit both Oracle and nonEOracle stored procedures, leveraging your investments in a distributed, multi database environment. Oracle stored procedures can access multiple data stores easily, without any special coding for heterogeneous data access.

Languages

Any application or tool that supports the Oracle Database 10g server can access over thirty different data sources through the Oracle gateways. A wide variety of open system tools from Oracle and thirdEparty vendors can be used, even if the data is stored in legacy, proprietary formats. Hundreds of tools are supported, including ad hoc query tools, Web browsers, turnkey applications, and application development tools.

Oracle Database Server Technology and Tools

The gateway is integrated into the Oracle Database server technology, which provides global query optimization, transaction coordination for multisite transactions, support for all Oracle Net configurations, and so on. Tools and applications that support the Oracle Database server can be used to access heterogeneous data through the gateway.

SQL*Plus

You can use SQL*Plus for moving data between databases. This product gives you the ability to copy data from your department databases to corporate Oracle databases.

TwoEPhase Commit and Multisite Transactions

The gateway can participate as a partner in multisite transactions and twoEphase commit. How this occurs depends on the capabilities of the underlying data source, meaning that the gateway can be implemented as any one of the following:

• A full twoEphase commit partner

• A commit point site

• A singleEsite update partner

• A readEonly partner

The deciding factors for the implementation of the gateway are the locking and transactionEhandling capabilities of the target database.

Oracle Transparent Gateway for DRDA, by default, is configured as a commit point site (that is, commit confirm protocol). Optionally, you can configure the gateway as readEonly if you choose to enforce readEonly capability through the gateway. Other protocols are not supported. Refer to "ReadEOnly Gateway" in Chapter 11, "Using the Gateway".

Site Autonomy

All Oracle Database server products, including gateways, supply site autonomy. For example, administration of a data source remains the responsibility of the original system administrator. Site autonomy also functions so that gateway products do not override the security measures that are established by the data source or the operating environment.

Migration and Coexistence

The integration of a data source through the gateway does not require any changes to be made to applications at the data source. The result is that the Oracle Database server technology is nonintrusive, providing coexistence and an easy migration path.

Security

The gateway does not bypass existing security mechanisms. Gateway security coexists with the security mechanisms that are already used in the operating environment of the data source.

Functionally, gateway security is identical to that of an Oracle Database server, as described in the Oracle Database Administrator's Guide. Oracle Database security is mapped to the data dictionary of the data source.

SQL Differences

Not all SQL implementations are the same. The Oracle Database 10g server supports a larger set of builtEin functions than the databases that are currently accessed through the gateway. The Oracle integrating server and the gateway work together to convert SQL to a form that is compatible with the specific DRDA Server.

During this conversion, an Oracle Database 10g server function can be converted to a function that is recognizable to the specific DRDA Server. For example, the Oracle Database 10g server NVL function is converted to the DB2 VALUE function.

Alternatively, the Oracle integrating server withholds functions that are not executable by the DRDA Server, and it performs them after rows are fetched from the DRDA database. This processing generally applies to SELECT statements. The Oracle integrating server and the gateway cannot perform this kind of manipulation on UPDATE, INSERT, or DELETE statements because doing so changes transaction semantics.

SQL*Plus

Use SQL*Plus and the Oracle Transparent Gateway for DRDA to create a distributed database system, providing an easyEtoEuse transfer facility for moving data between the distributed databases. One possible use is to distribute the data in your corporate Oracle Database to departmental DRDA databases. You can also distribute data in your corporate DRDA database to departmental Oracle Databases.

Heterogeneous Services Architecture

This release of the Oracle Transparent Gateway for DRDA uses the Oracle Heterogeneous Services component within the Oracle Database 10g server. Heterogeneous Services is the building block for the next generation of Oracle Enterprise Integration Gateways.

For detailed information about Heterogeneous Services, refer to the Oracle Database Heterogeneous Connectivity Administrator's Guide.

Performance Enhancements

Oracle Transparent 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 9 gateways. The actual performance improvement at your site might vary, depending on your installation type and workload.

Fetch Reblocking

The array size of the application for SELECT is effective between the application and the Oracle integrating server. However, the array block size and the block fetch between the Oracle integrating server 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 the Oracle Database Heterogeneous Connectivity Administrator's Guide for more information.

Oracle Database 10g Passthrough Supported

You can use the Oracle Database 10g DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE feature to pass commands or statements (that are available in the DRDA database) through the gateway.

Retrieving Result Sets Through Passthrough

Oracle Transparent Gateway for DRDA provides a facility to retrieve result sets from a select SQL statement that is run with passthrough. Refer to "Retrieving Result Sets Through Passthrough" for additional information.

Support for TCP/IP

This release of the gateway supports the TCP/IP communication protocol between the gateway and the DRDA Server. Refer to Chapter 8, "Configuring TCP/IP" for further information.

Native Semantics

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

Columns Supported in a Result Set

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

EXPLAIN_PLAN Improvement

The EXPLAIN_PLAN table contains the actual SQL statements passed to the DRDA Server from the Oracle Database 10g server through the gateway.

Heterogeneous Database Integration

The gateway support for ANSIEstandard 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.

Minimum Impact on Existing Systems

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

Configuring an IBM system for DRDA access typically consists of defining the SNA or TCP/IP resources involved and establishing access security definitions specific to the target database.

Large Base of Data Access

DRDA Application Server Function is supported by most IBM DB2 database products.

Application Portability

The ability of the gateway 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 Corporation provides gateways.

Remote Data Access

Location flexibility is maximized because the gateway architecture permits network connections between each of the components. The application can use the Oracle clientEserver capability to connect to a remote Oracle integrating server through Oracle Net. The Oracle integrating server can connect to a remote gateway using a database link. The gateway connects to DRDA Servers through SNA or TCP/IP network facilities.

The benefits of remote access are that it:

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

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

• Expands the number of available data sources

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

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

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

Support for Distributed Applications

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 systems and thus risk unsynchronized and inconsistent data. Avoiding massive data replication can also reduce aggregate disk storage requirements over all of your systems.

However, if your system design requires moving data among the systems 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 that are specific to your DRDA database through the gateway to be run by the DRDA database. For example, you can pass DB2/OS390 commands through the gateway for DB2 to run. You can also run stored procedures defined in nonEOracle databases.

Application Development and End User Tools

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

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

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.

Password Encryption Utility

This release of the gateway includes a utility to support encryption of plainEtext passwords in the gateway initialization file. Refer to Chapter 13, "Security Considerations" for details.

Support for DB2/OS390 V6, V7, and V8 Stored Procedures

This release of the gateway supports the native stored procedure catalogs in DB2 V6, V7, and V8 (SYSIBM.SYSROUTINES and SYSIBM.SYSPARMS).

Codepage Map Facility

This release of the gateway supports external mapping of IBM CCSIDs to Oracle character sets. Refer to "Gateway Codepage Map Facility" in Appendix D, "National Language Support".

IBM DB2 Universal Database Support

This release supports IBM DB2 Universal Database.

IBM DB2 Version 5.1 ASCII Tables

IBM DB2 version 5.1 supports ASCII and EBCDIC character sets. The character set selection is defined during table creation. The Oracle Transparent Gateway for DRDA supports access to EBCDIC tables and ASCII tables. Refer to Appendix D, "National Language Support".

Read-Only Support

This release enables the gateway to be configured as a read-only gateway. In this mode, no modifying of user data is permitted. For more information, refer to "DRDA_READ_ONLY".

Support for Graphic and Multibyte Data

This release of the gateway adds support for DB2 GRAPHIC and VARGRAPHIC data types. Refer to Chapter 12, "Developing Applications".

Support for DB2/UDB on Intel Hardware

This release of the gateway adds support for DRDA Servers running on Microsoft Windows and Linux on Intel hardware.

Data Dictionary Support for DB2/UDB

This release of the gateway adds Oracle data dictionary support for DB2 UDB V7. Refer to "Sample SQL scripts" for more information.