Oracle® Transparent Gateway for DRDA
Installation and User’s Guide
10g Release 1 (10.1.0.2.0) for Microsoft Windows
Part Number B12010-01
March 2004
Oracle Transparent Gateway for DRDA Installation and User's Guide, 10g Release 1 (10.1.0.2.0) for Microsoft
Windows
Part Number B12010-01
Copyright © 2001, 2004, Oracle. All rights reserved.
Primary Author: Platform Technologies Division
The Programs (which include both the software and documentation) contain proprietary information; they
are provided under a license agreement containing restrictions on use and disclosure and are also protected
by copyright, patent, and other intellectual and industrial property laws. Reverse engineering, disassembly,
or decompilation of the Programs, except to the extent required to obtain interoperability with other
independently created software or as specified by law, is prohibited.
The information contained in this document is subject to change without notice. If you find any problems in
the documentation, please report them to us in writing. This document is not warranted to be error-free.
Except as may be expressly permitted in your license agreement for these Programs, no part of these
Programs may be reproduced or transmitted in any form or by any means, electronic or mechanical, for any
purpose.
If the Programs are delivered to the United States Government or anyone licensing or using the Programs on
behalf of the United States Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data
delivered to U.S. Government customers are "commercial computer software" or "commercial technical data"
pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As
such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation
and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license
agreement, and, to the extent applicable, the additional rights set forth in FAR 52.227-19, Commercial
Computer Software Restricted Rights (June 1987). Oracle Corporation, 500 Oracle Parkway, Redwood City,
CA 94065
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently

1.3 Gateway Capabilities 1-3
1.3.1 Transparency at All Levels 1-3
1.3.2 Extended Database Services 1-4
1.3.3 Extended Advanced Networking, Internet and Intranet Support 1-4
1.3.4 Dynamic Dictionary Mapping 1-5
1.3.5 SQL 1-5
1.3.6 Data Definition Language 1-5
1.3.7 Data Control Language 1-5
1.3.8 Passthrough and Native DB2 SQL 1-5
1.3.9 Stored Procedures 1-6
1.3.9.1 Oracle Stored Procedures 1-6
1.3.9.2 Native DB2 Stored Procedures 1-6
1.3.10 Languages 1-6
1.3.11 Oracle Database Server Technology and Tools 1-6
1.3.12 SQL*Plus 1-6
1.3.13 Two-Phase Commit and multi-site Transactions 1-6
1.3.14 Site Autonomy 1-7
1.3.15 Migration and Coexistence 1-7
iv
1.3.16 Security 1-7
1.4 Terms 1-7
1.5 Architecture 1-8
1.6 Implementation 1-9
1.7 How the Gateway Works 1-9
1.7.1 SQL Differences 1-10
1.8 Oracle Tools and the Gateway 1-10
1.8.1 SQL*Plus 1-10
1.9 Features 1-11
1.9.1 Heterogeneous Services Architecture 1-11
1.9.2 Performance Enhancements 1-11

3.1.1 Processor 3-2
3.1.2 Memory 3-2
3.1.3 Network Attachment 3-2
3.1.4 CD-ROM Drive 3-2
v
3.1.5 Disk Space 3-2
3.2 Software Requirements 3-2
3.2.1 Operating System 3-3
3.2.2 DRDA Databases 3-3
3.2.3 Communications 3-3
3.2.4 Oracle Database server 3-3
3.2.5 Oracle Networking Products 3-4
3.3 Documentation Requirements 3-4
4 Installing the Gateway
4.1 Introduction 4-2
4.2 Before You Begin 4-2
4.3 Checklist for Gateway Installation 4-2
4.4 Installation Overview 4-3
4.5 Preinstallation 4-3
4.6 Installing the Gateway from CD-ROM 4-3
4.6.1 Step 1: Log on to the host 4-3
4.6.2 Step 2: Load the CD-ROM into the CD-ROM Drive 4-3
4.6.3 Step 3: Start the Oracle Universal Installer on Microsoft Windows 4-3
4.6.4 Step 4: Step through the Oracle Universal Installer 4-4
4.6.5 Step 5: Verify Installation Success 4-4
4.7 Installation Complete 4-4
4.7.1 Deinstalling the Gateway 4-4
5 Configuring the DRDA Server
5.1 Checklists for Configuring the DRDA Server 5-2
5.1.1 DB2/OS390 5-2

6.4 Creating SNA Definitions for the Gateway 6-3
6.4.1 Sample SNA Server Definitions 6-3
6.4.2 Definition Types 6-3
6.4.3 SNA Server Definitions 6-4
6.4.3.1 Server Selection 6-4
6.4.3.2 Service Properties 6-5
6.4.3.3 Link Service Definition 6-5
6.4.3.4 Connection Definition 6-7
6.4.3.5 Local LU Definition 6-9
6.4.3.6 Mode Definition 6-11
6.4.3.7 Remote LU Definition 6-13
6.4.3.8 CPI-C Symbolic Destination Names 6-14
6.5 Testing the Connection 6-15
6.6 Using SNA Session Security Validation 6-16
6.7 SNA Conversation Security 6-17
6.7.1 SNA Security Option SECURITY=PROGRAM 6-17
6.7.2 SNA Security Option SECURITY=SAME 6-17
7 Configuring IBM Communication Server
7.1 Before You Begin 7-2
7.2 Checklist for Configuring the Communications Interfaces 7-2
7.3 Creating IBM Communication Server Profiles for the Gateway 7-2
7.3.1 Independent Versus Dependent LUs 7-2
7.3.2 Creating SNA Definitions for the Gateway 7-3
7.3.2.1 Sample IBM Communication Server Definitions 7-3
7.4 Definition Types 7-3
7.4.1 IBM Communication Server Definitions 7-3
7.4.1.1 Creating the Configuration 7-4
7.4.1.2 Defining the Node 7-5
7.5 Testing the Connection 7-19
7.6 Using SNA Session Security Validation 7-20

10.1 Configuration Checklist 10-2
10.2 Choosing a Gateway System Identifier (SID) 10-4
10.2.1 Enter the SID on the Worksheet 10-4
10.3 Gateway Configuration 10-4
10.4 Configuring the Host 10-4
10.4.1 Step 1: Copy the gateway initialization 10-4
10.4.2 Step 2: Determine settings for gateway initialization parameters 10-5
10.4.2.1 Required Parameters 10-5
10.4.2.2 Optional Parameters 10-5
10.4.3 Step 3: Tailor the initsid.ora File 10-5
10.4.4 Binding the DRDA Gateway Package 10-6
10.4.5 Binding Packages on DB2/Universal Database (DB2/UDB) 10-7
10.5 DRDA Gateway Package Considerations 10-7
10.5.1 Before Binding the DRDA Gateway Package 10-8
10.5.1.1 Step 1: Check all DRDA parameter settings 10-8
10.5.1.2 Step 2: If using DB2/UDB, then create ORACLE2PC table 10-8
10.5.2 Sample SQL scripts 10-8
10.5.2.1 Step 1: Run data dictionary scripts 10-8
viii
10.5.2.2 Step 1a: Upgrading from a previous gateway version 10-8
10.5.2.3 Step 1b: Creating the Data Dictionary tables and views 10-8
10.5.2.4 Step 2: DB2/UDB or other server 10-8
10.5.2.5 Step 2a: If server is DB2/UDB, grant authority to package 10-9
10.5.2.6 Step 2b: If server is not DB2/UDB, create the ORACLE2PC table 10-9
10.6 Backup and Recovery of Gateway Configuration 10-9
10.7 Configuring the Oracle Integrating Server 10-9
10.7.1 Step 1: Create a database link 10-9
10.7.2 Step 2: Create synonyms and views 10-9
10.8 Accessing the Gateway from Other Oracle Servers 10-9
10.9 Accessing Other DRDA Servers 10-10

12.3.1 Oracle Application and DRDA Server Stored Procedure Completion 12-4
12.3.2 Procedural Feature Considerations with DB2 12-5
12.4 Database Link Behavior 12-5
12.5 Oracle Server SQL Construct Processing 12-6
ix
12.5.1 Compatible SQL Functions 12-6
12.5.2 Translated SQL Functions 12-6
12.5.3 Compensated SQL Functions 12-6
12.5.4 Native Semantic SQL Functions 12-7
12.5.5 DB2/OS390 SQL Compatibility 12-7
12.5.6 DB2/Universal Database SQL Compatibility 12-9
12.5.7 DB2/400 SQL Compatibility 12-12
12.5.8 DB2/VM SQL Compatibility 12-15
12.6 Native Semantics 12-17
12.6.1 SQL Functions That Can Be Enabled 12-18
12.6.2 SQL Functions That Can Be Disabled 12-19
12.6.3 SQL Set Operators and Clauses 12-19
12.7 DRDA Datatype to Oracle Datatype Conversion 12-19
12.7.1 Performing Character String Operations 12-20
12.7.2 Converting Character String Datatypes 12-21
12.7.3 Performing Graphic String Operations 12-21
12.7.4 Performing Date and Time Operations 12-21
12.7.4.1 Processing TIME and TIMESTAMP Data 12-21
12.7.4.2 Processing DATE Data 12-22
12.7.4.3 Performing Date Arithmetic 12-22
12.7.5 Dates 12-23
12.7.6 HS_NLS_DATE_FORMAT Support 12-23
12.7.7 Oracle TO_DATE Function 12-24
12.7.8 Performing Numeric Datatype Operations 12-24
12.7.9 Mapping the COUNT Function 12-24

14.1.2 Step 2: Transferring initsid.gtwboot Gateway Boot Initialization parameters. 14-2
14.1.3 Step 3: Transferring initsid.ora Gateway Initialization File parameters. 14-2
14.2 Backout Considerations When Migrating to New Releases 14-2
14.3 New and Changed Parameters When Migrating to Release 10 14-3
14.3.1 New Parameters 14-3
14.3.1.1 New Gateway Initialization File Parameters 14-3
14.3.2 Parameters That Have Been Changed in Usage 14-3
14.3.3 Parameters That Have Been Renamed 14-4
14.3.4 Obsolete Parameters 14-4
14.4 DRDA Server Considerations 14-4
14.5 Oracle Net Considerations 14-5
15 Error Messages, Diagnosis, and Reporting
15.1 Interpreting Gateway Error Messages 15-2
15.1.1 Errors Detected by the Oracle Integrating Server 15-2
15.1.2 Errors Detected by the Gateway 15-2
15.1.3 Errors Detected in the DRDA Software 15-2
15.1.4 Communication Errors 15-3
15.1.5 Errors Detected by the Server Database 15-3
15.2 Mapped Errors 15-4
15.3 Gateway Error Codes 15-4
15.4 SQL Tracing and the Gateway 15-6
15.4.1 SQL Tracing in the Oracle Database 15-6
15.4.2 SQL Tracing in the Gateway 15-6
A Oracle DB2 Data Dictionary Views
A.1 Supported Views A-2
A.2 Data Dictionary View Tables A-3
A.2.1 ALL_CATALOG A-3
A.2.2 ALL_COL_COMMENTS A-3
A.2.3 ALL_CONS_COLUMNS A-3
A.2.4 ALL_CONSTRAINTS A-4

C DRDA-Specific Parameters
C.1 Modifying the Gateway Initialization File C-2
C.2 Setting Parameters in the Gateway Initialization File C-2
C.3 Syntax and Usage C-2
C.4 Gateway Initialization File Parameters C-2
C.4.1 DRDA_CACHE_TABLE_DESC C-2
C.4.2 DRDA_CAPABILITY C-3
C.4.3 DRDA_CODEPAGE_MAP C-3
C.4.4 DRDA_COMM_BUFLEN C-3
C.4.5 DRDA_CONNECT_PARM (SNA format) C-3
C.4.6 DRDA_CONNECT_PARM (TCP/IP format) C-3
C.4.7 DRDA_CMSRC_CM_IMMEDIATE C-4
C.4.8 DRDA_DEFAULT_CCSID C-4
C.4.9 DRDA_DESCRIBE_TABLE C-4
C.4.10 DRDA_DISABLE_CALL C-5
C.4.11 DRDA_FLUSH_CACHE C-5
C.4.12 DRDA_GRAPHIC_PAD_SIZE C-5
C.4.13 DRDA_GRAPHIC_LIT_CHECK C-5
C.4.14 DRDA_GRAPHIC_TO_MBCS C-6
C.4.15 DRDA_ISOLATION_LEVEL C-6
xii
C.4.16 DRDA_LOCAL_NODE_NAME C-6
C.4.17 DRDA_MBCS_TO_GRAPHIC C-7
C.4.18 DRDA_OPTIMIZE_QUERY C-7
C.4.19 DRDA_PACKAGE_COLLID C-7
C.4.20 DRDA_PACKAGE_CONSTOKEN C-8
C.4.21 DRDA_PACKAGE_NAME C-8
C.4.22 DRDA_PACKAGE_OWNER C-8
C.4.23 DRDA_PACKAGE_SECTIONS C-9
C.4.24 DRDA_READ_ONLY C-9

xiii
E Configuration Worksheet
F Quick Reference to Oracle SQL Functions
G Sample Applications
G.1 DB2INS G-2
G.2 ORAIND G-4
Index
xiv
xv
Send Us Your Comments
Oracle Transparent Gateway for DRDA Installation and User's Guide, 10g Release
1 (10.1.0.2.0) for Microsoft Windows
Part Number B12010-01
Oracle welcomes your comments and suggestions on the quality and usefulness of this
publication. Your input is an important part of the information used for revision.
■ Did you find any errors?
■ Is the information clearly presented?
■ Do you need more information? If so, where?
■ Are the examples correct? Do you need more examples?
■ What features did you like most about this manual?
If you find any errors or have any other suggestions for improvement, please indicate
the title and part number of the documentation and the chapter, section, and page
number (if available). You can send comments to us at the following e-mail address:
[email protected]
If you would like a reply, please give your name, address, telephone number, and
electronic mail address (optional).
If you have problems with the software, please contact your local Oracle Support
Services.
xvi
xvii

facilitate access by the disabled community. Standards will continue to evolve over
time, and Oracle is actively engaged with other market-leading technology vendors to
address technical obstacles so that our documentation can be accessible to all of our
customers. For additional information, visit the Oracle Accessibility Program Web site
at
http://www.oracle.com/accessibility/
Accessibility of Code Examples in Documentation JAWS, a Windows screen reader,
may not always correctly read the code examples in this document. The conventions
for writing code require that closing braces should appear on an otherwise empty line;
however, JAWS may not always read a line of text that consists solely of a bracket or
brace.
Related Documents
The Oracle Transparent Gateway for DRDA Installation and User's Guide (for
Microsoft Windows) is included as part of your product. Also included is:
Oracle Database Heterogeneous Connectivity Administrator's Guide
This guide contains information common to all heterogeneous gateways, including
important information on functions, parameters, and error messages.
Oracle Database Administrator's Guide
Oracle Database Concepts
Oracle Database Error Messages
Oracle Database Performance Tuning Guide
Oracle Database Security Guide
xix
Conventions
In this manual, "Windows" refers to any Microsoft Windows operating system.
In examples, an implied carriage return occurs at the end of each line, unless otherwise
noted. You must press the Return key at the end of a line of input.
Examples of input and output for the gateway and the Oracle environment are shown
in a special font:
> mkdir D:\ORACLE\your_name

Storage Measurements
Storage measurements use the following abbreviations:
■ KB, for kilobyte, which equals 1,024 bytes
■ MB, for megabyte, which equals 1,048,576 bytes
■ GB, for gigabyte, which equals 1,073,741,824 bytes
Introduction 1-1
1
Introduction
The Oracle Transparent Gateway for DRDA enables you to:
■ Integrate heterogeneous database management systems so that they appear as a
single homogeneous database system
■ Read and write data from Oracle applications to data in DB2/OS390, DB2/400,
DB2 Universal Database, DB2/VM, and IBM SQL/DS on VM databases in
addition to any Oracle Database server data.
Read this chapter for information about the architecture, uses, and features of the
Oracle Transparent Gateway for DRDA. It contains the following sections:
■ Introduction to the Oracle Transparent Gateway on page 1-2
■ Release 10g Gateways on page 1-2
■ Gateway Capabilities on page 1-3
■ Terms on page 1-7
■ Architecture on page 1-8
■ Implementation on page 1-9
■ How the Gateway Works on page 1-9
■ Oracle Tools and the Gateway on page 1-10
■ Features on page 1-11
Introduction to the Oracle Transparent Gateway
1-2 Oracle Transparent Gateway for DRDA Installation and User's Guide
1.1 Introduction to the Oracle Transparent Gateway
In today’s global economy, information is a company’s most valuable resource.
Whether you need to analyze new markets, tailor your products to meet local

Database 10g server than previous versions, enabling improved performance and
enhanced functionality while still providing transparent integration of Oracle and
non-Oracle data. For example, connection initialization information is available in the
local Oracle Database 10g server, reducing the number of round trips and the amount
of data sent over the network. SQL execution is also faster, because statements that are
issued by an application are parsed and translated once and can then be reused by
multiple applications.
Release 10g gateways leverage any enhancements in the Oracle Database 10g server,
and you can quickly extend those benefits to your non-Oracle data.
Gateway Capabilities
Introduction 1-3
1.2.1 Advantages of the Gateway
Oracle Transparent Gateway for DRDA enables Oracle applications to access the
DRDA Application Servers, such as DB2 for OS/390 (MVS), through Structured Query
Language (SQL). The gateway and the Oracle Database 10g server together create the
appearance that all data resides on a local Oracle Database 10g server, even though
data might be widely distributed. If data is moved from a DRDA Application Server
database to an Oracle Database server, then no changes in application design or
function are needed. The gateway handles all differences in both data types and SQL
functions between the application and the database.
1.3 Gateway Capabilities
Oracle Transparent Gateway for DRDA gives you the power to integrate your
heterogeneous system into a single, seamless environment. This integration enables
you to make full use of existing hardware and applications throughout your
corporate-wide environment. You can eliminate the need to rewrite applications for
each configuration, and you can avoid the tedious, error-prone process of manual data
transfer. Together with the Oracle tools, networking, and data server technology, the
Oracle Transparent Gateway for DRDA sets a high standard for seamless,
enterprise-wide information access.
Oracle Transparent Gateway for DRDA enables applications to read and update DB2

features. Advanced Oracle Database 10g server functions, such as outer joins, are
available even if the target data stores do not support them in a native
environment. The method by which the gateways are integrated with the Oracle
Database 10g server ensures that the newest features of each database release are
always available immediately to the gateway.
■ Distributed capabilities
Heterogeneous data can be integrated seamlessly because Oracle Database
distributed capabilities, such as JOIN and UNION, can be applied against
non-Oracle data without any special programming or mapping.
■ Distributed query optimization
The Oracle Database 10g server can utilize its advanced query optimization
techniques to ensure that SQL statements are executed efficiently against any of
your data. The data distribution and storage characteristics of local and remote
data are equally considered.
■ Two-phase commit protection
The Oracle server two-phase commit mechanism provides consistency across data
stores by ensuring that a transaction that spans data stores is still treated as a
single unit of work. Changes are not committed (or permanently stored) in any
data store unless the changes can be committed in all data stores that will be
affected.
■ Stored procedures and database triggers
The same Oracle stored procedures and database triggers can be used to access all
of your data, thereby ensuring uniform enforcement of your business rules across
the enterprise.
1.3.3 Extended Advanced Networking, Internet and Intranet Support
The gateway integration with the Oracle Database 10g server extends (to non-Oracle
data) the benefits of the Oracle Internet software and Oracle Net software and extends
the benefits of the Oracle client/server and server/server connectivity software. These
powerful features include:
■ Application server support

Inefficient duplication is not necessary with Oracle Transparent Gateway for DRDA.
The gateway uses the existing native dictionaries of each database. Your applications
access data using the dictionaries that are designed specifically for each database,
which means that no redundant dictionary ever needs to be created or maintained.
1.3.5 SQL
Oracle Transparent Gateways ease your application development and maintenance by
allowing you to access any data using a uniform set of SQL. Changes to the location,
storage characteristics, or table structure do not require any changes to your
applications. ANSI and ISO standard SQL are supported, along with powerful Oracle
extensions.
1.3.6 Data Definition Language
Oracle Applications can create tables in target data stores by using native data
definition language (DDL) statements.
1.3.7 Data Control Language
You can issue native data control language (DCL) statements from an Oracle
environment, allowing central administration of user privileges and access levels for
heterogeneous data stores.
1.3.8 Passthrough and Native DB2 SQL
Execution of native DB2 SQL can be passed through the gateway for execution directly
against DB2. This enables applications to send statements, such as a DB2 CREATE
TABLE, to the gateway for execution on a target DB2 system.