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

• 1.1 Version 10 Gateways
  • 1.1.1 Advantages of the Gateway
    • 1.1.1.1 Transparency at All Levels
    • 1.1.1.2 Extension of Database Services
    • 1.1.1.3 Extension of Advanced Networking, Internet, and Intranet Support
    • 1.1.1.4 Dynamic Dictionary Mapping
  • 1.1.2 SQL
    • 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.3.3 Native DB2 User Defined Functions
  • 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.5 Security
• 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.2.4 Changes and Enhancements in Release 10.2.0.2.0
• 2.3 Known Problems
• 2.4 Known Restrictions

3 System Requirements

• 3.1 Resource Requirements
  • 3.1.1 CPU
  • 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.2.4 DB2 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 Preinstallation Tasks
  • 4.2.2 Step 2:  Install the Oracle Transparent Gateway for DB2 Software
• 4.3 Postinstallation 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
• 4.4 Summary

5 Configuring a Gateway Service

• 5.1 Overview
• 5.2 Gateway Service Definition
  • 5.2.1 Service Name
  • 5.2.2 TYPE
  • 5.2.3 PROC
  • 5.2.4 PARM
  • 5.2.5 MAXAS
  • 5.2.6 JOBNAME
  • 5.2.7 SID
• 5.3 Gateway Region JCL
  • 5.3.1 ORA$ENV
  • 5.3.2 ORA$FPS
  • 5.3.3 ORA$LIB
  • 5.3.4 SQLNET
  • 5.3.5 STEPLIB
  • 5.3.6 SYSPRINT
  • 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 DEDICATED_TCB
  • 5.4.6 IDLE_TIMEOUT | ITIMEOUT
  • 5.4.7 INIT_ADR_SPACES | INTADSPC
  • 5.4.8 INIT_STACK_SIZE | INTSTKSZ
  • 5.4.9 LOGON_AUTH | LGNAUTH
  • 5.4.10 MAX_SESSION_MEM | MAXSMEM
  • 5.4.11 MAX_SESSIONS | MAXSESS
  • 5.4.12 PRIMARY_ASC_MODE | PRIMASCM
  • 5.4.13 REGION_MEM_RESERVE | REGMRES
  • 5.4.14 REGION_MEM_RESERVE (region_memory)
  • 5.4.15 SERVER_LOADMOD | SRVRLMOD
  • 5.4.16 SMF_STAT_RECNO | SMFSTRCN
  • 5.4.17 TRACE_DSNAME | TDSN
  • 5.4.18 G4DB2ENV (Environment Variables and HS Initialization parameters)
  • 5.4.19 Parameters for Environment Variables in Member G4DB2ENV
  • 5.4.20 CURRDEGREE
  • 5.4.21 DB2DESCTAB
  • 5.4.22 DB2LONGMSG
  • 5.4.23 DB2WARNING
  • 5.4.24 DB2STATS
  • 5.4.25 DB2READONLY
  • 5.4.26 FDS_CLASS_VERSION
  • 5.4.27 FLUSH_CACHE_ON_COMMIT
  • 5.4.28 DB2CAP
  • 5.4.29 TARGET
  • 5.4.30 DB2SSN
  • 5.4.31 DB2PLAN
  • 5.4.32 ORARECID
  • 5.4.33 ORA_MAX_DATE
  • 5.4.34 ORACLE_TIMESTAMP = YES| NO (default)
  • 5.4.35 TRACELEVEL
  • 5.4.36 TO_NUMBER_OFF = YES | NO (default)
  • 5.4.37 CNV_LIT_FMT = YES | NO (default)
  • 5.4.38 EMPTYSTR_TO_NULL_OFF = YES | NO (default)
  • 5.4.39 EMPTYSTR_TO_NULL_WHERE_OFF = YES | NO (default)
  • 5.4.40 LIKE_OFF = YES | NO (default)
  • 5.4.41 NVL_TO_VALUE_OFF = YES | NO (default)
  • 5.4.42 SELECT_CONCAT_ON = YES | NO (default)
  • 5.4.43 HS Initialization Parameters for Member G4DB2ENV
• 5.5 Checklists
  • 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 User IDs 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.1 Overview
• 6.2 OSDI Listener Architecture
• 6.3 OSDI Listener File Names
• 6.4 Configuring the OSDI Listener
  • 6.4.1 Network Service Definition
  • 6.4.2 Service Name
  • 6.4.3 TYPE
  • 6.4.4 PROC
  • 6.4.5 PARM
    • 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 Materialized View
• 8.9 Copying Data from the Oracle Database Server to the DB2 Server
  • 8.9.1 Triggers
  • 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 Execution
  • 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 Result 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,UPDATE, and DELETE Clauses
• 9.9 Oracle Database Server and DB2 Differences
  • 9.9.1 Mass Delete from a Segmented Tablespace
  • 9.9.2 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 Database Server External Error
  • 10.6.4 Abend
  • 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
• 10.8 GTF

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.1 SID
    • 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 Globalization Support

• 12.1 Overview
• 12.2 Obsolete NLS Parameters
• 12.3 DB2 Character Sets Handled Automatically
• 12.4 Oracle Database 10g for z/OS
  • 12.4.1 Double-Byte Character Support
• 12.5 Oracle Database Server and Client Configuration
• 12.6 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.5 Structures
• A.6 Service Group Definition Commands
  • A.6.1 DEFINE
  • A.6.2 Define Parameters
  • A.6.3 ALTER
  • A.6.4 Alter Parameters
  • A.6.5 SHOW
    • A.6.5.1 Show Parameters
• A.7 Service Definition Commands
  • A.7.1 DEFINE
  • A.7.2 Define Parameters
  • A.7.3 ALTER
  • A.7.4 Alter Parameters
  • A.7.5 SHOW
  • A.7.6 Show Parameters
• A.8 Operating Commands
• A.9 Available Commands
• A.10 Commands
  • A.10.1 START
  • A.10.2 DISPLAY
  • A.10.3 DRAIN
  • A.10.4 RESUME
  • A.10.5 RESUME Parameters
  • A.10.6 STOP
  • 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.12 ALL_USERS
• C.13 ALL_VIEWS
• 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

• E.1 DB2IND
• E.2 ORAIND

F Installation Reference

• F.1 Choosing Data Set Name Qualifiers

Index