11 Using the Gateway

Using the gateway involves connecting to the gateway system and the remote DRDA database that is associated with it. Understanding how to process and how to use database links is important. Database links are discussed in detail in the Oracle Database Administrator's Guide. Read the database link information in that guide to understand database link processing. Then, read this chapter to understand how to set up a database link to a remote DRDA database.

This chapter contains the following sections:

Processing a Database Link
Accessing the Gateway
Accessing AS/400 File Members
Using the Synonym Feature
Performing Distributed Queries
ReadEOnly Gateway
Replicating in a Heterogeneous Environment
Copying Data from the Oracle Server to the DRDA Server
Copying Data from the DRDA Server to the Oracle Server
Tracing SQL Statements

Processing a Database Link

The database and application administrators of a distributed database system are responsible for managing the necessary database links that define paths to the DRDA database.

Creating Database Links

To create a database link and define a path to a remote database, 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 remote database. The USING clause points to a tnsnames.ora connect descriptor.

Note:

If you do not specify a user ID and a password in the CONNECT TO clause, then the Oracle server user ID and password are used. For additional information, refer to Chapter 13, "Security Considerations".

The following syntax creates a database link to access information in the DRDA Server database:

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

where:

dblink is the complete database link name.

userid is the user ID that is used to establish a session in the remote database. This user ID must be a valid DRDA Server user ID. It must be authorized to any table or file on the DRDA Server that is referenced in the SQL commands. The user ID cannot be longer than eight characters.

password is the password that is used to establish a session in the remote database. This must be a valid DRDA Server password. The password cannot be longer than eight characters.

tns_name_entry specifies the Oracle Net TNS connect descriptor that is used to identify the gateway.

Guidelines for Database Links

Database links are active for the duration of a gateway session. If you want to close a database link during a session, then use the ALTER session statement.

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

DROP PUBLIC DATABASE LINK dblink;

Attention:

A database link should not be dropped if it is required to resolve an inEdoubt distributed transaction. Refer to the Oracle Database Administrator's Guide for additional information about dropping database links.

Examining Available Database Links

The data dictionary of each database stores the definitions of all the database links in that database. Your USER_DB_LINKS data dictionary view shows your defined database links. The ALL_DB_LINKS data dictionary views show all defined database links.

Limiting the Number of Active Database Links

You can limit the number of connections from a user process to remote databases by using the parameter OPEN_LINKS. This parameter controls the number of remote connections that 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.

Accessing the Gateway

To access the gateway, complete the following steps on the Oracle integrating server.

Step 1: Log in to the Oracle integrating server

This is the first step to accessing the gateway.

Step 2: Create a database link to the DRDA database

For example, use:

CREATE PUBLIC DATABASE LINK DRDA
CONNECT TO user id IDENTIFIED BY password
USING 'tns_name_entry'

Step 3: Retrieve data from the DRDA database

This query fetches the TABLE file in the library SECURE, using the name ORACLE as the DRDA Server user profile. The ORACLE user profile must have the suitable privilege on the DRDA Server to access the SECURE.TABLE files:

SELECT * FROM SECURE.TABLE@DRDA

The following messages are displayed if insufficient privileges were granted to the ORACLE user profile:

ORA-1031:   insufficient privileges
TG4DRDA V10.2.0.1.0 grc=0, drc=-777 (83TC,0000), errp=ARIXO,
sqlcode=-551, sqlstate=42501, errd=FFFFFF9C,0,0,0,0,0
errmc=USER SELECT SECURE.TABLE

Accessing AS/400 File Members

There is nothing specific to DRDA or to the gateway that enables or does not enables access to AS/400 files and file members. However, DB2/400 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/400 can reference the correct file member.

The method involves running the console command Create Logical File (CRTLF). This action creates a logical association between the file name and the file member name.

For additional information, refer to the AS/400 Command documentation or to the DB2/400 SQL reference.

Using the Synonym Feature

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

The following statement creates a systemwide synonym for the EMP file in the DRDA Server with ownership of Oracle:

CREATE PUBLIC SYNONYM EMP FOR ORACLE.EMP@DRDA

Performing Distributed Queries

The Oracle Transparent Gateway technology enables the processing of distributed queries that join Oracle servers and DRDA Servers, and any other data store for which Oracle Corporation provides a gateway. These complex operations can be completely transparent to the users requesting the data.

The distributed query optimizer (DQO) capability can provide better performance of distributed queries. Statistical data regarding tables from DRDA Servers is retrieved and passed to the Oracle integrating server. The DQO capability is turned on and off by the DRDA_OPTIMIZE_QUERY parameter. Refer to "DRDA_OPTIMIZE_QUERY" of Appendix C, "DRDAESpecific Parameters".

Example of a Distributed Query

The following example joins data between an Oracle server, DB2/OS390, 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;

This 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

TwoEPhase Commit Processing

To fully participate in a twoEphase commit transaction, a server must support the PREPARE TRANSACTION statement. The PREPARE TRANSACTION statement ensures that all participating databases are prepared to COMMIT or ROLLBACK a specific unit of work.

The Oracle server supports the PREPARE TRANSACTION statement. Any number of Oracle servers can participate in a distributed twoEphase commit transaction. The PREPARE TRANSACTION statement is performed automatically when a COMMIT transaction is run explicitly by an application or implicitly at the normal end of the application. No other action is needed.

The gateway does not support the PREPARE TRANSACTION statement limiting the twoEphase commit protocol when the gateway participates in a distributed transaction. The gateway becomes the commit focal point site of a distributed transaction. Because the gateway is configured as commit/confirm, it is always the commit point site, regardless of the commit point strength setting. The gateway commits the unit of work after verifying that all Oracle databases in the transaction have successfully committed their work. Because the gateway must coordinate the distributed transaction, only one gateway can participate in an Oracle twoEphase commit transaction.

TwoEphase commit transactions are recorded in the ORADRDA.ORACLE2PC table, which is created during installation. This table is created when the o2pc.sql script is run. The owner of this table also owns the package. Refer to "DRDA Gateway Package Considerations" for more information.

Distributed DRDA Transactions

Because the ORACLE2PC table is used to record the status of a gateway transaction, the table must reside at the database where the DRDA update takes place. Therefore, all updates that take place over the gateway must be local to the IBM database.

Note:

Updates to the ORACLE2PC table cannot be part of an IBM distributed transaction.

For additional information about the twoEphase commit process, refer to the Oracle Database Administrator's Guide.

ReadEOnly Gateway

The readEonly option can provide improved performance and security. This improved performance depends on your configuration and parameter selections. A gateway initialization parameter, DRDA_READ_ONLY, is used to control whether the gateway is enabled in this mode.

If you enable the readEonly feature, then only queries (SELECT statements) are permitted by the gateway. The capabilities that control whether updates are permitted through the gateway are not enabled. These capabilities include INSERT, UPDATE, DELETE, and storedEprocedure support (passEthrough SQL and DB2 stored procedures). Statements attempting to modify records on the DRDA Server are rejected.

Oracle recommends that you do not routinely switch between settings of the DRDA_READ_ONLY parameter. If you need both update and DRDA_READ_ONLY functionality, then you should create two separate instances of the gateway with different readEonly settings.

Replicating in a Heterogeneous Environment

Oracle Transparent Gateway for DRDA provides a number of options for replicating Oracle and nonEOracle data throughout the enterprise.

Oracle Database 10g Server Triggers

When updates are made to the Oracle Database server, synchronous copies of Oracle and nonEOracle data can be maintained automatically by using Oracle Database 10g server triggers.

Oracle Snapshots

Oracle Transparent Gateway for DRDA can use the Oracle snapshot feature to automatically replicate nonEOracle data into the Oracle Database server. The complete refresh capability of Oracle Snapshot can be used to propagate a complete copy or a subset of the nonEOracle data into the Oracle Database server at userEdefined intervals.

Copying Data from the Oracle Server to the DRDA Server

The COPY command enables you to copy data from an Oracle Database server to a DRDA Server database. The Oracle SQL command INSERT 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 local database to the DRDA Server, use:

COPY FROM user id/password@dblink-
INSERT destination_table -
USING query

For example, to select all rows from the local Oracle EMP table, to insert them into the EMP table on the DRDA Server, and to 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 options. However, INSERT is the only option supported when copying to the DRDA Server. For more information about the COPY command, refer to the SQL*Plus User's Guide and Reference.

Copying Data from the DRDA Server to the Oracle Server

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

CREATE TABLE table_name
AS query

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 an Oracle Database server. For more information about the COPY command, refer to the SQL*Plus User's Guide and Reference.

Tracing SQL Statements

SQL statements run 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. The Oracle integrating server and the gateway can change the statements depending upon the situation.

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

SQL tracing reduces gateway performance. Use tracing only while testing and debugging the 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 15, " Error Messages, Diagnosis, and Reporting".