Contents

Title and Copyright Information

Send Us Your Comments

Preface

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

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

Enhancements in this Release

Changes in this Release

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

Oracle Server

Oracle Networking Products

Documentation Requirements

4 Installing the Gateway

Introduction

Before You Begin

Checklist for Gateway Installation

Installation Overview

Installing the Gateway from CD-ROM

Step 1: Log on to the Pentium-Based host

Step 2: Load the CD-ROM into the CD-ROM Drive

Step 3: Start the Oracle Universal Installer on MS Windows NT

Step 4: Step through the Oracle Universal Installer

Step 5: Verify Installation Success

Installation Complete

 Deinstalling the Gateway

5 Oracle Net

Checklists for Oracle Net

Checklist for Configuring Oracle Net

Checklist for Advanced Networking Option Encryption

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

Before You Begin

Steps for Configuring the Communications Interfaces

Creating SNA Server Profiles for the Gateway

Independent Versus Dependent LUs

Creating SNA Definitions for the Gateway

Sample SNA Server Definitions

Definition Types

SNA Server Version 3 Definitions

Testing the Connection

Configuring TCP/IP

8 Configuring the Gateway

Configuration Checklist

Choosing a Gateway System Identifier (SID)

Configuring the Pentium-Based Host

Step 1: Copy the gateway initialization

Step 2: Determine settings for gateway initialization parameters

Binding the DRDA Gateway Package

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

DRDA Gateway Package Considerations

Before Binding the DRDA Gateway Package

Sample SQL scripts

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 Oracle Server to the DRDA Server

Copying Data from the DRDA Server to the Oracle 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 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

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

Processing DDL Statements through Passthrough

Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function

Retrieving Results Sets Through Passthrough

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

12 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

13 Migration and Coexistence with Existing Gateways

Migrating Existing Gateway Instances to the 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

DRDA Server Considerations

Oracle Net Considerations

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

ORA_MAX_DATE

ORACLE_DRDA_TCTL

ORACLE_DRDA_TRACE

FDS_CLASS

FDS_CLASS_VERSION

FDS_INSTANCE

LOG_DESTINATION

TRACE_LEVEL

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

NLS Parameters in the Gateway Startup Shell Script

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