Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documents

SQL*Plus Prompts

Storage Measurements

1 Introduction

Introduction to the Oracle Transparent Gateway

Protection of Current Investment

Release 10g Gateways

Advantages of the Gateway

Gateway Capabilities

Transparency at All Levels

Extended Database Services

Extended Advanced Networking, Internet and Intranet Support

Dynamic Dictionary Mapping

Data Definition Language

Data Control Language

Passthrough and Native DB2 SQL

Stored Procedures

Oracle Stored Procedures

Native DB2 Stored Procedures

Oracle Database server Technology and Tools

Two-Phase Commit and Multi-site Transactions

Migration and Coexistence

How the Gateway Works

SQL Differences

Oracle Tools and the Gateway

Heterogeneous Services Architecture

Performance Enhancements

Fetch Reblocking

Oracle Database 10g 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

Password Encryption Utility

Support for DB2/OS390 V6 and V7 Stored Procedures

Codepage Map Facility

IBM DB2 Universal Database Support

IBM DB2 Version 5.1 ASCII Tables

Read-Only Support

2 Release Information

Changes and Enhancements

Bugs Fixed in Release 10

Known Restrictions

DB2 Considerations

SQL Limitations

3 System Requirements

Hardware Requirements

Network Attachment

Software Requirements

Operating System

Communication Server

Oracle Database server

Oracle Networking Products

Documentation Requirements

4 Installing the Gateway

Before You Begin

Checklist for Gateway Installation

Installation Overview

Before Beginning Installation

Installing the Gateway from CD-ROM

Step 1: Log on to the host

Step 2: Create the product installation directory

Step 3: Set the ORACLE_HOME environment variable

Step 4: Mount the CD-ROM

Step 4a: on Solaris

Step 4b: on AIX, enter:

Step 4c: on HP-UX, enter:

Step 4d: on Linux, enter:

Step 5: Set the DISPLAY Variable

Step 6: Start the Oracle Universal Installer

Step 7: Step through the Oracle Universal Installer

Step 8: Verify installation success

Installation Complete

De-installing the Gateway

5 Configuring the DRDA Server

Checklists for Configuring the DRDA Server

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 instance

Step 5: Configure DB2 Distributed Data Facility for gateway

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

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

6 Configuring SunLink for the Gateway

Checklist for Configuring the Communications Interfaces

Step 1: Setting up a Gateway Name

Step 2: Setting up a Configuration File

Starting the SunLink Peer-to-Peer Version 9 Software

Step 3: Side Information File

Partner_LU_name

Sample Side Information File for Version 9

Step 4: Test the Connection

Using SNA Session Security Validation

SNA Conversation Security

SNA Security Option SECURITY=PROGRAM

SNA Security Option SECURITY=SAME

7 Configuring SNAP-IX Interfaces

Steps for Configuring the Communications Interfaces

Before You Begin

SNAP-IX Configuration Tool

Creating SNAP-IX Profiles for the Gateway

Independent Versus Dependent LUs

Creating SNA Definitions for the Gateway

Sample SNAP-IX Definitions

Configuring SNAP-IX

Invokingxsnaadmin

Using SNA Session Security Validation

SNA Conversation Security

SNA Security Option SECURITY=PROGRAM

SNA Security Option SECURITY=SAME

Testing the Connection

8 Configuring IBM Communication Server

Checklist for Configuring the Communications Interfaces

Step 1: Configuring Communication Server Profiles

Step 2: Creating Communication Server Profiles for the Gateway

Sample Profile Definitions

Local LU Profile

Partner LU Profile

Partner LU Location Profile

Side Information Profile

Step 3: Testing the Connection

Using SNA Session Security Validation

SNA Conversation Security

SNA Security Option SECURITY=PROGRAM

SNA Security Option SECURITY=SAME

9 Configuring SNAPlus2

Steps for Configuring the Communications Interfaces

Before You Begin

SNAPlus2 Configuration Tool

Creating SNAPlus2 Profiles for the Gateway

Independent Versus Dependent LUs

Creating SNA Definitions for the Gateway

Sample SNAPlus2 Definitions

Configuring SNAPlus2

Invoking xsnapadmin

Using SNA Session Security Validation

SNA Conversation Security

SNA Security Option SECURITY=PROGRAM

SNA Security Option SECURITY=SAME

Testing the Connection

10 Configuring TCP/IP

Before You Begin

Configuring TCP/IP under UNIX

11 Oracle Net

Checklists for Oracle Net

Configuring Oracle Net

Advanced Security Encryption

Setting Up Advanced Security Encryption for Test

Testing Advanced Security Encryptions

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

Testing Advanced Security Encryptions

Step 1: Connect Gateway and Oracle Integrating Server

Step 2: Reset Configuration Parameters on the Gateway

12 Configuring the Gateway

Configuration Checklists

Choosing a Gateway System Identifier (SID)

Gateway Configuration

Configuring the Host

Step 1: Choose the initsid.ora file

Step 2: Tailor the initsid.ora file

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: If server is DB2/OS390, DB2/400, or DB2/UDB, then run data dictionary scripts

Backup and Recovery of Gateway Configuration

Configuring the Oracle Integrating Server

Step 1: Create a database link

Step 2: Create synonyms and views

Accessing the Gateway from Other Oracle Database Servers

Accessing Other DRDA Servers

Gateway Installation and Configuration Complete

13 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 Oracle Integrating 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

Oracle Database 10g Server Triggers

Oracle Snapshots

Copying Data from Oracle Database 10g Server to DRDA Server

Copying Data from DRDA Server to Oracle Database 10g Server

Tracing SQL Statements

14 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 Database Server SQL Construct Processing

Compatible SQL Functions

Translated SQL Functions

Compensated SQL Functions

Post-Processing

Native Semantic SQL Functions

DB2/OS390 SQL Compatibility

DB2/Universal Database SQL Compatibility

DB2/400 SQL Compatibility

DB2/VM SQL Compatibility

Native Semantics

SQL Functions That Can Be Enabled

SQL Functions That 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

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 DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE

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

15 Security Considerations

Security Overview

Authenticating Application Logons

Defining and Controlling Database Links

Link Accessibility

Links and CONNECT Clauses

TCP/IP Security

Processing Inbound Connections

User ID Mapping

DB2/Universal Database

Passwords in the Gateway Initialization File

Using the g4drpwd Utility

16 Migration and Coexistence with Existing Gateways

Migrating Existing V4, V8, or V9 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 to Release 10

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

17 Error Messages, Diagnosis, and Reporting

Interpreting Gateway Error Messages

Errors Detected by the Oracle Integrating Server

Errors Detected by the Gateway

Errors Detected in the DRDA Software

Communication Errors

Errors Detected by the Server Database

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_COL_COMMENTS

ALL_CONS_COLUMNS

ALL_CONSTRAINTS

ALL_IND_COLUMNS

ALL_TAB_COLUMNS

ALL_TAB_COMMENTS

COLUMN_PRIVILEGES

TABLE_PRIVILEGES

USER_COL_COMMENTS

USER_CONSTRAINTS

USER_CONS_COLUMNS

USER_TAB_COLUMNS

USER_TAB_COMMENTS

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_CACHE_TABLE_DESC

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_GRAPHIC_PAD_SIZE

DRDA_GRAPHIC_LIT_CHECK

DRDA_GRAPHIC_TO_MBCS

DRDA_ISOLATION_LEVEL

DRDA_LOCAL_NODE_NAME

DRDA_MBCS_TO_GRAPHIC

DRDA_OPTIMIZE_QUERY

DRDA_PACKAGE_COLLID

DRDA_PACKAGE_CONSTOKEN

DRDA_PACKAGE_NAME

DRDA_PACKAGE_OWNER

DRDA_PACKAGE_SECTIONS

DRDA_RECOVERY_PASSWORD

DRDA_RECOVERY_USERID

DRDA_REMOTE_DB_NAME

DRDA_SECURITY_TYPE

FDS_CLASS_VERSION

HS_FDS_FETCH_ROWS

LOG_DESTINATION

ORACLE_DRDA_TCTL

ORACLE_DRDA_TRACE

D National Language Support

Overview of NLS Interactions

Client and Oracle Integrating Server Configuration

Gateway Language Interaction with DRDA Server

Gateway Configuration

NLS Parameters in the Gateway Initialization File

HS_NLS_DATE_FORMAT

HS_NLS_DATE_LANGUAGE

Gateway Codepage Map Facility

Multi-Byte and Double-Byte Support in the Gateway

Message Availability

Example of NLS Configuration

E Configuration Worksheet

F Quick Reference to Oracle SQL Functions

G Sample Applications

Index