Contents

List of Tables

Title and Copyright Information

Preface

• Intended Audience
• Product Name
• Documentation Accessibility
• The Oracle Documentation Set
• Related Documents
• SQL*Plus Prompts
• Storage Measurements
• Conventions Used in this Guide
• Switches

1 Introduction

• Version 10 Gateways
  • Advantages of the Gateway
    • Transparency at All Levels
    • Extension of Database Services
    • Extension of Advanced Networking, Internet, and Intranet Support
    • Dynamic Dictionary Mapping
  • SQL
    • Data Definition Language
    • Data Control Language
    • Passthrough and Native DB2 SQL
  • Stored Procedures
    • Oracle Stored Procedures
    • Native DB2 Stored Procedures
    • Native DB2 User Defined Functions
  • Applications
    • Oracle Developer
    • Oracle Discoverer
    • SQL*Plus
    • Oracle Database Server Technology and Tools   
• 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 Release 8.1.7
  • Changes and Enhancements in Release 9.2.0
  • Changes and Enhancements in Release 10.1.0.2.0
  • Changes and Enhancements in Release 10.2.0.2.0
• Known Problems
• Known Restrictions

3 System Requirements

• Resource Requirements
  • CPU
  • Disk Space
  • Virtual Memory
• Software Requirements
  • Operating System
  • UNIX System Services (USS)
  • IBM Maintenance
• Oracle Database Server Requirements 
• Oracle Net Requirements 
  • Oracle Net IBM TCP/IP
• Distribution Kit

4 Installation

• Installation Checklists
  • Installation Checklist
  • Post-installation Checklist       
• Product Installation
  • Step 1:  Perform Preinstallation Tasks
  • Step 2:  Install the Oracle Transparent Gateway for DB2 Software
• Postinstallation Steps 
  • Step 1: Download and Install Patches
  • Step 2: Add Program Properties
  • Step 3: Authorize the Gateway Load Library
  • Step 4: Re-IPL z/OS or Use Dynamic z/OS Commands
• Summary

5 Configuring a Gateway Service

• Overview
• Gateway Service Definition
  • Service Name
  • TYPE
  • PROC
  • PARM
  • MAXAS
  • JOBNAME
  • SID
• Gateway Region JCL
  • ORA$ENV
  • ORA$FPS
  • ORA$LIB
  • SQLNET
  • STEPLIB
  • SYSPRINT
  • Sample Gateway Region JCL Procedure
• Gateway Region Parameters
  • ALERT_DSNAME | ADSN
  • ALERT_MAX | AMAX
  • ALERT_MIN | AMIN
  • DSN_PREFIX_DB | ORAPREFD
  • DEDICATED_TCB
  • IDLE_TIMEOUT | ITIMEOUT
  • INIT_ADR_SPACES | INTADSPC
  • INIT_STACK_SIZE | INTSTKSZ
  • LOGON_AUTH | LGNAUTH
  • MAX_SESSION_MEM | MAXSMEM
  • MAX_SESSIONS | MAXSESS
  • PRIMARY_ASC_MODE | PRIMASCM
  • REGION_MEM_RESERVE | REGMRES
  • REGION_MEM_RESERVE (region_memory)
  • SERVER_LOADMOD | SRVRLMOD
  • SMF_STAT_RECNO | SMFSTRCN
  • TRACE_DSNAME | TDSN
  • G4DB2ENV (Environment Variables and HS Initialization parameters)
  • Parameters for Environment Variables in Member G4DB2ENV
  • CURRDEGREE
  • DB2DESCTAB
  • DB2LONGMSG
  • DB2WARNING
  • DB2STATS
  • DB2READONLY
  • FDS_CLASS_VERSION
  • FLUSH_CACHE_ON_COMMIT
  • DB2CAP
  • TARGET
  • DB2SSN
  • DB2PLAN
  • ORARECID
  • ORA_MAX_DATE
  • ORACLE_TIMESTAMP = YES| NO (default)
  • TRACELEVEL
  • TO_NUMBER_OFF = YES | NO (default)
  • CNV_LIT_FMT = YES | NO (default)
  • EMPTYSTR_TO_NULL_OFF = YES | NO (default)
  • EMPTYSTR_TO_NULL_WHERE_OFF = YES | NO (default)
  • LIKE_OFF = YES | NO (default)
  • NVL_TO_VALUE_OFF = YES | NO (default)
  • SELECT_CONCAT_ON = YES | NO (default)
  • HS Initialization Parameters for Member G4DB2ENV
• Checklists
  • Configuration Checklist
  • Post Configuration Checklist
• Creating a Gateway Instance
  • Step 1: Run the Configuration Utility
  • Step 2: Customize JCL Procedures and Parameter Files
    • JCL Procedures
    • Parameter Files
  • Step 3: Copy the Subsystem PROCs to a System PROCLIB
  • 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
    • DATACLAS (classname)
    • DEFAULT_SPACE (primary secondary)
    • FILE_GROUP (name)
    • MGMTCLAS (classname)
    • STORCLAS (classname)
    • UNIT (unitname)
    • VOLUMES (volser)
    • SQLNETLG
  • Step 10: Associate User IDs with Services
  • Step 11: Define and Start OSDI Services
  • Step 12: Start the Gateway
• Post-configuration Steps
  • Step 1: Move Reentrant Modules to z/OS 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 File Names
• Configuring the OSDI Listener
  • Network Service Definition
  • Service Name
  • TYPE
  • PROC
  • PARM
    • Example of Network Service Definition
  • OSDI Listener Region JCL
    • Example of Network Service Procedure JCL
    • Example of NET8LOG output
  • TCP/IP Network Considerations
  • Client-Server Access Using the OSDI Listener
    • Remote Clients
    • Name Server
    • LDAP Server
• Operating the OSDI Listener
• Formatting OSDI Listener Trace Files
• Oracle Advanced Security Option Encryption
  • Setting Up ASO Encryption for Test
    • Checklist for Setting Up ASO Encryption
    • Step 1:  Set ASO Encryption Parameters for the Server
    • Step 2:  Set ASO Encryption Parameters for the Client
  • Testing ASO Encryption
    • Checklist for Testing ASO Encryption
    • Step 1:  Connect Client and Server
    • Step 2:  Reset Configuration Parameters on Server

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
• 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 Materialized View
• 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 Execution
  • Procedural Feature Considerations with DB2     
• Passing DB2 SQL Statements Through the Gateway
  • Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function
    • Examples
  • Retrieving Result Sets Through Passthrough
    • Example
• 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,UPDATE, and DELETE Clauses
• Oracle Database Server and DB2 Differences
  • Mass Delete from a Segmented Tablespace
  • 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 Database Server 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
    • OSDI Subsystems
    • Gateway Service
    • Oracle Net or Network Service
    • Commands and Messages
    • Error Diagnosis and Reporting
  • Configuring and Initializing an OSDI Subsystem
  • Configuring a Gateway Service
    • SID
    • Gateway Instance JCL
    • Oracle Net Access
  • File Processing Considerations
  • Operating a Gateway Service
  • Oracle Net or Network Service
    • Configuring Network Service
    • Operating Network Service
    • Computer Associates or Interlink SNS/TCPaccess Support
    • IXCF Support
    • Using Network Service
• Migration and Upgrade
  • Release Incompatibilities
    • Local Database Links
  • Migration and Upgrade Steps
    • Step 1: Create and Configure an OSDI Subsystem
    • Step 2: Create an OSDI Gateway Service
    • Step 3: Create and Configure OSDI Net Service
    • Step 4: Establish Security
    • Step 5: Ensure User Exits Are Available to DB2
    • Step 6: Prepare the DB2 Environment
    • Step 7: Start the OSDI Services
    • Step 8: Test the Gateway
  • Configuring Multiple OSDI Gateway Services
  • MPM/TNS and OSDI Coexistence
  • Release 10g Coexistence with Prior Releases of the Gateway

12 National Language Support

• Overview
• Obsolete NLS Parameters
• DB2 Character Sets Handled Automatically
• Oracle Database 10g for z/OS
  • Double-Byte Character Support
• Oracle Database Server and Client Configuration
• Message Availability

A OSDI Subsystem Command Reference

• OSDI Command Reference
• Command Types and Processing
• System Symbols in Commands
• Definition Commands
• Structures
• Service Group Definition Commands
  • DEFINE
  • Define Parameters
  • ALTER
  • Alter Parameters
  • SHOW
    • Show Parameters
• Service Definition Commands
  • DEFINE
  • Define Parameters
  • ALTER
  • Alter Parameters
  • SHOW
  • Show Parameters
• Operating Commands
• Available Commands
• Commands
  • START
  • DISPLAY
  • DRAIN
  • RESUME
  • RESUME Parameters
  • STOP
  • STOP Parameters
• OSDI Command Keyword Abbreviations

B The Oracle SMF Interface

• Activating SMF Records
  • Specifying the Oracle Gateway Record Type
    • Using the OSDI SMF_STAT_RECNO Parameter
  • 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
• ORAFMTO Sample Formatting Program

C 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
• OTGDB2.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

D Quick Reference to Oracle SQL Functions

E Sample Applications

• DB2IND
• ORAIND

F Installation Reference

• Choosing Data Set Name Qualifiers

Index