Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Intended Audience
• Documentation Accessibility
• Product Name
• Platform Name
• Conventions
• Related Documents
• Storage Measurements

1 Introduction

• 1.1 Introduction
  • 1.1.1 Protection of Current Investment
• 1.2 10g Release 2 Gateways
  • 1.2.1 Advantages of the Gateway
    • 1.2.1.1 Transparency at All Levels
    • 1.2.1.2 Extended Database Services
    • 1.2.1.3 Extended Advanced Networking, Internet, and Intranet Support
    • 1.2.1.4 Dynamic Dictionary Mapping
  • 1.2.2 SQL
    • 1.2.2.1 Passthrough and Native DB2/400 SQL
  • 1.2.3 Stored Procedures and Functions
    • 1.2.3.1 Oracle Stored Procedures and Functions
    • 1.2.3.2 Native DB2/400 Stored Procedures
    • 1.2.3.3 Native DB2/400 Stored Functions (User Defined Functions)
  • 1.2.4 Languages
  • 1.2.5 SQL*Plus
  • 1.2.6 Oracle Database 10g Server Technology and Tools
• 1.3 Two-Phase Commit and Multisite Transactions
• 1.4 Site Autonomy
• 1.5 Migration and Coexistence
• 1.6 Security
• 1.7 Gateway Architecture
• 1.8 How the Gateway Works
  • 1.8.1 Gateway Changes to the AS/400 System
  • 1.8.2 SQL Differences
  • 1.8.3 Heterogeneous Services Architecture

2 Release Information

• 2.1 Product Set
• 2.2 Changes and Enhancements
  • 2.2.1 Bugs
• 2.3 Known Problems
• 2.4 Known Restrictions
  • 2.4.1 General Restrictions
    • 2.4.1.1 SQL Development Kit, Product 5722-ST1
    • 2.4.1.2 Selecting CHAR Column Returns Width Doubled
    • 2.4.1.3 Coexistence with Previous Version Gateway: Data Dictionary Tables
    • 2.4.1.4 Oracle Fast Refresh Snapshots
    • 2.4.1.5 Oracle SQL Command INSERT
    • 2.4.1.6 Verify the QCRTAUT Parameter Setting Before Installation
    • 2.4.1.7 Date Arithmetic
    • 2.4.1.8 Julian Dates Support
    • 2.4.1.9 GRAPHIC Constants in SQL Commands
  • 2.4.2 Owners of DB2/400 Components
    • 2.4.2.1 DD Basic Tables and Views
    • 2.4.2.2 Binary Literal Notation
    • 2.4.2.3 Programmatic Limitations
    • 2.4.2.4 Columns Defined with RAW Data
    • 2.4.2.5 GLOBAL_NAMES Initialization Parameter
    • 2.4.2.6 Precompiler Limitations
    • 2.4.2.7 Some SQL Functions Post processed
  • 2.4.3 DB2/400 SQL Limitations
    • 2.4.3.1 Oracle Bind Variables
    • 2.4.3.2 CONNECT BY Is Not Supported
    • 2.4.3.3 Oracle Incremental Materialized View Refresh
    • 2.4.3.4 LONG Data Types

3 System Requirements

• 3.1 Hardware Requirements
  • 3.1.1 Processor
  • 3.1.2 Memory
  • 3.1.3 CD-ROM Drive
  • 3.1.4 Disk Space
• 3.2 Software Requirements
  • 3.2.1 Operating System Requirements
  • 3.2.2 Oracle Integrating Server
• 3.3 Documentation Requirements
• 3.4 Distribution Kit

4 Installing the Gateway

• 4.1 Gateway Coexistence
• 4.2 Checklists
  • 4.2.1 Preinstallation Checklist
  • 4.2.2 Installation Checklist
  • 4.2.3 Postinstallation Checklist
• 4.3 Preinstallation Steps
  • 4.3.1 Step 1: Log On
  • 4.3.2 Step 2: Verify System Value for the QCRTAUT Parameter
• 4.4 Installation Steps
  • 4.4.1 Step 1: Mount the CD-ROM
  • 4.4.2 Step 2: Start the Installation Process
  • 4.4.3 Step 3: Set Required Parameters
    • 4.4.3.1 Panel
    • 4.4.3.2 Action
  • 4.4.4 Step 4: Verify Installation
    • 4.4.4.1 Panel
    • 4.4.4.2 Action
  • 4.4.5 Step 5: Finish the Installation
  • 4.4.6 Step 6: Start the Gateway Subsystem
• 4.5 Postinstallation Steps
  • 4.5.1 Journaling
  • 4.5.2 Step 1: Verify the Gateway Version
  • 4.5.3 Step 2: Verify Journaling
  • 4.5.4 Step 3: Journal the ORACLE2PC File
  • 4.5.5 Step 4: Configure Oracle Net
  • 4.5.6 Step 5: Remove Observability
  • 4.5.7 Step 6: Raise the Gateway Priority Level

5 Oracle Net

• 5.1 Overview of Oracle Net
  • 5.1.1 Distributed Processing
  • 5.1.2 Distributed Database
  • 5.1.3 Terminology for Oracle Net
  • 5.1.4 Oracle Net for AS/400 Architecture
  • 5.1.5 File Name Structure
    • 5.1.5.1 Trace Files for Job Members
• 5.2 Connecting to the AS/400 through TCP/IP
  • 5.2.1 Step 1: Add a TCP/IP Connect Descriptor to tnsnames.ora
  • 5.2.2 Step 2: Specify SID Names
• 5.3 Checklists for Configuring Oracle Net
  • 5.3.1 Checklist for Configuring Oracle Net for TCP/IP
  • 5.3.2 Connecting Through TCP/IP (on the Oracle Server) Checklist
  • 5.3.3 Preliminary Step: Define a Physical Line
• 5.4 Configuring Oracle Net for TCP/IP AS/400
  • 5.4.1 Configuring for TCP/IP
  • 5.4.2 Step 1: Configure the Port Number
  • 5.4.3 Step 2: Define the Host Name
  • 5.4.4 Step 3: Verify the Host Name
  • 5.4.5 Step 4: Verify that the Listener is Started
• 5.5 TCP/IP Connection Problems
• 5.6 Resolving Connection Problems
  • 5.6.1 AS/400 Connection Problems
    • 5.6.1.1 The AS/400 is not reachable:
    • 5.6.1.2 The AS/400 is reachable, but you cannot access AS/400 data:
  • 5.6.2 ORA-28509
    • 5.6.2.1 HS= is missing from your tnsnames.ora file:
    • 5.6.2.2 HS= Not in Correct Place:
    • 5.6.2.3 TCP/IP Configuration:
    • 5.6.2.4 Gateway Listener is Not Up:
  • 5.6.3 ORA-28511
    • 5.6.3.1 Gateway or Listener is Not Up:
    • 5.6.3.2 Set Fully Qualified host.domain Name
  • 5.6.4 ORA-28500
  • 5.6.5 ORA-12154
• 5.7 Troubleshooting TCP/IP Configuration
  • 5.7.1 Option 10 of CFGTCP: Work with TCP/IP Host Table Entries
  • 5.7.2 Option 12 of CFGTCP: Change TCP/IP Domain (CHGTCPDMN)

6 Configuring the Gateway

• 6.1 Gateway Commands
  • 6.1.1 Running the Commands
  • 6.1.2 CRTORAGTWI, Copy the Gateway
  • 6.1.3 CHGORANET, Change Network Parameters
    • 6.1.3.1 Changing the ORA(SQLNET_CHG) File
    • 6.1.3.2 Changing the ORA(LISTEN_CHG) File
  • 6.1.4 CHGORAPJE, Change Prestarted Job Parameters
  • 6.1.5 CHGORATUN, Change Initialization Parameters
  • 6.1.6 CHGGTWDBG, Change Debugging Parameters
  • 6.1.7 CRTORADDB, Create Data Dictionary Views
  • 6.1.8 CHGRECOPRF, Change Recovery Profile Parameters
• 6.2 Setting Optional Parameters
  • 6.2.1 Retrieving Data
  • 6.2.2 Data Conversion
  • 6.2.3 Read-Only Gateway

7 Using the Gateway

• 7.1 Database Link Behavior
  • 7.1.1 Creating Database Links
  • 7.1.2 Creating Database Links Using Oracle Net
  • 7.1.3 Closing Database Links
  • 7.1.4 Accessing Data through Database Links
  • 7.1.5 Dropping Database Links
  • 7.1.6 Examining Available Database Links
  • 7.1.7 Limiting the Number of Active Database Links
• 7.2 Using DB2/400 Cursors
• 7.3 Using the Synonym Feature
• 7.4 Accessing the Gateway
  • 7.4.1 Viewing Jobs
• 7.5 Accessing OS/400 File Members
  • 7.5.1 Accessing Flat Files
• 7.6 Performing Distributed Queries
  • 7.6.1 Example of a Distributed Query
    • 7.6.1.1 SQL Command, Example 1
    • 7.6.1.2 SQL Command, Example 2
  • 7.6.2 Two-Phase Commit Processing
    • 7.6.2.1 Recovering Failed Transactions
• 7.7 Replicating in a Heterogeneous Environment
  • 7.7.1 Oracle Database Server Triggers
  • 7.7.2 Oracle Materialized View
• 7.8 Copying Data from Oracle Server to DB2/400 Server
  • 7.8.1 Triggers
  • 7.8.2 SQL*Plus COPY Command
• 7.9 Copying Data to Oracle Server from DB2/400 Server

8 Administering the Gateway

• 8.1 Overview of the Oracle Gateway Monitor
• 8.2 Starting the Oracle Gateway Monitor
• 8.3 Starting and Stopping the Gateway
  • 8.3.1 Working with Oracle Gateway Jobs
  • 8.3.2 Starting and Stopping the Gateway Using the Command Line
• 8.4 Starting the TCP/IP Listener
• 8.5 Displaying the Gateway Call Stack
• 8.6 Working with Gateway Job Locks
• 8.7 Displaying Open Files
• 8.8 Gateway Security
• 8.9 Streams Replication

9 Developing Applications

• 9.1 Gateway Appearance to Application Programs
  • 9.1.1 Connecting to DB2/400 Databases with Database Links
  • 9.1.2 Read and Write Access to DB2/400 Databases
  • 9.1.3 Accessing Tables in Multiple Databases
• 9.2 Array Processing
  • 9.2.1 Fetch Reblocking
• 9.3 Using Oracle Stored Procedures with the Gateway
• 9.4 Using DB2/400 Stored Procedures with the Gateway
  • 9.4.1 Executing DB2/400 Stored Procedures from Applications
  • 9.4.2 Considerations for Using Stored Procedures with DB2/400
• 9.5 Using Oracle Stored Functions with the Gateway
• 9.6 Using DB2/400 Stored Functions with the Gateway
  • 9.6.1 Executing DB2/400 Stored Functions from Applications
  • 9.6.2 Considerations for Using Stored Functions with DB2/400
• 9.7 Passing DB2/400 SQL Statements Through the Gateway
  • 9.7.1 Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function
  • 9.7.2 Examples
  • 9.7.3 Using Passthrough Statements to Create Tables
  • 9.7.4 Retrieving Results Sets Through Passthrough
    • 9.7.4.1 Example
• 9.8 Converting DB2/400 Data Types to Oracle Data Types
  • 9.8.1 DB2/400 GRAPHIC Support
  • 9.8.2 For DBCS Users
    • 9.8.2.1 Double-Byte Character Support
  • 9.8.3 Performing Character String Operations
  • 9.8.4 Converting Character String Data Types
  • 9.8.5 Performing Date and Time Operations
  • 9.8.6 Dates in the 21st Century
  • 9.8.7 Performing Numeric Data Type Operations
• 9.9 SQL Functions
• 9.10 Oracle Server SQL Construct Processing
  • 9.10.1 SELECT without the FOR UPDATE Clause
  • 9.10.2 SELECT FOR UPDATE, INSERT, and DELETE Clauses
• 9.11 Oracle Server and DB2/400 Differences
  • 9.11.1 Oracle Bind Variables
  • 9.11.2 Performing Zoned Decimal Operations
• 9.12 Oracle Data Dictionary Emulation in a DB2/400 Server
  • 9.12.1 Using the Gateway Data Dictionary
  • 9.12.2 DB2/400 Special Registers
• 9.13 Oracle Developer Forms Compatibility

10 Error Messages, Diagnosis, and Reporting

• 10.1 Message and Error Code Processing
  • 10.1.1 Mapping DB2/400 Error Messages to Oracle Error Messages
  • 10.1.2 Interpreting Gateway Message Formats
  • 10.1.3 Diagnosing Errors Detected by the Oracle Database 10g Server
• 10.2 Contacting Oracle Support Services
• 10.3 Error Categories
  • 10.3.1 Documentation Errors
  • 10.3.2 Incorrect Output
  • 10.3.3 Oracle External Error
  • 10.3.4 Performance
  • 10.3.5 Missing Functionality

A Oracle Net Files and Members

• A.1 File Members
  • A.1.1 LOG File Members
  • A.1.2 ORA File Members
    • A.1.2.1 LISTENER
    • A.1.2.2 LISTENER_CHG
    • A.1.2.3 LISTENLOCK
    • A.1.2.4 SQLNET
    • A.1.2.5 SQLNET_CHG
    • A.1.2.6 Displaying File Members
  • A.1.3 TRC File Members
  • A.1.4 ORA files in the Integrated File System (IFS)
• A.2 Agent Trace Files

B National Language Support

• B.1 Overview of National Language Support
• B.2 Gateway Configuration
  • B.2.1 CHGORATUN Language Specification
• B.3 DB2/400 Coded Character Set Considerations
  • B.3.1 Supported Character Sets
  • B.3.2 Supported Languages and Territories
• B.4 Oracle Server and Client Configuration
• B.5 Message Availability
• B.6 DB2/400 GRAPHIC Support
• B.7 Character Set Expansions

C Data Dictionary Views

• C.1 ALL_CATALOG
• C.2 ALL_COL_COMMENTS
• C.3 ALL_CONS_COLUMNS
• C.4 ALL_CONSTRAINTS
• C.5 ALL_IND_COLUMNS
• C.6 ALL_INDEXES
• C.7 ALL_OBJECTS
• C.8 ALL_SYNONYMS
• C.9 ALL_TAB_COLUMNS
• C.10 ALL_TAB_COMMENTS
• C.11 ALL_TABLES
• C.12 ALL_USERS
• C.13 ALL_VIEWS
• C.14 COLUMN_PRIVILEGES
• C.15 DICTIONARY
• C.16 DUAL
• C.17 OTGREGISTER
• C.18 TABLE_PRIVILEGES
• C.19 USER_CATALOG
• C.20 USER_COL_COMMENTS
• C.21 USER_CONS_COLUMNS
• C.22 USER_CONSTRAINTS
• C.23 USER_INDEXES
• C.24 USER_OBJECTS
• C.25 USER_SYNONYMS
• C.26 USER_TAB_COLUMNS
• C.27 USER_TAB_COMMENTS
• C.28 USER_TABLES
• C.29 USER_USERS
• C.30 USER_VIEWS

Index