| Oracle® Transparent Gateway for DB2 Installation and User's Guide 10g Release 2 (10.2) for IBM z/OS (OS/390) Part Number B16220-02 |
|
|
View PDF |
| Oracle® Transparent Gateway for DB2 Installation and User's Guide 10g Release 2 (10.2) for IBM z/OS (OS/390) Part Number B16220-02 |
|
|
View PDF |
In today's fast-paced global economy, information is a company's most valuable resource. Companies often have a variety of applications and data that are geographically scattered and use incompatible networks, platforms, and storage formats. In a single company, data is likely to be dispersed across several systems and hundreds of desktops. Oracle Open Gateways simplify complex systems and remove obstacles to information mobility.
This chapter includes the following sections:
Oracle Open Gateways use the facilities of the Oracle Database 10g heterogeneous services. For further information about Heterogeneous Services, refer to the Oracle Database Heterogeneous Connectivity Administrator's Guide.
The Oracle Database 10g database server continues to improve its distributed database facilities that make the integration of enterprise data practical. Data can be accessed remotely using both SQL and PL/SQL procedure calls in a transparent manner, as if the data were local. Also, data can be stored in both Oracle Database 10g database servers and non-Oracle servers.
Version 10 gateways are tightly integrated with the Oracle Database 10g database server, 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 server, reducing the number of round trips and the amount of data sent over the network. Running SQL 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 the enhancements in the Oracle Database 10g database server, and you can quickly extend those benefits to your non-Oracle data.
Oracle Transparent Gateway for DB2 enables Oracle client applications to access DB2 through Structured Query Language (SQL). The gateway and Oracle database server together create the appearance that all data resides on a local Oracle database server, even though data might be widely distributed. If data is moved from a DB2 database to an Oracle database server, then no changes in client application design or function are needed. The gateway handles all differences in data types and SQL functions between the application and the database.
Oracle Transparent Gateway for DB2 gives you the power to integrate your heterogeneous system into a single, seamless environment, enabling 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 avoid the tedious, error-prone process of manual data transfer. Together with the Oracle tools, networking, and data server technology, Oracle Transparent Gateway for DB2 provides seamless, enterprise-wide information access.
By using Oracle Transparent Gateway for DB2, you can achieve transparency at every level within your enterprise.
Location transparency
Users can access tables by name, without having to understand the physical location of the tables.
Network transparency
The gateways exploit Oracle Net, enabling protocol independence. Regardless of the quantity or type of communication protocols used, data access is seamless. Protocols supported for Oracle Net include IBM TCP/IP High Performance Native Sockets (HPNS), and Oracle Net cross memory driver.
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 use a single dialect of SQL for any data store, eliminating the need to code for database-specific access methods or SQL implementations.
Following are some of the more sophisticated Oracle Database 10g database server services available through the gateway.
SQL functions
Your application can access all your data using Oracle SQL that is rich in features. Advanced Oracle Database 10g database server functions, such as outer joins, are available even if the target data stores do not support them in a native environment. The manner in which the gateways are integrated with the Oracle Database 10g database server ensures that the latest features of each database release are always available immediately to the gateway.
Distributed capabilities
Heterogeneous data can be integrated seamlessly because Oracle distributed capabilities, such as JOIN and UNION, can be applied against non-Oracle data without any special programming or mapping.
Distributed query optimization
The Oracle Database 10g database 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 considered equally.
The Oracle 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 are affected.
Stored procedures and database triggers
The same Oracle stored procedures and database triggers can be used to access all your data, ensuring uniform enforcement of business rules across the enterprise.
The gateway integration with the Oracle Database 10g database server extends to non-Oracle data, the benefits of the Oracle internet and Oracle Net, and the Oracle client/server and server/server connectivity software. These powerful features include:
Any Internet or intranet application that can access data in the Oracle database server can also incorporate information from data stores accessible through the gateways. Web browsers can connect to the Oracle database server using any application server product that supports Oracle software.
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 the Advanced Networking Option (ANO).
Oracle Mobile Agents, an Oracle industry-leading mobile technology, enables wireless communication to Oracle Database 10g database servers or any databases accessible through the gateways. This gives 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 this information in a separate data dictionary stored in a hub. Applications access information using the hub dictionary instead of the native dictionaries of each database. This approach requires a great deal of manual configuration and maintenance. Administrators 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 DB2. The gateway uses the existing native dictionaries of each database. The applications access data using the dictionaries designed specifically for each database, which means that no redundant dictionary ever needs to be created or maintained.
Oracle Transparent Gateways ease the 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.
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 and update 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 database server. Using Oracle stored procedures can increase the 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.
The gateway can run DB2 stored procedures using standard Oracle PL/SQL. The Oracle application runs the DB2 stored procedure as if it were an Oracle remote procedure.
DB2 User Defined Functions (UDFs) can be coded in Oracle SQL statements or as function calls in PL/SQL. The Oracle database calls the DB2 UDF to perform the function passing constants, column values or bind variables. The results of the UDF are then returned into the SQL statement or PL/SQL statement for processing. This feature allows you to leverage DB2 UDF development work and make those DB2 functions available to Oracle SQL and PL/SQL programs as if they were Oracle functions.
Any application or tool that supports the Oracle Database 10g database server can access over 30 different data sources through the Oracle gateways. A wide variety of open system tools from Oracle and third-party vendors can be used, even if the data is stored in legacy, proprietary formats. Hundreds of tools are supported, including improvised query tools, web browsers, turnkey applications, and application development tools.
Use Oracle Developer to build applications that can manipulate data stored in your DB2 database and Oracle database server. This includes designing forms, producing graphic displays, and creating a wide range of objects.
Use Oracle Discoverer to analyze, manipulate, and copy data residing in your DB2 database. This product gives you access to corporate data using a powerful data analysis tool.
Use SQL*Plus for moving data between the databases. This product gives you the ability to copy data between your department databases and corporate Oracle database servers.
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.
The gateway can participate as a partner in multisite 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 implementing the gateway are the locking and transaction-handling capabilities of your target database.
Oracle Transparent Gateway for DB2, 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.
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. With site autonomy implemented, gateway products do not override the security methods of 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. As a result of this, 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.
With the gateway, you can continue to develop your information systems without foregoing your investments in current data and applications. Access your Oracle and DB2 data with a single set of applications and 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 want to migrate to Oracle database server technology and productivity, then the gateway allows you to control the pace of your migration. As you transfer applications from your previous technology to the Oracle database server, you can use the gateway to move the DB2 data into Oracle database servers.
The gateway architecture consists of three main components.
The Oracle database server is an Oracle instance functioning as a client to access the gateway.
Oracle Transparent Gateway for DB2 must be installed on a z/OS system.
The DB2 database server is the one being accessed by the gateway. IBM terminology for a DB2 server is DB2 Server for z/OS or DB2 Universal Database (UDB) for z/OS.
Multiple Oracle database servers can access the same gateway. A single gateway installation can be configured to access one DB2 server. Any number of DB2 gateways can access the same DB2 server.
The gateway has no database functions. The gateway provides an interface by which the Oracle database server can direct SQL operations to a DB2 database.
Using a database link, the gateway is identified to the Oracle database server. The database link is the same construct used to identify other Oracle database servers.
Tables on the DB2 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 server, then you can refer to tables on the DB2 server using simple names as though the table were local to the Oracle database server.
When the Oracle database server encounters a reference to a table on the DB2 server, the applicable portion of the SQL statement is sent to the gateway for processing. Any host variables associated with the SQL statement are bound to the gateway and, therefore, to the DB2 server.
The gateway is responsible for sending these SQL statements to the DB2 server. The DB2 server is responsible for running the SQL statements and for fielding and returning responses.
