Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Related Documents

SQL*Plus Prompts

Storage Measurements

1 Introduction

1.1 Introduction to the Oracle Transparent Gateway

1.1.1 Protection of Current Investment

1.2 Release 10g Gateways

1.2.1 Advantages of the Gateway

1.3 Gateway Capabilities

1.3.1 Transparency at All Levels

1.3.2 Extended Database Services

1.3.3 Extended Advanced Networking, Internet and Intranet Support

1.3.4 Dynamic Dictionary Mapping

1.3.6 Data Definition Language

1.3.7 Data Control Language

1.3.8 Passthrough and Native DB2 SQL

1.3.9 Stored Procedures

1.3.9.1 Oracle Stored Procedures

1.3.9.2 Native DB2 Stored Procedures

1.3.10 Languages

1.3.11 Oracle Database Server Technology and Tools

1.3.12 SQL*Plus

1.3.13 Two-Phase Commit and multi-site Transactions

1.3.14 Site Autonomy

1.3.15 Migration and Coexistence

1.3.16 Security

1.5 Architecture

1.6 Implementation

1.7 How the Gateway Works

1.7.1 SQL Differences

1.8 Oracle Tools and the Gateway

1.9.1 Heterogeneous Services Architecture

1.9.2 Performance Enhancements

1.9.3 Fetch Reblocking

1.9.4 Oracle Database 10g Passthrough Supported

1.9.5 Retrieving Result Sets Through Passthrough

1.9.6 Support for TCP/IP

1.9.7 Native Semantics

1.9.8 Columns Supported in a Result Set

1.9.9 EXPLAIN_PLAN Improvement

1.9.10 Heterogeneous Database Integration

1.9.11 Minimum Impact on Existing Systems

1.9.12 Large Base of Data Access

1.9.13 Application Portability

1.9.14 Remote Data Access

1.9.15 Support for Distributed Applications

1.9.16 Application Development and End User Tools

1.9.17 Password Encryption Utility

1.9.18 Support for DB2/OS390 V6 and V7 Stored Procedures

1.9.19 Codepage Map Facility

1.9.20 IBM DB2 Universal Database Support

1.9.21 IBM DB2 Version 5.1 ASCII Tables

1.9.22 Read-Only Support

2 Release Information

2.1 Product Set

2.2 Changes and Enhancements

2.3 Bugs Fixed in Release 10

2.4 Known Problems

2.5 Known Restrictions

2.5.1 DB2 Considerations

2.5.2 SQL Limitations

3 System Requirements

3.1 Hardware Requirements

3.1.1 Processor

3.1.3 Network Attachment

3.1.4 CD-ROM Drive

3.1.5 Disk Space

3.2 Software Requirements

3.2.1 Operating System

3.2.2 DRDA Databases

3.2.3 Communications

3.2.4 Oracle Database server

3.2.5 Oracle Networking Products

3.3 Documentation Requirements

4 Installing the Gateway

4.1 Introduction

4.2 Before You Begin

4.3 Checklist for Gateway Installation

4.4 Installation Overview

4.5 Preinstallation

4.6 Installing the Gateway from CD-ROM

4.6.1 Step 1: Log on to the host

4.6.2 Step 2: Load the CD-ROM into the CD-ROM Drive

4.6.3 Step 3: Start the Oracle Universal Installer on Microsoft Windows

4.6.4 Step 4: Step through the Oracle Universal Installer

4.6.5 Step 5: Verify Installation Success

4.7 Installation Complete

4.7.1 Deinstalling the Gateway

5 Configuring the DRDA Server

5.1 Checklists for Configuring the DRDA Server

5.1.1 DB2/OS390

5.1.3 DB2/UDB (Universal Database)

5.2.1 Step 1: Configure the Communications Server

5.2.2 Step 2: Define the user ID that owns the package

5.2.3 Step 3: Define the recovery user ID

5.2.4 Step 4: Determine DRDA location name for DB2 instance

5.2.5 Step 5: Configure DB2 Distributed Data Facility for gateway

5.3.1 Step 1: Configure the Communications Server

5.3.2 Step 2: Define the user ID that owns the package

5.3.3 Step 3: Define the recovery user ID

5.3.4 Step 4: Determine DRDA location name for DB2/400 instance

5.4 DB2/UDB (Universal Database)

5.4.1 Step 1: Configure the SNA Communications Server

5.4.2 Step 2: Define the user ID that owns the package

5.4.3 Step 3: Define the recovery user ID

5.4.4 Step 4: Determine DRDA location name for DB2/UDB instance

5.5.1 Step 1: Configure the Communications Server

5.5.2 Step 2: Define the user ID that owns the package

5.5.3 Step 3: Define the recovery user ID

5.5.4 Step 4: Determine DRDA location name for DB2/VM instance

6 Configuring Microsoft SNA Server or Host Integration Server

6.1 Before You Begin

6.2 Steps for Configuring the Communications Interfaces

6.3 Creating SNA Server Profiles for the Gateway

6.3.1 Independent Versus Dependent LUs

6.4 Creating SNA Definitions for the Gateway

6.4.1 Sample SNA Server Definitions

6.4.2 Definition Types

6.4.3 SNA Server Definitions

6.4.3.1 Server Selection

6.4.3.2 Service Properties

6.4.3.3 Link Service Definition

6.4.3.4 Connection Definition

6.4.3.5 Local LU Definition

6.4.3.6 Mode Definition

6.4.3.7 Remote LU Definition

6.4.3.8 CPI-C Symbolic Destination Names

6.5 Testing the Connection

6.6 Using SNA Session Security Validation

6.7 SNA Conversation Security

6.7.1 SNA Security Option SECURITY=PROGRAM

6.7.2 SNA Security Option SECURITY=SAME

7 Configuring IBM Communication Server

7.1 Before You Begin

7.2 Checklist for Configuring the Communications Interfaces

7.3 Creating IBM Communication Server Profiles for the Gateway

7.3.1 Independent Versus Dependent LUs

7.3.2 Creating SNA Definitions for the Gateway

7.3.2.1 Sample IBM Communication Server Definitions

7.4 Definition Types

7.4.1 IBM Communication Server Definitions

7.4.1.1 Creating the Configuration

7.4.1.2 Defining the Node

7.5 Testing the Connection

7.6 Using SNA Session Security Validation

7.7 SNA Conversation Security

7.7.1 SNA Security Option SECURITY=PROGRAM

7.7.2 SNA Security Option SECURITY=SAME

8 Configuring TCP/IP

8.1 Before You Begin

8.1.1 Port Number

8.2 Configuring TCP/IP

9 Oracle Net

9.1 Checklists for Oracle Net

9.1.1 Configuring Oracle Net

9.1.2 Advanced Security Encryption

9.1.2.1 Setting Up Advanced Security Encryption for Test

9.1.2.2 Testing Advanced Security Encryptions

9.2 Oracle Net and SQL*Net Introduction

9.3 Oracle Net Overview

9.3.1 Distributed Processing.

9.3.2 Distributed Database

9.3.3 Terminology for Oracle Net

9.4 Configuring Oracle Net

9.4.1 Step 1: Modify listener.ora file

9.4.2 Step 2: Modify tnsnames.ora file

9.5 Advanced Security Encryption

9.6 Setting Up Advanced Security Encryption for Test

9.6.1 Step 1: Set Advanced Security Encryption Parameters for the Gateway

9.6.2 Step 2: Set Advanced Security Encryption Parameters

9.7 Testing Advanced Security Encryptions

9.7.1 Step 1: Connect Gateway and Oracle Integrating Server

9.7.2 Step 2: Reset Configuration Parameters on the Gateway

10 Configuring the Gateway

10.1 Configuration Checklist

10.2 Choosing a Gateway System Identifier (SID)

10.2.1 Enter the SID on the Worksheet

10.3 Gateway Configuration

10.4 Configuring the Host

10.4.1 Step 1: Copy the gateway initialization

10.4.2 Step 2: Determine settings for gateway initialization parameters

10.4.2.1 Required Parameters

10.4.2.2 Optional Parameters

10.4.3 Step 3: Tailor the initsid.ora File

10.4.4 Binding the DRDA Gateway Package

10.4.5 Binding Packages on DB2/Universal Database (DB2/UDB)

10.5 DRDA Gateway Package Considerations

10.5.1 Before Binding the DRDA Gateway Package

10.5.1.1 Step 1: Check all DRDA parameter settings

10.5.1.2 Step 2: If using DB2/UDB, then create ORACLE2PC table

10.5.2 Sample SQL scripts

10.5.2.1 Step 1: Run data dictionary scripts

10.5.2.2 Step 1a: Upgrading from a previous gateway version

10.5.2.3 Step 1b: Creating the Data Dictionary tables and views

10.5.2.4 Step 2: DB2/UDB or other server

10.5.2.5 Step 2a: If server is DB2/UDB, grant authority to package

10.5.2.6 Step 2b: If server is not DB2/UDB, create the ORACLE2PC table

10.6 Backup and Recovery of Gateway Configuration

10.7 Configuring the Oracle Integrating Server

10.7.1 Step 1: Create a database link

10.7.2 Step 2: Create synonyms and views

10.8 Accessing the Gateway from Other Oracle Servers

10.9 Accessing Other DRDA Servers

10.10 Gateway Installation and Configuration Complete

11 Using the Gateway

11.1 Processing a Database Link

11.1.1 Creating Database Links

11.1.2 Guidelines for Database Links

11.1.3 Dropping Database Links

11.1.4 Examining Available Database Links

11.1.5 Limiting the Number of Active Database Links

11.2 Accessing the Gateway

11.2.1 Step 1: Login to the Oracle Integrating Server

11.2.2 Step 2: Create a database link to the DRDA database

11.2.3 Step 3: Retrieve data from the DRDA database

11.3 Accessing AS/400 File Members

11.4 Using the Synonym Feature

11.5 Performing Distributed Queries

11.5.1 Example of a Distributed Query

11.5.2 Two-Phase Commit Processing

11.5.3 Distributed DRDA Transactions

11.6 Read-Only Gateway

11.7 Replicating in a Heterogeneous Environment

11.7.1 Oracle Database 10g Server Triggers

11.7.2 Oracle Snapshots

11.8 Copying Data from the Oracle Server to the DRDA Server

11.9 Copying Data from the DRDA Server to the Oracle Server

11.10 Tracing SQL Statements

12 Developing Applications

12.1 Gateway Appearance to Application Programs

12.1.1 Fetch Reblocking

12.2 Using Oracle Stored Procedures with the Gateway

12.3 Using DRDA Server Stored Procedures with the Gateway

12.3.1 Oracle Application and DRDA Server Stored Procedure Completion

12.3.2 Procedural Feature Considerations with DB2

12.4 Database Link Behavior

12.5 Oracle Server SQL Construct Processing

12.5.1 Compatible SQL Functions

12.5.2 Translated SQL Functions

12.5.3 Compensated SQL Functions

12.5.4 Native Semantic SQL Functions

12.5.5 DB2/OS390 SQL Compatibility

12.5.6 DB2/Universal Database SQL Compatibility

12.5.7 DB2/400 SQL Compatibility

12.5.8 DB2/VM SQL Compatibility

12.6 Native Semantics

12.6.1 SQL Functions That Can Be Enabled

12.6.2 SQL Functions That Can Be Disabled

12.6.3 SQL Set Operators and Clauses

12.7 DRDA Datatype to Oracle Datatype Conversion

12.7.1 Performing Character String Operations

12.7.2 Converting Character String Datatypes

12.7.3 Performing Graphic String Operations

12.7.4 Performing Date and Time Operations

12.7.4.1 Processing TIME and TIMESTAMP Data

12.7.4.2 Processing DATE Data

12.7.4.3 Performing Date Arithmetic

12.7.6 HS_NLS_DATE_FORMAT Support

12.7.7 Oracle TO_DATE Function

12.7.8 Performing Numeric Datatype Operations

12.7.9 Mapping the COUNT Function

12.7.10 Performing Zoned Decimal Operations

12.8 Passing Native SQL Statements through the Gateway

12.8.1 Processing DDL Statements through Passthrough

12.8.2 Using the DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE Function

12.8.2.1 Examples

12.8.3 Retrieving Results Sets Through Passthrough

12.8.3.1 Example

12.9 Oracle Data Dictionary Emulation on a DRDA Server

12.9.1 Using the Gateway Data Dictionary

12.9.2 Using the DRDA Catalog

12.10 Defining the Number of DRDA Cursors

13 Security Considerations

13.1 Security Overview

13.2 Authenticating Application Logons

13.3 Defining and Controlling Database Links

13.3.1 Link Accessibility

13.3.2 Links and CONNECT Clauses

13.4 TCP/IP Security

13.5 Processing Inbound Connections

13.5.1 User ID Mapping

13.5.1.1 DB2/OS390

13.5.1.2 DB2/VM

13.5.1.3 DB2/400

13.5.1.4 DB2/Universal Database

13.6 Passwords in the Gateway Initialization File

13.7 Using the g4drpwd Utility

14 Migration and Coexistence with Existing Gateways

14.1 Migrating Existing V4, V8, or V9 Gateway Instances to New Release

14.1.1 Step 1: Install the new Release

14.1.2 Step 2: Transferring initsid.gtwboot Gateway Boot Initialization parameters.

14.1.3 Step 3: Transferring initsid.ora Gateway Initialization File parameters.

14.2 Backout Considerations When Migrating to New Releases

14.3 New and Changed Parameters When Migrating to Release 10

14.3.1 New Parameters

14.3.1.1 New Gateway Initialization File Parameters

14.3.2 Parameters That Have Been Changed in Usage

14.3.3 Parameters That Have Been Renamed

14.3.4 Obsolete Parameters

14.4 DRDA Server Considerations

14.5 Oracle Net Considerations

15 Error Messages, Diagnosis, and Reporting

15.1 Interpreting Gateway Error Messages

15.1.1 Errors Detected by the Oracle Integrating Server

15.1.2 Errors Detected by the Gateway

15.1.3 Errors Detected in the DRDA Software

15.1.4 Communication Errors

15.1.5 Errors Detected by the Server Database

15.2 Mapped Errors

15.3 Gateway Error Codes

15.4 SQL Tracing and the Gateway

15.4.1 SQL Tracing in the Oracle Database

15.4.2 SQL Tracing in the Gateway

A Oracle DB2 Data Dictionary Views

A.1 Supported Views

A.2 Data Dictionary View Tables

A.2.1 ALL_CATALOG

A.2.2 ALL_COL_COMMENTS

A.2.3 ALL_CONS_COLUMNS

A.2.4 ALL_CONSTRAINTS

A.2.5 ALL_INDEXES

A.2.6 ALL_IND_COLUMNS

A.2.7 ALL_OBJECTS

A.2.8 ALL_SYNONYMS

A.2.9 ALL_TABLES

A.2.10 ALL_TAB_COLUMNS

A.2.11 ALL_TAB_COMMENTS

A.2.12 ALL_USERS

A.2.13 ALL_VIEWS

A.2.14 COLUMN_PRIVILEGES

A.2.15 DICTIONARY

A.2.17 TABLE_PRIVILEGES

A.2.18 USER_CATALOG

A.2.19 USER_COL_COMMENTS

A.2.20 USER_CONSTRAINTS

A.2.21 USER_CONS_COLUMNS

A.2.22 USER_INDEXES

A.2.23 USER_OBJECTS

A.2.24 USER_SYNONYMS

A.2.25 USER_TABLES

A.2.26 USER_TAB_COLUMNS

A.2.27 USER_TAB_COMMENTS

A.2.28 USER_USERS

A.2.29 USER_VIEWS

B Sample Files

B.1 Sample Gateway Initialization File

B.2 Sample Oracle Net tnsnames.ora File

B.3 Sample Oracle Net listener.ora File

C DRDA-Specific Parameters

C.1 Modifying the Gateway Initialization File

C.2 Setting Parameters in the Gateway Initialization File

C.3 Syntax and Usage

C.4 Gateway Initialization File Parameters

C.4.1 DRDA_CACHE_TABLE_DESC

C.4.2 DRDA_CAPABILITY

C.4.3 DRDA_CODEPAGE_MAP

C.4.4 DRDA_COMM_BUFLEN

C.4.5 DRDA_CONNECT_PARM (SNA format)

C.4.6 DRDA_CONNECT_PARM (TCP/IP format)

C.4.7 DRDA_CMSRC_CM_IMMEDIATE

C.4.8 DRDA_DEFAULT_CCSID

C.4.9 DRDA_DESCRIBE_TABLE

C.4.10 DRDA_DISABLE_CALL

C.4.11 DRDA_FLUSH_CACHE

C.4.12 DRDA_GRAPHIC_PAD_SIZE

C.4.13 DRDA_GRAPHIC_LIT_CHECK

C.4.14 DRDA_GRAPHIC_TO_MBCS

C.4.15 DRDA_ISOLATION_LEVEL

C.4.16 DRDA_LOCAL_NODE_NAME

C.4.17 DRDA_MBCS_TO_GRAPHIC

C.4.18 DRDA_OPTIMIZE_QUERY

C.4.19 DRDA_PACKAGE_COLLID

C.4.20 DRDA_PACKAGE_CONSTOKEN

C.4.21 DRDA_PACKAGE_NAME

C.4.22 DRDA_PACKAGE_OWNER

C.4.23 DRDA_PACKAGE_SECTIONS

C.4.24 DRDA_READ_ONLY

C.4.25 DRDA_RECOVERY_PASSWORD

C.4.26 DRDA_RECOVERY_USERID

C.4.27 DRDA_REMOTE_DB_NAME

C.4.28 DRDA_SECURITY_TYPE

C.4.29 FDS_CLASS

C.4.30 FDS_CLASS_VERSION

C.4.31 FDS_INSTANCE

C.4.32 HS_FDS_FETCH_ROWS

C.4.33 HS_LANGUAGE

C.4.34 HS_NLS_NCHAR

C.4.35 LOG_DESTINATION

C.4.36 ORA_MAX_DATE

C.4.37 ORA_NLS33

C.4.38 ORACLE_DRDA_TCTL

C.4.39 ORACLE_DRDA_TRACE

C.4.40 TRACE_LEVEL

D National Language Support

D.1 Overview of NLS Interactions

D.2 Client and Oracle Integrating Server Configuration

D.3 Gateway Language Interaction with DRDA Server

D.3.1 Gateway Configuration

D.3.2 NLS Parameters in the Gateway Initialization File

D.3.2.1 HS_LANGUAGE

D.3.2.2 HS_NLS_NCHAR

D.3.2.3 HS_NLS_DATE_FORMAT

D.3.2.4 HS_NLS_DATE_LANGUAGE

D.4 Gateway Codepage Map Facility

D.5 Multi-Byte and Double-Byte Support in the Gateway

D.6 Message Availability

D.7 Example of NLS Configuration

E Configuration Worksheet

F Quick Reference to Oracle SQL Functions

G Sample Applications

Index