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 Oracle database and the AS/400.

This chapter contains the following sections:

Database Link Behavior
Using DB2/400 Cursors
Using the Synonym Feature
Accessing the Gateway
Accessing OS/400 File Members
Performing Distributed Queries
Replicating in a Heterogeneous Environment
Copying Data from Oracle Server to DB2/400 Server
Copying Data to Oracle Server from DB2/400 Server

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 Oracle database and the gateway. The connection remains established until the session ends. Another session or user can access the same database link but will get a different 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 Oracle Database Administrator's Guide. Information for using database links with the gateway is given here in this manual.

Creating Database Links

To create a database link in your Oracle Database 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 that is used to establish a session in the remote AS/400 system. It must be authorized to any table or file on the DB2/400 server that is referenced in the SQL commands . The user ID cannot be longer than ten characters.

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

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

Closing Database Links

After being 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 dblink statement.

Accessing Data through Database Links

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

SELECT * FROM table@gateway

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 that is using the database link is used as the implicit qualifier.

Oracle strongly recommends that your table or view specifications (or both) always be 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 Oracle Database Administrator's Guide for additional information about dropping database links.

Examining Available Database Links

The data dictionary of each Oracle Database stores the definitions of all the database links in that database. The USER_DB_LINKS data dictionary view shows the database links that are defined for a specific Oracle Database 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, which is accessible only to users with DBA authorization, shows all database links that are 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 Oracle Database Administrator's Guide for additional information about limiting the number of active database links.

Using DB2/400 Cursors

The maximum number of DB2/400 cursors that the gateway can open per Oracle Database session is 200. Although the gateway can open 200 cursors, other Oracle Database 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 Oracle Database 10g server. When a synonym is defined, you do not need to know the underlying table or network protocol that is being used. A synonym can be public, which means that all users can make reference to the synonym. A synonym can also be defined as private, which means that every user must have a synonym defined to access the underlying DB2/400 table. Refer to the Oracle Database 10g server documentation for details about 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 users 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 Oracle Database 10g server:

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'

Retrieve data from the AS/400 database using one of the following methods:
- If the CONNECT TO clause of the database link has 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 OS/400 user profile must have the appropriate AS/400 privileges to access the CORPDATA.EMP file:
```
SELECT * FROM CORPDATA.EMP@AS400
```
  The following messages are displayed if insufficient privileges were granted to Oracle user:
```
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
```
Update data on the AS/400.

Before attempting to update a file on the AS/400, ensure that the file is currently being journaled. If the file is not journaled, then the following 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. Use the WRKACTJOB SBS(myinst) command to see only the jobs supporting your gateway instance; substitute your gateway instance name for myinst. If the gateway subsystem 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 of the RUNORAGT* jobs. Two RUNORAGTTP jobs are usually present, both in PSRW (pre-start, or ready to run) status.

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

Accessing OS/400 File Members

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

SELECT * FROM CORPDATA.EMP.MBR1

If your OS/400 file has multiple members, then you can use OS/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, then you would enter the following command:

CRTLF FILE((CORPDATA/EMPLF) DTAMBRS((CORPDATA/EMP (MBR1 MBR2))) + SRCFILE(myinst/QDDSSRC) SRCMBR(EMPLF)

where member EMPLF in file myinst/QDDSSRC is as follows

A*  ACCESS TWO DIFFERENT MBRS IN AN EMP *FILE USING A LOGICAL
          A*  FILE (THE TWO MEMBERS ARE NAMED EXTERNALLY ON CRTLF COMMAND)
          A          R EMPTABLE                  PFILE(CORPDATA/EMP)
          A          K EMPNO

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

SELECT * FROM CORPDATA.EMPLF

will return all of 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 OS/400 files that are created by a facility other than DB2/400, for example the CRTPF and CRTSRCPF commands can create such files. 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 tables are externally described. You can access externally described flat files through the gateway in the same way that you access DB2/400 tables. Files in OS/400 that are created with the CRTPF command (which specifies DDS source) are externally described. Files in OS/400 that are created with the CRTPF command and without specifying DDS source are not externally described. The latter type of file can be accessed by the gateway. However, only one column will be available, the column name will be the same as the member name, and the data will be described as RAW(n), where n is the record length that was used in the CRTPF command. Files in the integrated file system (IFS) are not accessible to the gateway because they are 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, then 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 with data from the Oracle Database 10g server and DB2/400, and any other data store for which Oracle provides a gateway. These complex operations can be completely invisible to the users requesting the data.

Example of a Distributed Query

SQL Command, Example 1

Assume that dblink DB2 points to a database gateway for DB2 on z/OS, and assume that dblink ORACLESERVR points to a remote Oracle Database instance, and assume that dblink AS400 points to an instance of the Oracle Database Gateway for DB2/400. Then the following example joins data between those three database servers.

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 SYNONYM EMP FOR EMP@ORACLESRVR
CREATE VIEW DETAILS (CUSTNAME,PROJNO,ENAME,SPEND) AS
  SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P.HOURS)
  FROM ORDERS O, EMP E, PROJECTS P
  WHERE O.PROJNO = P.PROJNO AND P.EMPNO = E.EMPNO
  GROUP BY O.CUSTNAME, P.PROJNO, E.ENAME

SQL Command, Example 2

Using the following SQL statement, a user can retrieve information from the three data stores in Example 1 using a single command, as follows:

SELECT * FROM DETAILS;

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

Table 7-1 Example Results from SQL Distributed Queries

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 that all participating nodes that are referenced in a distributed transaction are prepared to commit or to abort the transaction, regardless of intervening failures.

The Oracle database supports two-phase commit transactions. So, 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 that 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 the Oracle Database Administrator's Guide.

Recovering Failed Transactions

If a two-phase commit transaction fails because the database connection is lost, then the pending transaction is stored on the Oracle Database. Every time a user attempts to login to the gateway via s specific database link, the Oracle Server checks to see if there are any pending (probably failed) transactions that had previously used that same database link. If so, Oracle suspends the current gateway login and instead tells the Gateway to login in using the Recovery User Profile and Password and to perform some recovery operations. After these recovery operations are complete, Oracle will then continue with the normal gateway login (which had been suspended).

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 panel shown in Example 4-2, "Install Oracle Database Gateway Panel, With Name Choices". If you need to change these values, then you must run the CHGRECOPRF command.

Replicating in a Heterogeneous Environment

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

Oracle Database Server Triggers

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

Oracle Materialized View

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

Copying Data from Oracle Server to DB2/400 Server

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

Triggers
SQL*Plus COPY command

Triggers

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

For example, suppose that you have an Oracle table ORA_EMP that contains fields ENAME and EMPNO. Suppose that 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 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 Oracle Database 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 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 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:

Although the SQL*Plus COPY command supports the APPEND, CREATE, INSERT, and REPLACE options , INSERT is the only option that is 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 Oracle Server from DB2/400 Server

Use one of the following options to copy data from the DB2/400 server to the Oracle Database:

Use the CREATE TABLE command to copy data from the DB2/400 server to the Oracle database. To create a table on your local database and to insert rows from a DB2/400 table, use:
```
CREATE TABLE table_name AS query;
```
The next example creates the table EMP in the local Oracle database and inserts the rows from the EMP table on the DB2/400 server:
```
CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP@gateway;
```
Use the INSERT command to copy data from the DB2/400 server to the Oracle Database:
```
INSERT INTO oracle_table SELECT * FROM db2table@gateway;
```
The following example selects all rows from the EMP table on the DB2/400 server and inserts them into the local Oracle EMP table:
```
INSERT INTO EMP SELECT * FROM SCOTT.EMP@gateway;
```
Use the CREATE MATERIALIZED VIEW command to automatically and asynchronously copy DB2/400 server data into the Oracle database . The complete refresh capability can be used to propagate a complete copy or a subset. For more information about creating materialized views, refer to the Oracle Database SQL Reference. To create a copy:
```
CREATE MATERIALIZED VIEW empdb2 
  PCTFREE 5 PCTUSED 60 
  TABLESPACE users 
  STORAGE (INITIAL 50K NEXT 50K) 
  REFRESH COMPLETE NEXT SYSDATE + 1 
  WITH ROWID
  AS 
    SELECT * FROM SCOTT.EMP@gateway;
```
The following example creates a materialized view of data that is refreshed every day after the first refresh. If you require only a subset of the AS/400 data, then a WHERE clause is added, as in the following example:
```
CREATE MATERIALIZED VIEW empdb2 
  PCTFREE 5 PCTUSED 60 
  TABLESPACE users 
  STORAGE (INITIAL 50K NEXT 50K) 
  REFRESH COMPLETE NEXT SYSDATE + 1 
  WITH ROWID
  AS 
    SELECT * FROM SCOTT.EMP@gateway 
  WHERE deptno=20;
```
Use the SQL*Plus COPY command to copy data from the DB2/400 server to the Oracle Database:
```
COPY FROM username/password@tns_alias -
INSERT destination_table -
USING query;
```
where username or password, or both, are valid at the Oracle Database that is identified by tns_alias, and where query refers to a remote table that is accessed through a gateway database link.

The following example selects all rows from the EMP table in DB2/400 and inserts them into the local Oracle EMP table:

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