Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Introduction

Oracle Transparent Gateway for DB2/400 Introduction

Protection of Current Investment

Release 9i Gateways

Advantages of the Gateway

SQL

Stored Procedures

Languages

SQL*Plus

Oracle9i Database Server Technology and Tools

Two-Phase Commit and Multi-site Transactions

Site Autonomy

Migration and Coexistence

Security

Gateway Architecture

How the Gateway Works

Gateway Changes to Your AS/400 System

SQL Differences

Heterogeneous Services Architecture

2 Release Information

Product Set

Changes and Enhancements

Known Problems

Known Restrictions

Owners of DB2/400 Components

DB2/400 SQL Limitations

3 System Requirements

Hardware Requirements

Processor

Memory

CD-ROM Drive

Disk Space

Software Requirements

Operating System Requirements

Oracle Integrating Server

Documentation Requirements

Distribution Kit

4 Installing the Gateway

Gateway Co-existence

Checklists

Pre-Installation Checklist

Installation Checklist

Post-Installation Checklist

Preinstallation Steps

Step 1: Log on

Step 2: System Value for the QCRTAUT Parameter

Installation Steps

Step 1: Mount the CD-ROM

Step 2: Start the Install Process

Step 3: Set Required Parameters

Step 4: Verify Installation

Step 5: Finish the Installation

Step 6: Start the Gateway Subsystem

Post-Installation Steps

Journalling

Step 1: Verify the Gateway Version

Step 2: Verify Journalling

Step 3: Journal the ORACLE2PC File

Step 4: Configure Oracle Net

Step 5: Remove Observability

Step 6: Raise the Gateway Priority Level

5 Oracle Net

Overview of Oracle Net

Distributed Processing

Distributed Database

Terminology for Oracle Net

Oracle Net for AS/400 Architecture

Oracle Net File Name Structure

Checklists for Configuring Oracle Net

Checklist for Configuring Oracle Net for TCP/IP

Connecting through TCP/IP (on the Oracle Server) Checklist

Preliminary Step: Define a Physical Line

Configuring Oracle Net for TCP/IP AS/400

Configuring for TCP/IP

Step 1: Modify the Port Number

Step 2: Define the Host Name

Step 3: Verify the Host Name

Step 4: Verify that the Listener is Started

Connecting to the AS/400 through TCP/IP

Step 1: Add a TCP/IP Connect Descriptor to TNSNAMES.ORA

Step 2: Specify SID Names

Troubleshooting TCP/IP Connection Problems

Resolving Connection Problems

Troubleshooting Your TCP/IP Configuration

6 Configuring the Gateway

Gateway Commands

Running the Commands

CRTORAGTWI, Copy the Gateway

CHGORANET, Change Network Parameters

CHGORAPJE, Change Prestarted Job Parameters

CHGORATUN, Change Initialization Parameters

CHGGTWDBG, Change Debugging Parameters

CRTORADDB, Create Data Dictionary Views

CHGRECOPRF, Change Recovery Profile Parameters

Setting Optional Parameters

Retrieving Data

Data Conversion

Read-Only Gateway

7 Using the Gateway

Database Link Behavior

Creating Database Links

Creating Database Links Using Oracle Net

Closing Database Links

Accessing Data through Database Links

Dropping Database Links

Examining Available Database Links

Limiting the Number of Active Database Links

Using DB2/400 Cursors

Using the Synonym Feature

Accessing the Gateway

Viewing Jobs

Accessing AS/400 File Members

Accessing Flat Files

Performing Distributed Queries

Example of a Distributed Query

Two-Phase Commit Processing

Replicating in a Heterogeneous Environment

Oracle9i database server Triggers

Oracle Snapshots

Copying Data from the Oracle9i database server to the DB2/400 Server

Triggers

SQL*Plus COPY Command

Copying Data to the Oracle9i database server from the DB2/400 Server

8 Administering the Gateway

Overview of the Oracle Gateway Monitor

Starting the Oracle Gateway Monitor

Starting and Stopping the Gateway

Working with Oracle Gateway Jobs

Starting and Stopping the Gateway Using the Command Line

Starting the TCP/IP Listener

Displaying the Gateway Call Stack

Working with Gateway Job Locks

Displaying Open Files

Gateway Security

9 Developing Applications

Gateway Appearance to Application Programs

Connecting to DB2/400 Databases with Database Links

Read and Write Access to DB2/400 Databases

Accessing Tables in Multiple Databases

Array Processing

Fetch Reblocking

Using Oracle Stored Procedures with the Gateway

Using DB2/400 Stored Procedures with the Gateway

Executing DB2/400 Stored Procedures from Applications

Considerations for Using Stored Procedures with DB2/400

Passing DB2/400 SQL Statements through the Gateway

Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function

Examples

Using Passthrough Statements to Create Tables

Retrieving Results Sets Through Passthrough

Converting DB2/400 Datatypes to Oracle Datatypes

DB2/400 GRAPHIC Support

For DBCS Users

Performing Character String Operations

Converting Character String Datatypes

Performing Date and Time Operations

Dates in the 21st Century

Performing Numeric Datatype Operations

SQL Functions

Oracle Server SQL Construct Processing

SELECT without the FOR UPDATE Clause

SELECT FOR UPDATE, INSERT, and DELETE Clauses

Oracle Server and DB2/400 Differences

Mass Delete from a Segmented Tablespace

Oracle Bind Variables

Mapping the COUNT Function

Performing Zoned Decimal Operations

Oracle Data Dictionary Emulation in a DB2/400 Server

Using the Gateway Data Dictionary

DB2/400 Special Registers

Oracle Developer Forms Compatibility

10 Error Messages, Diagnosis, and Reporting

Message and Error Code Processing

Mapping DB2/400 Error Messages to Oracle Error Messages

Interpreting Gateway Message Formats

Diagnosing Errors Detected by the Oracle9i database server

Contacting Oracle Support Services

Error Categories

Documentation Errors

Incorrect Output

Oracle External Error

Performance

Missing Functionality

A Oracle Net Files and Members

LOG File Members

ORA File Members

TRC File Members

Agent Trace Files

B National Language Support

Overview of National Language Support

Gateway Configuration

CHGORATUN Language Specification

DB2/400 Coded Character Set Considerations

Supported Character Sets

Supported Languages and Territories

Oracle Server and Client Configuration

Message Availability

DB2/400 GRAPHIC Support

C Data Dictionary Views

ALL_CATALOG

ALL_COL_COMMENTS

ALL_CONSTRAINTS

ALL_IND_COLUMNS

ALL_INDEXES

ALL_OBJECTS

ALL_SYNONYMS

ALL_TAB_COLUMNS

ALL_TAB_COMMENTS

ALL_TABLES

ALL_USERS

ALL_VIEWS

COLUMN_PRIVILEGES

OTGREGISTER

TABLE_PRIVILEGES

USER_CATALOG

USER_COL_COMMENTS

USER_CONS_COLUMNS

USER_CONSTRAINTS

USER_INDEXES

USER_OBJECTS

USER_SYNONYMS

USER_TAB_COLUMNS

USER_TAB_COMMENTS

USER_TABLES

USER_USERS

USER_VIEWS

Index