Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Related Publications

Documentation Accessibility

Accessibility of Code Examples in Documentation

Conventions

1 Introduction

Introduction to the Oracle Transparent Gateway

Protection of Current Investment

Release 9i Gateways

Advantages of the Gateway

Gateway Capabilities

Transparency at All Levels

Extended Database Services

Extended Advanced Networking, Internet and Intranet Support

Dynamic Dictionary Mapping

SQL

Data Definition Language

Data Control Language

Passthrough and Native DB2 SQL

Stored Procedures

Oracle Stored Procedures

Native DB2 Stored Procedures

Languages

Oracle Server Technology and Tools

SQL*Plus®

Two-Phase Commit and Multisite Transactions

Site Autonomy

Migration and Coexistence

Security

Terms

Architecture

Implementation

How the Gateway Works

SQL Differences

Oracle Tools and the Gateway

SQL*Plus

Features

Heterogeneous Services Architecture

Performance Enhancements

Fetch Reblocking

Oracle 9i Passthrough Supported

Retrieving Result Sets Through Passthrough

Support for TCP/IP

Native Semantics

Columns Supported in a Result Set

EXPLAIN_PLAN Improvement

Heterogeneous Database Integration

Minimum Impact on Existing Systems

Large Base of Data Access

Application Portability

Remote Data Access

Support for Distributed Applications

Application Development and End User Tools

2 Release Information

Product Set

Changes and Enhancements, Release 9.0.1.0.1

Enhancements in this Release

Support for DB2/MVS V6 Stored Procedures

Codepage Map Facility

Improved Oracle Data Dictionaries

Changes in this Release

Oracle Server Dependencies

Gateway Parameters

TNSNAMES.ORA

Sun Solaris Support

IBM DB2 Universal Database Support

IBM DB2 Version 5.1 ASCII Tables

Read-Only Support

Known Problems

Known Restrictions

For Owners of DB2 Components

SQL Limitations

3 System Requirements

Hardware Requirements

Processor

Memory

Network Attachment

CD-ROM Drive

Disk Space

Software Requirements

Operating System

DRDA Databases

Communications

SunLink Version 9.0 Required Patches

Oracle Server

Oracle Networking Products

Documentation Requirements

4 Installing the Gateway

Introduction

Before You Begin

Checklist for Gateway Installation

Installation Overview

Verify SNA Library Install Paths

Installing the Gateway from CD-ROM

Step 1: Login as the Oracle database administrator (DBA) user

Step 2: Create the product installation directory

Step 3: Set the ORACLE_HOME environment variable

Step 4: Mount the CD-ROM

Step 5: Start the Oracle Universal Installer

Step 6: Step through the Oracle Universal Installer

Step 7: Verify installation success

Installation Complete

De-installing the Gateway

5 Oracle Net

Checklists for Oracle Net

Checklist for Configuring Oracle Net

Checklist for Advanced Networking Option Encryption

Setting Up Advanced Security Encryption for Test

Testing Advanced Security Encryptions

Oracle Net and SQL*Net Introduction

Oracle Net Overview

Distributed Processing.

Distributed Database

Terminology for Oracle Net

Configuring Oracle Net

Step 1: Modify listener.ora file

Step 2: Modify tnsnames.ora file

Advanced Security Encryption

Setting Up Advanced Security Encryption for Test

Step 1: Set Advanced Security Encryption Parameters for the Gateway

Step 2: Set Advanced Security Encryption Parameters for the Integrating Oracle Server

Testing Advanced Security Encryptions

Step 1: Connect Gateway and Integrating Oracle Server

Step 2: Reset Configuration Parameters on the Gateway

6 Configuring the DRDA Server

Checklists for Configuring the DRDA Server

Checklist for the DB2/MVS

Checklist for DB2/400

Checklist for DB2/Universal Database

Checklist for DB2/VM

DB2/MVS

Step 1: Configure the Communications Server

Step 2: Define the user ID that owns the package

Step 3: Define the recovery user ID

Step 4: Determine DRDA location name for DB2 instance

Step 5: Configure DB2 Distributed Data Facility for gateway

DB2/400

Step 1: Configure the Communications Server

Step 2: Define the user ID that owns the package

Step 3: Define the recovery user ID

Step 4: Determine DRDA location name for DB2/400 instance

DB2/UDB (Universal Database)

Step 1: Configure the Communications Server

Step 2: Define the user ID that owns the package

Step 3: Define the recovery user ID

Step 4: Determine DRDA location name for DB2/UDB instance

DB2/VM

Step 1: Configure the Communications Server

Step 2: Define the user ID that owns the package

Step 3: Define the recovery user ID

Step 4: Determine DRDA location name for DB2/VM instance

7 Configuring the Communications Interfaces

Checklist for Configuring the Communications Interfaces

Step 1: Setting up a Gateway Name

Step 2: Setting up a Configuration File

Starting the SNA Peer-to-Peer Version 9 Software

Step 3: Side Information File

Partner_LU_name

Mode_name

TP_name

Specifying the Side Information File

Sample Side Information File for Version 9

Step 4: Test the Connection

Configuring TCP/IP

8 Configuring the Gateway

Configuration Checklists

Configuring the Gateway

Configuring the Sun Host

Binding the DRDA Gateway Package

Binding Packages on DB2/Universal Database (DB2/UDB)

Before Binding the DRDA Gateway Package

Sample SQL scripts

Configuring the Integrating Oracle Server

Accessing the Gateway from Other Oracle Servers

Accessing Other DRDA Servers

Choosing a Gateway System Identifier (SID)

Gateway Configuration and the Startup Shell Script

Configuring the Sun Host

Step 1: Choose an appropriate Startup Shell Script

Step 2: Create the gateway Startup Shell Script

Step 3: Tailor the Startup Shell Script

Binding the DRDA Gateway Package

Binding Packages on DB2/Universal Database (DB2/UDB)

DRDA Gateway Package Considerations

Before Binding the DRDA Gateway Package

Step 1: Check all DRDA parameter settings

Step 2: If using DB2/UDB, then create ORACLE2PC table

Sample SQL scripts

Step 1: Remove obsolete tables and views

Step 2: Replace current table and views

Step 3: If server is DB2/MVS or DB2/400, then run data dictionary scripts

Step 4: DB2/UDB or other server

Step 4a: If server is DB2/UDB, grant authority to package

Step 4b: If server is not DB2/UDB, create the ORACLE2PC table

Backup and Recovery of Gateway Configuration

Configuring the Integrating Oracle Server

Step 1: Create a database link

Step 2: Create synonyms and views

Accessing the Gateway from Other Oracle Servers

Accessing Other DRDA Servers

Gateway Installation and Configuration Complete

9 Using the Gateway

Processing a Database Link

Creating Database Links

Guidelines for Database Links

Dropping Database Links

Examining Available Database Links

Limiting the Number of Active Database Links

Accessing the Gateway

Step 1: Login to the integrating Oracle Server

Step 2: Create a database link to the DRDA database

Step 3: Retrieve data from the DRDA database

Accessing AS/400 File Members

Using the Synonym Feature

Performing Distributed Queries

Example of a Distributed Query

Two-Phase Commit Processing

Distributed DRDA Transactions

Read-Only Gateway

Replicating in a Heterogeneous Environment

Oracle9i Server Triggers

Oracle Snapshots

Oracle Replication Services

Copying Data from the Oracle9i Server to the DRDA Server

Copying Data from the DRDA Server to the Oracle9i Server

Tracing SQL Statements

10 Developing Applications

Gateway Appearance to Application Programs

Fetch Reblocking

Using Oracle Stored Procedures with the Gateway

Using DRDA Server Stored Procedures with the Gateway

Oracle Application and DRDA Server Stored Procedure Completion

Procedural Feature Considerations with DB2

Database Link Behavior

Oracle Server SQL Construct Processing

Compatible SQL Functions

Translated SQL Functions

Compensated SQL Functions

Native Semantic SQL Functions

DB2/MVS SQL Compatibility

DB2/Universal Database SQL Compatibility

DB2/400 SQL Compatibility

DB2/VM SQL Compatibility

Native Semantics

SQL Functions Which Can Be Enabled

SQL Functions Which Can Be Disabled

SQL Set Operators and Clauses

DRDA Datatype to Oracle Datatype Conversion

Performing Character String Operations

Converting Character String Datatypes

Performing Graphic String Operations

Performing Date and Time Operations

Processing TIME and TIMESTAMP Data

Processing DATE Data

Performing Date Arithmetic

Dates in the 21st Century

HS_NLS_DATE_FORMAT Support

Oracle TO_DATE Function

Performing Numeric Datatype Operations

Mapping the COUNT Function

Performing Zoned Decimal Operations

Passing Native SQL Statements through the Gateway

Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function

Examples

Retrieving Results Sets Through Passthrough

Example

Oracle Data Dictionary Emulation on a DRDA Server

Using the Gateway Data Dictionary

Using the DRDA Catalog

Defining the Number of DRDA Cursors

11 Security Considerations

Security Overview

Authenticating Application Logons

Defining and Controlling Database Links

Link Accessibility

Links and CONNECT Clauses

TCP/IP Security

Using SNA Session Security Validation

SNA Conversation Security

SNA Security Option SECURITY=PROGRAM

SNA Security Option SECURITY=SAME

Processing Inbound Connections

User ID Mapping

DB2/MVS

DB2/VM

DB2/400

DB2/Universal Database

12 Migration and Coexistence with Existing Gateways

Migrating Existing Gateway Instances to New Release

Step 1: Install the new Release

Step 2: Transferring initsid.gtwboot Gateway Boot Initialization parameters.

Step 3: Transferring initsid.ora Gateway Initialization File parameters.

Backout Considerations When Migrating to New Releases

New and Changed Parameters When Migrating from Version 4 to Release 9i of the Gateway

New Parameters

New Startup Shell Parameters

New Gateway Initialization File Parameters

Parameters That Have Been Changed in Usage

Parameters That Have Been Renamed

Obsolete Parameters

DRDA Server Considerations

Oracle Net Considerations

13 Error Messages, Diagnosis, and Reporting

Interpreting Gateway Error Messages

Errors Detected by the Integrating Oracle Server

Errors Detected by the Gateway

Errors Detected in the DRDA Software

Communication Errors

Errors Detected by the Server Database

Mapped Errors

Gateway Error Codes

SQL Tracing and the Gateway

SQL Tracing in the Oracle Database

SQL Tracing in the Gateway

A Oracle DB2 Data Dictionary Views

Supported Views

Data Dictionary View Tables

ALL_CATALOG

ALL_COL_COMMENTS

ALL_CONS_COLUMNS

ALL_CONSTRAINTS

ALL_INDEXES

ALL_IND_COLUMNS

ALL_OBJECTS

ALL_SYNONYMS

ALL_TABLES

ALL_TAB_COLUMNS

ALL_TAB_COMMENTS

ALL_USERS

ALL_VIEWS

COLUMN_PRIVILEGES

DICTIONARY

DUAL

TABLE_PRIVILEGES

USER_CATALOG

USER_COL_COMMENTS

USER_CONSTRAINTS

USER_CONS_COLUMNS

USER_INDEXES

USER_OBJECTS

USER_SYNONYMS

USER_TABLES

USER_TAB_COLUMNS

USER_TAB_COMMENTS

USER_USERS

USER_VIEWS

B Sample Files

Sample Gateway Initialization File

Sample Startup Shell Script

Sample Oracle Net tnsnames.ora File

Sample Oracle Net listener.ora File

C DRDA-Specific Parameters

Modifying the Gateway Initialization File

Setting Parameters in the Gateway Initialization File

Syntax and Usage

Gateway Initialization File Parameters

DRDA_CAPABILITY

DRDA_CODEPAGE_MAP

DRDA_COMM_BUFLEN

DRDA_CONNECT_PARM (SNA format)

DRDA_CONNECT_PARM (TCP/IP format)

DRDA_CMSRC_CM_IMMEDIATE

DRDA_DEFAULT_CCSID

DRDA_DESCRIBE_TABLE

DRDA_DISABLE_CALL

DRDA_FLUSH_CACHE

DRDA_ISOLATION_LEVEL

DRDA_LOCAL_NODE_NAME

DRDA_OPTIMIZE_QUERY

DRDA_OVERRIDE_FROM_CODEPAGE (obsolete)

DRDA_OVERRIDE_TO_CODEPAGE (obsolete)

DRDA_PACKAGE_COLLID

DRDA_PACKAGE_CONSTOKEN

DRDA_PACKAGE_NAME

DRDA_PACKAGE_OWNER

DRDA_PACKAGE_SECTIONS

DRDA_RDBMS_TYPE (obsolete)

DRDA_RECOVERY_PASSWORD

DRDA_READ_ONLY

DRDA_RECOVERY_USERID

DRDA_REMOTE_DB_NAME

DRDA_SECURITY_TYPE

LOG_DESTINATION

ORA_MAX_DATE

ORACLE_DRDA_TCTL

ORACLE_DRDA_TRACE

TRACE_LEVEL

Startup Shell Script Parameters

FDS_CLASS

FDS_CLASS_VERSION

FDS_INSTANCE

D National Language Support

Overview of NLS Interactions

Client and Integrating Oracle Server Configuration

Gateway Language Interaction with DRDA Server

Gateway Configuration

NLS Parameters in the Gateway Initialization File

HS_LANGUAGE

HS_NLS_DATE_FORMAT

HS_NLS_DATE_LANGUAGE

NLS Parameters in the Gateway Startup Shell Script

ORA_NLS33

NLS_LANG

Gateway Codepage Map Facility

Message Availability

Example of NLS Configuration

E Configuration Worksheet

F Quick Reference to Oracle SQL Functions

G Sample Applications

DB2INS

ORAIND

Index