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:
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.
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.
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".
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.
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.
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.
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.
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.
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.
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
To access the gateway, complete the following steps on the Oracle Database 10g server:
Log in to 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
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.
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.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.
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.
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
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:
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 applicationCOMMIT
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.
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.
Oracle Database Gateway for DB2/400 provides a number of options for replicating Oracle data and non-Oracle data throughout the enterprise.
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 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.
Data can be copied from the Oracle Database 10g server to the DB2/400 server by two methods:
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.
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*PlusCOPY
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.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;