| Oracle® Transparent Gateway for DB2/400 Installation and User's Guide 10g Release 2 (10.2) for IBM iSeries OS/400 Part Number B16222-01 |
|
|
View PDF |
| Oracle® Transparent Gateway for DB2/400 Installation and User's Guide 10g Release 2 (10.2) for IBM iSeries OS/400 Part Number B16222-01 |
|
|
View PDF |
This chapter presents information about the architecture, uses, and features of the Oracle Transparent Gateway for DB2/400. It 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 fast access to current and complete information.
Company growth and diversification often result in geographically scattered data that is processed with a collage of applications that may be using incompatible networks, platforms, and storage formats. Diverse application standards and storage formats can make integration of information difficult. For many years, Oracle has offered integration technologies to overcome these technical barriers. Oracle Open Gateways simplify complex systems and remove obstacles to information flow, thereby 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 Oracle applications while you continue to use existing IBM applications to access the 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, then 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:
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/400 in addition to reading and writing data from Oracle applications to data in any Oracle server (DB2/400 Large objects can be read only at this time).
The Oracle Database 10g server provides the foundation for the next generation of the Enterprise Integration Gateways Release 10.2.0.1.0, which deliver enhanced integration capabilities by exploiting Oracle 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 about Oracle Heterogeneous Services, refer to Oracle Database Heterogeneous Connectivity Administrator's Guide.
Version 10g Release 2 gateways are closely integrated with the Oracle Database 10g server, and they enable 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 Oracle Database 10g 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.
Version 10g Release 2 gateways leverage the enhancements in the Oracle Database 10g 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 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 DB2/400 database to an Oracle Database, 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/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.
Operating system transparency:
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 use a single dialect of SQL for any data store, thereby eliminating the need to code for database-specific access methods or SQL implementations.
The following are some of the more sophisticated Oracle Database 10g server services that are available through the gateway.
Your application can access all your data using Oracle SQL, which is rich in features. Advanced Oracle Database 10g server functionalities, 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 server ensures that the latest 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.
Distributed query optimization:
The Oracle Database 10g server can use 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.
Stored procedures and database triggers:
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 Oracle Database 10g 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 an Oracle database 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 Oracle Database 10g 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 Oracle Database 10g server or any databases that are 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. Administrators 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 that are designed specifically for each database, which means that no redundant dictionary need ever be created or maintained.
Oracle Transparent Gateway for DB2/400 eases 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.
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 Oracle Database Heterogeneous Connectivity Administrator's Guide for more information about DCL.
The gateway enables you to exploit both Oracle and non-Oracle stored procedures and functions, leveraging your investments in a distributed, multi-database environment. Oracle stored procedures and functions can access and update multiple data stores easily, with no special coding for heterogeneous data access.
Oracle stored procedures and functions enable you to access and update DB2/400 data using centralized business rules that are stored in the Oracle Database 10g server. The use of Oracle stored procedures and functions can increase the database performance by minimizing network traffic. For example, 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.
The gateway can execute DB2/400 User Defined Functions using standard Oracle PL/SQL. In addition, a User Defined Function may be executed within a SQL statement. In both cases, the Oracle application executes the DB2/400 User Defined Function as if it were a remote Oracle stored function. When the User Defined Function is executed as part of a SELECT statement, it may not cause any database changes on the remote (DB2/400) database.
Any application or tool that supports the Oracle Database 10g server can access over 30 different data sources through the Oracle gateways. A wide variety of open system tools from Oracle and from 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 Oracle Database 10g 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 10g 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 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 Oracle Database 10g 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 methods of the data source or of 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 Oracle Database 10g server technology is nonintrusive, 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 Oracle Database 10g server, as described in the Oracle Database Administrator's Guide. 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.
Client:
The client is an Oracle application or tool.
The Oracle Database 10g server is an Oracle instance.
Oracle Transparent Gateway for DB2/400:
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 SQL 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 by using the OS/400 STRSBS command or by using the ORAMON command. Individual gateway tasks run in a subsystem, as does the LISTENER task for TCP/IP.
The DB2/400 database is the database that is being accessed by the gateway.
Multiple Oracle Database 10g 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 for each AS/400 system. Multiple gateways can be installed on a single AS/400 system.
The gateway has no database functions of its own. Instead, it provides an interface by which the Oracle Database 10g server can direct SQL operations to a DB2/400 database.
The gateway that is supporting the DB2/400 server is identified to the Oracle Database 10g server by 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 DB2/400 server are referenced in SQL as:
table_name@dblink_name
or, preferably, as:
library.table_name@dblink_name
If you create synonyms or views in the Oracle Database, then you can refer to tables on the DB2/400 server by using simple names as though the tables were local to the Oracle Database 10g server.
When the Oracle Database 10g 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 detecting errors and returning responses.
Installing the gateway on the AS/400 system produces the following changes to the system:
A library is created, with the instance name as given in the first installation panel. (For an example, refer to Example 4-1, "Install Oracle Transparent Gateway Panel"). Almost all of the objects needed for this gateway instance will be found in this library, with the exception of some objects dealing with service programs and the items mentioned in parts 3 and 4 of this list.
The gateway can also be installed into a previously created DB2/400 SQL COLLECTION that contains objects of type *FILE (physical and logical files), a journal of type *JRN, a journal receiver of type *JRNRCV, and a data dictionary object of type *DTADCT. The collection name will be the name of the library in which these objects are created by DB2/400.
A user profile is created. The user profile has the same name as the library that is created when you install the gateway.
A subdirectory entry is created in the /home directory in the Integrated File System. The subdirectory entry name will be the same as the instance name. So, if ORACLE is the instance name, then a subdirectory node will be created at /home/ORACLE. Further nodes will be created below this node. Agent trace files will appear (if requested) in a subdirectory of the created node. An initialization file will also appear in a subdirectory of this node.
A library named ORASRVLIB is created. If the installation software finds that it must install the service programs that are found on the installation medium, then a library with a name such as ORASRVnnnn (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.
A message queue with the name of the installation library is created in the QUSRSYS library. This is as a result of using the CRTUSRPRF command to create the user profile that corresponds to the installation library.
A library named ORATEMP will be created to hold intermediate installation objects during the install.
Not all SQL implementations are the same. The Oracle Database 10g server may support a larger set of built-in functions than the databases that are currently being 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 Oracle Database 10g server. The SUBSTR function is an example of this, because the second and third arguments of SUBSTR in an Oracle Database 10g server may be negative. In DB2/400, the second and third arguments of the SUBSTR function cannot be negative. In any case, the Oracle Database 10g 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 Oracle Database 10g server function can be converted to a function that is recognizable to the specific DB2/400 server. For example, the Oracle Database 10g 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 those functions on the Oracle server 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 post processing generally applies to SELECT statements.
The SUBSTR function, when used with two or three parameters, is an example of a function that is always post processed on the Oracle server side since the semantics of the SUBSTR function with two or thee parameters differs between Oracle 10g and DB2/400. 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.
This release of the Oracle Transparent Gateway for DB2/400 uses 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 the Oracle Database Heterogeneous Connectivity Administrator's Guide.
