Skip Headers

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
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

7
Using the Gateway

After installing the gateway, you can administer database links, access the gateway, access AS/400 file members, perform distributed queries, and copy data between the Oracle9i database server and the AS/400.

This chapter contains the following sections:

Database Link Behavior

A connection to the gateway is established through a database link when it is first used in a gateway session or transaction.  In this context, connection refers to the connection between the Oracle9i database server and the gateway.  The connection remains established until the session ends.  Another session or user can access the same database link and get a connection to the gateway and DB2/400 database.

Connections to the DB2/400 database might be limited by factors that include memory, gateway parameters, or DB2/400 server resources.

The database and application administrators of a distributed database system are responsible for managing the necessary database links defining paths to the gateway.

Database links are discussed in detail in the Oracle9i Administrator's Reference.  Information for using database links with the gateway is discussed here.

Creating Database Links

To create a database link in Oracle and to define a path to the gateway, use the CREATE DATABASE LINK statement.  The CONNECT TO clause specifies the remote user ID and password to use when creating a session in the gateway.  If you do not specify a user ID and password in the CONNECT TO clause, then the Oracle logon user ID and password are used.

The USING clause specifies a TNSNAMES.ORA connect descriptor.

Creating Database Links Using Oracle Net

Oracle Net is required.  The following syntax creates a database link to access information in the DB2/400 database using Oracle Net:

CREATE DATABASE LINK dblink
        CONNECT TO userid identified by password
        USING 'tns_name_entry';

where:

dblink

is the complete database link name (such as gateway).

userid

is the user ID used to establish a session in the remote database.  It must be authorized to any table or file on the DB2/400 server referenced in the SQL commands.  The user ID cannot be longer than ten characters.

password

is the password used to establish a session in the remote database.  This must be a valid DB2/400 server password.  The password cannot be longer than ten characters.

tns_name_entry

specifies the Oracle Net TNS connect descriptor used to identify the gateway subsystem.  This is identical to the CONNECT_NAME found in entries in the TNSNAMES.ORA member.  Refer to "Step 1: Add a TCP/IP Connect Descriptor to TNSNAMES.ORA".

Closing Database Links

Once used, a database link remains open for the duration of the gateway session.  If you want to close a database link during a session, then you can do so with the ALTER session CLOSE DATABASE LINK statement.

Accessing Data through Database Links

DB2/400 tables, views, and synonyms available to the user ID specified in the CONNECT TO clause can be accessed with the following syntax:

SELECT * FROM table@gateway

or

SELECT * FROM user.table@gateway

The CONNECT TO user ID provides implicit qualification for unqualified tables.  For example:

SELECT * FROM EMP@gateway

resolves to SCOTT.EMP on DB2/400 if the CONNECT TO user is SCOTT.  If no CONNECT TO statement is defined with the database link, then the Oracle user ID using the database link is used as the implicit qualifier.

Oracle Corporation strongly recommends that your table or view specifications (or both) be always qualified with the table or view owner, as in the following example:

SELECT * from user.table@gateway.

Dropping Database Links

You can drop a database link with the DROP DATABASE LINK statement.  For example, to drop the public database link named dblink, enter the following statement:

DROP PUBLIC DATABASE LINK dblink;

Do not drop a database link if it might be required to resolve an in-doubt distributed transaction.  Refer to the Oracle9i Administrator's Reference for additional information about dropping database links.

Examining Available Database Links

The data dictionary of each database stores the definitions of all the database links in that database.  The USER_DB_LINKS data dictionary view shows the database links defined for a specific Oracle user.  The ALL_DB_LINKS data dictionary views show all defined database links both public and private.  The user has access to all these views.  The DBA_DB_LINKS dictionary view, accessible only to users with DBA authorization, shows all database links defined in the Oracle database instance.

Limiting the Number of Active Database Links

You can limit the number of connections from a user process to remote databases with the INIT.ORA parameter OPEN_LINKS.  This parameter controls the number of remote connections any single user process can use concurrently with a single SQL statement.  Refer to the Oracle9i Administrator's Reference for additional information about limiting the number of active database links.

Using DB2/400 Cursors

The maximum number of DB2/400 cursors the gateway can open per Oracle session is 200.  Although the gateway can open 200 cursors, other Oracle or DB2/400 limits might affect how many cursors can actually be opened for a specific application.

Use the CHGORATUN command to change the maximum number of cursors.

Using the Synonym Feature

You can provide complete data, location, and network transparency by using the synonym feature of the Oracle9i database server.  When a synonym is defined, you do not need to know the underlying table or network protocol being used.  A synonym can be public, which means all users can make reference to the synonym.  A synonym can also be defined as private, which means every user must have a synonym defined to access a DB2/400 table.  Refer to the Oracle9i database server documentation for details on the synonym feature.

The following statement creates a system-wide synonym named EMPDB2 in the Oracle database for the SCOTT.EMP file in the DB2/400 server:

CREATE PUBLIC SYNONYM EMPDB2 FOR SCOTT.EMP@gateway

Only those with database administrator authority can create public synonyms.  You can use a similar statement to create a private synonym if you do not have database administrator authority:

CREATE SYNONYM EMPDB2 FOR SCOTT.EMP@gateway

Accessing the Gateway

To access the gateway, complete the following steps on the Oracle9i database server:

  1. Login to the Oracle9i database server.

  2. Create a database link to the AS/400 database with the following syntax:

    CREATE DATABASE LINK AS400
       CONNECT TO userid IDENTIFIED BY password
       USING 'as400'
    
    
  3. Retrieve data from the AS/400 database using one of these methods:

    • if the CONNECT TO clause of the database link specified ORACLE as the user ID, then this query retrieves data from the EMP file in the ORACLE library, using the name ORACLE as the AS/400 user profile:

      SELECT * FROM EMP@AS400
      
      
    • this query retrieves the EMP file in the library or collection CORPDATA, using the name ORACLE as the AS/400 user profile.  The ORACLE AS/400 user profile must have the appropriate AS/400 privileges to access the CORPDATA.EMP file:

      SELECT * FROM CORPDATA.EMP@AS400
      
      

      These messages are displayed if insufficient privileges were granted to Oracle:

      ERROR at line 1:
      ORA-28500:  Connection from Oracle to non-Oracle system 
      returned this message 
      SQL0551:    Not authorized to object EMP in CORPDATA type 
      *FILE 
      ORA-02063:  preceding 2 lines from AS400
      
      
  4. Update data on the AS/400.

  5. Before attempting to update a file on the AS/400, ensure the file is currently being journalled.  If the file is not journalled, these messages are displayed:

    ORA-28500:  Connection from Oracle to non-Oracle system returned 
    this message 
    SQL7008:    EMP in CORPDATA not valid for operation.
    ORA-02063:  preceding 2 lines from AS400
    

Viewing Jobs

Use the WRKACTJOB command to view all jobs.  If the gateway job is active, then you will see the "Listener" job in SELW (select wait) status on the WRKACTJOB panel under the gateway subsystem.  When the listener attempts to "hand-off" a new connection to a pre-started job, its status is CPCW (wait for completion of CPI communications call).

From the WRKACTJOB panel, press PF14 to see all the RUNORAGT* jobs.  Two RUNORAGTTP jobs are usually present, both in PSRW (pre-start, or ready to run) status.

If active gateway connection exists, then the RUNORAGT* jobs can be in either RUN or TIMW (time wait) status.

Accessing AS/400 File Members

An AS/400 physical file can have multiple members.  However, the AS/400 does not allow you to directly access a physical file member through SQL.  For example, if you have an AS/400 file named EMP in the library CORPDATA with members MBR1, MBR2, and MBR3, issuing the SQL statement:

SELECT * FROM CORPDATA.EMP.MBR1

results in an error.

If your AS/400 file has multiple members, you can use AS/400 logical files to point to some or all of the members.  To access a file member, create a logical file over the physical file members.  In the physical file data members parameter (DTAMBRS) of the CRTLF command, specify which members you want to access.  Using the previous example, if you wanted to access members MBR1 and MBR2 of EMP, you would enter the following command:

CRTLF FILE(CORPDATA/EMPLF) DTAMBRS((CORPDATA/EMP (MBR1 MBR2)))

This creates a logical file, EMPLF, that points to MBR1 and MBR2 of the EMP file.  The SQL statement:

SELECT * FROM CORPDATA.EMPLF

returns all the rows that are contained in MBR1 and MBR2.

You can also specify DTAMBRS(*ALL) to include all members of a physical file in a logical file.


Note:

To insert into a specific member, you must create a logical file for that member.


Accessing Flat Files

Flat files are AS/400 files that are created by a utility other than DB2/400.  All flat files have an externally described attribute.  The externally described attribute refers to whether or not a file is defined by an external template.  All DB2/400 files are externally described.  You can access externally described flat files through the gateway in the same way that you access DB2/400 file members.  You cannot access flat files that are not externally described.  Files in the Integrated File System are not externally defined and are therefore not accessible to DB2/400.

The syntax for accessing flat files is:

library.file

where:

library

is the library name.

file

is the file member (flat file) name.

If flat files have multiple members, you need to create a view for each member or combination of members.

Performing Distributed Queries

The gateway technology can execute distributed queries that join the Oracle9i database server and the AS/400, and any other data store for which Oracle Corporation provides a gateway.  These complex operations can be completely invisible to the users requesting the data.

Example of a Distributed Query

SQL command 1

This example joins data between the Oracle database server and the AS/400:

SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P.HOURS) 
FROM ORDERS@DB2 O, EMP@ORACLESRVR E, PROJECTS@AS400 P 
WHERE O.PROJNO = P.PROJNO 
AND P.EMPNO = E.EMPNO 
GROUP BY O.CUSTNAME, P.PROJNO, E.ENAME

Through a combination of views and synonyms, the process of distributed queries may become invisible to the user.  For example:

CREATE SYNONYM ORDERS FOR ORDERS@DB2
CREATE SYNONYM PROJECTS FOR PROJECTS@AS400
CREATE VIEW DETAILS (CUSTNAME,PROJNO,ENAME,SPEND)
AS 
SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P.HOURS)
FROM ORDERS O, EMP@ORACLESRVR E, PROJECTS P
WHERE O.PROJNO = P.PROJNO
AND P.EMPNO = E.EMPNO
GROUP BY O.CUSTNAME, P.PROJNO, E.ENAME
SQL command 2

Using the following SQL statement, a user can retrieve information from these three data stores in a single simple command:

SELECT * FROM DETAILS;

The results from SQL command 2 are the same as those from SQL command 1, and might look like the following:

CUSTNAME PROJNO ENAME SPEND

ABC Co.

1

Jones

400

ABC Co.

1

Smith

180

XYZ Inc.

2

Jones

400

XYZ Inc.

2

Smith

180

Two-Phase Commit Processing

For a database to fully participate in a two-phase commit transaction, it must support both a prepare phase and a commit phase for committing transactions.  The prepare phase ensures all participating nodes referenced in a distributed transaction are prepared to commit or abort the transaction, regardless of intervening failures.

The Oracle9i database server supports two-phase commit transactions.  Therefore, any number of Oracle database servers can participate in a distributed two-phase commit transaction.  The prepare phase is performed when a COMMIT is issued at the end of a distributed transaction.


Note:

The prepare phase occurs automatically when an application COMMIT is executed.  No other action is necessary.


DB2/400 does not support a prepare phase for committing a transaction.  Consequently, it does not support two-phase commit transactions.  Therefore, the two-phase commit protocol is limited when the gateway participates in a distributed transaction.  In this case, the gateway becomes the commit point site of the distributed transaction.  Because the gateway is configured as commit and confirm, it is always the commit point site, regardless of the COMMIT_POINT_STRENGTH setting of any of the participating Oracle databases.  The gateway commits the local AS/400 unit of work after verifying all Oracle databases in the transaction have successfully committed their work.

Because the gateway must drive the distributed transaction, only one gateway can participate in an Oracle two-phase commit transaction.

Two-phase commit transactions are recorded in the ORACLE2PC physical file.  You must journal this file before issuing a distributed transaction to the AS/400.  Refer to "Step 3: Journal the ORACLE2PC File" for more information.

For additional information about the two-phase commit process, refer to Oracle9i Database Administrator's Guide.

Recovering Failed Transactions

If a two-phase commit transaction fails because the database connection is lost, the pending transaction is stored on the Oracle9i database server.  Every time a user logs in to the gateway, the system checks to see if the tables on the Oracle9i database server that point to the DB2/400 link contain pending transactions.  If so, the gateway suspends the login request until the recovery process is complete.

During the recovery process, the server uses the recovery user ID and password to login to the system and resolve pending transactions.  After recovery is complete, the gateway executes the user's login request.  This recovery process is transparent to the user.

The recovery user ID and password are initially set in the "Install Oracle Transparent Gateway Panel, With Name Choices", Figure 4-2.  If you need to change these values, then you must run the CHGRECOPRF command.  Refer to "Step 3: Set Required Parameters" for more information.

Replicating in a Heterogeneous Environment

Oracle Transparent Gateway for DB2/400 provides a number of options for replicating Oracle and non-Oracle data throughout the enterprise.

Oracle9i database server Triggers

When updates are made to the Oracle9i database server, synchronous copies of Oracle and non-Oracle data can be maintained automatically by using Oracle9i database server triggers. 

Oracle Snapshots

Oracle Transparent Gateway for DB2/400 can use the Oracle snapshot feature to automatically replicate non-Oracle data into the Oracle9i database server.  This complete refresh capability of Oracle snapshots can be used to propagate a complete copy or a subset of the non-Oracle data into the Oracle database server at user-defined intervals.

Copying Data from the Oracle9i database server to the DB2/400 Server

Data can be copied from the Oracle database server to the DB2/400 server by two methods:

Triggers

When updates are made to the Oracle9i database server, synchronous copies of Oracle and non-Oracle data can be maintained automatically by using Oracle database server triggers.

For example, suppose you have a Oracle table ORA_EMP that contains fields ENAME and EMPNO.  You also have a table called DB2_EMP, which is a copy of ORA_EMP and which resides on DB2/400.  You want all changes made to the Oracle ENAME field to be reflected immediately in your DB2_EMP table on DB2/400.  In the following scenario, an Oracle database server trigger can be developed to run every time an update is made to the ENAME field in your Oracle ORA_EMP table:

CREATE OR REPLACE trigger EMP_TRIGGER
 after update of ename on SCOTT.ORA_EMP
 for each row
BEGIN
     UPDATE SCOTT.DB2_EMP@tg4db2
     SET ENAME = :NEW.ENAME
     WHERE EMPNO = :NEW.EMPNO;
END;

where tg4db2 is the name of the database link that is used to access the gateway.

SQL*Plus COPY Command

The SQL*Plus COPY command copies data from the Oracle9i database server to the DB2/400 server.  The SQL command INSERT is not supported as a way to copy tables from the Oracle server to the gateway.  The following command:

INSERT INTO gateway_table@gateway SELECT * FROM oracle_table;

displays the following message:

ORA-2025: All tables in the SQL statement must be at the remote 
database.

Use the following SQL*Plus syntax to copy data from your local Oracle database server to the DB2/400 server:

COPY FROM username/password@ORACLESRVR - 
INSERT destination_table@gateway - 
USING query;

The next example selects all rows from the local Oracle EMP table and inserts them into the EMP table on the DB2/400 server:

COPY FROM SCOTT/TIGER@ORACLESRVR -
INSERT SCOTT.EMP@gateway -
USING SELECT * FROM EMP;


Note:

Even though the SQL*Plus COPY command supports APPEND, CREATE, INSERT, and REPLACE options,  INSERT is the only option supported when copying to the DB2/400 server.  For more information about the COPY command, refer to the SQL*Plus User's Guide and Reference.


Copying Data to the Oracle9i database server from the DB2/400 Server

Use one of the following options to copy data from the DB2/400 server to the Oracle9i database server:


Go to previous page Go to next page
Oracle
Copyright © 2002 Oracle Corporation.

All Rights Reserved.
Go To Table Of Contents
Contents
Go To Index
Index