Oracle® Transparent Gateway for DRDA Installation and User's Guide
10g Release 1 (10.1) for UNIX
Part No. B12009-02
The Oracle Transparent Gateway for DRDA 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/OS390, DB2/400, DB2 Universal Database, and DB2/VM databases in addition to any Oracle Database server data.
Read this chapter for information about the architecture, uses, and features of the Oracle Transparent Gateway for DRDA.
This chapter contains the following sections:
In today's global economy, information is a company's most valuable resource. Whether you need to analyze new markets, tailor your products to meet local demands, increase your ability to handle complex customer information, or streamline operations, your company requires instant access to current and complete information.
Company growth and diversification often mean functioning with a collage of applications and geographically scattered data that may be using incompatible networks, platforms, and storage formats. Diverse application standards and storage formats can make integration of information difficult. Oracle Corporation offers integration technologies to overcome these technical barriers. Oracle Open Gateways simplify complex systems and remove obstacles to information, providing your company the opportunity to focus on business.
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 your Oracle data and DB2 data with a single set of applications while you continue to use existing IBM applications to access your IBM data. You can also use more productive database tools and move to a distributed database technology without giving up access to your current data.
If you choose to migrate to Oracle Database technology and productivity, the gateway allows 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.
The Oracle Database 10g server provides the foundation for the next generation of the Oracle Open Gateways Release 10g, which will deliver enhanced integration capabilities by exploiting Oracle Database 10g Heterogeneous Services. Heterogeneous Services is a component of the Oracle Database 10g server. The Oracle Database 10g server provides the common architecture for future generations of the gateways. For detailed information on Oracle Heterogeneous Services, refer to Oracle Database Heterogeneous Connectivity Administrator's Guide.
The version 10 gateways are even more tightly integrated with the Oracle Database 10g server 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 10g server, 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.
Version 10 gateways leverage any enhancements in the Oracle Database 10g server, and you can quickly extend those benefits to your non-Oracle data.
Oracle Transparent Gateway for DRDA enables Oracle applications to access the DRDA Application Servers, such as DB2 for 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, even 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.
Oracle Transparent Gateway for DRDA gives you the power to integrate your heterogeneous system into a single, seamless environment. This integration enables you to make full use of existing hardware and applications throughout your corporate-wide environment. You can eliminate the need to rewrite applications for each configuration, and you can avoid the tedious, error-prone process of manual data transfer. Together with the Oracle tools, networking, and data server technology, the Oracle Transparent Gateway for DRDA sets a high standard for seamless, enterprise-wide information access.
Oracle Transparent Gateway for DRDA enables applications to read and update DB2 data and Oracle data as if all of the data were stored in a single 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 allows you to integrate heterogeneous data seamlessly, it simplifies your gateway implementation, application development, and maintenance.
The Oracle Transparent Gateway for DRDA gives you transparency at every level within your enterprise.
End users can access tables by name without needing to understand the physical location of the tables.
Operating system transparency
You can access data 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 utilize a single dialect of SQL for any data store, eliminating the need to code for database-specific access methods or SQL implementations.
Your application can access all 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.
The Oracle Database 10g server can utilize its advanced query optimization techniques to ensure that SQL statements are executed efficiently against any of your data. The data distribution and storage characteristics of local and remote data are equally considered.
The Oracle server two-phase 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.
The same Oracle stored procedures and database triggers can be used to access all of your data, thereby ensuring uniform enforcement of your business rules across the enterprise.
The gateway integration with the Oracle Database 10g server extends (to non-Oracle data) the benefits of the Oracle Internet and Oracle Net software and extends the benefits of the Oracle client/server and server/server connectivity software. These powerful features include:
Any Internet or intranet application that can access data in Oracle can also incorporate information from data stores accessible through the gateways. Web browsers can connect to the Oracle Database using any application server product that supports Oracle software.
Oracle and Oracle Net can work together as a protocol converter, allowing applications to transparently access other data stores on platforms that do not support the client's network protocol. An Oracle Database 10g server can use TCP/IP to communicate with the gateway and another data store.
Non-Oracle data can be protected from unauthorized access or tampering during transmission to the client. This is done by using the hardware-independent and protocol-independent encryption and CHECKSUM services of Advanced Security.
Oracle Mobile Agents, an Oracle industry-leading 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.
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. Your applications access data using the dictionaries designed specifically for each database, which means no redundant dictionary ever needs to be created or maintained.
Oracle Transparent Gateways ease your application development and maintenance by allowing you to access any data using a uniform set of SQL. Changes to the location, storage characteristics, or table structure do not require any changes to your applications. ANSI and ISO standard SQL are supported, along with powerful Oracle extensions.
Oracle Applications can create tables in target data stores by using native data definition language (DDL) statements.
You can issue native data control language (DCL) statements from an Oracle environment, allowing central administration of user privileges and access levels for heterogeneous data stores.
Execution of native DB2 SQL can be passed through the gateway for execution directly against DB2. This enables applications to send statements, such as a DB2 CREATE TABLE, to the gateway for execution on a target DB2 system.
The gateway enables you to exploit both Oracle and non-Oracle 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 the heterogeneous data access.
Oracle stored procedures enable you to access and update DB2 data using centralized business rules stored in the Oracle Database 10g server. Using Oracle stored procedures can increase your database performance by minimizing network traffic. Instead of sending individual SQL statements across the network, an application can send a single EXECUTE command to begin an entire PL/SQL routine.
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 Corporation and third-party 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.
The gateway is integrated into the Oracle Database server technology, which provides global query optimization, transaction coordination for multi-site 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.
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.
The gateway can participate as a partner in multi-site transactions and two-phase 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 two-phase commit partner
A commit point site
A single-site update partner
A read-only partner
The deciding factors for the implementation of the gateway are the locking and transaction-handling capabilities of your 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 read-only if you choose to enforce read-only capability through the gateway. Other protocols are not supported. Refer to "Read-Only Gateway" in Chapter 13, "Using the Gateway".
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 such that gateway products do not override the security measures established by the data source or operating environment.
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 non-intrusive, providing coexistence and an easy migration path.
The gateway does not bypass existing security mechanisms. Gateway security coexists with the security mechanisms 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.
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 integrating server is any Oracle Database 10g server instance that communicates with the Oracle Transparent Gateway for DRDA to distribute database access operations to a DRDA Server. The Oracle integrating server can also be used for non-gateway applications.
DB2 Universal Database is a generic name for the UNIX-based or Windows-based implementations of DB2. DB2/UDB is frequently used as an abbreviation for DB2 Universal Database.
The Oracle Transparent Gateway for DRDA works with the Oracle Database 10g server to shield most of the differences of the non-Oracle Database from Oracle applications. This means that the Oracle applications can access the Oracle Database 10g server data and the DRDA database data as if it were Oracle data located at the Oracle integrating server.
The architecture consists of the following main components:
The client is an Oracle application or tool.
The Oracle integrating server is an Oracle instance accessed by an Oracle Database 10g server with procedural and distributed options. Usually, the Oracle integrating server is installed on the same host as the gateway, but this is not a requirement. The Oracle integrating server and the gateway communicate in the normal Oracle Database server-to-server manner.
If the Oracle integrating server 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 10g, you must install Oracle Net on the Oracle Database 10g server machine.
Oracle Transparent Gateway for DRDA
The gateway must be installed on hosts that are running the appropriate operating system.
If the Oracle integrating server is not on the same host, then you must also install Oracle Net so that the gateway and Oracle Database 10g server can communicate.
Multiple Oracle Database 10g servers can access the same gateway. A single host gateway installation can be configured to access more than one DRDA Server.
Figure 1-1 illustrates the gateway architecture that is described above.
Figure 1-1 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's ORACLE_HOME environment variable
A gateway system identifier (SID), comparable to an Oracle instance's ORACLE_SID
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 10g server
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 10g server user session that accesses a particular gateway creates an independent process on the host. This process runs the gateway session and executes SNA or TCP/IP functions to communicate with a DRDA Server.
The gateway that is supporting the DRDA Server is identified to the Oracle integrating server using a database link. The database link is the same construct that is used to identify other Oracle Database 10g server databases. Tables on the DRDA Server are referenced in SQL as:
If you create synonyms or views in the Oracle integrating server database, then you can refer to tables on the DRDA Server by using simple names as though the table were local to the Oracle integrating server.
When the Oracle integrating server 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 datatypes and DRDA datatypes are performed by the gateway. Both the Oracle integrating server and the application read and process only Oracle datatypes.
Not all SQL implementations are the same. The Oracle Database 10g server supports a larger set of built-in 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 IBM VALUE function.
Alternatively, the Oracle integrating server withholds functions that are not executable by the DRDA Server and 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.
Use the gateway to run applications, such as Oracle tools, that read and write data that is stored in DRDA databases.
While the Oracle Transparent Gateway for DRDA provides no new application or development facilities, it extends the reach of existing Oracle tools to include data in non-Oracle Databases that support DRDA.
Using the Oracle Transparent Gateway for DRDA with other Oracle products can greatly extend the capabilities of the stand-alone gateway. The following examples demonstrate how powerful the gateway is with other Oracle tools.
Use SQL*Plus and the Oracle Transparent Gateway for DRDA to create a distributed database system, providing an easy-to-use 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.
Following is a list of important features that characterize this release of the gateway.
This release of the Oracle Transparent Gateway for DRDA utilizes the Oracle Heterogeneous Services component within the Oracle Database 10g server. Heterogeneous Services is the building block for the next generation of Oracle Open Gateways.
For detailed information about heterogeneous services, refer to Oracle Database Heterogeneous Connectivity Administrator's Guide.
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.
The array size of the application for SELECT is effective between the application and the Oracle integrating server. However, the array blocksize 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 Oracle Database Heterogeneous Connectivity Administrator's Guide for more information.
You can use the Oracle Database 10g DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE feature to pass commands or statements available in your DRDA database through the gateway.
Oracle Transparent Gateway for DRDA provides a facility to retrieve result sets from a select SQL statement issued with passthrough. Refer to "Retrieving Results Sets Through Passthrough" for additional information.
This release of the gateway supports the TCP/IP communication protocol between the gateway and the DRDA Server. Refer to Chapter 10, "Configuring TCP/IP", for further information.
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.
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, VM, or UNIX 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.
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 Corporation 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 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 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, 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 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 DB2/OS390 commands through the gateway for DB2 to execute. You can also execute stored procedures defined in non-Oracle Databases.
Through the gateway, Oracle Corporation 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 users do not have to learn a new set of tools to access data 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 available from Oracle Corporation, you can develop a single set of applications to access Oracle and DRDA data. Users can use the decision support tools available from Oracle Corporation to access Oracle and DRDA data. These tools can run on remote machines 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.
This release of the gateway includes a utility to support encryption of plain-text passwords in the gateway Initialization File. Refer to Chapter 15, "Security Considerations" for details.
This release of the gateway supports the native stored procedure catalogs in DB2 V6 (SYSIBM.SYSROUTINES and SYSIBM.SYSPARMS).
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".
This release allows the gateway to be configured as a read-only gateway. In this mode, no modifying of user data will be allowed. For more information, refer to "DRDA_READ_ONLY".