7 Using the Gateway

7.1.1 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.

7.1.2 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".

7.1.3 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.

7.1.4 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

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 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.

7.1.5 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.

7.1.6 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.

7.1.7 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.

7.4.1 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.

7.5.1 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.

7.6.1 Example of a Distributed Query

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

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

SELECT * FROM DETAILS;

7.6.2 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 server 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.

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.

7.7.1 Oracle Database Server Triggers

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

7.7.2 Oracle Materialized View

Oracle Transparent Gateway for DB2/400 can use the Oracle Materialized View (snapshot) feature to automatically replicate non-Oracle data into the Oracle Database server. 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 server at user-defined intervals.

7.8.1 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.

7.8.2 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;