Contents

Title and Copyright Information

Send Us Your Comments

Preface

Documentation Accessibility

Accessibility of Code Examples in Documentation

1 Introduction

Version 9 Gateways

Advantages of the Gateway

SQL

Stored Procedures

Applications   

Two-Phase Commit and Multisite Transactions

Site Autonomy

Migration and Coexistence

Security

Protection of Current Investment

Gateway Architecture

How the Gateway Works

2 Release Information

Product Set

Changes and Enhancements

Changes and Enhancements in Version 8.1.7

Changes and Enhancements in Version 9.2.0

Known Problems

Known Restrictions

3 System Requirements

CPU

Disk Space

Virtual Memory

Software Requirements

Operating System

UNIX System Services (USS)

IBM Maintenance

Additional Software Requirements

Oracle Database Server Requirements

Oracle Net Requirements 

Distribution Kit

4 Installation

Installation Checklists

Setup and Initialization Checklist  

Installation Checklist

Post-installation Checklist 

Setup and Initialization Steps

Step 1:  Load the Oracle Installation JCL

Step 2:  Create the ISPF Libraries

Step 3:  Invoke the Product Installation and Customization Process

Step 4:  Select Product Set from the Oracle Product Install Menu (Panel OR@PRIM)

Step 5:  Select Options from the Oracle Primary Option Menu (Panel OR@INST)

Product Installation

Step 1:  Modify Tape Unit and Library Index (Panel ORINIP00)

Step 2:  Select Oracle Products for Installation (Panel ORPRODS)

Step 3:  Select Language Message Modules (Panel ORLANG)

Step 4:  INSTLIB/ISPSLIB File Tailoring Information (Panel ORINIP15)

Step 5:  Define JOB Cards for Install Jobs (Panel ORINIP20)

Step 6:  Define PROCs, CLISTs, Linklist, and Temporary Space (Panel ORINIP25)

Step 7:  Review/Modify Space Specifications for Major Libraries (Panel ORDSN)

Step 8:  Specify VOLSER for Other Libraries (Panel ORDSNO)

Step 9:  Product Installation Definition Complete (Panel ORINIP60)

Step 10: Generate the Installation Job

Step 11:  Run the Installation Job (ORIJA01)

Step 12:  Run the Generated Installation Jobs

Post-Installation Steps

Step 1: Define OSDI Subsystem for Oracle Gateways

Step 2: Associate Userids with Services

Step 3: Add Program Properties

Step 4: Authorize the Gateway Load Library

Step 5: Re-IPL OS/390 or Use Dynamic OS/390 Commands

5 Configuring the Gateway

Introduction

Checklists

Configuration Checklist

Post Configuration Checklist

Configuration Steps

Step 1:  Configuring a Gateway Service Using ISPF Panels

Step 2:  Run the sidJB00 Configuration Job

Step 3: Run the sidJC01 and sidJC02 Configuration Jobs

Step 4: Make Authorization and Local Date Exits Available to DB2

Step 5: Run the Scripts to Create Required Tables and Views in DB2

Step 6: Bind the DB2 Package

Step 7: Bind the DB2 Plan

Step 8: Grant EXECUTE on DB2 Plan

Step 9: Edit the PARMLIB Members

Step 10: Gateway Service Definition

Step 11: Setup Gateway JCL

Step 12: Start the Gateway

Post Configuration Steps

Step 1: Move Reentrant Modules to OS/390 Link Pack Areas

Step 2: Examine Oracle Dump Data Sets and Modify as Necessary

Step 3: Examine Oracle Trace Data Sets and Modify as Necessary

6 Oracle Net

Overview

OSDI Listener Architecture

OSDI Listener Filenames

Configuring the OSDI Listener

Network Service Definition

Service Name

TYPE

PROC

PARM

OSDI Listener Region JCL

TCP/IP Network Considerations

Client-Server Access Using the OSDI Listener

Operating the OSDI Listener

Formatting OSDI Listener Trace Files

Oracle Advanced Security Option Encryption

Setting Up ASO Encryption for Test

Testing ASO Encryption

7 Administering the Gateway

Operation of the Gateway Subsystem with OSDI

Controlling Access to OSDI Subsystem Commands

Controlling Access to OSDI Services

Gateway Security

SAF Router Considerations

Gateway User Exit Facility

Specifying an Exit Module

DB2AUTH Exit

Sample Exit Programs

8 Using the Gateway

Database Link Behavior

Creating Database Links

Creating Database Links Using Oracle Net

Guidelines for Database Links

Accessing Data through Database Links

Dropping Database Links

Examining Available Database Links

Limiting the Number of Active Database Links

Managing Threads

KEEPALIVE

Canceling DB2 Threads

Gateway CPU Time

Using DB2 Cursors

Using the Synonym Feature

Read-Only Gateway

Performing Distributed Queries

Example of a Distributed Query

Two-Phase Commit Processing

Distributed DB2 Transactions

Replicating in a Heterogeneous Environment

Oracle Database Server Triggers

Oracle Snapshots

Copying Data from the Oracle Database Server to the DB2 Server

Triggers

SQL*Plus COPY Command

STREAMS Replication

Copying Data to the Oracle Database Server from the DB2 Server

9 Developing Applications

Gateway Appearance to Application Programs

Array Processing

Fetch Reblocking

Using Oracle Stored Procedures with the Gateway

Using DB2 Stored Procedures with the Gateway

Oracle Application DB2 Stored Procedure Completion

Procedural Feature Considerations with DB2

Passing DB2 SQL Statements through the Gateway

Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function

Retrieving Results Sets Through Passthrough

DB2 Data Types to Oracle Data Type Conversion

Performing Character String Operations

Converting Character String Data Types

Performing Date and Time Operations

DB2 Local Date Exit

Date Considerations in SQL Coding

NLS_DATE_FORMAT Support

Oracle TO_DATE Function

Date Arithmetic

Performing Numeric Data Type Operations

Oracle ROWID Column

Double Byte Character Set Support

CHAR FOR BIT DATA

SQL Functions

Oracle Database Server SQL Construct Processing

SELECT without the FOR UPDATE Clause

SELECT FOR UPDATE, INSERT, and DELETE Clauses

Oracle Database Server and DB2 Differences

Mass Delete from a Segmented Tablespace

Mapping the COUNT Function

Oracle Bind Variables

Oracle Data Dictionary Emulation on a DB2 Server

Using the Gateway Data Dictionary

DB2 Special Registers

10 Error Messages, Diagnosis, and Reporting

Message and Error Code Processing

Mapping DB2 Error Messages to Oracle Error Messages

Interpreting Message Formats

Messages Generated by Oracle Transparent Gateway for DB2

Examples

Diagnosing Errors Detected by the Oracle Database Server

Oracle Support Services

Providing Error Documentation

General Documentation Requirements

Error Diagnosis

Components

Error Categories

Documentation Errors

Incorrect Output

Oracle External Error

Abend

Program Loop

Performance

Missing Functionality

System Dumps

System Dump Data Sets

Operator Initiated Dumps

GTF

11 Migration and Coexistence with Existing Gateways

OSDI Differences

Summary of Changes

Configuring and Initializing an OSDI Subsystem

Configuring a Gateway Service

File Processing Considerations

Operating a Gateway Service

Oracle Net or Network Service

Migration and Upgrade

Release Incompatibilities

Migration and Upgrade Steps

Configuring Multiple OSDI Gateway Services

MPM/TNS and OSDI Coexistence

A Data Set Names and Space Allocations

Using the NEWDSRPT CLIST

Oracle Default Data Sets

B OSDI Subsystem Command Reference

Command Types and Processing

System Symbols in Commands

Definition Commands

Structures

Service Group Definition Commands

DEFINE

ALTER

SHOW

Service Definition Commands

DEFINE

ALTER

SHOW

Operating Commands

Available Commands

Commands

START

DISPLAY

DRAIN

RESUME

STOP

KILL

OSDI Command Keyword Abbreviations

C National Language Support

Overview

Default Character Set Changes

Oracle9i Enterprise Edition for OS/390

Gateway Configuration

NLS Parameters in sidENV

DB2 Configuration

Supported Character Sets

Double-Byte Character Support

Supported Languages and Territories

Oracle Database Server and Client Configuration

Message Availability

D The Oracle SMF Interface

Activating SMF Records

Specifying the Oracle Gateway Record Type

Starting SMF Recording

Stopping SMF Recording

Events Generating SMF Records

Interpreting an Oracle SMF Record

Contents of the SMF Header Section

Contents of the SMF Correlation Section

Contents of the SMF OSDI Data Section

Contents of the SMF Database Engine Data Section

Contents of the SMF Oracle Net Data Section

ORAFMT Sample Formatting Program

E Data Dictionary Views

ALL_CATALOG

ALL_COL_COMMENTS

ALL_CON_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

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

F Quick Reference to Oracle SQL Functions

G Sample Applications

DB2IND

ORAIND

H Installation Reference

Choosing Data Set Name Qualifiers

Data Set Name Qualifier Rules

OR@INST Panel Options

Index