Skip Headers
Oracle® Transparent Gateway for DB2 Installation and User's Guide
10g Release 2 (10.2) for IBM z/OS (OS/390)

Part Number B16220-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

8 Using the Gateway

Using the gateway involves connecting to the gateway system and the remote DB2 database associated with it.

This chapter includes the following sections:

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

Connections to the DB2 database might be limited by factors that include memory, gateway parameters, or DB2 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 discussed here.

8.1.1 Creating Database Links

To create a database link 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.

8.1.2 Creating Database Links Using Oracle Net

The following syntax creates a database link to access information in the DB2 database using Oracle Net:

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

where:

Parameter Description
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 on the DB2 server referenced in the SQL commands. The user ID cannot be longer than eight characters.
password is the password used to establish a session in the remote database.

This must be a valid DB2 server password.

The password cannot be longer than eight characters.

tns_name_entry specifies the Oracle Net TNS connect descriptor used to identify the gateway instance.  For an example of a tnsnames entry, refer to Chapter 6.

8.1.3 Guidelines for 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.

8.1.4 Accessing Data through Database Links

DB2 tables, views, synonyms, and aliases available to the userid specified in the CONNECT TO clause can be accessed with the following syntax:

SELECT * FROM SCOTT.EMP@gateway

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

SELECT * FROM EMP@gateway

resolves to SCOTT.EMP on DB2 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. 

8.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 DATABASE LINK dblink;

Do not drop a database link if it is required to resolve an in-doubt distributed transaction.  Refer to the Oracle Database Administrator's Guide for additional information about dropping database links. 

8.1.6 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 gateway instance.

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

8.2 Managing Threads

Whenever a client connects to the Oracle database server to access data from a DB2 server, the Oracle database server creates and manages database access threads between the client and the Oracle database server.  DB2 creates and manages allied threads between the gateway and DB2.  The Oracle system manages the threads between the client and the Oracle database server.  The DB2 system manages the threads between the gateway and DB2. 

When using the gateway to access DB2 data in a client/server configuration, you can encounter the following scenarios:

  1. A user turns off a workstation abnormally and the thread connection remains active.

  2. A user leaves a workstation in an idle state for an extended period of time and the thread connection remains active. 

  3. A user or application uses a workstation to enter a long-running query that maintains a lock in DB2 and, thus, an active thread.

When connecting to DB2 through the gateway, it is important to remember that, if the client or server is abnormally terminated, then a connection can be left open indefinitely, unless specifically identified and closed by the system.

8.2.1 KEEPALIVE

The KEEPALIVE functionality can be used to resolve the first of the three scenarios mentioned previously.  For Oracle systems using KEEPALIVE, the identification of an inactive client/server connection is handled differently in the UNIX environment from the way it is handled in the z/OS environment.

With UNIX, an optional parameter SQLNET.EXPIRE_TIME in the SQLNET.ORA file determines how often Oracle Net sends a probe to verify whether a client/server connection is still active.  If the connection is inactive, then the Oracle database server cleans up the connections between the client, the Oracle database server, the gateway, and DB2. 

With z/OS, KEEPALIVE sends a probe to verify whether a client/server connection is still active.  The KEEPALIVE functionality is implicitly leveraged by the individual protocol vendors.  For example, if you are using the TCP/IP protocol and KEEPALIVE is enabled, then the KEEPALIVE functionality is used automatically by Oracle Net for z/OS. 

8.2.2 Canceling DB2 Threads

The DB2 command CANCEL THREAD can potentially be used to alleviate problems in scenario three by scheduling threads to be terminated.  The user or application must still attempt to access DB2 again before the thread can be terminated.

For further information about this feature, refer to the IBM documents for your platform and operating system.

8.3 Gateway CPU Time

Oracle Transparent Gateway for DB2 uses the RRSAF to connect to the target DB2 system.  When the gateway is used to access data in DB2, much of the DB2 SQL processing takes place in cross-memory mode, running under a TCB in the gateway address space with the RRSAF.  In turn, the CPU time charged to the gateway address space includes both CPU utilization of the gateway and the CPU time required for DB2 SQL processing.  Thus, the SMFXMCPU field, which records CPU utilization, is a combination of the gateway and the DB2 CPU time.  Therefore, this field does not represent pure gateway CPU time and is not a good predictor for judging gateway efficiency.

8.4 Using DB2 Cursors

The maximum number of DB2 cursors the gateway can open per Oracle session is 5000.  Although the gateway can open 5000 cursors, other Oracle database server or DB2 limits might affect how many cursors can actually be opened for a specific application.  The default is 50.

Ensure that the HS_OPEN_CURSORS parameter in member G4DB2ENV of the PARMLIB library is set to the maximum you require.

8.5 Using the Synonym Feature

You can provide complete data, location, and network transparency by using the synonym feature of the Oracle 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 table.  Refer to the Oracle Database product documentation for details about the synonym feature.

The following statement creates a system wide synonym EMPDB2 for the EMP file in the DB2 server Oracle library:

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

8.6 Read-Only Gateway

The read-only option can provide improved performance and security based on your configuration and parameter selections.  An environment parameter, DB2READONLY, is used to control whether the gateway is enabled in this mode.

If you enable the read-only feature, then only queries (SELECT statements) are allowed by DB2.  The capabilities that control whether updates are allowed through the gateway are disabled.  These capabilities include INSERT, UPDATE, DELETE, and stored-procedure support (pass-through SQL and DB2 stored procedures).  Statements attempting to modify records in DB2 are rejected. 

Oracle Corporation recommends that you do not routinely switch between settings of the DB2READONLY parameter.  If you need both update and DB2READONLY functionality, then you should install two separate instances of the gateway with different read-only settings.

If your system can tolerate an occasional dirty read, then you can bind the gateway plan using the isolation level (uncommitted read).  This eliminates DB2 locking problems and improves overall performance.

8.7 Performing Distributed Queries

The gateway technology enables the completion of distributed queries joining data from the Oracle database server and the DB2 server, and any other data store for which Oracle provides a gateway.  These complex operations are transparent to the users requesting such data retrieval. 

8.7.1 Example of a Distributed Query

The following example joins data between the Oracle database server and multiple DB2 servers:

SELECT O.CUSTNAME, P.PROJNO, E.ENAME, SUM(E.RATE*P.HOURS)
FROM ORDERS@GATEWAY_1 O, EMP@ORACLE9 E, PROJECTS@GATEWAY_2 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, using the following SQL statements, the process of distributed queries is made transparent to the user:

CREATE SYNONYM ORDERS FOR ORDERS@GATEWAY_1;
CREATE SYNONYM PROJECTS FOR PROJECTS@GTEWAY_2;
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;

With the views and synonyms in place, the user retrieves information from these three data stores by using one command:

SELECT * FROM DETAILS;

which produces 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

8.7.2 Two-Phase Commit Processing

The gateway must coordinate the distributed transaction and only one gateway can participate in an Oracle two-phase commit transaction.

Two-phase commit transactions are recorded in the DB2 table ORACLE2PC, which is created during gateway installation.

On all systems, the ORACLE2PC table must be available at all times.  For security reasons, users must not have direct access to this table.  The table is accessed and updated by the gateway internally.

8.7.3 Distributed DB2 Transactions

Because the ORACLE2PC table is used to record the status of a gateway transaction, the table must reside in the database where the DB2 update takes place.  Updates to the ORACLE2PC table cannot be part of an IBM distributed transaction.

8.8 Replicating in a Heterogeneous Environment

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

8.8.1 Oracle Database Server Triggers

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

8.8.2 Oracle Materialized View

Oracle Transparent Gateway for DB2 can use the Oracle materialized view 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.

8.9 Copying Data from the Oracle Database Server to the DB2 Server

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

8.9.1 Triggers

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

For example, you have an Oracle ORA_EMP table that contains ENAME and EMPNO.  You also have a table called DB2_EMP, which is a copy of ORA_EMP and which resides on DB2.  You want all changes made to the Oracle ENAME to be reflected immediately in the DB2_EMP table on DB2.  In this scenario, an Oracle database server trigger can be developed to run every time an update is made to ENAME 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 used to access the gateway.

8.9.2 SQL*Plus COPY Command

The SQL*Plus COPY command copies data from the Oracle database server to the DB2 server.  The SQL command INSERT is not supported.  The command:

INSERT INTO gateway_table 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 server:

COPY FROM username/password@ORACLE9
INSERT destination_table
USING query;

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

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

Note:

The SQL*Plus COPY command supports APPEND, CREATE, INSERT, and REPLACE options.

However, INSERT is the only option supported when copying to the DB2 server.

For more information about the COPY command, refer to the SQL*Plus User's Guide and Reference.

8.9.3 STREAMS Replication

TG4DB2 and Heterogeneous Services now support replication to DB2 using Oracle streams.  Oracle Streams is a rule-based process which allows changes to an Oracle table to be captured and applied to an equivalent DB2 table based on user-written rules.  Replication from DB2 to Oracle is not supported at this time.

Before setting up your Streams Replication environment ensure that archivelog is enabled, otherwise nothing else will work.

An example of a simple table replication follows:

First you should grant the necessary authorizations to your Streams admin userid.
   CONNECT SYS/SYS_PASSWORD AS SYSDBA
 
   GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE
     TO strmadmin IDENTIFIED BY strmadminpw;
 
   GRANT EXECUTE ON DBMS_APPLY_ADM TO strmadmin;
   GRANT EXECUTE ON DBMS_AQADM TO strmadmin;
   GRANT EXECUTE ON DBMS_CAPTURE_ADM TO strmadmin;
   GRANT EXECUTE ON DBMS_FLASHBACK TO strmadmin;
   GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO strmadmin;
   GRANT EXECUTE ON DBMS_STREAMS_ADM TO strmadmin;
 
   BEGIN
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
     privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
     grantee => 'strmadmin',
     grant_option => FALSE);
   END;
   /
 
   BEGIN
     DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
     privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
     grantee => 'strmadmin',
     grant_option => FALSE);
   END;
   /
 

Then, set up the Streams queue and the database link that the apply process will use.

CONNECT strmadmin/strmadminpw
 
   EXEC DBMS_STREAMS_ADM.SET_UP_QUEUE();
 
   DROP DATABASE LINK strmdblink.your.domain.com;
 
   CREATE DATABASE LINK strmdblink.your.domain.com
     CONNECT TO userid IDENTIFIED BY password
        USING 'tnsnames_entry';
 

Next, create the capture and apply processes and define the replication rules.

CONNECT SYS/SYS_PASSWORD AS SYSDBA
 
   ALTER SYSTEM ARCHIVE LOG CURRENT;
 
   CONNECT strmadmin/strmadminpw
 
   ---  ---------------------------------------------------------------
   ---  Stop the capture process if it's already active.
   ---  ---------------------------------------------------------------
   BEGIN
     DBMS_CAPTURE_ADM.STOP_CAPTURE(
       capture_name => 'db2_capture');
   END;
   /
 
   ---  ---------------------------------------------------------------
   ---  Stop the apply process if it's already active.
   ---  ---------------------------------------------------------------
   BEGIN
     DBMS_APPLY_ADM.STOP_APPLY(
       apply_name => 'apply_2_db2');
   END;
   /
   ---  ---------------------------------------------------------------
   ---  Define the capture rule, this one captures changes to scott.emp
   ---  ---------------------------------------------------------------
   BEGIN
     DBMS_STREAMS_ADM.ADD_SCHEMA_RULES(
       schema_name  => 'scott',
       streams_type => 'capture',
       streams_name => 'db2_capture',
       queue_name   => 'strmadmin.streams_queue',
       include_dml  => true,
       include_ddl  => true);
   END;
   /
 
   ---  ---------------------------------------------------------------
   ---  Set the capture instantiation level
   ---  ---------------------------------------------------------------
   DECLARE
     iscn NUMBER; -- Variable to hold instantiation SCN value
   BEGIN
     iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
 
     DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN(
       source_object_name => 'scott.emp',
       source_database_name => 'ORAv92',
       instantiation_scn => iscn,
       apply_database_link => 'strmdblink.your.domain.com');
   END;
   /
 
   ---  ---------------------------------------------------------------
   ---  Drop the apply process if it already exists.
   ---  ---------------------------------------------------------------
   BEGIN
     DBMS_APPLY_ADM.DROP_APPLY(
       apply_name => 'apply_2_db2');
   END;
   /
 
   ---  ---------------------------------------------------------------
   ---  Create the apply process
   ---  ---------------------------------------------------------------
   BEGIN
     DBMS_APPLY_ADM.CREATE_APPLY(
       queue_name => 'strmadmin.streams_queue',
       apply_name => 'apply_2_db2',
       apply_database_link => 'strmdblink.your.domain.com',
       apply_captured => true);
   END;
   /
 
   ---  ---------------------------------------------------------------
   ---  Create the apply rule
   ---  ---------------------------------------------------------------
   BEGIN
     DBMS_STREAMS_ADM.ADD_TABLE_RULES(
       table_name => 'scott.emp',
       streams_type => 'apply',
       streams_name => 'apply_2_db2',
       queue_name => 'strmadmin.streams_queue',
       include_dml => true,
       include_ddl => false,
       source_database => 'ORAv92');
   END;
   /
 
   ---  ---------------------------------------------------------------
   ---  Turn on tracing for the apply process (be careful, this
   ---  generates alot of output).
   ---  ---------------------------------------------------------------
   BEGIN
     DBMS_APPLY_ADM.SET_PARAMETER(
       apply_name => 'apply_2_db2',
       parameter => 'trace_level',
       value => 127 );
   END;
   /
   ---  ---------------------------------------------------------------
   ---  Turn off disable_on_error for the apply process
   ---  ---------------------------------------------------------------
   BEGIN
     DBMS_APPLY_ADM.SET_PARAMETER(
       apply_name => 'apply_2_db2',
       parameter => 'disable_on_error',
       value => 'n');
   END;
   /
 
   ---  ---------------------------------------------------------------
   ---  Start the apply process.
   ---  ---------------------------------------------------------------
   BEGIN
     DBMS_APPLY_ADM.START_APPLY(
       apply_name => 'apply_2_db2');
   END;
   /
 
   ---  ---------------------------------------------------------------
   ---  Start the capture process.
   ---  ---------------------------------------------------------------
   BEGIN
     DBMS_CAPTURE_ADM.START_CAPTURE(
       capture_name => 'db2_capture');
   END;
   /
 

For more detailed information about Oracle streams replication, refer to Oracle Streams Concepts and Administration.

8.10 Copying Data to the Oracle Database Server from the DB2 Server

Use one of the following options to copy data from the DB2 server to the Oracle database server: