Contents

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

The Oracle Documentation Set

Related Documents

SQL*Plus Prompts

Storage Measurements

Conventions Used in this Guide

Documentation Accessibility

1 Introduction

1.1 Version 10 Gateways

1.1.1 Advantages of the Gateway

1.1.1.1 Transparency at All Levels

1.1.1.2 Extends Database Services

1.1.1.3 Extends Advanced Networking, Internet, and Intranet Support

1.1.1.4 Dynamic Dictionary Mapping

1.1.2.1 Data Definition Language

1.1.2.2 Data Control Language

1.1.2.3 Passthrough and Native DB2 SQL

1.1.3 Stored Procedures

1.1.3.1 Oracle Stored Procedures

1.1.3.2 Native DB2 Stored Procedures

1.1.4 Applications

1.1.4.1 Oracle Developer

1.1.4.2 Oracle Discoverer

1.1.4.3 SQL*Plus

1.1.4.4 Oracle Database Server Technology and Tools

1.2 Two-Phase Commit and Multisite Transactions

1.3 Site Autonomy

1.4 Migration and Coexistence

1.6 Protection of Current Investment

1.7 Gateway Architecture

1.8 How the Gateway Works

2 Release Information

2.1 Product Set

2.2 Changes and Enhancements

2.2.1 Changes and Enhancements in Release 8.1.7

2.2.2 Changes and Enhancements in Release 9.2.0

2.2.3 Changes and Enhancements in Release 10.1.0.2.0

2.3 Known Problems

2.4 Known Restrictions

3 System Requirements

3.1 Resource Requirements

3.1.2 Disk Space

3.1.3 Virtual Memory

3.2 Software Requirements

3.2.1 Operating System

3.2.2 UNIX System Services (USS)

3.2.3 IBM Maintenance

3.3 Oracle Database Server Requirements

3.4 Oracle Net Requirements

3.4.1 Oracle Net IBM TCP/IP

3.5 Distribution Kit

4 Installation

4.1 Installation Checklists

4.1.1 Installation Checklist

4.1.2 Post-installation Checklist

4.2 Product Installation

4.2.1 Step 1: Perform Pre-installation Tasks

4.2.2 Step 2: Install the Oracle Transparent Gateway for DB2 Software

4.3 Post-Installation Steps

4.3.1 Step 1: Download and Install Patches

4.3.2 Step 2: Add Program Properties

4.3.3 Step 3: Authorize the Gateway Load Library

4.3.4 Step 4: Re-IPL z/OS or Use Dynamic z/OS Commands

5 Configuring a Gateway Service

5.2 Gateway Service Definition

5.2.1 Service Name

5.3 Gateway Region JCL

5.3.7 Sample Gateway Region JCL Procedure

5.4 Gateway Region Parameters

5.4.1 ALERT_DSNAME | ADSN

5.4.2 ALERT_MAX | AMAX

5.4.3 ALERT_MIN | AMIN

5.4.4 DSN_PREFIX_DB | ORAPREFD

5.4.5 IDLE_TIMEOUT | ITIMEOUT

5.4.6 INIT_ADR_SPACES | INTADSPC

5.4.7 INIT_STACK_SIZE | INTSTKSZ

5.4.8 LOGON_AUTH | LGNAUTH

5.4.9 MAX_SESSION_MEM | MAXSMEM

5.4.10 MAX_SESSIONS | MAXSESS

5.4.11 PRIMARY_ASC_MODE | PRIMASCM

5.4.12 REGION_MEM_RESERVE | REGMRES

5.4.13 REGION_MEM_RESERVE ( region_memory )

5.4.14 SERVER_LOADMOD | SRVRLMOD

5.4.15 SMF_STAT_RECNO | SMFSTRCN

5.4.16 TRACE_DSNAME | TDSN

5.4.17 G4DB2ENV (Environment variables and HS Initialization parameters)

5.4.18 Parameters for Environment Variables in Member G4DB2ENV

5.4.19 CURRDEGREE

5.4.20 DB2DESCTAB

5.4.21 DB2LONGMSG

5.4.22 DB2WARNING

5.4.23 DB2STATS

5.4.24 DB2READONLY

5.4.25 FDS_CLASS_VERSION

5.4.26 FLUSH_CACHE_ON_COMMIT

5.4.31 ORARECID

5.4.32 NLS_LANG

5.4.33 NLS_DATE_FORMAT

5.4.34 NLS_NCHAR

5.4.35 ORA_MAX_DATE

5.4.36 ORACLE_TIMESTAMP = YES| NO (default)

5.4.37 TRACELEVEL

5.4.38 TO_NUMBER_OFF = YES | NO (default)

5.4.39 CNV_LIT_FMT = YES | NO (default)

5.4.40 EMPTYSTR_TO_NULL_OFF = YES | NO (default)

5.4.41 EMPTYSTR_TO_NULL_WHERE_OFF = YES | NO (default)

5.4.42 LIKE_OFF = YES | NO (default)

5.4.43 NVL_TO_VALUE_OFF = YES | NO (default)

5.4.44 SELECT_CONCAT_ON = YES | NO (default)

5.4.45 HS Initialization Parameters for Member G4DB2ENV

5.5.1 Configuration Checklist

5.5.2 Post Configuration Checklist

5.6 Creating a Gateway Instance

5.6.1 Step 1: Run the Configuration Utility

5.6.2 Step 2: Customize JCL Procedures and Parameter Files

5.6.2.1 JCL Procedures

5.6.2.2 Parameter Files

5.6.3 Step 3: Copy the Subsystem PROCs to a System PROCLIB

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

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

5.6.6 Step 6: Bind the DB2 Package

5.6.7 Step 7: Bind the DB2 Plan

5.6.8 Step 8: Grant EXECUTE on DB2 Plan

5.6.9 Step 9: Edit the PARMLIB Members

5.6.9.1 DATACLAS(classname)

5.6.9.2 DEFAULT_SPACE(primary secondary)

5.6.9.3 FILE_GROUP(name)

5.6.9.4 MGMTCLAS(classname)

5.6.9.5 STORCLAS(classname)

5.6.9.6 UNIT(unitname)

5.6.9.7 VOLUMES(volser)

5.6.9.8 SQLNETLG

5.6.10 Step 10: Associate Userids with Services

5.6.11 Step 11: Define and Start OSDI Services

5.6.12 Step 12: Start the Gateway

5.7 Post Configuration Steps

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

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

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

6 Oracle Net

6.2 OSDI Listener Architecture

6.3 OSDI Listener Filenames

6.4 Configuring the OSDI Listener

6.4.1 Network Service Definition

6.4.2 Service Name

6.4.5.1 Example of Network Service Definition

6.4.6 OSDI Listener Region JCL

6.4.6.1 Example of Network Service Procedure JCL

6.4.6.2 Example of NET8LOG output

6.4.7 TCP/IP Network Considerations

6.4.8 Client-Server Access Using the OSDI Listener

6.4.8.1 Remote Clients

6.4.8.2 Name Server

6.4.8.3 LDAP Server

6.5 Operating the OSDI Listener

6.6 Formatting OSDI Listener Trace Files

6.7 Oracle Advanced Security Option Encryption

6.7.1 Setting Up ASO Encryption for Test

6.7.1.1 Checklist for Setting Up ASO Encryption

6.7.1.2 Step 1: Set ASO Encryption Parameters for the Server

6.7.1.3 Step 2: Set ASO Encryption Parameters for the Client

6.7.2 Testing ASO Encryption

6.7.2.1 Checklist for Testing ASO Encryption

6.7.2.2 Step 1: Connect Client and Server

6.7.2.3 Step 2: Reset Configuration Parameters on Server

7 Administering the Gateway

7.1 Operation of the Gateway Subsystem with OSDI

7.2 Controlling Access to OSDI Subsystem Commands

7.3 Controlling Access to OSDI Services

7.4 Gateway Security

7.5 SAF Router Considerations

7.6 Gateway User Exit Facility

7.6.1 Specifying an Exit Module

7.7 Sample Exit Programs

8 Using the Gateway

8.1 Database Link Behavior

8.1.1 Creating Database Links

8.1.2 Creating Database Links Using Oracle Net

8.1.3 Guidelines for Database Links

8.1.4 Accessing Data through Database Links

8.1.5 Dropping Database Links

8.1.6 Examining Available Database Links

8.1.7 Limiting the Number of Active Database Links

8.2 Managing Threads

8.2.1 KEEPALIVE

8.2.2 Canceling DB2 Threads

8.3 Gateway CPU Time

8.4 Using DB2 Cursors

8.5 Using the Synonym Feature

8.6 Read-Only Gateway

8.7 Performing Distributed Queries

8.7.1 Example of a Distributed Query

8.7.2 Two-Phase Commit Processing

8.7.3 Distributed DB2 Transactions

8.8 Replicating in a Heterogeneous Environment

8.8.1 Oracle Database Server Triggers

8.8.2 Oracle Snapshots

8.9 Copying Data from the Oracle Database Server to the DB2 Server

8.9.2 SQL*Plus COPY Command

8.9.3 STREAMS Replication

8.10 Copying Data to the Oracle Database Server from the DB2 Server

9 Developing Applications

9.1 Gateway Appearance to Application Programs

9.2 Array Processing

9.2.1 Fetch Reblocking

9.3 Using Oracle Stored Procedures with the Gateway

9.4 Using DB2 Stored Procedures with the Gateway

9.4.1 Oracle Application DB2 Stored Procedure Completion

9.4.2 Procedural Feature Considerations with DB2

9.5 Passing DB2 SQL Statements through the Gateway

9.5.1 Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function

9.5.1.1 Examples

9.5.2 Retrieving Results Sets Through Passthrough

9.5.2.1 Example

9.6 DB2 Data Types to Oracle Data Type Conversion

9.6.1 Performing Character String Operations

9.6.2 Converting Character String Data Types

9.6.3 Performing Date and Time Operations

9.6.4 DB2 Local Date Exit

9.6.5 Date Considerations in SQL Coding

9.6.6 NLS_DATE_FORMAT Support

9.6.7 Oracle TO_DATE Function

9.6.8 Date Arithmetic

9.6.9 Performing Numeric Data Type Operations

9.6.10 Oracle ROWID Column

9.6.11 Double Byte Character Set Support

9.6.12 CHAR FOR BIT DATA

9.7 SQL Functions

9.8 Oracle Database Server SQL Construct Processing

9.8.1 SELECT without the FOR UPDATE Clause

9.8.2 SELECT FOR UPDATE, INSERT, and DELETE Clauses

9.9 Oracle Database Server and DB2 Differences

9.9.1 Mass Delete from a Segmented Tablespace

9.9.2 Mapping the COUNT Function

9.9.3 Oracle Bind Variables

9.10 Oracle Data Dictionary Emulation on a DB2 Server

9.10.1 Using the Gateway Data Dictionary

9.10.2 DB2 Special Registers

10 Error Messages, Diagnosis, and Reporting

10.1 Message and Error Code Processing

10.1.1 Mapping DB2 Error Messages to Oracle Error Messages

10.1.2 Interpreting Message Formats

10.1.3 Messages Generated by Oracle Transparent Gateway for DB2

10.1.4 Examples

10.1.5 Diagnosing Errors Detected by the Oracle Database Server

10.2 Oracle Support Services

10.3 Providing Error Documentation

10.4 General Documentation Requirements

10.5 Error Diagnosis

10.5.1 Components

10.6 Error Categories

10.6.1 Documentation Errors

10.6.2 Incorrect Output

10.6.3 Oracle External Error

10.6.5 Program Loop

10.6.6 Performance

10.6.7 Missing Functionality

10.7 System Dumps

10.7.1 System Dump Data Sets

10.7.2 Operator Initiated Dumps

11 Migration and Coexistence with Existing Gateways

11.1 OSDI Differences

11.1.1 Summary of Changes

11.1.1.1 OSDI Subsystems

11.1.1.2 Gateway Service

11.1.1.3 Oracle Net or Network Service

11.1.1.4 Commands and Messages

11.1.1.5 Error Diagnosis and Reporting

11.1.2 Configuring and Initializing an OSDI Subsystem

11.1.3 Configuring a Gateway Service

11.1.3.2 Gateway Instance JCL

11.1.3.3 Oracle Net Access

11.1.4 File Processing Considerations

11.1.5 Operating a Gateway Service

11.1.6 Oracle Net or Network Service

11.1.6.1 Configuring Network Service

11.1.6.2 Operating Network Service

11.1.6.3 Computer Associates or Interlink SNS/TCPaccess Support

11.1.6.4 IXCF Support

11.1.6.5 Using Network Service

11.2 Migration and Upgrade

11.2.1 Release Incompatibilities

11.2.1.1 Local Database Links

11.2.2 Migration and Upgrade Steps

11.2.2.1 Step 1: Create and Configure an OSDI Subsystem

11.2.2.2 Step 2: Create an OSDI Gateway Service

11.2.2.3 Step 3: Create and Configure OSDI Net Service

11.2.2.4 Step 4: Establish Security

11.2.2.5 Step 5: Ensure User Exits are Available to DB2

11.2.2.6 Step 6: Prepare the DB2 Environment

11.2.2.7 Step 7: Start the OSDI Services

11.2.2.8 Step 8: Test the Gateway

11.2.3 Configuring Multiple OSDI Gateway Services

11.2.4 MPM/TNS and OSDI Coexistence

11.2.5 Release 10g Coexistence with Prior Releases of the Gateway

12 National Language Support

12.2 Default Character Set Changes

12.3 Oracle Database 10g for z/OS

12.4 Gateway Configuration

12.4.1 NLS Parameters in sidENV

12.4.2 DB2 Configuration

12.5 Supported Character Sets

12.5.1 Double-Byte Character Support

12.5.2 Supported Languages and Territories

12.6 Oracle Database Server and Client Configuration

12.7 Message Availability

A OSDI Subsystem Command Reference

A.1 OSDI Command Reference

A.2 Command Types and Processing

A.3 System Symbols in Commands

A.4 Definition Commands

A.6 Service Group Definition Commands

A.6.2 Define Parameters

A.6.4 Alter Parameters

A.6.5.1 Show Parameters

A.7 Service Definition Commands

A.7.2 Define Parameters

A.7.4 Alter Parameters

A.7.6 Show Parameters

A.8 Operating Commands

A.9 Available Commands

A.10.5 RESUME Parameters

A.10.7 STOP Parameters

A.11 OSDI Command Keyword Abbreviations

B The Oracle SMF Interface

B.1 Activating SMF Records

B.1.1 Specifying the Oracle Gateway Record Type

B.1.1.1 Using the OSDI SMF_STAT_RECNO Parameter

B.1.2 Starting SMF Recording

B.1.3 Stopping SMF Recording

B.2 Events Generating SMF Records

B.3 Interpreting an Oracle SMF Record

B.3.1 Contents of the SMF Header Section

B.3.2 Contents of the SMF Correlation Section

B.3.3 Contents of the SMF OSDI Data Section

B.3.4 Contents of the SMF Database Engine Data Section

B.3.5 Contents of the SMF Oracle Net Data Section

B.4 ORAFMTO Sample Formatting Program

C Data Dictionary Views

C.1 ALL_CATALOG

C.2 ALL_COL_COMMENTS

C.3 ALL_CON_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.14 COLUMN_PRIVILEGES

C.15 OTGDB2.OTGREGISTER

C.16 TABLE_PRIVILEGES

C.17 USER_CATALOG

C.18 USER_COL_COMMENTS

C.19 USER_CONS_COLUMNS

C.20 USER_CONSTRAINTS

C.21 USER_INDEXES

C.22 USER_OBJECTS

C.23 USER_SYNONYMS

C.24 USER_TAB_COLUMNS

C.25 USER_TAB_COMMENTS

C.26 USER_TABLES

C.27 USER_USERS

C.28 USER_VIEWS

D Quick Reference to Oracle SQL Functions

E Sample Applications

F Installation Reference

F.1 Choosing Data Set Name Qualifiers

Index