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

• Introduction
  • Protection of Current Investment
• 10g Release 2 Gateways
  • Advantages of the Gateway
    • Transparency at All Levels
    • Extended Database Services
    • Extended Advanced Networking, Internet, and Intranet Support
    • Dynamic Dictionary Mapping
  • SQL
    • Passthrough and Native DB2/400 SQL
  • Stored Procedures and Functions
    • Oracle Stored Procedures and Functions
    • Native DB2/400 Stored Procedures
    • Native DB2/400 Stored Functions (User Defined Functions)
  • Languages
  • SQL*Plus
  • Oracle Database 10g Server Technology and Tools
• Two-Phase Commit and Multisite Transactions
• Site Autonomy
• Migration and Coexistence
• Security
• Gateway Architecture
• How the Gateway Works
  • Gateway Changes to the AS/400 System
  • SQL Differences
  • Heterogeneous Services Architecture

2 Release Information

• Product Set
• Changes and Enhancements
  • Bugs
• Known Problems
• Known Restrictions
  • General Restrictions
    • SQL Development Kit, Product 5722-ST1
    • Selecting CHAR Column Returns Width Doubled
    • Coexistence with Previous Version Gateway: Data Dictionary Tables
    • Oracle Fast Refresh Snapshots
    • Oracle SQL Command INSERT
    • Verify the QCRTAUT Parameter Setting Before Installation
    • Date Arithmetic
    • Julian Dates Support
    • GRAPHIC Constants in SQL Commands
  • Owners of DB2/400 Components
    • DD Basic Tables and Views
    • Binary Literal Notation
    • Programmatic Limitations
    • Columns Defined with RAW Data
    • GLOBAL_NAMES Initialization Parameter
    • Precompiler Limitations
    • Some SQL Functions Post processed
  • DB2/400 SQL Limitations
    • Oracle Bind Variables
    • CONNECT BY Is Not Supported
    • Oracle Incremental Materialized View Refresh
    • LONG Data Types

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 Coexistence
• Checklists
  • Preinstallation Checklist
  • Installation Checklist
  • Postinstallation Checklist
• Preinstallation Steps
  • Step 1: Log On
  • Step 2: Verify System Value for the QCRTAUT Parameter
• Installation Steps
  • Step 1: Mount the CD-ROM
  • Step 2: Start the Installation Process
  • Step 3: Set Required Parameters
    • Panel
    • Action
  • Step 4: Verify Installation
    • Panel
    • Action
  • Step 5: Finish the Installation
  • Step 6: Start the Gateway Subsystem
• Postinstallation Steps
  • Journaling
  • Step 1: Verify the Gateway Version
  • Step 2: Verify Journaling
  • 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
  • File Name Structure
    • Trace Files for Job Members
• Connecting to the AS/400 through TCP/IP
  • Step 1: Add a TCP/IP Connect Descriptor to tnsnames.ora
  • Step 2: Specify SID Names
• 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: Configure the Port Number
  • Step 2: Define the Host Name
  • Step 3: Verify the Host Name
  • Step 4: Verify that the Listener is Started
• TCP/IP Connection Problems
• Resolving Connection Problems
  • AS/400 Connection Problems
    • The AS/400 is not reachable:
    • The AS/400 is reachable, but you cannot access AS/400 data:
  • ORA-28509
    • HS= is missing from your tnsnames.ora file:
    • HS= Not in Correct Place:
    • TCP/IP Configuration:
    • Gateway Listener is Not Up:
  • ORA-28511
    • Gateway or Listener is Not Up:
    • Set Fully Qualified host.domain Name
  • ORA-28500
  • ORA-12154
• Troubleshooting TCP/IP Configuration
  • Option 10 of CFGTCP: Work with TCP/IP Host Table Entries
  • Option 12 of CFGTCP: Change TCP/IP Domain (CHGTCPDMN)

6 Configuring the Gateway

• Gateway Commands
  • Running the Commands
  • CRTORAGTWI, Copy the Gateway
  • CHGORANET, Change Network Parameters
    • Changing the ORA(SQLNET_CHG) File
    • Changing the ORA(LISTEN_CHG) File
  • 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 OS/400 File Members
  • Accessing Flat Files
• Performing Distributed Queries
  • Example of a Distributed Query
    • SQL Command, Example 1
    • SQL Command, Example 2
  • Two-Phase Commit Processing
    • Recovering Failed Transactions
• Replicating in a Heterogeneous Environment
  • Oracle Database Server Triggers
  • Oracle Materialized View
• Copying Data from Oracle Server to DB2/400 Server
  • Triggers
  • SQL*Plus COPY Command
• Copying Data to Oracle Server from 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
• Streams Replication

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
• Using Oracle Stored Functions with the Gateway
• Using DB2/400 Stored Functions with the Gateway
  • Executing DB2/400 Stored Functions from Applications
  • Considerations for Using Stored Functions 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
    • Example
• Converting DB2/400 Data Types to Oracle Data Types
  • DB2/400 GRAPHIC Support
  • For DBCS Users
    • Double-Byte Character Support
  • Performing Character String Operations
  • Converting Character String Data Types
  • Performing Date and Time Operations
  • Dates in the 21st Century
  • Performing Numeric Data Type 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
  • Oracle Bind Variables
  • 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 Oracle Database 10g Server
• Contacting Oracle Support Services
• Error Categories
  • Documentation Errors
  • Incorrect Output
  • Oracle External Error
  • Performance
  • Missing Functionality

A Oracle Net Files and Members

• File Members
  • LOG File Members
  • ORA File Members
    • LISTENER
    • LISTENER_CHG
    • LISTENLOCK
    • SQLNET
    • SQLNET_CHG
    • Displaying File Members
  • TRC File Members
  • ORA files in the Integrated File System (IFS)
• Agent Trace Files

B Globalization Support

• Overview of Globalization 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
• Character Set Expansions

C Data Dictionary Views

• ALL_CATALOG
• ALL_COL_COMMENTS
• ALL_CONS_COLUMNS
• 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
• DICTIONARY
• DUAL
• 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