Using the gateway involves connecting to the gateway system and the remote DB2 database associated with it.
This chapter includes the following sections:
Copying Data from the Oracle Database Server to the DB2 Server
Copying Data to the Oracle Database Server from the DB2 Server
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.
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.
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:
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.
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.
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.
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.
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.
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:
A user turns off a workstation abnormally and the thread connection remains active.
A user leaves a workstation in an idle state for an extended period of time and the thread connection remains active.
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.
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.
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.
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.
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.
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
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.
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.
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 |
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.
Oracle Transparent Gateway for DB2 provides a number of options for replicating Oracle and non-Oracle data throughout the enterprise.
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.
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.
Data can be copied from the Oracle database server to the DB2 server by two methods:
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.
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*PlusCOPY
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.
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.
Use one of the following options to copy data from the DB2 server to the Oracle database server:
Use the CREATE TABLE
command to copy data from the DB2 server to the Oracle database server. To create a table on your local database and insert rows from a DB2 table, use:
CREATE TABLE table_name AS query;
The next example creates the table EMP
in the local Oracle database server and inserts the rows from the EMP
table on the DB2 server:
CREATE TABLE EMP AS SELECT * FROM SCOTT.EMP@gateway;
Use the INSERT
command to copy data from the DB2 server to the Oracle database server:
INSERT INTO oracle_table SELECT * FROM db2table@gateway;
The following example selects all rows from the EMP
table on the DB2 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 server data into the Oracle database server. The complete refresh capability can be used to propagate a complete copy or a subset. For more information about creating MATERIALIZED VIEW
s, 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 DB2 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 server to the Oracle database server:
COPY FROM username/password@gateway INSERT destination_table USING query;
The following example selects all rows from the EMP
table in DB2 and inserts them into the local Oracle EMP
table:
COPY FROM SCOTT/TIGER@gateway INSERT EMP USING SELECT * FROM SCOTT.EMP@gateway;