8 Using the Gateway

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:

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

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

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.