Release 9.2.0.1.0 for IBM AS/400
Part Number A97615-01
 Contents |
 Index |
| Oracle Transparent Gateway for DB2/400 Installation and User's Guide Release 9.2.0.1.0 for IBM AS/400 Part Number A97615-01 |
|
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 fast 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 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 DB2/400 gives your company the ability to develop its information systems without forfeiting your investments in current data and applications. The gateway gives you access to your Oracle and DB2/400 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/400 data into Oracle databases.
The Oracle Transparent Gateway for DB2/400 enables you to:
This chapter presents information about the architecture, uses, and features of the Oracle Transparent Gateway for DB2/400. It contains the following sections:
The Oracle9i database server provides the foundation for the next generation of the Enterprise Integration Gateways Release 9i, which deliver enhanced integration capabilities by exploiting Oracle Heterogeneous Services. Heterogeneous Services is a component of the Oracle9i database server. The Oracle9i database server provides the common architecture for future generations of the gateways. For detailed information on Oracle Heterogeneous Services, refer to Oracle9i Heterogeneous Connectivity Administrator's Guide.
Release 9i gateways are tightly integrated with the Oracle9i database server, enabling improved performance and enhanced functionality while still providing transparent integration of Oracle data and non-Oracle data. For example, connection initialization information is available in the local Oracle9i database server, reducing the number of round trips and the amount of data that is sent over the network. Structured Query Language (SQL) execution is also faster, because statements issued by an application are parsed and translated once and can then be reused by multiple applications.
Release 9i gateways leverage the enhancements in the Oracle9i database server, and you can quickly extend those benefits to your non-Oracle data.
Oracle Transparent Gateway for DB2/400 enables Oracle client applications to access DB2/400 through SQL. The gateway and Oracle9i database server together create the appearance that all data resides on a local Oracle9i database server, even though data might be widely distributed. If data is moved from a DB2/400 database to an Oracle database, then no changes in client application design or function are needed. The gateway handles all differences in datatypes and SQL functions between the application and the database.
Oracle Transparent Gateway for DB2/400 gives you the power to integrate your heterogeneous systems 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 world-class tools, networking, and data server technology, the Oracle Transparent Gateway for DB2/400 sets the standard for seamless, enterprise-wide information access.
By using the Oracle Transparent Gateway for DB2/400, you can achieve transparency at every level within your enterprise.
Users can access tables by name, without having to understand the physical location of the tables.
The gateways exploit the Oracle Net technology to allow users to access data across multiple networks, without concern for the network architecture or protocols. Multiple protocols are supported.
You can access data that is stored under multiple operating systems without being aware of the different operating systems that hold the data.
Data can be accessed regardless of the database or file format.
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.
The following are some of the more sophisticated Oracle9i database server services available through the gateway.
Your application can access all your data using Oracle SQL, which is rich in features. Advanced Oracle9i 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 Oracle9i database server ensures that the newest features of each database release are always available immediately to the gateway.
Heterogeneous data can be integrated seamlessly because Oracle database distributed capabilities, such as JOIN and UNION, can be applied against non-Oracle data without any special programming or mapping.
The Oracle9i database 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 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, nor permanently stored, in any data store unless the changes can be committed in all data stores that are affected.
The same Oracle stored procedures and database triggers can be used to access all of your data, ensuring uniform enforcement of your business rules across the enterprise.
The gateway integration with the Oracle9i database server extends to non-Oracle data the benefits of the 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 Oracle can also incorporate information from data stores that are 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 network protocol. For example, an application can use SPX/IPX to communicate with an Oracle9i database server, which can use TCP/IP to communicate with the gateway and another data store on a non-SPX/IPX platform.
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 Security.
Oracle Mobile Agents, an Oracle industry-leading mobile technology, enables wireless communication to Oracle9i database server or any databases accessible through the gateways. This gives field personnel direct access to enterprise data from mobile laptop commuters.
Before an application can access any information, the application must be told the structure of the data, such as the names of columns of a table and their lengths. Many other products require administrators to manually define this information in a separate data dictionary stored in a hub. Applications then 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 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 eliminated with Oracle Transparent Gateway for DB2/400. The simple setup of the gateway does not require any additional mapping. The gateway uses the existing native dictionaries of each database. Your applications access data using the dictionaries designed specifically for each database, which means that no redundant dictionary need ever be created or maintained.
Oracle Transparent Gateway for DB2/400 eases 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.
Native DB2/400 SQL can be passed through the gateway for running directly against DB2/400. This enables applications to send statements, such as a DB2/400 CREATE TABLE, to the gateway for execution on a target DB2/400 system.
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. Refer to Oracle9i Heterogeneous Connectivity Administrator's Guide for more information on DCL.
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, with no special coding for the heterogeneous data access.
Oracle stored procedures enable you to access and update DB2/400 data using centralized business rules that are stored in the Oracle9i database server. The use of 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.
The gateway can execute DB2/400 stored procedures using standard Oracle PL/SQL. The Oracle application executes the DB2/400 stored procedure as if it were an Oracle remote procedure.
Any application or tool that supports the Oracle9i database server can access over 30 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.
Use SQL*Plus for moving data between the databases. This product gives you the ability to copy data from your department databases to corporate Oracle databases.
The gateway is integrated into the Oracle9i 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 Oracle9i database server can be used to access heterogeneous data through the gateway.
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:
The deciding factors for the implementation of the gateway are the locking and transaction-handling capabilities of your target database.
Oracle Transparent Gateway for DB2/400, 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" for more information.
All Oracle9i 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 methods of the data source or the operating environment.
The integration of a data source through the gateway requires no changes to be made to applications at the data source. As a result of this, the Oracle9i 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 that are already used in the operating environment of the data source.
Functionally, gateway security is identical to that of an Oracle9i database server, as described in the Oracle9i Administrator's Reference. Oracle database security is mapped to the data dictionary of the data source.
The gateway architecture consists of four main components as shown in Figure 1-1.
The client is an Oracle application or tool.
The Oracle9i database server is an Oracle instance.
The Oracle Transparent Gateway for DB2/400 must be installed on an AS/400 system, and is responsible for issuing the dynamic SQL calls to DB2/400. The gateway can access files in an OS/400 collection or files that are externally described to DB2/400.
The gateway is not started as an Oracle instance and has no continuously running background processes. The gateway is started either using the OS/400 STRSBS command or by using the ORAMON command. Individual gateway tasks run in a subsystem, as does a LISTENER task if TCP/IP is used.
The DB2/400 database is the database that is being accessed by the gateway.
Multiple Oracle9i database servers can access the same gateway. A single gateway installation can be configured to access one, and only one, DB2/400 server, because there is only one instance of a DB2/400 database per AS/400 system.
The gateway has no database functions of its own. Instead, it provides an interface by which the Oracle9i database server can direct SQL operations to a DB2/400 database.
The gateway that is supporting the DB2/400 server is identified to the Oracle9i database server using a database link. The database link is the same construct used to identify other Oracle9i database server databases.
Tables on the DB2/400 server are referenced in SQL as:
table_name@dblink_name
or, preferably, as:
owner.table_name@dblink_name
If you create synonyms or views in the Oracle database, you can refer to tables on the DB2/400 server by using simple names as though the tables were local to the Oracle9i database server.
When the Oracle9i database server encounters a reference to a table on the DB2/400 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 DB2/400 server.
The gateway is responsible for sending these SQL statements to the DB2/400 server. The DB2/400 server is responsible for executing the SQL statements and for fielding and returning responses.
Installing the gateway on your AS/400 system produces the following changes to the system:
The gateway can also be installed into a previously-created DB2/400 SQL COLLECTION that contains nothing but logical files.
nnnn (where nnnn is a number) will be created. The relevant service programs and a file that is used for NLS transactions will be placed in that library. Additional files or members within files may be added to the ORASRVLIB library.
Not all SQL implementations are the same. The Oracle9i database server may support a larger set of built-in functions than the databases that are currently accessed through the gateway. Or, the semantics of some functions in the DB2/400 server may not agree with an identically named function in the Oracle9i database server. The SUBSTR function is an example of this, since the second and third arguments of SUBSTR in an Oracle9i database server may be negative. In DB2/400, the second and third arguments of the SUBSTR function cannot be negative. In any case, the Oracle9i database server and the gateway work together to convert the SQL to a form that is compatible with the specific DB2/400 server.
During this conversion, an Oracle9i database server function can be converted to a function recognizable to the specific DB2/400 server. For example, the Oracle9i database server NVL function is converted to the DB2/400 VALUE function.
Alternatively, the Oracle integrating server withholds functions that are not executable by the DB2/400 server and performs the functions on the Oracle side after the rows are fetched from the DB2/400 server. This is called post-processing because it occurs after the rows are fetched from DB2/400. 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. The SUBSTR function is an example of a function that is always post-processed on the Oracle server side.
This release of the Oracle Transparent Gateway for DB2/400 utilizes the Oracle Heterogeneous Services component within the Oracle9i server. Heterogeneous Services is the building block for the next generation of Oracle Open Gateways. For detailed information about heterogeneous services, refer to the Oracle9i Heterogeneous Connectivity Administrator's Guide.
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|