3 Using the Oracle Database Gateway for DRDA

3.1 DRDA Gateway Features

This section describes the following DRDA gateway features:

• CHAR Semantics

• Multi-byte Character Sets Ratio Suppression

• IPv6 Support

• Gateway Session IDLE Timeout

3.2 Processing a Database Link

The database and application administrators of a distributed database system are responsible for managing the database links that define paths to the Oracle Database Gateway for DRDA. The tasks are as follows:

• Creating Database Links

• Dropping Database Links

• Examining Available Database Links

• Limiting the Number of Active Database Links

CREATE PUBLIC DATABASE LINK dblink 
CONNECT TO userid IDENTIFIED BY password  
USING 'tns_name_entry';

DROP PUBLIC DATABASE LINK dblink;

3.3 Accessing the Gateway

To access the gateway, complete the following steps on the Oracle database:

1. Login to the Oracle Database

2. Create a database link to the DRDA Database

3. Retrieve data from the DRDA Database

Login to the Oracle Database

Login to the Oracle database to access the gateway

Create a database link to the DRDA Database

For example, use:

CREATE PUBLIC DATABASE LINK DRDA
CONNECT TO ORADRDA IDENTIFIED BY oracle_pw 
USING 'tns_name_entry' 

Retrieve data from the DRDA Database

This query fetches the TABLE table in the schema SECURE, using the name ORADRDA as the DRDA server user profile. The ORADRDA user profile must have the appropriate privileges on the DRDA server to access the SECURE.TABLE files:

SELECT * FROM SECURE.TABLE@DRDA

The following is an example of the error messages that are displayed if insufficient privileges are displayed:

ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Oracle][ODBC DB2 Wire Protocol driver][UDB DB2 for Windows, UNIX, and
Linux]ORADRDA DOES NOT HAVE PRIVILEGE TO PERFORM OPERATION SELECT ON THIS
OBJECT SECURE.TABLE.
ORA-02063: preceding 2 lines from DRDA

3.4 Accessing i5/OS File Members

Access to i5/OS files and file members is not specifically controlled by DRDA or the gateway. However, DB2 UDB for iSeries uses a naming convention that implies that the file member name is the same as the name of the file being addressed. For example, accessing schema.table implies that table is the file name and also that table is the file member name being accessed.

To access file members with names that differ from the associated file name, you must create a view within the file so that DB2 UDB for iSeries can reference the correct file member.

One method for creating this view involves issuing the i5/OS command Create Logical File (CRTLF). This action creates a logical association between the file name and the file member name.

3.5 Using the Synonym Feature

You can provide complete data, location, and network transparency by using the synonym feature of Oracle database. When a synonym is defined, the user need not know the underlying table or network protocol being used. A synonym can be public, which means it is available to all Oracle users. A synonym can also be defined as private, available only to the user who created it. Refer to Oracle Database Reference for details on the synonym feature.

The following statement creates a system-wide synonym for the EMP file in the DRDA server with ownership of ORACLE:

CREATE PUBLIC SYNONYM EMP FOR ORACLE.EMP@DRDA

3.6 Performing Distributed Queries

The Oracle Database Gateway technology enables the execution of distributed queries that join data in an Oracle database and in DRDA servers and data from any other data store for which Oracle provides a gateway. These complex operations can be completely transparent to the users requesting the data.

The following example joins data between an Oracle database, DB2 UDB for z/OS, and a DRDA server:

SELECT o.custname, p.projno, e.ename, sum(e.rate*p.hours)
FROM orders@DB2 o, EMP@ORACLE7 e, projects@DRDA p
WHERE o.projno = p.projno 
AND p.empno = e.empno
GROUP BY o.custname, p.projno, e.ename

A combination of views and synonyms, using the following SQL statements, keeps the process of distributed queries transparent to the user:

CREATE SYNONYM orders for orders@DB2;
CREATE SYNONYM PROJECTS for PROJECTS@DRDA;
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

The following SQL statement retrieves information from these three data stores in one command:

SELECT * FROM DETAILS;

The results of this command are:

CUSTNAME   PROJNO     ENAME      SPEND
---------  ---------  ---------  ---------
ABC Co.    1         Jones      400
ABC Co.    1         Smith      180
XYZ Inc.   2         Jones      400
XYZ Inc.   2         Smith      180

3.7 Replicating in a Heterogeneous Environment

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

Oracle Database Triggers

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

Oracle Snapshots

Oracle Database Gateway for DRDA can use the Oracle snapshot feature to automatically replicate non-Oracle data into Oracle database. The complete refresh capability of Oracle snapshot can be used to propagate a complete copy or a subset of the non-Oracle data into Oracle database at user-defined intervals.

3.8 Copying Data from Oracle Database to DRDA Server

The COPY command enables you to copy data from Oracle database to a DRDA server. The Oracle SQL INSERT command is not supported. If you use the INSERT command:

INSERT INTO DRDA_table SELECT * FROM local_table

then the following message is displayed:

ORA-2025: All tables in the SQL statement must be at the remote database

To copy data from your Oracle database to the DRDA server, use:

COPY FROM username/password@connect_identifier - 
INSERT destination_table -  
USING query 

For example, to select all rows from the local Oracle EMP table, insert them into the EMP table on the DRDA server, and commit the transaction, use:

COPY FROM scott/tiger@ORACLE -
INSERT scott.EMP@DRDA -
USING SELECT * FROM EMP

The SQL*Plus COPY command supports APPEND, CREATE, INSERT, and REPLACE commands. However, INSERT is the only command supported when copying to the DRDA server. For more information about the COPY command, refer to SQL*Plus User's Guide and Reference.

3.9 Copying Data from DRDA Server to Oracle Database

The CREATE TABLE command enables you to copy data from a DRDA server to Oracle database. To create a table on your Oracle database and to insert rows from a DRDA server table, use:

CREATE TABLE table_name 
AS query 

A SQL INSERT into an Oracle table can be done selecting data from the gateway as follows:

INSERT INTO local_table SELECT * FROM drda_table

The following example creates the table EMP in your local Oracle database and inserts the rows from the EMP table on the DRDA server:

CREATE TABLE EMP
AS SELECT * FROM scott.EMP@DRDA

Alternatively, you can use the SQL*Plus COPY command to copy data from a DRDA server to Oracle database. For more information about the COPY command, refer to SQL*Plus User's Guide and Reference.

3.10 Tracing SQL Statements

SQL statements issued through the gateway can be changed before reaching the DRDA database. These changes are made to make the format acceptable to the gateway or to make Oracle SQL compatible with DRDA server SQL. Oracle database and the gateway can change the statements depending on the situation.

For various reasons, you might need to assess whether the gateway has altered the statement correctly or whether the statement could be rewritten to improve performance. SQL tracing is a feature that allows you to view the changes made to a SQL statement by the Oracle database or the gateway.

SQL tracing reduces gateway performance. Use tracing only when testing and debugging your application. Do not enable SQL tracing when the application is running in a production environment. For more information about enabling SQL tracing, refer to the section on "SQL Tracing and the Gateway" in Chapter 5, "Error Messages, Diagnosis, and Reporting".