Index

A B C D E F G H I J K L M N O P Q R S T U V W Z

A

accessing AS/400 file members, 7.5

Advanced Security, 1.2.1.3

agent trace files

Oracle net feature, A.2

system changes when installing gateway, 1.8.1

ALL_CATALOG view, description, C.1

ALL_COL_COMMENTS view, description, C.2

ALL_CONS_COLUMNS view, description, C.3

ALL_CONSTRAINTS view, description, C.4

ALL_DB_LINKS data dictionary view, examining available database links, 7.1.6

ALL_IND_COLUMNS view, description, C.5

ALL_INDEXES view, description, C.6

ALL_OBJECTS view, description, C.7

ALL_SYNONYMS view, description, C.8

ALL_TAB_COLUMNS view, description, C.9

ALL_TAB_COMMENTS view, description, C.10

ALL_TABLES view, description, C.11

ALL_USERS view, description, C.12

ALL_VIEWS view, description, C.13

ALTER SESSION CLOSE DATABASE LINK statement, 7.1.3

ANO (Advanced Networking Option), gateway advantages, 1.2.1.3

API (applications program interface), distributed processing, 5.1

application development, database link, 9.1.1

application server support, 1.2.1.3

applications, SQL*Plus, 1.2.5

architecture, gateway, 1.7

array

definition, 9.2

processing

array block size, 6.2

tuning the gateway, 9.2

ARRAY BLOCK SIZE parameter

fetch reblocking, 9.2.1

setting optional parameters, 6.2

AS/400 database, accessing file members, 7.5

AUTOCREATE CONTROLLER parameter, 5.3.3

B

background processes in gateway, 1.7

bequeath mechanism, TCP/IP, 5.4

binary literal notation, restriction, 2.4.2.2

bind variables

Oracle bind variables become DB2/400 parameter markers, 9.11.1

restriction

binary literal notation, 2.4.2.2

columns defined with RAW data, 2.4.2.4

Oracle bind variables, 2.4.3.1

BLOCKSIZE data area

retrieving data, array processing, 6.2.1

setting optional parameters, array processing, 6.2

buffer size, ORA-28528 error, buffer too small for LONG data type, 2.4.3.4

C

CCSID

data conversion, 6.2.2

Globalization Support considerations, B.3

supported character sets, B.3.1

supported languages, B.3.2

supported territories, B.3.2

CFGTCP command, 5.7

changes to the system upon installation, 1.8.1

CHAR format, 9.8.5

character set

CCSID, data conversion, 6.2.2

CHGORATUN command, B.2.1

DB2/400 CCSID considerations, B.3

DB2/400 GRAPHIC support, setting NLS_LANG, 9.8.1, B.6

DBCS, double byte character set, 9.8.2

Globalization Support

character settings must be compatible, B.1

NLS_LANG specifies a single-byte character set, 9.8

supported character sets, B.3.1

GRAPHIC constants in SQL commands, 2.4.1.9

server and client configuration, B.4

SQL command to check character set of existing database, B.4

CHARACTER SET parameter, B.4

character strings

converting data types, 9.8.4

processing, 9.8.3

CHECKSUM command

Advanced Security, 1.2.1.3

CHGGTWDBG command

command description, 6.1.6

purpose, 6.1

CHGORANET command

assigning a port number, 5.4.2

changing parameter values in ORA(SQLNET), 5.1.5

changing parameters, A.1.2.1

purpose, 6.1

trace files, 5.1.5.1

CHGORAPJE command

command description, 6.1.4

prestarted jobs, 6.1.2

purpose, 6.1

CHGORATUN command

changing default BLOCKSIZE, 6.2.1

command description, 6.1.5

data conversion, 6.2.2

gateway configuration, B.2

language specification, B.2.1

number of DB2/400 cursors, 7.2

purpose, 6.1

CHGRECOPRF command

caution to synchronize recovery password, 6.1.8, 8.8

command description, 6.1.8

password for recovery user profile, 6.1.2

purpose, 6.1

CHGUSRPRF command, password for recovery user profile, 6.1.2

clauses

CONNECT BY, example of when not supported, 9.10.1

CONNECT TO, gateway security, 8.8

FOR UPDATE

SELECT statements without FOR UPDATE, 9.10.1

with no column names, 9.10.2

USING, creating a database link, 7.1.1

WHERE, split processing, 9.10.1

CLRPFM FILE command, clearing LOG file LISTENER member, A.1.1

code examples

accessing AS/400 file members, 7.5

accessing data through database links, 7.1.4

bind variable restrictions, 2.4.3.1

checking authority of QWTSETP program in QSYS library, 4.4

commands to give authority to gateway ID, 4.4

CONNECT TO user ID provides implicit qualification for unqualified tables, 7.1.4

converting character string data types, 9.8.4

copying data from DB2/400 to Oracle, 7.9

copying data with SQL*Plus commands, 7.8.2

date and time operations, 9.8.5

distributed query, SQL command one, 7.6.1.1

distributed query, SQL command two, 7.6.1.2

dropping database links, 7.1.5

executing stored procedures, 9.4.1

gateway appearance to application programs, 9.1

INSERT statement

from ASCII client, double-byte character support, 9.8.2.1

with FORCE_SB option, 9.8.2.1

interpreting gateway message formats, 10.1.2

journaling, 4.5.3

numeric data type operations, 9.8.7

Oracle bind variables, statement fragments not allowed, 9.11.1

passthrough function, 9.7.2

retrieving results sets through passthrough, 9.7.4.1

SQL expressions NOT supported for date arithmetic, 2.4.1.7

SQL*Plus command to check character set of an existing database, B.4

starting and stopping the gateway, 8.3.2

streams code for simple table replication, 8.9

to find primary authorization ID being used by gateway, using DB2/400 special registers, 9.12.2

triggers for updating non-Oracle data, 7.8.1

using the SQL*Plus COPY command, 7.8.2

coded character set identifier (CCSID), B.3

co-existence between gateways, 4.1

COLUMN_PRIVILEGES view, description, C.14

columns

DECIMAL, 9.8.7

INTEGER, 9.8.7

NUMERIC, 9.8.7

PACKED, 9.8.7

ROUTINE_NAME, 9.4.1

ROUTINE_SCHEMA, 9.4.1

SMALLINT, 9.8.7

commands

summary of gateway commands, 6.1

CFGTCP, troubleshooting TCP/IP configuration, 5.7

CHGGTWDBG

command description, 6.1.6

purpose, 6.1

CHGORANET

assigning port number, 5.4.2

command description, 6.1.3

purpose, 6.1

trace files, 5.1.5.1

CHGORANET, changing parameter values in ORA(SQLNET), 5.1.5

CHGORAPJE

command description, 6.1.4

purpose, 6.1

CHGORATUN

changing BLOCKSIZE, 6.2.1

command description, 6.1.5

data conversion, 6.2.2

gateway configuration, B.2

language specification, B.2.1

purpose, 6.1

CHGRECOPRF

caution to synchronize recovery password, 6.1.8, 8.8

purpose, 6.1

CLRPFM FILE, clearing LOG file LISTENER member, A.1.1

COPY

general restrictions, 2.4.1.5

note regarding limitations, 7.8.2

CREATE COLLECTION, setting Instance Name parameter during gateway installation, 4.4.3.2

CREATE MATERIALIZED VIEW, automatically and asynchronously copy DB2/400 server data, 7.9

CREATE MATERIALIZED VIEW, copying DB2/400 server data, 7.9

CREATE TABLE, copying DB2/400 server data, 7.9

CRTLF, accessing OS/400 file members, 7.5

CRTORADDB

command description, 6.1.7

purpose, 6.1

CRTORAGTWI, purpose, 6.1

CRTUSRPRF, message queue created in QUSRSYS library, 1.8.1

DSPLIND, displaying line description parameters, 5.3.3

DSPPGM, verifying installation and version of gateway, 4.5.2

ENDSBS, starting and stopping the gateway, 8.3

examples of codes and commands

see code examples

EXECUTE, using Oracle stored procedures, 1.2.3.1

INSERT, copying data from DB2/400 server, 7.9

JOIN, accessing tables in multiple databases, 9.1.3

ORAMON

gateway architecture, 1.7

starting the Oracle Gateway Monitor, 8.2

PING, verifying host name, 5.4.4

RUNSQLSTM, required for cloning an instance, 3.2.1

SBMJOB, removing IBM observability, 4.5.6

SQL*Plus

COPY, DB2/400 Server to Oracle server, 7.9

COPY, limitation when copying to the DB2/400 server, 7.8.2

COPY, Oracle server to DB2/400 Server, 7.8

INSERT, not supported to copy tables from Oracle server to gateway, 7.8.2

SQL, creating database links using Oracle Net, 7.1.2

STRORALSN, starting TCP/IP listener manually, 5.4.5

STRSBS, OS/400 command to start gateway, 1.7

WRKTRCLOG, make sure instance library is in library list before using this command, A.1.3

commit confirm protocol, 1.3

commit point site, in distributed transactions, 7.6.2

COMMIT_POINT_STRENGTH setting, 7.6.2

commitment control

considerations for using stored procedures with DB2/400, 9.4.2

journaling default setting, 4.5.1

compatible SQL functions, defined, 9.9

compensated SQL functions, defined, 9.9

CONCAT translated SQL function, 9.9

concurrent connections, TCP/IP, memory, 3.1.2

CONNECT BY clause

limitation, 9.10.1

not supported in SQL, 2.4.3.2

CONNECT TO clause

accessing data through database links, 7.1.4

accessing the gateway, 7.4

creating database links, 7.1.1

gateway security, 8.8

connection errors, 5.6

conventions for code input and output, Preface

convert SQL to make compatible with DB2/400 server, 1.8.2

COPY command

from Oracle server to DB2/400 Server

compared with triggers, 7.8

examples, 7.8.2

general restrictions, 2.4.1.5

note regarding limitations, 7.8.2

copying

data

from the DB2/400 server, 7.9

from the Oracle Database Server, 7.8.2

to DB2/400 server, 7.8

the gateway instance, CRTORAGTWI command, 6.1.2

CREATE COLLECTION command, installation procedure, setting required parameters, 4.4.3.2

CREATE DATABASE LINK command, and define a path to the gateway, 7.1.1

CREATE MATERIALIZED VIEW

command, automatically and asynchronously copy DB2/400 server data, 7.9

complete refresh, propagate a complete copy or a subset, 7.9

CREATE SNAPSHOT command, replaced by CREATE MATERIALIZED VIEW command, 7.9

CREATE TABLE command

copy data from DB2/400 server to Oracle Database server, 7.9

passthrough and native DB2/400 SQL, 1.2.2.1

CRTLF command, accessing OS/400 file members, 7.5

CRTORADDB command

description, 6.1.7

purpose, 6.1

CRTORAGTWI command

description, 6.1.2

instructions, 6.1.2

purpose, 6.1

CRTUSRPRF command, creating user profile, message queue created in QUSRSYS library, 1.8.1

cursor stability, journaling, 4.5.1

cursors for DB2/400, maximum number, 7.2

D

data control language (DCL), issue native DCL from Oracle environment, 1.2.2.1.2

data conversion, setting optional parameters, 6.2.2

data definition language (DDL), Oracle applications use native DDL, 1.2.2.1.1

data dictionary

create gateway, installation panel option, 6.1.2

creating views, batch job, 6.1.7

emulation in DB2/400 server, 9.12

installation and support, 4.4.3.2

post-installation steps, of gateway, 4.5

tables, restriction with v4 gateways, 2.4.1.3

using, 9.12.1

views

ALL_CATALOG, description, C.1

ALL_COL_COMMENTS, description, C.2

ALL_CONS_COLUMNS, description, C.3

ALL_CONSTRAINTS, description, C.4

ALL_DB_LINKS, examining available database links, 7.1.6

ALL_IND_COLUMNS, description, C.5

ALL_INDEXES, description, C.6

ALL_OBJECTS, description, C.7

ALL_SYNONYMS, description, C.8

ALL_TAB_COLUMNS, description, C.9

ALL_TAB_COMMENTS, description, C.10

ALL_TABLES, description, C.11

ALL_USERS, description, C.12

ALL_VIEWS, description, C.13

augmenting DB2/400 database catalogs, 9.12

COLUMN_PRIVILEGES, description, C.14

DBA_DB_LINKS, accessible only to users with DBA authorization, 7.1.6

DICTIONARY, description, C.15

DUAL, description, C.16

examining available database links, 7.1.6

OTGREGISTER, description, C.17

OTGREGISTER, finding primary authorization ID being used by gateway, 9.12.2

TABLE_PRIVILEGES, description, C.18

USER_CATALOG, description, C.19

USER_COL_COMMENTS, description, C.20

USER_CONS_COLUMNS, description, C.21

USER_CONSTRAINTS, description, C.22

USER_DB_LINKS, examining available database links, 7.1.6

USER_INDEXES, description, C.23

USER_OBJECTS, description, C.24

USER_SYNONYMS, description, C.25

USER_TAB_COLUMNS, description, C.26

USER_TAB_COMMENTS, description, C.27

USER_TABLES, description, C.28

USER_USERS, description, C.29

USER_VIEWS, description, C.30

data types

conversion

for Oracle tools, 9.8

from DB2/400 to Oracle, B.6

Oracle to DB2/400 data types, 9.8

converting character strings, 9.8.4

DATE, performing Date and Time operations, 9.8.5

DB2/400 GRAPHIC, 9.8.1

IBM DATE, Oracle DATE data types and IBM DATE data types are mapped to each other, 9.8.5

LONG

converting character string data types, 9.8.4

known restriction, 2.4.3.4

LONG VARCHAR, 9.8.4

Oracle DATE, Oracle DATE data types and IBM DATE data types are mapped to each other, 9.8.5

restrictions, 9.8

TIME, performing Date and Time operations, 9.8.5

TIMESTAMP, performing Date and Time operations, 9.8.5

VARCHAR, DB2 data type converted to Oracle VARCHAR2 data type, 9.8.4

zoned decimal, 9.11.2

DATABASE DOMAIN parameter, 6.1.5

database link

accessing data, 7.1.4

behavior, 7.1

closing, 7.1.3

connecting to DB2/400 databases, 9.1.1

creating, 7.1.1

data dictionary views

ALL_DB_LINKS, 7.1.6

DBA_DB_LINKS, 7.1.6

USER_DB_LINKS, 7.1.6

dropping links, 7.1.5

examining, 7.1.6

function in the gateway, 1.8

limiting, 7.1.7

using Oracle Net, 7.1.2

database triggers, extended database services, 1.2.1.2

date and time operations, Oracle and DB2/400, 9.8.5

date arithmetic, restriction, 2.4.1.7

DATE data type, performing Date and Time operations, 9.8.5

dates

21st century

main topic, 9.8.6

TO_DATE function, 9.8.6

DB_DOMAIN parameter, Oracle Database Server, 2.4.2.5

DB2/400

Application Server, 1.7

cursors, maximum number, 7.2

database

read, 9.1

write, 9.1

differences from Oracle server, 9.11

executing stored procedures, 9.4.1

GRAPHIC data type support and NCHAR, 9.8.1

native SQL, 1.2.2.1

native stored procedures

journal the ORACLE2PC table, 1.2.3.2

using DB2/400 stored procedures with the gateway, 9.4

server

compatibility with SQL form, 1.8.2

triggers, 7.8.1

VALUE function, SQL differences, 1.8.2

DBA_DB_LINKS data dictionary view, 7.1.6

DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE function, 9.7

DCL, issue native DCL from Oracle environment, 1.2.2.1.2

DD basic tables

restriction, 2.4.2.1

DDL, Oracle applications use native DDL, 1.2.2.1.1

debugging parameters, changing, 6.1.6

DECnet protocol, 1.2.1.1

default configuration of gateway as commit point site, 1.3

DELETE clause, Oracle server cannot post-process, 9.10.2, 9.10.2

design, gateway architecture, 1.7

developing applications, 9.1

diagnosing errors, 10.1.3

dictionary mapping, 1.2.1.4

DICTIONARY view, description, C.15

distributed

database, Oracle Net, 5.1.2

processing, Oracle Net, 5.1.1

queries, 7.6

documentation

errors, 10.3.1

requirements, 3.3

domain name, configuring TCP/IP, defining host name, 5.4.3

double-byte character support, 9.8.2.1

DROP DATABASE LINK statement, example, 7.1.5

DSPLIND command, displaying line description parameters, 5.3.3

DSPPGM command, verifying gateway installation and version, 4.5.2

DUAL view, description, C.16

E

EDTOBJAUT command, to check authority, 4.4

EMPDB2 synonym, using the synonym feature, 7.3

ENDSBS command

authorization to stop a gateway, 8.3

stopping a gateway, 8.3.2

error

ORA-1017, invalid user ID or password, caution to synchronize recovery password, 6.1.8

ORA-12154 connection error, resolving connection problems, 5.6.5

ORA-2068 connection error, resolving connection problems, 5.6.2.1

ORA-28500

file not journaled, 4.5.3

resolving connection problems, 5.6.4

ORA-28509, connection error, resolving connection problems, 5.6.2

ORA-28511, connection error, resolving connection problems, 5.6.3

ORA-28528, example - buffer too small for LONG data type, 2.4.3.4

ORA-3114, connection error, resolving connection problems, 5.6.2.1

SQL0105, "Mixed or Graphic String Constant Not Valid.", 9.8.2.1

errors

diagnosing via categories, 10.3

mapping DB2/400 messages to Oracle, 10.1.1

messages, diagnosis, and reporting, 10

reporting

documentation, 10.3.1

external Oracle error messages, 10.3.3

incorrect output, 10.3.2

performance, 10.3.4

examples

conventions for code input and output, Preface

examples of codes and commands

see code examples

gateway security, example code lines, 4.4

EXECUTE command, beginning a PL/SQL routine, 1.2.3.1

F

fetch reblocking, array processing, 9.2.1

file

INIT.ORA

GLOBAL_NAMES initialization parameter, 2.4.2.5

initialization parameter notes, 6.1.5

TNSNAMES.ORA

creating a database link, 7.1.1

need SID for TCP/IP, 5.2.2

file members, accessing, 7.5

flat files, accessing, 7.5.1

FOR BIT DATA option, binary literal notation, 2.4.2.2

FOR UPDATE clause

NOWAIT option, 9.10.2

SELECT without FOR UPDATE clause, 9.10.1

FORCE_SBCS, string to force double-byte character support, 9.8.2.1

functions

CONCAT, translated SQL function, 9.9

DB2/400 VALUE, SQL differences, 1.8.2

DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE, 9.7, 9.7, 9.7.3

NVL, SQL differences, 1.8.2

SQL

column, 9.9

compensated, 9.9

scalar, 9.9

translated, 9.9

SQL differences, converting Oracle Database server NVL function to VALUE, 1.8.2

SUBSTR, always post-processed by Oracle server, 1.8.2

SUBSTR, not same in Oracle server and other database servers, 1.8.2

TO_DATE, dates in the 21st century, 9.8.6

TRANSLATE, translated SQL function, 9.9

VALUE, translated SQL function, 9.9

G

gateway

access, 7.4

advantages

main topic, 1.2.1

migration and coexistence, 1.5

multisite transactions, 1.3

security, 1.6

site autonomy, 1.4

two-phase commit and multisite transactions, 1.3

architecture, 1.7

array processing, 9.2

call stack, displaying, 8.5

changes made to the AS/400 system, 1.8.1

co-existence with previous gateways, 4.1

commands

main topic, 6.1

CHGGTWDBG, isolating cause of suspected gateway problem, 6.1.6

CHGORANET, changing language settings, B.2

CHGORANET, changing network parameters, 6.1.3

CHGORANET, changing parameters, A.1.2.1

CHGORAPJE, changing prestarted job parameters, 6.1.4

CHGORATUN, changing BLOCKSIZE default value, 6.2.1

CHGORATUN, changing initialization parameters, authority required, 6.1.5

CHGORATUN, changing language settings, gateway configuration, B.2

CHGRECOPRF, changing recovery user password, 6.1.2

CHGUSRPRF, changing recovery user password in OS/400, 6.1.2

CRTORAGTWI, creating a gateway instance copy, 6.1.2

menu to access, 6.1.1

running, 6.1.1

summary of commands, 6.1

components

database gateway, 1.7

DB2 server, 1.7

Oracle Database 10g Server, 1.7

default configuration as commit point site, 1.3

development applications, 9.1

Globalization Support configuration, B.2

how it works, 1.8

initialization parameters, 6.1.5

job locks, 8.6

jobs, 7.4.1

known restrictions, 2.4

library files, A.1.1

multiple copies, 6.1.2

no continuously running background processes, 1.7

post-installation, 4.5

raising priority level, 4.5.7

removing observability, 4.5.6

retrieving data, 6.2.1

security, 8.8

setting optional parameters, 6.2

starting, 8.3.2

stopping, 8.3.2

transparency levels, 1.2.1.1

two-phase commit processing

protocol limitation in distributed transaction, 7.6.2

using Oracle stored procedures with the gateway, 9.3

using Oracle stored procedures, 9.3

verifying the version, 4.5.2

gateway monitor

see Oracle gateway monitor

GATEWAY NATIONAL LANGUAGE parameter, 6.1.5

GENERAL linkage convention, 9.4.2

GENERAL WITH NULLS linkage convention, 9.4.2

GLOBAL_NAMES, known restrictions, 2.4.2.5

Globalization Support, B

CCSID

Oracle language parameters must match, B.3

supported character sets, B.3.1

supported languages, B.3.2

supported territories, B.3.2

gateway commands

CHGORANET, B.2, B.2

CHGORATUN, B.2

gateway configuration

changing language settings, B.2

CHGORATUN language specification, B.2.1

message availability, B.5

Oracle server and client configuration, B.4

overview, B.1

GO CMDORAGTW command, invoking main menu, 6.1.1

GRAPHIC data type, support for DB2/400, B.6

H

hanging condition, connection problems, 5.6

hardware requirements, 3.1

heterogeneous services

enhanced integration capabilities, 1.2

initialization parameters, known restrictions for DB2/400, 2.4.2.5

host name, TCP/IP configuration, 5.4.3

HS= parameter, mandatory keyword, using TCP/IP connect descriptor, 5.2.1

HS_DB_DOMAIN, Heterogeneous Services initialization parameter, 2.4.2.5

HS_RPC_FETCH SIZE parameter, 9.2.1

HS_RPC_FETCH_REBLOCKING parameter, 9.2.1

I

IBM DATE data type, 9.8.5

implicit protocol conversion, 1.2.1.3

incorrect output, errors, 10.3.2

Incremental Materialized View refresh, gateway restriction, 2.4.3.3

inherit mechanism, TCP/IP, 5.4

initialization parameters

changing, 6.1.5

GLOBAL_NAMES, 2.4.2.5

INIT.ORA file

GLOBAL_NAMES initialization parameter, 2.4.2.5

initialization parameter notes, 6.1.5

INSERT clause, Oracle server cannot post-process, 9.10.2, 9.10.2

INSERT command

copy data from DB2/400 server to Oracle Database server, 7.9

not supported to copy tables, 7.8.2

restriction, 2.4.1.5

INSERT statement, example for double-byte character support, 9.8.2.1

installation

before installing, pre-installation steps, 4.3

checking distribution kit, 3.4

completion, 4.4.5

mount product tape, 4.4.1

required parameters, 4.4.3

set required parameters, 4.4.3

start process, 4.4.2

starting the gateway subsystem, 4.4.6

verify, 4.4.4

INTEGER column, performing numeric data type operations, 9.8.7

Integrated File System

system changes when installing the gateway, 1.8.1

internal process communication, 5.4

Internet support, gateway advantages, 1.2.1.3

isolation level

journaling 4.5.1, 4.5.1

using passthrough statements to create tables, 9.7.3

isolation level, journaling, default setting, 4.5.1

J

job locks, working with gateway job locks, 8.6

job logs, finding gateway version, RUNORAGxxx job, 4.5.2

JOBCTL special authority, 4.4

jobs, viewing, 7.4.1

JOIN capability, distributed capabilities, 1.2.1.2

JOIN command, accessing tables in multiple databases, 9.1.3

journaling

AS/400 files, 4.5.3

cursor stability, 4.5.1

definition, 4.5.1

error messages, 9.7.3

isolation level, default, 4.5.1

new tables, 9.7.3

ORACLE2PC file, 4.5.4

requirements, 4.5.1

Julian dates, restriction, 2.4.1.8

K

known problems, 2.3

known restrictions

date arithmetic, 2.4.1.7

DB2/400

components, 2.4.2

GLOBAL_NAMES initialization parameter, 2.4.2.5

OCI, 2.4.2.3

snapshots, 2.4.1.4

L

Languages, gateway architecture, 1.2.4

libraries

created as a SQL collection, journaling, 4.5.3

data dictionary created in library OTGDB2, 4.4.3.2

installation procedure creates new, or uses pre-existing, but empty, library, 4.4.3.2

library files, LOG file members, A.1.1

library is created, gateway changes to AS/400 system, 1.8.1

member names in gateway library files, A

new tables, 9.7.3

Oracle Net parameter files are installed in gateway instance library, 5.1.5

ORASRVLIB, 1.8.1

QSYS, checking authority of QWSETP program, 4.4

QUSRSYS, 1.8.1

starting the Oracle Gateway Monitor, 8.2

syntax for accessing flat files, 7.5.1

target library, using passthrough statements to create tables, 9.7.3

listener

client connection, 5.1.4

Oracle Net for AS/400 architecture, 5.1.4

prestarted jobs, 5.1.4

TNS, 5.1.4

LISTENER member

clearing entries to reduce size of LOG file, A.1.1

in LOG file, A.1.1

of ORA file, A.1.2.1

LISTENLOCK, member of ORA file, A.1.2.3

literal values, restriction, 2.4.2.2

local domain name, TCP/IP configuration, 5.4.3

local host name, TCP/IP configuration, 5.4.3

LOG file members, Oracle Net, A.1.1

logical file members, OS/400 logical files, 7.5

LONG data type

converting character string data types, 9.8.4

restriction, 2.4.3.4

LONG VARCHAR data type, 9.8.4

M

mapping error messages, 10.1.1

Materialized View

old name was "snapshot", 7.7.2

can be used to propagate a complete copy or a subset of non-Oracle data, 7.7.2

CREATE MATERIALIZED VIEW command, automatically and asynchronously copy DB2/400 server data, 7.9

Incremental Materialized View refresh

gateway restriction, 2.4.3.3

member

command to display members of ORA file, A.1.2.6

accessing AS/400 file members, 7.5

LISTENER, member of ORA file, A.1.2.1

LISTENLOCK, member of ORA file, A.1.2.3

LOG file, A.1.1

names format, 5.1.5

ORA file, A.1.2

SQLNET, 5.1.5

SQLNET, member of ORA file, A.1.2.4

SQLNET_CHG, member of ORA file, A.1.2.5

TRC file, A.1.3

messages

and error code processing, 10.1

diagnosing, 10.1.3

error, 10

journaling, 9.7.3

ORA-00947, 10.1.3

Metalink Note 136294.1, connection problems, 5.6.2.1

missing functionality, error categories, 10.3.5

Mobile Agents, wireless communication, 1.2.1.3

monitoring the gateway

overview, 8.1

starting Oracle gateway monitor, 8.2

multiple rows, retrieving, 6.2.1

multisite transactions, 1.3

N

network parameters, changing, 6.1.3

NOWAIT option, example when not supported, 9.10.2

null values, with stored procedures, 9.4.2

numeric column, performing numeric data type operations, 9.8.7

numeric data type, DB2/400 performs automatic conversions, 9.8.7

NVARCHAR2 data types, DB2/400 GRAPHIC support, 9.8.1

NVL function, SQL differences, 1.8.2

O

observability, gateway is shipped with IBM observability, it can be removed, 4.5.6

OCI restriction (Oracle Call Interface), programmatic limitation, 2.4.2.3

OPEN_LINKS parameter, limiting the number of active database links, 7.1.7

operations, date and time, 9.8.5

ORA file members, A.1.2

ORA(LISTENER) file

contains parameter values for the listener, A.1.2.1

making changes with ORA(LISTENER_CHG), A.1.2.2

some network parameters not displayed on CHGORANET panel, 6.1.3.2

ORA(LISTENER_CHG) file, used to make changes to ORA(LISTENER) file, A.1.2.2

ORA(LISTENLOCK) file, ensuring only one instance of RUNORALSN program is running, A.1.2.3

ORA(SQLNET) file

contains network parameter values for Oracle Net, A.1.2.4

some network parameters not displayed on CHGORANET panel, 6.1.3.1

ORA-00947 error message, not accompanied by DB2/400 error messages, 10.1.3

ORA-1017 error, invalid user ID or password, 6.1.8

ORA-12154 connection error, resolving connection problems, 5.6.5

ORA-2068 connection error, resolving connection problems, 5.6.2.1

ORA-28500 error

file not journaled, 4.5.3

resolving connection problems, 5.6.4

ORA-28509 connection error, resolving connection problems, 5.6.2

ORA-28511 connection error, resolving connection problems, 5.6.3

ORA-28528 error, example - buffer too small for LONG data type, 2.4.3.4

ORA-3114 connection error, resolving connection problems, 5.6.2.1

Oracle Call Interface (OCI), restriction, programmatic limitation, 2.4.2.3

Oracle Database 10g server

post-processing of SUBSTR SQL function, 2.4.2.7

Oracle Database Server

copying data from the DB2/400 server, 7.9

database links, 1.8

database triggers, 1.2.1.2

differences from DB2/400, 9.11

distributed capabilities, 1.2.1.2

distributed query optimization, 1.2.1.2

extended database services, 1.2.1.2

mapping DB2/400 error messages, 10.1.1

performing character string operations, 9.8.3

SQL, extended database services, 1.2.1.2

stored procedures, 1.2.1.2

triggers

copying data from Oracle server, 7.8.1

replicating in a distributed environment, 7.7.1

two-phase commit protection, 1.2.1.2

Oracle Database server

and array size, 9.2.1, 9.2.1

and error detection, 10.1.3

copying data to DB2/400 server, 7.8

date and time operations, 9.8.5

initialization parameter notes, 6.1.5

Oracle DATE data type, 9.8.5

Oracle Developer

Forms compatibility, 9.13

Oracle gateway monitor

display gateway call stack, 8.5

display open files, 8.7

ORAMON command, 8.2

overview, 8.1

starting, 8.2

starting and stopping gateway, 8.3

starting TCP/IP listener, 8.4

working with gateway job locks, 8.6

working with gateway jobs, 8.3.1

Oracle integrating server

and GLOBAL_NAMES initialization parameter, 2.4.2.5

function

regarding SQL, 1.8.2

Oracle Net

chapter, 5

API, 5.1

communications, 5.1.4

configuration supported by Oracle server, 1.2.6

configuring, 4.5.5

configuring APPC/LU6.2

define physical line, 5.3.3

configuring TCP/IP

checklist, 5.4.1

define local host name, 5.4.3

for TCP/IP AS/400, 5.4

modify port number, 5.4.2

start the listener, 5.4.5

verify host name, 5.4.4

connecting

TCP/IP, 5.2.2

connecting TCP/IP

add connect descriptor, 5.2.1

checklist, 5.2, 5.3.2

to AS/400, 5.2

connections, Oracle Net for AS/400 architecture, 5.1.4

creating database links, 7.1.2

distributed database, 5.1.2

distributed processing, 5.1.1

file names, 5.1.5

listener, 5.1.4

LOG file members, A.1.1

ORA file members, A.1.2

overview, 5.1

TCP/IP listener, default trace output file, A.1.3

terminology

client, 5.1.3

driver, 5.1.3

host, 5.1.3

network, 5.1.3

protocol, 5.1.3

terminology, main topic, 5.1.3

tracing job members, 5.1.5.1

TRC file members, A.1.3

Oracle Precompilers

restriction, 2.4.2.6

Oracle subsystem

error reporting, 10.3.3

Oracle Support Services

contacting, 10.2

ORACLE2PC

file

journaling two-phase commit transactions, 4.5.4

recording two-phase commit transactions, 7.6.2

table, using DB2/400 stored procedures, 1.2.3.2

ORADBMBOPT Data Area (or Environment variable), 9.8.2.1

ORAMON command

starting the gateway, 1.7

starting the Oracle gateway monitor, 8.2

ORARAW, data area, 6.2

ORASRVLIB library, creation, 1.8.1

OTGREGISTER view

description, C.17

finding primary authorization ID being used by gateway, 9.12.2

outer joins, SQL functions, 1.2.1.2

P

PACKED DECIMAL column, performing numeric data type operations, 9.8.7, 9.8.7

panel examples

Change Oracle Gateway Debugging Option, 6.1.6

Change Oracle Gateway Initialization Parameters (page 1), 6.1.5

Change Oracle Gateway Initialization Parameters (page 2), 6.1.5

Change Oracle Network Parameters, 6.1.3

Change Oracle Prestart Parameters, 6.1.4

Change Recovery Profile Parameters, 6.1.8

Change TCP/IP Domain (CHGTCPDMN), 5.4.3

CMDORAGTW Oracle Commands, 6.1.1

Create Oracle Database Gateway, 6.1.2

Create Oracle Database Gateway Panel, with new values, 6.1.2

Install Oracle Database Gateway, 4.4.3.2

Install Oracle Database Gateway Panel, With Name Choices, 4.4.3.2

Verify Oracle Install, 4.4.4.2

parameters

ARRAY BLOCK SIZE, 9.2.1

ARRAY BLOCK SIZE, setting, 6.2

AUTOCREATE CONTROLLER, 5.3.3

data dictionary, 6.1.7

DATABASE DOMAIN, 6.1.5

DB_DOMAIN

Oracle9 server, 2.4.2.5

debugging, 6.1.6

HS_DB_DOMAIN, Heterogeneous Services initialization parameter, 2.4.2.5

HS_PRC_FETCH_REBLOCKING, 9.2.1

HS_RPC_FETCH SIZE, 9.2.1

initialization, 6.1.5

network, 6.1.3

OPEN_LINKS, 7.1.7

optional, 6.2

READONLY, 6.2.3

required, 4.4.3

RPC FETCH REBLOCKING

*YES is default value, 9.2.1

array size for SELECT, 9.2.1

initialization parameter notes, 6.1.5

may be changed by CHGORATUN command, 9.2.1

RPC FETCH SIZE

array size for SELECT, 9.2.1

initialization parameter notes, 6.1.5

may be changed by CHGORATUN command, 9.2.1

setting optional parameters for gateway configuration, 6.2

setting required parameters for gateway installation, 4.4.3

SQLNET.ORA, file name structure, 5.1.5

passthrough

and Native DB2/400 SQL, introduction, 1.2.2.1

examples, 9.7.2

function, 9.7

result sets

example, 9.7.4.1

retrieval, 9.7.4

sending SQL statement directly to DB2/400 without being interpreted by Oracle server, 9.7

performance issues, 10.3.4

PING command

troubleshooting gateway configuration, 5.7.1

verifying host name, 5.4.4

PL/SQL

routine, 1.2.3.1

standard Oracle, 1.2.3.2

port number

changing the default gateway port number, 5.4.2

configuring for listener, 5.4.2

default used by Oracle Net listener (1521), 5.2.1

TCP/IP

gateway installation panel, 4.4.3.2

name choices of install panel, 4.4.3.2

post-installation

configure Oracle Net, 4.5.5

journal ORACLE2PC file, 4.5.4

raise priority level, 4.5.7

remove observability, 4.5.6

steps, 4.5

verify journaling, 4.5.3

post-processing of SQL functions, 1.8.2

precompiler limitations, 2.4.2.6

prestarted jobs, changing parameters, 6.1.4

priority level, raising gateway level, 4.5.7

processing SQL, 9.10

processor requirement, 3.1.1

product set, 2.1

protocols

APPC/LU6.2, 1.2.1.1

commit confirm, 1.3

DECnet, 1.2.1.1

definition, 5.1.3

SPX/IPX, 1.2.1.1, 1.2.1.3

TCP/IP, 1.2.1.1, 1.2.1.3

Q

QCCSID, default coded character set identifier, 6.2.2

QCRTAUT parameter

do not use value *EXCLUDE, 4.3.2

restriction, 2.4.1.6

QSECOFR user class, log on authority, 4.3.1

QSYS2/SYSPARMS DB2/400 table, 9.4.1

QSYS2/SYSPROCS DB2/400 table, 9.4.1

qualified host name

description, 5.4.3

Option 12 of CFGTCP, 5.7.2

queries, distributed, example, 7.6.1

queries, distributed, performing, 7.6

QUSRSYS library, creating user profile that corresponds to installation library, 1.8.1

R

RAW

columns, restriction, 2.4.2.2

data restrictions, 2.4.2.4

reading DB2/400 database, read and write access, 9.1.2

read-only capabilities on the gateway, 6.2.3

READONLY parameter, read-only gateway option may provide improved performance and security, 6.2.3

recovery

process, 7.6.2.1

user and password, 6.1.2

release information, 2

remote transaction program, 3.1.2

replication, heterogeneous environment, 7.7

requirements

documentation, 3.3

hardware, 3.1

software, 3.2

software, journaling, 4.5.1

result sets, retrieving with passthrough, 9.7

retrieving multiple rows

array processing, 9.2

using BLOCKSIZE, 6.2.1

ROUTINE_NAME column, executing DB2/400 stored procedures, 9.4.1

ROUTINE_SCHEMA column, executing DB2/400 stored procedures, 9.4.1

routines, PL/SQL, 1.2.3.1

RPC FETCH REBLOCKING parameter

*YES is default value, 9.2.1

entering on Change Oracle Gateway Initialization Parameters panel, 6.1.5

initialization parameter notes, 6.1.5

may be changed by CHGORATUN command, 9.2.1

RPC FETCH SIZE parameter

changing initialization parameters, 6.1.5

entering on Change Oracle Gateway Initialization Parameters panel, 9.2.1, 9.2.1

running gateway commands, 6.1.1

RUNORALSN program runs the TCP/IP listener, A.1.2.3

RUNSQLSTM command required for cloning an instance, 3.2.1

S

SBMJOB command

QSECOFR ID, 4.5.6

removing IBM observability, 4.5.6

SECADM special authority, 4.3.1

security

advanced, 1.2.1.3

ANO, 1.2.1.3

encryption, 1.2.1.3

on the gateway, 8.8

SELECT FOR UPDATE clause, Oracle server cannot post-process, 9.10.2

SELECT statement

array size in fetch reblocking, 9.2.1

read and write access to DB2/400 databases, 9.1.2

SELECT without the FOR UPDATE clause, 9.10.1

SQL differences, 1.8.2

session connection, 7.1

SID names, specifying, 5.2.2

SMALLINT column, performing numeric data type operations, 9.8.7

snapshot

complete refresh

restriction, fast refresh snapshots not supported, 2.4.1.4

older name for Oracle Materialized View, see Materialized View, 7.7.2

software requirements, 3.2

special authority

*JOBCTL, 4.4

*SECADM, 4.3.1

special registers in DB2/400, 9.12.2

SPX/IPX protocol

implicit protocol conversion, 1.2.1.3

network transparency, 1.2.1.1

SQL

ANSI standard, 1.2.2

commands, 7.1.2

compatibility with gateway through Oracle integrating server, 1.8.2

constructs, processing, 9.10

conversion for compatibility withDB2/400 server, 1.8.2

differences, 1.8.2

functions

column, 9.9

compatibility, 9.9

compensated, 9.9

scalar, 9.9

translated, 9.9

ISO standard, 1.2.2

known restrictions, 2.4.3

limitations, 2.4.3

passthrough, 9.7

statements

gateway appearance to application programs, 9.1

passing SQL statements through gateway, 9.7

SQL*Plus

commands

COPY, copying data from Oracle to DB2/400-7.8, 7.8

COPY, general restrictions, 2.4.1.5

COPY, limitation, 7.8.2

INSERT, limitation, 7.8.2

moving data between databases, 1.2.5

SQL0105 error, "Mixed or Graphic String Constant Not Valid.", 9.8.2.1

SQLNET member, file name structure, 5.1.5

SQLNET(ORA) file,file name structure, 5.1.5

SQLNET, member of ORA file, A.1.2.4

SQLNET_CHG, member of ORA file, A.1.2.5

SQLNET.ORA parameter, mapped to member SQLNET, 5.1.5

starting the gateway, 8.3.2

statements

ALTER SESSION CLOSE DATABASE LINK, 7.1.3

CONNECT TO, 7.1.4

CREATE DATABASE LINK, 7.1.1

DB2

CREATE TABLE, 1.2.2.1

DROP DATABASE LINK, 7.1.5

INSERT, example for double-byte character support, 9.8.2.1

SELECT

fetch reblocking, 9.2.1

retrieve data from multiple databases, 9.1.2

without the FOR UPDATE clause, 9.10.1

SQL

gateway appearance to application programs, 9.1

passing through gateway, 9.7

stopping the gateway, 8.3.2

stored procedures

columns

ROUTINE_NAME, 9.4.1

ROUTINE_SCHEMA, 9.4.1

considerations, 9.4.2

database triggers, extended database services, 1.2.1.2

DB2/400 with the gateway, 9.4

execution of DB2/400 procedures from applications, 9.4.1

gateway capabilities, intro, 1.2.3

gateway support of Oracle stored procedures, 9.3

linkage conventions, 9.4.2

native DB2, 1.2.3.2

Oracle stored procedures, introduction, 1.2.3.1

PL/SQL, 9.3

synonyms for, 9.3

two-phase commit, 9.3

using, 9.3

streams

replication, 8.9

example code, 8.9

replication from an Oracle Database to DB2/400 is supported, 8.9

streams will not work if archivelog is not enabled, 8.9

STRORALSN command, 5.4.5

STRSBS command, 1.7

Structured Query Language, See SQL, 1.2

SUBSTR function, always post-processed by Oracle server, 1.8.2

SUBSTR function, not same in Oracle server and other database servers, 1.8.2

support, contacting Oracle Support Services, 10.2

synonym feature, 7.3

syntax, conventions used in this book, Preface

T

TABLE_PRIVILEGES view, description, C.18

TCP/IP

AS/400 database, 5.1.4

bequeath mechanism, 5.4

concurrent connections, memory, 3.1.2

configuring Oracle Net, 5.4

connecting to the AS/400, 5.2

connection flow, 5.4

connection problems

troubleshooting, 5.5

inherit mechanism, 5.4

listener

gateway architecture, 1.7

starting, 8.4

local domain name, 5.4.3

local host name, 5.4.3

port numbers

cloning a gateway instance, 6.1.2

configuring for Oracle Net listener, 5.4.2

name choices on install panel, 4.4.3.2

protocol

implicit protocol conversion, 1.2.1.3

network transparency, 1.2.1.1

socket interface, 4.4

starting the listener, 8.4

terminology, Oracle Net

client, 5.1.3

driver, 5.1.3

host, 5.1.3

network, 5.1.3

protocol, 5.1.3

TIME data type

CHAR format, 9.8.5

performing Date and Time operations, 9.8.5

TIMESTAMP data type

CHAR format, 9.8.5

performing Date and Time operations, 9.8.5

TNS

connect descriptor, 7.1.2

TNSNAMES.ORA file

add a TCP/IP connect descriptor, 5.2.1

connect descriptor, 7.1.1

specify a system identifier (SID) name, 5.2.2

TO_CHAR function, Julian dates support, 2.4.1.8

TO_DATE function

dates in the 21st century, 9.8.6

Julian dates support, 2.4.1.8

performing date and time operations, 9.8.5

trace files

agent trace files will appear if requested, 1.8.1

for job members, file name structure, 5.1.5.1

trace file member names, format, 5.1.5.1

TRANSLATE translated SQL function, 9.9

translated SQL functions, defined, 9.9

transparency

access method, 1.2.1.1

data storage, 1.2.1.1

location, 1.2.1.1

network, 1.2.1.1

operating system, 1.2.1.1

TRC file

member name format, 5.1.5.1

members in TRC file, A.1.3

trace files for job members, 5.1.5.1

triggers

Oracle Database server-7.7.1, 7.7.1

synchronous copies of Oracle and non-Oracle data maintained automatically, 7.8.1

twenty-first century dates, 9.8.6

two-phase commit

description, 7.6.2

in multi-site transactions, 1.3

prepare phase not supported by DB2/400, 7.6.2

recorded in, 7.6.2

transaction recovery, 7.6.2.1

transaction treated as a single unit of work, 1.2.1.2

updates to DB2/400 made within Oracle stored procedure, 9.3

U

UNION capability, distributed capabilities, 1.2.1.2

user class

QSECOFR, 4.3.1

USER, 4.4

user ID

cannot be longer than ten characters, 7.1.2

CONNECT TO user ID provides implicit qualification for unqualified tables, 7.1.4

gateway recovery caution, 6.1.8

instance password for gateway copy, 6.1.2

instance password, name choices on install panel, 4.4.3.2

Oracle user ID passed over database link for gateway security, 8.8

Oracle user ID used as implicit qualifier, 7.1.4

OS/400 user ID identical to name of install library, 4.4

OS/400 user ID known as gateway ID, 4.4

recovery user ID, recovering failed transactions, 7.6.2.1

remote user ID specified by CONNECT TO clause, creating database links, 7.1.1

starting the Oracle gateway monitor, 8.2

that is used to establish a session in remote AS/400 system, 7.1.2

user ID, CONNECT TO, 7.1.4

USER_CATALOG view, description, C.19

USER_COL_COMMENTS view, description, C.20

USER_CONS_COLUMNS view, description, C.21

USER_CONSTRAINTS view, description, C.22

USER_DB_LINKS data dictionary view, examining available database links, 7.1.6

USER_INDEXES view, description, C.23

USER_OBJECTS view, description, C.24

USER_SYNONYMS view, description, C.25

USER_TAB_COLUMNS view, description, C.26

USER_TAB_COMMENTS view, description, C.27

USER_TABLES view, description, C.28

USER_USERS view, description, C.29

USER_VIEWS view, description, C.30

USING clause, specifies a TNSNAMES.ORA connect descriptor, 7.1.1

V

VALUE translated SQL function, 9.9

VARCHAR

DB2/400 VARCHAR data type length, 9.8.4

general restrictions, column returns its width doubled, 2.4.1.2

table, data type mapping and restrictions, 9.8

VARCHAR LONG, long data types, 2.4.3.4

VARCHAR2 data types, DB2/400 GRAPHIC support, 9.8.1

views, data dictionary

ALL_CATALOG, description, C.1

ALL_COL_COMMENTS, description, C.2

ALL_CONS_COLUMNS, description, C.3

ALL_CONSTRAINTS, description, C.4

ALL_DB_LINKS, examining available database links, 7.1.6

ALL_IND_COLUMNS, description, C.5

ALL_INDEXES, description, C.6

ALL_OBJECTS, description, C.7

ALL_SYNONYMS, description, C.8

ALL_TAB_COLUMNS, description, C.9

ALL_TAB_COMMENTS, description, C.10

ALL_TABLES, description, C.11, C.11

ALL_USERS, description, C.12

ALL_VIEWS, description, C.13

COLUMN_PRIVILEGES, description, C.14

DBA_DB_LINKS, examining available database links, 7.1.6

DICTIONARY, description, C.15

DUAL, description, C.16

OTGREGISTER

DB2/400 special registers, 9.12.2

OTGREGISTER, description, C.17

TABLE_PRIVILEGES, description, C.18

USER_CATALOG, description, C.19

USER_COL_COMMENTS, description, C.20

USER_CONS_COLUMNS, description, C.21

USER_CONSTRAINTS, description, C.22

USER_DB_LINKS, examining available database links, 7.1.6

USER_INDEXES, description, C.23

USER_OBJECTS, description, C.24

USER_SYNONYMS, description, C.25

USER_TAB_COLUMNS, description, C.26

USER_TAB_COMMENTS, description, C.27

USER_TABLES, description, C.28

USER_USERS, description, C.29

USER_VIEWS, description, C.30

W

WHERE clause

copying data, refreshed, 7.9

SELECT without the FOR UPDATE clause, 9.10.1

wireless communication, 1.2.1.3

writing to DB2/400 database, read and write access, 9.1.2

WRKTRCLOG command

agent trace files, A.2

Displaying File Members, A.1.2.6

make sure instance library is in library list before using this command, A.1.3

Z

zoned decimal operations, 9.11.2