Contents

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions
• SQL*Plus Prompts
• DOS Prompts
• Storage Measurements
• Directory Names

1 Introduction

• Introduction to the Oracle Transparent Gateway
• • Protection of Current Investment
• Release 10g Gateways
• • Advantages of the Gateway
• Gateway Capabilities
• • Transparency at All Levels
  • Extended Database Services
  • Extended 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
  • Languages
  • Oracle Database Server Technology and Tools
  • SQL*Plus
  • Two-Phase Commit and Multisite Transactions
  • Site Autonomy
  • Migration and Coexistence
  • Security
• Terms
• Architecture
• Implementation
• How the Gateway Works
• • SQL Differences
• Oracle Tools and the Gateway
• • SQL*Plus
• Features
• • Heterogeneous Services Architecture
  • Performance Enhancements
  • Fetch Reblocking
  • Oracle Database 10g Passthrough Supported
  • Retrieving Result Sets Through Passthrough
  • Support for TCP/IP
  • Native Semantics
  • Columns Supported in a Result Set
  • EXPLAIN_PLAN Improvement
  • Heterogeneous Database Integration
  • Minimum Impact on Existing Systems
  • Large Base of Data Access
  • Application Portability
  • Remote Data Access
  • Support for Distributed Applications
  • Application Development and End User Tools
  • Password Encryption Utility
  • Support for DB2/OS390 V6, V7, and V8 Stored Procedures
  • Codepage Map Facility
  • IBM DB2 Universal Database Support
  • IBM DB2 Version 5.1 ASCII Tables
  • Read-Only Support
  • Support for Graphic and Multibyte Data
  • Support for DB2/UDB on Intel Hardware
  • Data Dictionary Support for DB2/UDB

2 Release Information

• Product Set
• Changes and Enhancements
• Bugs Fixed in 10g Release 2 (10.2)
• Known Problems
• Known Restrictions
• • DB2 Considerations
  • SQL Limitations

3 System Requirements

• Hardware Requirements
• • Processor
  • Memory
  • Network Attachment
  • Disk Space
• Software Requirements
• • Operating System
  • DRDA Databases
  • Communications
  • Oracle Database server
  • Oracle Networking Products
• Documentation Requirements

4 Installing the Gateway

• Introduction
• Before You Begin
• Checklist for Gateway Installation
• Installation Overview
• Preinstallation
• Installing the Gateway from the Installation Media
• • Step 1: Log on to the host
  • Step 2: Load the CD-ROM into the CD-ROM Drive
  • Step 3: Start the Oracle Universal Installer on Microsoft Windows
  • Step 4: Step through the Oracle Universal Installer
  • Step 5: Verify Installation Success
• Installation Complete
• • Removing the Gateway

5 Configuring the DRDA Server

• Checklists for Configuring the DRDA Server
• • DB2/OS390
  • DB2/400
  • DB2/UDB (Universal Database)
  • DB2/VM
• DB2/OS390
• • Step 1: Configure the Communications Server
  • Step 2: Define the user ID that owns the package
  • Step 3: Define the recovery user ID
  • Step 4: Determine DRDA location name for DB2 instance
  • Step 5: Configure DB2 Distributed Data Facility for Gateway
• DB2/400
• • Step 1: Configure the Communications Server
  • Step 2: Define the user ID that owns the package
  • Step 3: Define the recovery user ID
  • Step 4: Determine DRDA location name for DB2/400 instance
• DB2/UDB (Universal Database)
• • Step 1: Configure the SNA Communications Server
  • Step 2: Define the user ID that owns the package
  • Step 3: Define the recovery user ID
  • Step 4: Determine DRDA location name for DB2/UDB instance
• DB2/VM
• • Step 1: Configure the Communications Server
  • Step 2: Define the user ID that owns the package
  • Step 3: Define the recovery user ID
  • Step 4: Determine DRDA location name for DB2/VM instance

6 Configuring Microsoft SNA Server or Host Integration Server

• Before You Begin
• Steps for Configuring the Communications Interfaces
• Creating SNA Server Profiles for the Gateway
• • Independent Versus Dependent LUs
• Creating SNA Definitions for the Gateway
• • Sample SNA Server Definitions
  • Definition Types
  • SNA Server Definitions
  • • Server Selection
    • Service Properties
    • Link Service Definition
    • Connection Definition
    • Local LU Definition
    • Mode Definition
    • Remote LU Definition
    • CPI-C Symbolic Destination Names
• Testing the Connection
• Using SNA Session Security Validation
• SNA Conversation Security
• • SNA Security Option SECURITY=PROGRAM
  • SNA Security Option SECURITY=SAME

7 Configuring IBM Communication Server

• Before You Begin
• Checklist for Configuring the Communications Interfaces
• Creating IBM Communication Server Profiles for the Gateway
• • Independent Versus Dependent LUs
  • Creating SNA Definitions for the Gateway
  • • Sample IBM Communication Server Definitions
• Definition Types
• • IBM Communication Server Definitions
  • • Creating the Configuration
    • Defining the Node
• Testing the Connection
• Using SNA Session Security Validation
• SNA Conversation Security
• • SNA Security Option SECURITY=PROGRAM
  • SNA Security Option SECURITY=SAME

8 Configuring TCP/IP

• Before You Begin
• • Port Number
• Configuring TCP/IP

9 Oracle Net

• Checklists for Oracle Net
• • Configuring Oracle Net
  • Advanced Security Encryption
  • • Setting Up Advanced Security Encryption for Test
    • Testing Advanced Security Encryptions
• Oracle Net and SQL*Net Introduction
• Oracle Net Overview
• • Distributed Processing
  • Distributed Database
  • Terminology for Oracle Net
• Configuring Oracle Net
• • Step 1: Modify the listener.ora file
  • Step 2: Modify the tnsnames.ora file
• Advanced Security Encryption
• Setting Up Advanced Security Encryption for Test
• • Step 1: Set Advanced Security Encryption Parameters for the Gateway
  • Step 2: Set Advanced Security Encryption Parameters
• Testing Advanced Security Encryptions
• • Step 1: Connect the Gateway and Oracle the Integrating Server
  • Step 2: Reset Configuration Parameters on the Gateway

10 Configuring the Gateway

• Configuration Checklist
• Choosing a Gateway System Identifier (SID)
• • Enter the SID on the Worksheet
• Gateway Configuration
• Configuring the Host
• • Step 1: Copy the gateway initialization
  • Step 2: Determine settings for gateway initialization parameters
  • • Required Parameters
    • Optional Parameters
  • Step 3: Tailor the initsid.ora File
  • Binding the DRDA Gateway Package
  • Binding Packages on DB2/Universal Database (DB2/UDB)
• DRDA Gateway Package Considerations
• • Before Binding the DRDA Gateway Package
  • • Step 1: Check all DRDA parameter settings
    • Step 2: If using DB2/UDB, then create ORACLE2PC table
  • Sample SQL scripts
  • • Step 1:  Run Data Dictionary scripts
    • Step 1a:  Upgrading from a previous gateway version
    • Step 1b: Creating the Data Dictionary tables and views
    • Step 2: DB2/UDB or other server
    • Step 2a: If server is DB2/UDB, then grant authority to package
    • Step 2b: If server is not DB2/UDB, then create the ORACLE2PC table
• Backup and Recovery of Gateway Configuration
• Configuring the Oracle Integrating Server
• • Step 1: Create a database link
  • Step 2: Create synonyms and views
• Accessing the Gateway from Other Oracle Servers
• Accessing Other DRDA Servers
• Gateway Installation and Configuration Complete

11 Using the Gateway

• Processing a Database Link
• • Creating Database Links
  • Guidelines for Database Links
  • Dropping Database Links
  • Examining Available Database Links
  • Limiting the Number of Active Database Links
• Accessing the Gateway
• • Step 1: Log in to the Oracle integrating server
  • Step 2: Create a database link to the DRDA database
  • Step 3: Retrieve data from the DRDA database
• Accessing AS/400 File Members
• Using the Synonym Feature
• Performing Distributed Queries
• • Example of a Distributed Query
  • Two-Phase Commit Processing
  • Distributed DRDA Transactions
• Read-Only Gateway
• Replicating in a Heterogeneous Environment
• • Oracle Database 10g Server Triggers
  • Oracle Snapshots
• Copying Data from the Oracle Server to the DRDA Server
• Copying Data from the DRDA Server to the Oracle Server
• Tracing SQL Statements

12 Developing Applications

• Gateway Appearance to Application Programs
• • Fetch Reblocking
• Using Oracle Stored Procedures with the Gateway
• Using DRDA Server Stored Procedures with the Gateway
• • Oracle Application and DRDA Server Stored Procedure Completion
  • Procedural Feature Considerations with DB2
• Database Link Behavior
• Oracle Server SQL Construct Processing
• • Compatible SQL Functions
  • Translated SQL Functions
  • Compensated SQL Functions
  • Native Semantic SQL Functions
  • DB2/OS390 SQL Compatibility
  • DB2/Universal Database SQL Compatibility
  • DB2/400 SQL Compatibility
  • DB2/VM SQL Compatibility
• Native Semantics
• • SQL Functions That Can Be Enabled
  • SQL Functions That Can Be Disabled
  • SQL Set Operators and Clauses
• DRDA Data Type to Oracle Data Type Conversion
• • Performing Character String Operations
  • Converting Character String data types
  • Performing Graphic String Operations
  • Performing Date and Time Operations
  • • Processing TIME and TIMESTAMP Data
    • Processing DATE Data
    • Performing Date Arithmetic
  • Dates
  • HS_NLS_DATE_FORMAT Support
  • Oracle TO_DATE Function
  • Performing Numeric Data Type Operations
  • Mapping the COUNT Function
  • Performing Zoned Decimal Operations
• Passing Native SQL Statements through the Gateway
• • Processing DDL Statements through Passthrough
  • Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function
  • • Examples
  • Retrieving Result Sets Through Passthrough
  • • Example
• Oracle Data Dictionary Emulation on a DRDA Server
• • Using the Gateway Data Dictionary
  • Using the DRDA Catalog
• Defining the Number of DRDA Cursors

13 Security Considerations

• Security Overview
• Authenticating Application Logons
• Defining and Controlling Database Links
• • Link Accessibility
  • Links and CONNECT Clauses
• TCP/IP Security
• Processing Inbound Connections
• • User ID Mapping
  • • DB2/OS390
    • DB2/VM
    • DB2/400
    • DB2/Universal Database
• Passwords in the Gateway Initialization File

14 Migration and Coexistence with Existing Gateways

• Migrating Existing V4, V8, or V9 Gateway Instances to New Release
• • Step 1: Install the new Release
  • Step 2: Transferring initsid.gtwboot Gateway Boot Initialization parameters.
  • Step 3: Transferring initsid.ora gateway initialization file parameters.
• Backout Considerations When Migrating to New Releases
• New and Changed Parameters When Migrating to Release 10
• • New Parameters
  • • New Gateway Initialization File Parameters
  • Parameters That Have Been Changed in Usage
  • Parameters That Have Been Renamed
  • Obsolete Parameters
• DRDA Server Considerations
• Oracle Net Considerations

15 Error Messages, Diagnosis, and Reporting

• Interpreting Gateway Error Messages
• • Errors Detected by the Oracle Integrating Server
  • Errors Detected by the Gateway
  • Errors Detected in the DRDA Software
  • Communication Errors
  • Errors Detected by the Server Database
• Mapped Errors
• Gateway Error Codes
• SQL Tracing and the Gateway
• • SQL Tracing in the Oracle Database
  • SQL Tracing in the Gateway

A Oracle DB2 Data Dictionary Views

• Supported Views
• Data Dictionary View Tables
• • ALL_CATALOG
  • ALL_COL_COMMENTS
  • ALL_CONS_COLUMNS
  • ALL_CONSTRAINTS
  • ALL_INDEXES
  • ALL_IND_COLUMNS
  • ALL_OBJECTS
  • ALL_SYNONYMS
  • ALL_TABLES
  • ALL_TAB_COLUMNS
  • ALL_TAB_COMMENTS
  • ALL_USERS
  • ALL_VIEWS
  • COLUMN_PRIVILEGES
  • DICTIONARY
  • DUAL
  • TABLE_PRIVILEGES
  • USER_CATALOG
  • USER_COL_COMMENTS
  • USER_CONSTRAINTS
  • USER_CONS_COLUMNS
  • USER_INDEXES
  • USER_OBJECTS
  • USER_SYNONYMS
  • USER_TABLES
  • USER_TAB_COLUMNS
  • USER_TAB_COMMENTS
  • USER_USERS
  • USER_VIEWS

B Sample Files

• Sample gateway initialization file
• Sample Oracle Net tnsnames.ora File
• Sample Oracle Net listener.ora File

C DRDA-Specific Parameters

• Modifying the Gateway Initialization File
• Setting Parameters in the Gateway Initialization File
• Syntax and Usage
• Gateway Initialization File Parameters
• • DRDA_CACHE_TABLE_DESC
  • DRDA_CAPABILITY
  • DRDA_CODEPAGE_MAP
  • DRDA_COMM_BUFLEN
  • DRDA_CONNECT_PARM (SNA format)
  • DRDA_CONNECT_PARM (TCP/IP format)
  • DRDA_CMSRC_CM_IMMEDIATE
  • DRDA_DEFAULT_CCSID
  • DRDA_DESCRIBE_TABLE
  • DRDA_DISABLE_CALL
  • DRDA_FLUSH_CACHE
  • DRDA_GRAPHIC_PAD_SIZE
  • DRDA_GRAPHIC_LIT_CHECK
  • DRDA_GRAPHIC_TO_MBCS
  • DRDA_GRAPHIC_CHAR_SIZE
  • DRDA_ISOLATION_LEVEL
  • DRDA_LOCAL_NODE_NAME
  • DRDA_MBCS_TO_GRAPHIC
  • DRDA_OPTIMIZE_QUERY
  • DRDA_PACKAGE_COLLID
  • DRDA_PACKAGE_CONSTOKEN
  • DRDA_PACKAGE_NAME
  • DRDA_PACKAGE_OWNER
  • DRDA_PACKAGE_SECTIONS
  • DRDA_READ_ONLY
  • DRDA_RECOVERY_PASSWORD
  • DRDA_RECOVERY_USERID
  • DRDA_REMOTE_DB_NAME
  • DRDA_SECURITY_TYPE
  • FDS_CLASS
  • FDS_CLASS_VERSION
  • FDS_INSTANCE
  • HS_FDS_FETCH_ROWS
  • HS_LANGUAGE
  • HS_NLS_NCHAR
  • LOG_DESTINATION
  • ORA_MAX_DATE
  • ORA_NLS10
  • ORACLE_DRDA_TCTL
  • ORACLE_DRDA_TRACE
  • TRACE_LEVEL

D National Language Support

• Overview of NLS Interactions
• Client and Oracle Integrating Server Configuration
• Gateway Language Interaction with DRDA Server
• • Gateway Configuration
  • NLS Parameters in the Gateway Initialization File
  • • HS_LANGUAGE
    • HS_NLS_NCHAR
    • HS_NLS_DATE_FORMAT
    • HS_NLS_DATE_LANGUAGE
• Gateway Codepage Map Facility
• Multibyte and Double-Byte Support in the Gateway
• Message Availability
• Example of NLS Configuration

E Configuration Worksheet

F Quick Reference to Oracle SQL Functions

G Sample Applications

• DB2INS
• ORAIND

Index