User's Guide

Oracle® Database Gateway for

Teradata

User's Guide

21c

F31871-01

November 2020

Oracle Database Gateway for Teradata User's Guide, 21c

F31871-01

Primary Author: Rhonda Day

Contributing Authors: Vira Goorah, Juan Pablo Ahues-Vasquez, Peter Castro, Charles Benet, Peter Wong,

Govind Lakkoju

This software and related documentation are provided under a license agreement containing restrictions on

use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your

license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,

transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse

engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is

prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If

you find any errors, please report them to us in writing.

If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on

behalf of the U.S. Government, then the following notice is applicable:

U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software,

any programs embedded, installed or activated on delivered hardware, and modifications of such programs)

and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government

end users are "commercial computer software" or "commercial computer software documentation" pursuant

to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such,

the use, reproduction, duplication, release, display, disclosure, modification, preparation of derivative works,

and/or adaptation of i) Oracle programs (including any operating system, integrated software, any programs

embedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oracle

computer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in the

license contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloud

services are defined by the applicable contract for such services. No other rights are granted to the U.S.

Government.

This software or hardware is developed for general use in a variety of information management applications.

It is not developed or intended for use in any inherently dangerous applications, including applications that

may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you

shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its

safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this

software or hardware in dangerous applications.

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of

their respective owners.

Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are

used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc,

and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered

trademark of The Open Group.

This software or hardware and documentation may provide access to or information about content, products,

and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly

disclaim all warranties of any kind with respect to third-party content, products, and services unless otherwise

set forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not

be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content,

products, or services, except as set forth in an applicable agreement between you and Oracle.

Contents

Preface

Audience ix

Documentation Accessibility ix

See Also:

Oracle Database Heterogeneous Connectivity User's Guide for more

information about Heterogeneous Services.

1.3 About Oracle Database Gateway for Teradata

The capabilities, SQL mappings, data type conversions, and interface to the remote

non-Oracle system are contained in the gateway. The gateway interacts with

Heterogeneous Services to provide the transparent connectivity between Oracle and

non-Oracle systems.

The gateway must be installed on a machine running either the Teradata database or

the Teradata client. This machine can be the same machine as the Oracle database

or on the same machine as the Teradata database or on a third machine as a

standalone. Each configuration has its advantages and disadvantages. The issues to

consider when determining where to install the gateway are network traffic, operating

system platform availability, hardware resources and storage.

Chapter 1

About Heterogeneous Services Technology

1-2

Teradata Gateway Features and

Restrictions

After the gateway is installed and configured, you can use the gateway to access

Teradata data, pass Teradata commands from applications to the Teradata database,

perform distributed queries, and copy data.

Topics:

• Remote Insert Rowsource

• Using the Pass-Through Feature

• Executing Stored Procedures and Functions

• CHAR Semantics

• Multi-byte Character Sets Ratio Suppression

• IPv6 Support

• Gateway Session IDLE Timeout

• Database Compatibility Issues for Teradata

• Known Restrictions

• Known Problems

• Remote Insert Rowsource

A remote insert rowsource feature allows remote insert requiring local Oracle

data to work through the Oracle database and Oracle Database Gateway. This

functionality requires that the Oracle database and the Oracle Database Gateway

to be version 12.2 or later.

• Using the Pass-Through Feature

• Executing Stored Procedures and Functions

• CHAR Semantics

• Multi-byte Character Sets Ratio Suppression

• IPv6 Support

• Gateway Session IDLE Timeout

• Database Compatibility Issues for Teradata

• Known Restrictions

• Known Problems

2.1 Remote Insert Rowsource

A remote insert rowsource feature allows remote insert requiring local Oracle data to

work through the Oracle database and Oracle Database Gateway. This functionality

2-1

requires that the Oracle database and the Oracle Database Gateway to be version

12.2 or later.

By Oracle Database design, some distributed statement must be executed at the

database link site. But in certain circumstances, there is data needed to execute

these queries that must be fetched from the originating Oracle Database. Under

homogeneous connections, the remote Oracle database would call back the source

Oracle database for such data. But in heterogeneous connections, this is not viable,

because this means that the Foreign Data Store would have to query call back

functions, or data, that can only be provided by the Oracle instance that issued the

query. In general, these kinds of statements are not something that can be supported

through the Oracle Database Gateway.

The following categories of SQL statements results in a callback:

• Any DML with a sub-select, which refers to a table in Oracle database.

• Any

DELETE

INSERT

UPDATE

or "

SELECT... FOR UPDATE..."

SQL statement

containing SQL functions or statements that needs to be executed at the

originating Oracle database.

These SQL functions include

USER

USERENV

, and

SYSDATE

; and involve the

selection of data from the originating Oracle database.

• Any SQL statement that involves a table in Oracle database, and a

LONG

LOB

column in a remote table.

An example of a remote

INSERT

statement that can work through the remote insert

rowsource feature is as follows:

INSERT INTO gateway_table@gateway_link select * from local_table;

2.2 Using the Pass-Through Feature

The gateway can pass Teradata commands or statements from the application directly

to the Teradata database using the

DBMS_HS_PASSTHROUGH

package.

Use the

DBMS_HS_PASSTHROUGH

package in a PL/SQL block to specify the statement to

be passed to the Teradata database, as follows:

DECLARE

num_rows INTEGER;

BEGIN

num_rows := DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@TERA('command');

END;

Where

command

cannot be one of the following:

•

BEGIN TRANSACTION

•

COMMIT

•

END TRANSACTION

•

ROLLBACK

Chapter 2

Using the Pass-Through Feature

2-2

The

DBMS_HS_PASSTHROUGH

package supports passing bind values and executing

SELECT

statements.

See Also:

Oracle Database PL/SQL Packages and Types Reference and Chapter

3, Features of Oracle Database Gateways, of Oracle Database

Heterogeneous Connectivity User's Guide for more information about the

DBMS_HS_PASSTHROUGH

package.

2.3 Executing Stored Procedures and Functions

Using the procedural feature, the gateway can execute stored procedures that are

defined in the Teradata database. It is not necessary to relink the gateway or define

the procedure to the gateway, but the procedure's access privileges must permit

access by the gateway.

Standard PL/SQL statements are used to execute a stored procedure.

The gateway supports stored procedures in three mutually exclusive modes:

• Normal mode: Have access to

OUT

arguments only

• Return value mode: Have a return value for all stored procedures

• Resultset mode: Out values are available as last result set

• Return Values and Stored Procedures

• Result Sets and Stored Procedures

2.3.1 Return Values and Stored Procedures

By default, all stored procedures and functions do not return a return value to the user.

To enable return values, set the

HS_FDS_PROC_IS_FUNC

parameter in the initialization

parameter file.

See Also:

Initialization Parameters for information about both editing the initialization

parameter file and the

HS_FDS_PROC_IS_FUNC

parameter.

Note:

If you set the

HS_FDS_PROC_IS_FUNC

gateway initialization parameter, you

must change the syntax of the procedure execute statement for all existing

stored procedures.

Chapter 2

Executing Stored Procedures and Functions

2-3

In the following example, the employee name

JOHN SMYTHE

is passed to the Teradata

stored procedure

REVISE_SALARY

. The stored procedure retrieves the salary value from

the Teradata database to calculate a new yearly salary for

JOHN SMYTHE

. The revised

salary returned in

RESULT

is used to update

EMP

in a table of an Oracle database:

DECLARE

INPUT VARCHAR2(15);

RESULT NUMBER(8,2);

BEGIN

INPUT := 'JOHN SMYTHE';

RESULT := REVISE_SALARY@TERA(INPUT);

UPDATE EMP SET SAL = RESULT WHERE ENAME =: INPUT;

END;

The procedural feature automatically converts non-Oracle data types to and from

PL/SQL data types.

2.3.2 Result Sets and Stored Procedures

The Oracle Database Gateway for Teradata provides support for stored procedures

which return result sets.

By default, all stored procedures and functions do not return a result set to the user.

To enable result sets, set the

HS_FDS_RESULTSET_SUPPORT

parameter in the initialization

parameter file.

See Also:

Initialization Parameters for information about both editing the initialization

parameter file and the

HS_FDS_RESULTSET_SUPPORT

parameter. For further

information about Oracle support for result sets in non-Oracle databases see

Oracle Database Heterogeneous Connectivity User's Guide.

Note:

If you set the

HS_FDS_RESULTSET_SUPPORT

gateway initialization parameter,

you must change the syntax of the procedure execute statement for all

existing stored procedures or errors will occur.

When accessing stored procedures with result sets through the Oracle Database

Gateway for Teradata, you will be in the sequential mode of Heterogeneous Services.

The Oracle Database Gateway for Teradata returns the following information to

Heterogeneous Services during procedure description:

• All the input arguments of the remote stored procedure

• None of the output arguments

• One out argument of type ref cursor (corresponding to the first result set returned

by the stored procedure)

Chapter 2

Executing Stored Procedures and Functions

2-4

Client programs have to use the virtual package function

dbms_hs_result_set.get_next_result_set

to get the ref cursor for subsequent result

sets. The last result set returned is the out argument from the procedure.

The limitations of accessing result sets are the following:

• Result sets returned by a remote stored procedure have to be retrieved in the

order in which they were placed on the wire

• On execution of a stored procedure, all result sets returned by a previously

executed stored procedure will be closed (regardless of whether the data has

been completely

In the following example, the Teradata stored procedure is executed to fetch the

contents of the

emp

and

dept

tables from Teradata:

CREATE PROCEDURE refcurproc (@arg1 varchar(255), @arg2 varchar(255) output)

SECLECT @arg2 = @arg1

SELECT * FROM EMP

SELECT * FROM DEPT

This stored procedure assigns the input parameter

arg1

to the output parameter

arg2

opens the query

SELECT * FROM EMP

in ref cursor

rc1

, and opens the query

SELECT *

FROM DEPT

in ref cursor

rc2

Note:

Chained mode must be set before creating the stored procedure. Issue the

following command in Teradata:

set chained on

• OCI Program Fetching from Result Sets in Sequential Mode

• PL/SQL Program Fetching from Result Sets in Sequential Mode

2.3.2.1 OCI Program Fetching from Result Sets in Sequential Mode

The following example shows OCI program fetching from result sets in sequential

mode:

OCIEnv *ENVH;

OCISvcCtx *SVCH;

OCIStmt *STMH;

OCIError *ERRH;

OCIBind *BNDH[3];

OraText arg1[20];

OraText arg2[255];

OCIResult *rset;

OCIStmt *rstmt;

ub2 rcode[3];

ub2 rlens[3];

sb2 inds[3];

OraText *stmt = (OraText *) "begin refcurproc@TERA(:1,:2,:3); end;";

OraText *n_rs_stm = (OraText *)

"begin :ret := DBMS_HS_RESULT_SET.GET_NEXT_RESULT_SET@TERA; end;";

Chapter 2

Executing Stored Procedures and Functions

2-5

/* Prepare procedure call statement */

/* Handle Initialization code skipped */

OCIStmtPrepare(STMH, ERRH, stmt, strlen(stmt), OCI_NTV_SYNTAX, OCI_DEFAULT);

/* Bind procedure arguments */

inds[0] = 0;

strcpy((char *) arg1, "Hello World");

rlens[0] = strlen(arg1);

OCIBindByPos(STMH, &BNDH[0], ERRH, 1, (dvoid *) arg1, 20, SQLT_CHR,

(dvoid *) &(inds[0]), &(rlens[0]), &(rcode[0]), 0, (ub4 *) 0,

OCI_DEFAULT);

inds[1] = -1;

OCIBindByPos(STMH, &BNDH[1], ERRH, 1, (dvoid *) arg2, 20, SQLT_CHR,

(dvoid *) &(inds[1]), &(rlens[1]), &(rcode[1]), 0, (ub4 *) 0,

OCI_DEFAULT);

inds[2] = 0;

rlens[2] = 0;

OCIDescriptorAlloc(ENVH, (dvoid **) &rset, OCI_DTYPE_RSET, 0, (dvoid **) 0);

OCIBindByPos(STMH, &BNDH[2], ERRH, 2, (dvoid *) rset, 0, SQLT_RSET,

(dvoid *) &(inds[2]), &(rlens[2]), &(rcode[2]),

0, (ub4 *) 0, OCI_DEFAULT);

/* Execute procedure */

OCIStmtExecute(SVCH, STMH, ERRH, 1, 0, (CONST OCISnapshot *) 0,

(OCISnapshot *) 0, OCI_DEFAULT);

/* Convert result set to statement handle */

OCIResultSetToStmt(rset, ERRH);

rstmt = (OCIStmt *) rset;

/* After this the user can fetch from rstmt */

/* Issue get_next_result_set call to get handle to next_result set */

/* Prepare Get next result set procedure call */

OCIStmtPrepare(STMH, ERRH, n_rs_stm, strlen(n_rs_stm), OCI_NTV_SYNTAX,

OCI_DEFAULT);

/* Bind return value */

OCIBindByPos(STMH, &BNDH[1], ERRH, 1, (dvoid *) rset, 0, SQLT_RSET,

(dvoid *) &(inds[1]), &(rlens[1]), &(rcode[1]),

0, (ub4 *) 0, OCI_DEFAULT);

/* Execute statement to get next result set*/

OCIStmtExecute(SVCH, STMH, ERRH, 1, 0, (CONST OCISnapshot *) 0,

(OCISnapshot *) 0, OCI_DEFAULT);

/* Convert next result set to statement handle */

OCIResultSetToStmt(rset, ERRH);

rstmt = (OCIStmt *) rset;

/* Now rstmt will point to the second result set returned by the

remote stored procedure */

/* Repeat execution of get_next_result_set to get the output arguments */

Chapter 2

Executing Stored Procedures and Functions

2-6

2.3.2.2 PL/SQL Program Fetching from Result Sets in Sequential Mode

Assume that the table

loc_emp

is a local table exactly like the Teradata emp table. The

same assumption applies for

loc_dept

outargs

is a table with columns corresponding

to the out arguments of the Teradata stored procedure.

create or replace package rcpackage is

type RCTYPE is ref cursor;

end rcpackage;

declare

rc1 rcpackage.rctype;

rec1 loc_emp%rowtype;

rc2 rcpackage.rctype;

rec2 loc_dept%rowtype;

rc3 rcpackage.rctype;

rec3 outargs%rowtype;

out_arg varchar2(255);

begin

-- Execute procedure

out_arg := null; refcurproc@TERA('Hello World', out_arg, rc1);

-- Fetch 20 rows from the remote emp table and insert them into loc_emp

for i in 1 .. 20 loop

fetch rc1 into rec1;

insert into loc_emp (rec1.empno, rec1.ename, rec1.job,

rec1.mgr, rec1.hiredate, rec1.sal, rec1.comm, rec1.deptno);

end loop;

-- Close ref cursor

close rc1;

-- Get the next result set returned by the stored procedure

rc2 := dbms_hs_result_set.get_next_result_set@TERA;

-- Fetch 5 rows from the remote dept table and insert them into loc_dept

for i in 1 .. 5 loop

fetch rc2 into rec2;

insert into loc_dept values (rec2.deptno, rec2.dname, rec2.loc);

end loop;

--Close ref cursor

close rc2;

-- Get the output arguments from the remote stored procedure

-- Since we are in sequential mode, they will be returned in the

-- form of a result set

rc3 := dbms_hs_result_set.get_next_result_set@TERA;

-- Fetch them and insert them into the outarguments table

fetch rc3 into rec3;

insert into outargs (rec3.outarg, rec3.retval);

-- Close ref cursor

close rc3;

Chapter 2

Executing Stored Procedures and Functions

2-7

end;

2.4 CHAR Semantics

This feature allows the gateway to optionally run in

CHAR

Semantics mode. Rather than

always describing Teradata

CHAR

columns as

CHAR(n BYTE)

, this feature describes

them as

CHAR(n CHAR)

and

VARCHAR(n CHAR)

. The concept is similar to Oracle

database

CHAR

Semantics. You need to specify

HS_NLS_LENGTH_SEMANTICS=CHAR

activate this option. Refer to Initialization Parameters for more detail.

2.5 Multi-byte Character Sets Ratio Suppression

This feature optionally suppresses the ratio expansion from Teradata database

to Oracle databases involving a multi-byte character set (for example, from

US7ASCII

AL32UTF8

, or from

KO16MSWIN949

KO16KSC5601

). By default, Oracle

gateways assume the worst ratio to prevent data being truncated or allocation

of buffer with insufficient size. However, if you have specific knowledge of your

Teradata database and do not want the expansion to occur, you can specify

HS_KEEP_REMOTE_COLUMN_SIZE

parameter to suppress the expansion. Refer to

Initialization Parameters for more detail.

2.6 IPv6 Support

Besides full IPv6 support between Oracle databases and the gateway, IPv6 is

also supported between this gateway and Teradata. Refer to

HS_FDS_CONNECT_INFO

parameter in Initialization Parameters for more detail.

2.7 Gateway Session IDLE Timeout

You can optionally choose to terminate long idle gateway sessions automatically with

the gateway parameter

HS_IDLE_TIMEOUT

. Specifically, when a gateway session is idle

for more than the specified time limit, the gateway session is terminated with any

pending changes rolled back.

2.8 Database Compatibility Issues for Teradata

Teradata and Oracle databases function differently in some areas, causing

compatibility problems. The compatibility issues are described in the following links:

• Schema Considerations

• Naming Rules

• Data Types

• Queries

• Locking

• Schema Considerations

• Naming Rules

• Data Types

Chapter 2

CHAR Semantics

2-8

• Queries

• Locking

2.8.1 Schema Considerations

The Oracle concept of a schema does not exist in Teradata. A schema name included

in a query is recognized by Teradata as a database name. In the Oracle database,

the schema of an object is identical to the owner of that object. However, when one

retrieves or references an

OWNER

field in a Data Dictionary view such as

ALL_TABLES

the

OWNER

field really is referencing a Teradata database name.

When querying data dictionary tables, the following results are returned:

•

ALL_*

data dictionary tables, data for every Teradata database is returned. The

Teradata database name is returned in the

OWNER

INDEX_OWNER

, or

TABLE_OWNER

column depending on the data dictionary table being referenced.

•

ALL_USERS

data dictionary table, each Teradata database name is returned in the

USERNAME

column.

•

USER_*

data dictionary tables, data for the default Teradata database is returned

for the

OWNER

TABLE_OWNER

column depending on the data dictionary table

being referenced. If a default Teradata database is not defined, the DBC Teradata

system database is used.

2.8.2 Naming Rules

Naming rule issues include the following:

• Rules for Naming Objects

• Case Sensitivity

• Rules for Naming Objects

• Case Sensitivity

2.8.2.1 Rules for Naming Objects

Oracle and Teradata use different database object naming rules. For example, the

maximum number of characters allowed for each object name can be different.

Also, the use of single and double quotation marks, case sensitivity, and the use of

alphanumeric characters can all be different.

2.8.2.2 Case Sensitivity

The Oracle database defaults to uppercase unless you surround identifiers with double

quote characters. For example, to refer to the Teradata table called

emp

, enter the

name with double quote characters, as follows:

SQL> SELECT * FROM "emp"@TERA;

However, to refer to the Teradata table called

emp

in the Teradata database named

Scott from an Oracle application, enter the following:

SQL> SELECT * FROM "Scott"."emp"@TERA;

Chapter 2

Database Compatibility Issues for Teradata

2-9

If the Teradata table called

emp

in the Teradata database named

SCOTT

, a name

consisting entirely of uppercase letters, you can enter the owner name without double

quote characters, as follows:

SQL> SELECT * FROM SCOTT."emp"@TERA;

SQL> SELECT * FROM scott."emp"@TERA;

Oracle recommends that you surround all Teradata object names with double quote

characters and use the exact letter case for the object names as they appear in

the Teradata data dictionary. This convention is not required when referring to the

supported Oracle data dictionary tables or views listed in Data Dictionary.

If existing applications cannot be changed according to these conventions, create

views in Oracle to associate Teradata names to the correct letter case. For example,

to refer to the Teradata table

emp

from an existing Oracle application by using only

uppercase names, define the following view:

SQL> CREATE VIEW EMP (EMPNO, ENAME, SAL, HIREDATE)

AS SELECT "empno", "ename", "sal", "hiredate"

FROM "emp"@TERA;

With this view, the application can issue statements such as the following:

SQL> SELECT EMPNO, ENAME FROM EMP;

Using views is a workaround solution that duplicates data dictionary information

originating in the Teradata data dictionary. You must be prepared to update the

Oracle view definitions whenever the data definitions for the corresponding tables are

changed in the Teradata database.

2.8.3 Data Types

Data type issues include the following:

• Binary Literal Notation

• Data Type Conversion

• Binary Literal Notation

• Data Type Conversion

2.8.3.1 Binary Literal Notation

Oracle SQL uses hexadecimal digits surrounded by single quotes to express literal

values being compared or inserted into columns defined as data type

RAW

This notation is not converted to syntax compatible with the Teradata

VARBINARY

and

BINARY

data types (a

surrounded by single quotes followed by hexadecimal digits).

For example, the following statement is not supported:

SQL> INSERT INTO BINARY_TAB@TERA VALUES ('ff'xb)

where

BINARY_TAB

contains a column of data type

VARBINARY

BINARY

. Use bind

variables when inserting into or updating

VARBINARY

and

BINARY

data types.

Chapter 2

Database Compatibility Issues for Teradata

2-10

2.8.3.2 Data Type Conversion

Teradata does not support implicit date conversions. Such conversions must be

explicit.

For example, the gateway issues an error for the following

SELECT

statement:

SELECT DATE_COL FROM TEST@TERA WHERE DATE_COL = "1-JAN-2001";

To avoid problems with implicit conversions, add explicit conversions, as in the

following:

SELECT DATE_COL FROM TEST@TERA WHERE DATE_COL = TO_DATE("1-JAN-2001")

See Also:

Data Type Conversion for more information about restrictions on data types.

2.8.4 Queries

Query issues include the following:

• Row Selection

• Empty Bind Variables

• Row Selection

• Empty Bind Variables

2.8.4.1 Row Selection

Teradata evaluates a query condition for all selected rows before returning any of the

rows. If there is an error in the evaluation process for one or more rows, no rows are

returned even though the remaining rows satisfy the condition.

Oracle evaluates the query condition row-by-row and returns a row when the

evaluation is successful. Rows are returned until a row fails the evaluation.

2.8.4.2 Empty Bind Variables

For

VARCHAR

bind variables, the gateway passes empty bind variables to the Teradata

database as a

NULL

value.

2.8.5 Locking

The locking model for an Teradata database differs significantly from the Oracle model.

The gateway depends on the underlying Teradata behavior, so the following possible

scenarios can affect Oracle applications that access Teradata through the gateway:

• Read access might block write access.

• Write access might block read access.

Chapter 2

Database Compatibility Issues for Teradata

2-11

• Statement-level read consistency is not guaranteed.

See Also:

Teradata documentation for information about the Teradata locking

model.

2.9 Known Restrictions

If you encounter incompatibility problems not listed in this section or in "Known

Problems", contact Oracle Support Services. The following topics describe the known

restrictions and includes suggestions for dealing with them when possible:

• Transactional Integrity

• Transaction Capability

• COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors

• Pass-Through Feature

• Bind Variables for Date Columns

• SQL Syntax

• SQL*Plus

• Database Links

• Transactional Integrity

• Transaction Capability

• COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors

• Pass-Through Feature

• Bind Variables for Date Columns

• SQL Syntax

• SQL*Plus

• Database Links

2.9.1 Transactional Integrity

The gateway cannot guarantee transactional integrity in the following cases:

• When a statement that is processed by the gateway causes an implicit commit in

the target database

• When the target database is configured to work in autocommit mode

Chapter 2

Known Restrictions

2-12

Note:

Oracle strongly recommends the following:

– If you know that executing a particular statement causes an implicit

commit in the target database, then ensure that this statement is

executed in its own transaction.

– Do not configure the target database to work in autocommit mode.

2.9.2 Transaction Capability

The gateway does not support savepoints. If a distributed update transaction is under

way involving the gateway and a user attempts to create a savepoint, the following

error occurs:

ORA-02070: database dblink does not support savepoint in this context

By default, the gateway is configured as

COMMIT_CONFIRM

and it is always the commit

point site when the Teradata database is updated by the transaction.

2.9.3 COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open

Cursors

Any

COMMIT

ROLLBACK

issued in a PL/SQL cursor loop closes all open cursors, which

can result in the following error:

ORA-1002: fetch out of sequence

To prevent this error, move the

COMMIT

ROLLBACK

statement outside the cursor loop.

2.9.4 Pass-Through Feature

Oracle recommends that you place a DDL statement in its own transaction when

executing such a statement with the pass-through feature. An explicit

COMMIT

must be

issued after the DDL statement.

If the SQL statements being passed through the gateway result in an implicit commit at

the Teradata database, the Oracle transaction manager is unaware of the commit and

an Oracle

ROLLBACK

command cannot be used to roll back the transaction.

2.9.5 Bind Variables for Date Columns

You cannot compare columns of data type

TIME

TIMESTAMP

to a bind variable.

The following SQL statement causes an error message:

SQL> select time_column from time_table@TERA where time_column = :a;

The following error is issued:

Invalid operation on an ANSI Datetime or Interval value.

Chapter 2

Known Restrictions

2-13

2.9.6 SQL Syntax

Restrictions on SQL syntax are listed as follows:

• WHERE CURRENT OF Clause

• CONNECT BY Clause

• ROWID

• EXPLAIN PLAN Statement

• WHERE CURRENT OF Clause

• CONNECT BY Clause

• ROWID

• EXPLAIN PLAN Statement

See Also:

Supported SQL Syntax and Functions for more information about restrictions

on SQL syntax.

2.9.6.1 WHERE CURRENT OF Clause

UPDATE

and

DELETE

statements with the

WHERE CURRENT OF

clause are not supported

by the gateway because they rely on the Oracle

ROWID

implementation. To update or

delete a specific row through the gateway, a condition style

WHERE

clause must be

used.

2.9.6.2 CONNECT BY Clause

The gateway does not support the

CONNECT BY

clause in a

SELECT

statement.

2.9.6.3 ROWID

The Oracle

ROWID

implementation is not supported.

2.9.6.4 EXPLAIN PLAN Statement

The

EXPLAIN PLAN

statement is not supported.

2.9.7 SQL*Plus

You need to use double quotes to wrap around lowercase table names.

For example:

copy from tkhouser/tkhouser@inst1 insert loc_tkhodept using select * from

"tkhodept"@TERA;

Chapter 2

Known Restrictions

2-14

2.9.8 Database Links

The gateway is not a shared server process and cannot support shared database

links. Each gateway session spawns a separate gateway process and connections

cannot be shared.

2.10 Known Problems

This section describes known problems and includes suggestions for correcting them

when possible. If you have any questions or concerns about the problems, contact

Oracle Support Services. A current list of problems is available online. Contact your

local Oracle office for information about accessing the list.

The known problems are as follows:

• Teradata LONG VARCHAR Data Type

• Schema Names and PL/SQL

• Data Dictionary Views and PL/SQL

• Teradata LONG VARCHAR Data Type

• Schema Names and PL/SQL

• Data Dictionary Views and PL/SQL

2.10.1 Teradata LONG VARCHAR Data Type

The following restrictions apply when using

LONG VARCHAR

data types:

• An unsupported SQL function cannot be used in a SQL statement that accesses a

column defined as Teradata data type

LONG VARCHAR

• You cannot use SQL*Plus to select data from a column defined as Teradata

data type

LONG VARCHAR

when the data is greater than 80 characters in length.

Oracle recommends using Pro*C or Oracle Call Interface to access such data in a

Teradata database.

•

LONG VARCHAR

data types must be

NULLABLE

for

INSERT

UPDATE

to work.

• A table including a

LONG VARCHAR

column must have a unique index defined on the

table or the table must have a separate column that serves as a primary key.

•

LONG VARCHAR

data cannot be read through pass-through queries.

The gateway does not support the PL/SQL function

COLUMN_VALUE_LONG

of the

DBMS_SQL

package.

See Also:

Supported SQL Syntax and Functions for more information about restrictions

on SQL syntax.

Chapter 2

Known Problems

2-15

2.10.2 Schema Names and PL/SQL

If you do not prefix a Teradata database object with its schema name in a SQL

statement within a PL/SQL block, the following error message occurs:

ORA-6550 PLS-201 Identifier table_name must be declared.

Change the SQL statement to include the schema name of the object.

2.10.3 Data Dictionary Views and PL/SQL

You cannot refer to data dictionary views in SQL statements that are inside a PL/SQL

block.

Chapter 2

Known Problems

2-16

Case Studies

The following case studies for Teradata demonstrate some of the features of the

Oracle Database Gateway. You can verify that the gateway is installed and operating

correctly by using the demonstration files included on the distribution media.

The demonstration files are automatically copied to disk when the gateway is installed.

Topics:

• Case Descriptions

• Installation Media Contents

• Demonstration Files

• Demonstration Requirements

• Creating Demonstration Tables

• Case 1: Simple Queries

• Case 2: A More Complex Query

• Case 3: Joining Teradata Tables

• Case 4: Write Capabilities

• Case 5: Data Dictionary Query

• Case 6: The Pass-Through Feature

• Case Descriptions

• Installation Media Contents

• Demonstration Files

• Demonstration Requirements

• Creating Demonstration Tables

• Case 1: Simple Queries

• Case 2: A More Complex Query

• Case 3: Joining Teradata Tables

• Case 4: Write Capabilities

• Case 5: Data Dictionary Query

• Case 6: The Pass-Through Feature

3.1 Case Descriptions

The cases illustrate:

• A simple query (Case 1)

• A more complex query (Case 2)

3-1

• Joining Teradata tables (Case 3)

• Write capabilities (Case 4)

• A data dictionary query (Case 5)

• The pass-through feature (Case 6)

3.2 Installation Media Contents

The distribution media contains the following:

• Demonstration files.

• One SQL script file that creates the demonstration tables in the Teradata

database.

• One SQL script file that drops the demonstration tables from the Teradata

database.

3.3 Demonstration Files

After a successful gateway installation, use the demonstration files stored in the

See Also:

Oracle Database SQL Language Reference for detailed descriptions of

keywords, parameters, and options.

B.1.1 DELETE

The

DELETE

statement is fully supported. However, only Oracle functions supported by

Teradata can be used.

See Also:

"Functions Supported by Teradata" for a list of supported functions.

B-1

B.1.2 INSERT

The

INSERT

statement is fully supported. However, only Oracle functions supported by

Teradata can be used.

See Also:

"Functions Supported by Teradata" for a list of supported functions.

B.1.3 SELECT

The

SELECT

statement is fully supported, with these exceptions:

•

CONNECT BY

condition

•

NOWAIT

•

START WITH

condition

•

WHERE

CURRENT

B.1.4 UPDATE

The

UPDATE

statement is fully supported. However, only Oracle functions supported by

Teradata can be used. Also, you cannot have SQL statements in the subquery that

refer to the same table name in the outer query. Subqueries are not supported in the

SET

clause.

See Also:

"Functions Supported by Teradata" for a list of supported functions.

B.2 Oracle Functions

All functions are evaluated by the Teradata database after the gateway has converted

them to Teradata SQL.

• Functions Not Supported by Teradata

• Functions Supported by Teradata

B.2.1 Functions Not Supported by Teradata

Oracle SQL functions with no equivalent function in Teradata are not supported in

DELETE

INSERT

, or

UPDATE

statements, but are evaluated by the Oracle database

if the statement is a

SELECT

statement. That is, the Oracle database performs post-

processing of

SELECT

statements sent to the gateway.

Appendix B

Oracle Functions

B-2

If an unsupported function is used in a

DELETE

INSERT

, or

UPDATE

, statement, the

following Oracle error occurs:

ORA-02070: database db_link_name does not support function in this context

B.2.2 Functions Supported by Teradata

The gateway translates the following Oracle database functions in SQL statements to

their equivalent Teradata functions:

• Arithmetic Operators

• Comparison Operators

• Group Functions

• String Functions

• Other Functions

• Arithmetic Operators

• Comparison Operators

• Group Functions

• String Functions

• Other Functions

B.2.2.1 Arithmetic Operators

Oracle Teradata

+ +

- -

* *

/ /

B.2.2.2 Comparison Operators

Oracle Teradata

= =

> >

< <

>= >=

<= <=

^= <>

IS NOT NULL IS NOT NULL

IS NULL IS NULL

Appendix B

Oracle Functions

B-3

B.2.2.3 Group Functions

Oracle Teradata

AVG AVG

COUNT COUNT

MAX MAX

MIN MIN

SUM SUM

B.2.2.4 String Functions

Oracle Teradata

INSTR POSITION

B.2.2.5 Other Functions

Oracle Teradata

ABS ABS

EXP EXP

LOG(a, b) LOG10(b) / LOG10(a)

POWER **

SQRT SQRT

Appendix B

Oracle Functions

B-4

Data Dictionary

The Oracle Database Gateway for Teradata User's Guide translates a query that

refers to an Oracle database data dictionary table into a query that retrieves the

data from Teradata system catalog tables. You perform queries on data dictionary

tables over the database link in the same way you query data dictionary tables in the

Oracle database. The gateway data dictionary is similar to the Oracle database data

dictionary in appearance and use.

Topics:

• Data Dictionary Mapping

• Gateway Data Dictionary Descriptions

• Teradata System Catalog Tables

• Accessing the Gateway Data Dictionary

• Direct Queries to Teradata Tables

• Supported Views and Tables

• Data Dictionary Mapping

• Gateway Data Dictionary Descriptions

• ALL_CATALOG

• ALL_COL_COMMENTS

• ALL_CONS_COLUMNS

• ALL_CONSTRAINTS

• ALL_IND_COLUMNS

• ALL_INDEXES

• ALL_OBJECTS

• ALL_TAB_COLUMNS

• ALL_TAB_COMMENTS

• ALL_TABLES

• ALL_USERS

• ALL_VIEWS

• DBA_CATALOG

• DBA_COL_COMMENTS

• DBA_OBJECTS

• DBA_TAB_COLUMNS

• DBA_TAB_COMMENTS

• DBA_TABLES

C-1

• DICT_COLUMNS

• DICTIONARY

• DUAL

• USER_CATALOG

• USER_COL_COMMENTS

• USER_CONS_COLUMNS

• USER_CONSTRAINTS

• USER_IND_COLUMNS

• USER_INDEXES

• USER_OBJECTS

• USER_TAB_COLUMNS

• USER_TAB_COMMENTS

• USER_TABLES

• USER_USERS

• USER_VIEWS

C.1 Teradata System Catalog Tables

Teradata data dictionary information is stored in the Teradata database as Teradata

system catalog tables. The Teradata system catalog tables define the structure of

a database. When you change data definitions, Teradata reads and modifies the

Teradata system catalog tables to add information about the user tables.

C.2 Accessing the Gateway Data Dictionary

Accessing a gateway data dictionary table or view is identical to accessing a data

dictionary in an Oracle database. You issue a SQL

SELECT

statement specifying a

database link. The Oracle database data dictionary view and column names are used

to access the gateway data dictionary in an Oracle database. Synonyms of supported

views are also acceptable. For example, the following statement queries the data

dictionary table

ALL_CATALOG

to retrieve all table names in the Teradata database:

SQL> SELECT * FROM ALL_CATALOG@TERA;

When a data dictionary access query is issued, the gateway:

1. Maps the requested table, view, or synonym to one or more Teradata system

table names. The gateway translates all data dictionary column names to their

corresponding Teradata database column names within the query. Refer to "Data

Dictionary Mapping" for details.

2. Sends the translated query to Teradata.

3. Might convert the retrieved Teradata data to give it the appearance of the Oracle

database data dictionary table.

4. Passes the data dictionary information from the translated Teradata system

catalog table to the Oracle database.

Appendix C

Teradata System Catalog Tables

C-2

Note:

The values returned when querying the gateway data dictionary might

not be the same as the ones returned by the Oracle Enterprise Manager

DESCRIBE

command.

C.3 Direct Queries to Teradata Tables

Queries issued directly to individual Teradata system catalog tables are allowed but

they return different results because the Teradata system catalog table column names

differ from those of the data dictionary view. Also, certain columns in a Teradata

system catalog table cannot be used in data dictionary processing.

C.4 Supported Views and Tables

The gateway supports the following views and tables:

Supported Views and Tables Supported Views and Tables

ALL_CATALOG ALL_COL_COMMENTS

ALL_CONS_COLUMNS ALL_CONSTRAINTS

ALL_IND_COLUMNS ALL_INDEXES

ALL_OBJECTS ALL_TAB_COLUMNS

ALL_TAB_COMMENTS ALL_TABLES

ALL_USERS ALL_VIEWS

DBA_CATALOG DBA_COL_COMMENTS

DBA_OBJECTS DBA_TABLES

DBA_TAB_COLUMNS DBA_TAB_COMMENTS

DICT_COLUMNS DICTIONARY

DUAL USER_COL_COMMENTS

USER_CATALOG USER_CONSTRAINTS

USER_CONS_COLUMNS USER_INDEXES

USER_IND_COLUMNS USER_TAB_COLUMNS

USER_OBJECTS USER_TABLES

USER_TAB_COMMENTS USER_VIEWS

USER_USERS

No other Oracle database data dictionary tables or views are supported. If you use

a view not on the list, you receive the Oracle database error code for no more rows

available.

Queries through the gateway of any data dictionary table or view beginning with ALL_

can returns rows from the Teradata database even when access privileges for those

Teradata objects have not been granted. When querying an Oracle database with the

Oracle data dictionary, rows are returned only for those objects you are permitted to

access.

Appendix C

Direct Queries to Teradata Tables

C-3

C.5 Data Dictionary Mapping

The table in this section lists Oracle data dictionary view names and the equivalent

Teradata system tables used. A plus sign (+) indicates that a join operation is involved.

Table C-1 Oracle Data Dictionary View Names and Teradata Equivalents

View Name Teradata System Table Name

ALL_CATALOG DBC.TablesX

DBC.IndicesX

ALL_COL_COMMENTS DBC.ColumnsX

ALL_CONS_COLUMNS DBC.Indices

ALL_CONSTRAINTS DBC.IndicesX

ALL_IND_COLUMNS DBC.Indices

DBC.Tables

DBC.Columns

ALL_INDEXES DBC.Indices

ALL_OBJECTS DBC.IndicesX

DBC.TablesX

ALL_TAB_COLUMNS DBC.ColumnsX

DBC.TablesX

ALL_TAB_COMMENTS DBC.Tables

ALL_TABLES DBC.TablesX

ALL_USERS DBC.Databases

ALL_VIEWS DBC.Tables

DBA_CATALOG DBC.Tables

DBC.Indices

DBA_COL_COMMENTS DBC.Columns

DBC.Tables

DBA_OBJECTS DBC.Indices

DBC.Tables

DBA_TAB_COLUMNS DBC.Columns

DBC.Tables

DBA_TAB_COMMENTS DBC.Tables

DBA_TABLES DBC.Tables

DICT_COLUMNS DBC.Columns

DICTIONARY DBC.Tables

DUAL DBC.Tables

USER_CATALOG DBC.Tables

DBC.Indices

USER_COL_COMMENTS DBC.Columns

USER_CONS_COLUMNS DBC.Indices

USER_CONSTRAINTS DBC.IndicesX

USER_IND_COLUMNS DBC.Indices

DBC.Tables

DBC.Columns

USER_INDEXES DBC.Indices

USER_OBJECTS DBC.Indices

DBC.Tables

USER_TAB_COLUMNS DBC.ColumnsX

DBC.TablesX

USER_TAB_COMMENTS DBC.TablesX

USER_TABLES DBC.Tables

USER_USERS DBC.DatabasesX

Appendix C

Data Dictionary Mapping

C-4

Table C-1 (Cont.) Oracle Data Dictionary View Names and Teradata Equivalents

View Name Teradata System Table Name

USER_VIEWS DBC.Tables

• Default Column Values

C.5.1 Default Column Values

There is a minor difference between the gateway data dictionary and a typical Oracle

database data dictionary. The Oracle database columns that are missing in a Teradata

system catalog table are filled with zeros, spaces, null values, not-applicable values

(N.A.), or default values, depending on the column type.

C.6 Gateway Data Dictionary Descriptions

The gateway data dictionary tables and views provide the following information:

• Name, data type, and width of each column

• The contents of columns with fixed values

They are described here with information retrieved by an SQL*PLUS

DESCRIBE

command. The values in the Null? column might differ from the Oracle database data

dictionary tables and views. Any hardcoded default value is shown to the right of an

item, but this is not information returned by

DESCRIBE

C.7 ALL_CATALOG

Table C-2 ALL_CATALOG

Name Type Value

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TABLE_TYPE VARCHAR2(11)

TABLE

", "

VIEW

", or

INDEX

C.8 ALL_COL_COMMENTS

Table C-3 ALL_COL_COMMENTS

Name Type Value

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

COMMENTS VARCHAR2(4000)

" "

Appendix C

Gateway Data Dictionary Descriptions

C-5

C.9 ALL_CONS_COLUMNS

Table C-4 ALL_CONS_COLUMNS

Name Type Value

OWNER VARCHAR2(30)

CONSTRAINT_NAME VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(4000)

POSITION NUMBER

C.10 ALL_CONSTRAINTS

Table C-5 ALL_CONSTRAINTS

Name Type Value

OWNER VARCHAR2(30)

CONSTRAINT_NAME VARCHAR2(30)

CONSTRAINT_TYPE VARCHAR2(1)

" or "

TABLE_NAME VARCHAR2(30)

SEARCH_CONDITION VARCHAR2(1)

" "

R_OWNER VARCHAR2(30)

" "

R_CONSTRAINT_NAME VARCHAR2(30)

" "

DELETE_RULE VARCHAR2(9)

" "

STATUS VARCHAR2(8)

" "

DEFERRABLE VARCHAR2(14)

" "

DEFERRED VARCHAR2(9)

" "

VALIDATED VARCHAR2(13)

" "

GENERATED VARCHAR2(14)

" "

BAD VARCHAR2(3)

" "

RELY VARCHAR2(4)

" "

LAST_CHANGE DATE NULL

INDEX_OWNER VARCHAR2(30)

" "

INDEX_NAME VARCHAR2(30)

" "

INVALID VARCHAR2(7)

" "

VIEW_RELATED VARCHAR2(14)

" "

Appendix C

ALL_CONS_COLUMNS

C-6

C.11 ALL_IND_COLUMNS

Table C-6 ALL_IND_COLUMNS

Name Type Value

INDEX_OWNER VARCHAR2(30)

INDEX_NAME VARCHAR2(30)

TABLE_OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(4000)

COLUMN_POSITION NUMBER

COLUMN_LENGTH NUMBER

DESCEND VARCHAR2(4)

DESC

" or "

ASC

C.12 ALL_INDEXES

Table C-7 ALL_INDEXES

Name Type Value

OWNER VARCHAR2(30)

INDEX_NAME VARCHAR2(30)

INDEX_TYPE VARCHAR2(27)

" "

TABLE_OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TABLE_TYPE VARCHAR2(5)

TABLE

UNIQUENESS VARCHAR2(9)

UNIQUE

" or

NONUNIQUE

COMPRESSION VARCHAR2(8)

" "

PREFIX_LENGTH NUMBER 0

TABLESPACE_NAME VARCHAR2(30)

" "

INI_TRANS NUMBER 0

MAX_TRANS NUMBER(10) 0

INITIAL_EXTENT NUMBER(10) 0

NEXT_EXTENT NUMBER(10) 0

MIN_EXTENTS NUMBER(10) 0

MAX_EXTENTS NUMBER(10) 0

PCT_INCREASE NUMBER(10) 0

PCT_THRESHOLD NUMBER(10) 0

INCLUDE_COLUMN NUMBER(10) 0

FREELISTS NUMBER(10) 0

Appendix C

ALL_IND_COLUMNS

C-7

Table C-7 (Cont.) ALL_INDEXES

Name Type Value

FREELIST_GROUPS NUMBER(10) 0

PCT_FREE NUMBER(10) 0

LOGGING VARCHAR2(3)

" "

BLEVEL NUMBER 0

LEAF_BLOCKS NUMBER 0

DISTINCT_KEYS NUMBER

AVG_LEAF_BLOCKS_PER_KEY NUMBER 0

AVG_DATA_BLOCKS_PER_KEY NUMBER 0

CLUSTERING_FACTOR NUMBER 0

STATUS CHAR(1)

" "

NUM_ROWS NUMBER 0

SAMPLE_SIZE NUMBER 0

LAST_ANALYZED DATE NULL

DEGREE VARCHAR2(40)

" "

INSTANCES VARCHAR2(3)

" "

PARTITIONED VARCHAR2(3)

" "

TEMPORARY VARCHAR2(1)

" "

GENERATED VARCHAR2(1)

" "

SECONDARY VARCHAR2(1)

" "

BUFFER_POOL VARCHAR2(7)

" "

USER_STATS VARCHAR2(3)

" "

DURATION VARCHAR2(15)

" "

PCT_DIRECT_ACCESS NUMBER 0

ITYP_OWNER VARCHAR2(30)

" "

ITYP_NAME VARCHAR2(30)

" "

PARAMETERS VARCHAR2(1000)

" "

GLOBAL_STATS VARCHAR2(3)

" "

DOMIDX_STATUS VARCHAR2(12)

" "

DOMIDX_OPSTATUS VARCHAR2(6)

" "

FUNCIDX_STATUS VARCHAR2(8)

" "

JOIN_INDEX VARCHAR2(3)

IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)

DROPPED VARCHAR2(3)

VISIBILITY VARCHAR2(9)

DOMIDX_MANAGEMENT VARCHAR2(14)

Appendix C

ALL_INDEXES

C-8

C.13 ALL_OBJECTS

Table C-8 ALL_OBJECTS

Name Type Value

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(30)

SUBOBJECT_NAME VARCHAR2(30) NULL

OBJECT_ID NUMBER 0

DATA_OBJECT_ID NUMBER 0

OBJECT_TYPE VARCHAR2(19)

TABLE

" or "

VIEW

or "

INDEX

" or

PROCEDURE

CREATED DATE NULL

LAST_DDL_TIME DATE NULL

TIMESTAMP VARCHAR2(19) NULL

STATUS VARCHAR2(7) NULL

TEMPORARY VARCHAR2(1) NULL

GENERATED VARCHAR2(1) NULL

SECONDARY VARCHAR2(1) NULL

NAMESPACE NUMBER NULL

EDITION_NAME VARCHAR2(30) NULL

C.14 ALL_TAB_COLUMNS

Table C-9 ALL_TAB_COLUMNS

Name Type Value

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

DATA_TYPE VARCHAR2(106)

DATA_TYPE_MOD VARCHAR2(3)

" "

DATA_TYPE_OWNER VARCHAR2(30)

" "

DATA_LENGTH NUMBER

DATA_PRECISION NUMBER

DATA_SCALE NUMBER

NULLABLE VARCHAR2(1)

" or "

COLUMN_ID NUMBER

DEFAULT_LENGTH NUMBER 0

Appendix C

ALL_OBJECTS

C-9

Table C-9 (Cont.) ALL_TAB_COLUMNS

Name Type Value

DATA_DEFAULT VARCHAR2(1024)

" "

NUM_DISTINCT NUMBER 0

LOW_VALUE RAW(32) 0

HIGH_VALUE RAW(32) 0

DENSITY NUMBER 0

NUM_NULLS NUMBER 0

NUM_BUCKETS NUMBER 0

LAST_ANALYZED DATE NULL

SAMPLE_SIZE NUMBER 0

CHARACTER_SET_NAME VARCHAR2(44)

" "

CHAR_COL_DECL_LENGTH NUMBER 0

GLOBAL_STATS VARCHAR2(3)

" "

USER_STATS VARCHAR2(3)

" "

AVG_COL_LEN NUMBER 0

CHAR_LENGTH NUMBER

CHAR_USED VARCHAR2(1)

V80_FMT_IMAGE VARCHAR2(3)

DATA_UPGRADED VARCHAR2(3)

HISTOGRAM VARCHAR2(15)

C.15 ALL_TAB_COMMENTS

Table C-10 ALL_TAB_COMMENTS

Name Type Value

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TABLE_TYPE VARCHAR2(11)

TABLE

" or "

VIEW

COMMENTS VARCHAR2(4000)

" "

C.16 ALL_TABLES

Table C-11 ALL_TABLES

Name Type Value

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

Appendix C

ALL_TAB_COMMENTS

C-10

Table C-11 (Cont.) ALL_TABLES

Name Type Value

TABLESPACE_NAME VARCHAR2(30)

" "

CLUSTER_NAME VARCHAR2(30)

" "

IOT_NAME VARCHAR2(8)

" "

PCT_FREE NUMBER 0

PCT_USED NUMBER 0

INI_TRANS NUMBER 0

MAX_TRANS NUMBER 0

INITIAL_EXTENT NUMBER 0

NEXT_EXTENT NUMBER 0

MIN_EXTENTS NUMBER 0

MAX_EXTENTS NUMBER 0

PCT_INCREASE NUMBER 0

FREELISTS NUMBER 0

FREELIST_GROUPS NUMBER 0

LOGGING VARCHAR2(3)

" "

BACKED_UP VARCHAR2(1)

" "

NUM_ROWS NUMBER

BLOCKS NUMBER

EMPTY_BLOCKS NUMBER 0

AVG_SPACE NUMBER 0

CHAIN_CNT NUMBER 0

AVG_ROW_LEN NUMBER 0

AVG_SPACE_FREELIST_BLOCKS NUMBER 0

NUM_FREELIST_BLOCKS NUMBER 0

DEGREE VARCHAR2(10)

" "

INSTANCES VARCHAR2(10)

" "

CACHE VARCHAR2(5)

" "

TABLE_LOCK VARCHAR2(8)

" "

SAMPLE_SIZE NUMBER 0

LAST_ANALYZED DATE NULL

PARTITIONED VARCHAR2(3)

" "

IOT_TYPE VARCHAR2(12)

" "

TEMPORARY VARCHR2(1)

" "

SECONDARY VARCHR2(1)

" "

NESTED VARCHAR2(3)

" "

BUFFER_POOL VARCHAR2(7)

" "

ROW_MOVEMENT VARCHAR2(8)

" "

Appendix C

ALL_TABLES

C-11

Table C-11 (Cont.) ALL_TABLES

Name Type Value

GLOBAL_STATS VARCHAR2(3)

" "

USER_STATS VARCHAR2(3)

" "

DURATION VARCHAR2(15)

" "

SKIP_CORRUPT VARCHAR2(8)

" "

MONITORING VARCHAR2(3)

" "

CLUSTER_OWNER VARCHAR2(30)

DEPENDENCIES VARCHAR2(8)

COMPRESSION VARCHAR2(8)

DROPPED VARCHAR2(3)

C.17 ALL_USERS

Table C-12 ALL_USERS

Name Type Value

USERNAME VARCHAR2(30)

USER_ID NUMBER 0

CREATED DATE NULL

C.18 ALL_VIEWS

Table C-13 ALL_VIEWS

Name Type Value

OWNER VARCHAR2(30)

VIEW_NAME VARCHAR2(30)

TEXT_LENGTH NUMBER 0

TEXT VARCHAR2(1)

" "

TYPE_TEXT_LENGTH NUMBER 0

TYPE_TEXT VARCHAR2(4000)

" "

OID_TEXT_LENGTH NUMBER 0

OID_TEXT VARCHAR2(4000)

" "

VIEW_TYPE_OWNER VARCHAR2(30)

" "

VIEW_TYPE VARCHAR2(30)

" "

SUPERVIEW_NAME VARCHAR2(30)

EDITIONING_VIEW VARCHAR2(1)

READ_ONLY VARCHAR2(1)

Appendix C

ALL_USERS

C-12

C.19 DBA_CATALOG

Table C-14 DBA_CATALOG

Name Type Value

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TABLE_TYPE VARCHAR2(11)

TABLE

" or "

VIEW

C.20 DBA_COL_COMMENTS

Table C-15 DBA_COL_COMMENTS

Name Type Value

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

COMMENTS VARCHAR2(4000)

" "

C.21 DBA_OBJECTS

Table C-16 DBA_OBJECTS

Name Type Value

OWNER VARCHAR2(30)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30) NULL

OBJECT_ID NUMBER 0

DATA_OBJECT_ID NUMBER 0

OBJECT_TYPE VARCHAR2(19)

TABLE

" or "

VIEW

or "

INDEX

" or

PROCEDURE

CREATED DATE NULL

LAST_DDL_TIME DATE NULL

TIMESTAMP VARCHAR2(19) NULL

STATUS VARCHAR2(7) NULL

TEMPORARY VARCHAR2(1) NULL

GENERATED VARCHAR2(1) NULL

SECONDARY VARCHAR2(1) NULL

NAMESPACE NUMBER

Appendix C

DBA_CATALOG

C-13

Table C-16 (Cont.) DBA_OBJECTS

Name Type Value

EDITION_NAME VARCHAR2(30)

C.22 DBA_TAB_COLUMNS

Table C-17 DBA_TAB_COLUMNS

Name Type Value

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

DATA_TYPE VARCHAR2(106)

DATA_TYPE_MOD VARCHAR2(3)

" "

DATA_TYPE_OWNER VARCHAR2(30)

" "

DATA_LENGTH NUMBER

DATA_PRECISION NUMBER

DATA_SCALE NUMBER

NULLABLE VARCHAR2(1)

" or "

COLUMN_ID NUMBER

DEFAULT_LENGTH NUMBER 0

DATA_DEFAULT VARCHAR2(1024)

" "

NUM_DISTINCT NUMBER 0

LOW_VALUE RAW(32) 0

HIGH_VALUE RAW(32) 0

DENSITY NUMBER 0

NUM_NULLS NUMBER 0

NUM_BUCKETS NUMBER 0

LAST_ANALYZED DATE NULL

SAMPLE_SIZE NUMBER 0

CHARACTER_SET_NAME VARCHAR2(44)

" "

CHAR_COL_DECL_LENGTH NUMBER 0

GLOBAL_STATS VARCHAR2(3)

" "

USER_STATS VARCHAR2(3)

" "

AVG_COL_LEN NUMBER 0

CHAR_LENGTH NUMBER

CHAR_USED VARCHAR2(1)

V80_FMT_IMAGE VARCHAR2(3)

DATA_UPGRADED VARCHAR2(3)

Appendix C

DBA_TAB_COLUMNS

C-14

Table C-17 (Cont.) DBA_TAB_COLUMNS

Name Type Value

HISTOGRAM VARCHAR2(15)

C.23 DBA_TAB_COMMENTS

Table C-18 DBA_TAB_COMMENTS

Name Type Value

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TABLE_TYPE VARCHAR2(11)

TABLE

" or "

VIEW

COMMENTS VARCHAR2(4000)

" "

C.24 DBA_TABLES

Table C-19 DBA_TABLES

Name Type Value

OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TABLESPACE_NAME VARCHAR2(30)

" "

CLUSTER_NAME VARCHAR2(30)

" "

IOT_NAME VARCHAR2(30)

" "

STATUS VARCHAR2(8)

PCT_FREE NUMBER 0

PCT_USED NUMBER 0

INI_TRANS NUMBER 0

MAX_TRANS NUMBER 0

INITIAL_EXTENT NUMBER 0

NEXT_EXTENT NUMBER 0

MIN_EXTENTS NUMBER 0

MAX_EXTENTS NUMBER 0

PCT_INCREASE NUMBER 0

FREELISTS NUMBER 0

FREELIST_GROUPS NUMBER 0

LOGGING VARCHAR2(3)

" "

BACKED_UP VARCHAR2(1)

" "

NUM_ROWS NUMBER

Appendix C

DBA_TAB_COMMENTS

C-15

Table C-19 (Cont.) DBA_TABLES

Name Type Value

BLOCKS NUMBER

EMPTY_BLOCKS NUMBER 0

AVG_SPACE NUMBER 0

CHAIN_CNT NUMBER 0

AVG_ROW_LEN NUMBER 0

AVG_SPACE_FREELIST_BLOCKS NUMBER 0

NUM_FREELIST_BLOCKS NUMBER 0

DEGREE VARCHAR2(10)

" "

INSTANCES VARCHAR2(10)

" "

CACHE VARCHAR2(5)

" "

TABLE_LOCK VARCHAR2(8)

" "

SAMPLE_SIZE NUMBER 0

LAST_ANALYZED DATE NULL

PARTITIONED VARCHAR2(3)

" "

IOT_TYPE VARCHAR2(12)

" "

TEMPORARY VARCHAR2(1)

" "

SECONDARY VARCHAR2(1)

" "

NESTED VARCHAR2(3)

" "

BUFFER_POOL VARCHAR2(7)

" "

ROW_MOVEMENT VARCHAR2(8)

" "

GLOBAL_STATS VARCHAR2(3)

" "

USER_STATS VARCHAR2(3)

" "

DURATION VARCHAR2(15)

" "

SKIP_CORRUPT VARCHAR2(8)

" "

MONITORING VARCHAR2(3)

" "

CLUSTER_OWNER VARCHAR2(30)

DEPENDENCIES VARCHAR2(8)

COMPRESSION VARCHAR2(8)

DROPPED VARCHAR2(3)

C.25 DICT_COLUMNS

Table C-20 DICT_COLUMNS

Name Type Value

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

Appendix C

DICT_COLUMNS

C-16

Table C-20 (Cont.) DICT_COLUMNS

Name Type Value

COMMENTS VARCHAR2(4000)

" "

C.26 DICTIONARY

Table C-21 DICTIONARY

Name Type Value

TABLE_NAME VARCHAR2(30)

COMMENTS VARCHAR2(4000)

" "

C.27 DUAL

Table C-22 DUAL

Name Type Value

DUMMY VARCHAR2(1)

C.28 USER_CATALOG

Table C-23 USER_CATALOG

Name Type Value

TABLE_NAME VARCHAR2(30)

TABLE_TYPE VARCHAR2(11)

TABLE

" or "

VIEW

C.29 USER_COL_COMMENTS

Table C-24 USER_COL_COMMENTS

Name Type Value

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

COMMENTS VARCHAR2(4000)

" "

Appendix C

DICTIONARY

C-17

C.30 USER_CONS_COLUMNS

Table C-25 USER_CONS_COLUMNS

Name Type Value

OWNER VARCHAR2(30)

CONSTRAINT_NAME VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(4000)

POSITION NUMBER

C.31 USER_CONSTRAINTS

Table C-26 USER_CONSTRAINTS

Name Type Value

OWNER VARCHAR2(30)

CONSTRAINT_NAME VARCHAR2(30)

CONSTRAINT_TYPE VARCHAR2(1)

" or "

TABLE_NAME VARCHAR2(30)

SEARCH_CONDITION CHAR(1)

" "

R_OWNER VARCHAR2(30)

R_CONSTRAINT_NAME VARCHAR2(30)

DELETE_RULE VARCHAR2(9)

" "

STATUS VARCHAR2(8)

" "

DEFERRABLE VARCHAR2(14)

" "

DEFERRED VARCHAR2(9)

" "

VALIDATED VARCHAR2(13)

" "

GENERATED VARCHAR2(14)

" "

BAD VARCHAR2(3)

" "

RELY VARCHAR2(4)

" "

LAST_CHANGE DATE NULL

INDEX_OWNER VARCHAR2(30)

INDEX_NAME VARCHAR2(30)

INVALID VARCHAR2(7)

VIEW_RELATED VARCHAR2(14)

Appendix C

USER_CONS_COLUMNS

C-18

C.32 USER_IND_COLUMNS

Table C-27 USER_IND_COLUMNS

Name Type Value

INDEX_NAME VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(4000)

COLUMN_POSITION NUMBER

COLUMN_LENGTH NUMBER

CHAR_LENGTH NUMBER

DESCEND VARCHAR2(4)

DESC

" or "

ASC

C.33 USER_INDEXES

Table C-28 USER_INDEXES

Name Type Value

INDEX_NAME VARCHAR2(30)

INDEX_TYPE VARCHAR2(27)

" "

TABLE_OWNER VARCHAR2(30)

TABLE_NAME VARCHAR2(30)

TABLE_TYPE VARCHAR2(11)

TABLE

UNIQUENESS VARCHAR2(9)

UNIQUE

" or

NONUNIQUE

COMPRESSION VARCHAR2(8)

" "

PREFIX_LENGTH NUMBER 0

TABLESPACE_NAME VARCHAR2(30)

" "

INI_TRANS NUMBER 0

MAX_TRANS NUMBER 0

INITIAL_EXTENT NUMBER 0

NEXT_EXTENT NUMBER 0

MIN_EXTENTS NUMBER 0

MAX_EXTENTS NUMBER 0

PCT_INCREASE NUMBER 0

PCT_THRESHOLD NUMBER 0

INCLUDE_COLUMN NUMBER 0

FREELISTS NUMBER 0

FREELIST_GROUPS NUMBER 0

PCT_FREE NUMBER 0

Appendix C

USER_IND_COLUMNS

C-19

Table C-28 (Cont.) USER_INDEXES

Name Type Value

LOGGING VARCHAR2(3)

" "

BLEVEL NUMBER 0

LEAF_BLOCKS NUMBER 0

DISTINCT_KEYS NUMBER

AVG_LEAF_BLOCKS_PER_KEY NUMBER 0

AVG_DATA_BLOCKS_PER_KEY NUMBER 0

CLUSTERING_FACTOR NUMBER

STATUS VARCHAR2(8)

" "

NUM_ROWS NUMBER 0

SAMPLE_SIZE NUMBER 0

LAST_ANALYZED DATE NULL

DEGREE VARCHAR2(40)

" "

INSTANCES VARCHAR2(40)

" "

PARTITIONED VARCHAR2(3)

" "

TEMPORARY VARCHAR2(1)

" "

GENERATED VARCHAR2(1)

" "

SECONDARY VARCHAR2(1)

" "

BUFFER_POOL VARCHAR2(7)

" "

USER_STATS VARCHAR2(3)

" "

DURATION VARCHAR2(15)

" "

PCT_DIRECT_ACCESS NUMBER 0

ITYP_OWNER VARCHAR2(30)

" "

ITYP_NAME VARCHAR2(30)

" "

PARAMETERS VARCHAR2(1000)

" "

GLOBAL_STATS VARCHAR2(3)

" "

DOMIDX_STATUS VARCHAR2(12)

" "

DOMIDX_OPSTATUS VARCHAR2(6)

" "

FUNCIDX_STATUS VARCHAR2(8)

" "

JOIN_INDEX VARCHAR2(3)

IOT_REDUNDANT_PKEY_ELIM VARCHAR2(3)

DROPPED VARCHAR2(3)

VISIBILITY VARCHAR2(10)

DOMIDX_MANAGEMENT VARCHAR2(14)

Appendix C

USER_INDEXES

C-20

C.34 USER_OBJECTS

Table C-29 USER_OBJECTS

Name Type Value

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(30) NULL

OBJECT_ID NUMBER 0

DATA_OBJECT_ID NUMBER 0

OBJECT_TYPE VARCHAR2(19)

TABLE

" or "

VIEW

or "

INDEX

" or

PROCEDURE

CREATED DATE NULL

LAST_DDL_TIME DATE NULL

TIMESTAMP VARCHAR2(19) NULL

STATUS VARCHAR2(7) NULL

TEMPORARY VARCHAR2(1) NULL

GENERATED VARCHAR2(1) NULL

SECONDARY VARCHAR2(1) NULL

NAMESPACE NUMBER

EDITION_NAME VARCHAR2(30)

C.35 USER_TAB_COLUMNS

Table C-30 USER_TAB_COLUMNS

Name Type Value

TABLE_NAME VARCHAR2(30)

COLUMN_NAME VARCHAR2(30)

DATA_TYPE VARCHAR2(106)

DATA_TYPE_MOD VARCHAR2(3)

" "

DATA_TYPE_OWNER VARCHAR2(30)

" "

DATA_LENGTH NUMBER

DATA_PRECISION NUMBER

DATA_SCALE NUMBER

NULLABLE VARCHAR2(1)

" or "

COLUMN_ID NUMBER

DEFAULT_LENGTH NUMBER 0

DATA_DEFAULT CHAR(1)

" "

NUM_DISTINCT NUMBER 0

Appendix C

USER_OBJECTS

C-21

Table C-30 (Cont.) USER_TAB_COLUMNS

Name Type Value

LOW_VALUE RAW(32) 0

HIGH_VALUE RAW(32) 0

DENSITY NUMBER 0

NUM_NULLS NUMBER 0

NUM_BUCKETS NUMBER 0

LAST_ANALYZED DATE NULL

SAMPLE_SIZE NUMBER 0

CHARACTER_SET_NAME VARCHAR2(44)

" "

CHAR_COL_DECL_LENGTH NUMBER 0

GLOBAL_STATS VARCHAR2(3)

" "

USER_STATS VARCHAR2(3)

" "

AVG_COL_LEN NUMBER 0

CHAR_LENGTH NUMBER

CHAR_USED VARCHAR2(1)

V80_FMT_IMAGE VARCHAR2(3)

DATA_UPGRADED VARCHAR2(3)

HISTOGRAM VARCHAR2(15)

C.36 USER_TAB_COMMENTS

Table C-31 USER_TAB_COMMENTS

Name Type Value

TABLE_NAME VARCHAR2(30)

TABLE_TYPE VARCHAR2(11)

TABLE

" or "

VIEW

COMMENTS VARCHAR2(4000)

" "

C.37 USER_TABLES

Table C-32 USER_TABLES

Name Type Value

TABLE_NAME VARCHAR2(30)

TABLESPACE_NAME VARCHAR2(30)

" "

CLUSTER_NAME VARCHAR2(30)

" "

IOT_NAME VARCHAR2(30)

" "

STATUS VARCHAR2(8)

Appendix C

USER_TAB_COMMENTS

C-22

Table C-32 (Cont.) USER_TABLES

Name Type Value

PCT_FREE NUMBER 0

PCT_USED NUMBER 0

INI_TRANS NUMBER 0

MAX_TRANS NUMBER 0

INITIAL_EXTENT NUMBER 0

NEXT_EXTENT NUMBER 0

MIN_EXTENTS NUMBER 0

MAX_EXTENTS NUMBER 0

PCT_INCREASE NUMBER 0

FREELISTS NUMBER 0

FREELIST_GROUPS NUMBER 0

LOGGING VARCHAR2(3)

" "

BACKED_UP VARCHAR2(1)

" "

NUM_ROWS NUMBER

BLOCKS NUMBER

EMPTY_BLOCKS NUMBER 0

AVG_SPACE NUMBER 0

CHAIN_CNT NUMBER 0

AVG_ROW_LEN NUMBER 0

AVG_SPACE_FREELIST_BLOCKS NUMBER 0

NUM_FREELIST_BLOCKS NUMBER 0

DEGREE VARCHAR2(10)

" "

INSTANCES VARCHAR2(10)

" "

CACHE VARCHAR2(5)

" "

TABLE_LOCK VARCHAR2(8)

" "

SAMPLE_SIZE NUMBER 0

LAST_ANALYZED DATE NULL

PARTITIONED VARCHAR2(3)

" "

IOT_TYPE VARCHAR2(12)

" "

TEMPORARY VARCHAR2(1)

" "

SECONDARY VARCHAR2(1)

" "

NESTED VARCHAR2(3)

" "

BUFFER_POOL VARCHAR2(7)

" "

ROW_MOVEMENT VARCHAR2(8)

" "

GLOBAL_STATS VARCHAR2(3)

" "

USER_STATS VARCHAR2(3)

" "

DURATION VARCHAR2(15)

" "

Appendix C

USER_TABLES

C-23

Table C-32 (Cont.) USER_TABLES

Name Type Value

SKIP_CORRUPT VARCHAR2(8)

" "

MONITORING VARCHAR2(3)

" "

C.38 USER_USERS

Table C-33 USER_USERS

Name Type Value

USERNAME VARCHAR2(30)

USER_ID NUMBER 0

ACCOUNT_STATUS VARCHAR2(32)

OPEN

LOCK_DATE DATE NULL

EXPIRY_DATE DATE NULL

DEFAULT_TABLESPACE VARCHAR2(30) NULL

TEMPORARY_TABLESPACE VARCHAR2(30) NULL

CREATED DATE NULL

INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(30) NULL

EXTERNAL_NAME VARCHAR2(4000) NULL

C.39 USER_VIEWS

Table C-34 USER_VIEWS

Name Type Value

VIEW_NAME VARCHAR2(30)

TEXT_LENGTH NUMBER 0

TEXT CHAR(1)

" "

TYPE_TEXT_LENGTH NUMBER 0

TYPE_TEXT VARCHAR2(4000)

" "

OID_TEXT_LENGTH NUMBER 0

OID_TEXT VARCHAR2(4000)

" "

VIEW_TYPE_OWNER VARCHAR2(30)

" "

VIEW_TYPE VARCHAR2(30)

" "

SUPERVIEW_NAME VARCHAR2(30)

EDITIONING_VIEW VARCHAR2(1)

Appendix C

USER_USERS

C-24

Initialization Parameters

The Oracle database initialization parameters in the

init.ora

file are distinct from

gateway initialization parameters. Set the gateway parameters in the initialization

parameter file using an agent-specific mechanism, or set them in the Oracle data

dictionary using the

DBMS_HS

package. The gateway initialization parameter file must

be available when the gateway is started.

The following topics contain a list of the gateway initialization parameters that can be

set for each gateway and their description. The topics also describe the initialization

parameter file syntax:

• Initialization Parameter File Syntax

• Oracle Database Gateway for Teradata Initialization Parameters

• Initialization Parameter File Syntax

• Oracle Database Gateway for Teradata Initialization Parameters

• HS_DB_DOMAIN

• HS_DB_INTERNAL_NAME

• HS_DB_NAME

• HS_DESCRIBE_CACHE_HWM

• HS_LANGUAGE

• HS_LONG_PIECE_TRANSFER_SIZE

• HS_OPEN_CURSORS

• HS_RPC_FETCH_REBLOCKING

• HS_RPC_FETCH_SIZE

• HS_TIME_ZONE

• HS_TRANSACTION_MODEL

• IFILE

• HS_FDS_TIMESTAMP_MAPPING

• HS_FDS_DATE_MAPPING

• HS_FDS_CONNECT_INFO

• HS_FDS_DEFAULT_OWNER

• HS_FDS_TRANSACTION_ISOLATION

• HS_FDS_PROC_IS_FUNC

• HS_FDS_RECOVERY_ACCOUNT

• HS_FDS_RECOVERY_PWD

• HS_FDS_TRACE_LEVEL

D-1

• HS_FDS_TRANSACTION_LOG

• HS_FDS_FETCH_ROWS

• HS_IDLE_TIMEOUT

• HS_NLS_LENGTH_SEMANTICS

• HS_KEEP_REMOTE_COLUMN_SIZE

• HS_FDS_REMOTE_DB_CHARSET

• HS_FDS_SUPPORT_STATISTICS

• HS_FDS_ARRAY_EXEC

D.1 Initialization Parameter File Syntax

The syntax for the initialization parameter file is as follows:

• The file is a sequence of commands.

• Each command should start on a separate line.

• End of line is considered a command terminator (unless escaped with a

backslash).

• If there is a syntax error in an initialization parameter file, none of the settings take

effect.

• Set the parameter values as follows:

[SET][PRIVATE] parameter=value

Where:

parameter

is an initialization parameter name. It is a string of characters starting

with a letter and consisting of letters, digits and underscores. Initialization

parameter names are case sensitive.

value

is the initialization parameter value. It is case sensitive. An initialization

parameter value is either:

– A string of characters that does not contain any backslashes, white space or

double quotation marks (")

– A quoted string beginning with a double quotation mark and ending with a

double quotation mark. The following can be used inside a quoted string:

* backslash (\) is the escape character

* \n inserts a new line

* \t inserts a tab

* \" inserts a double quotation mark

* \\ inserts a backslash

A backslash at the end of the line continues the string on the next line. If a

backslash precedes any other character then the backslash is ignored.

For example, to enable tracing for an agent, set the

HS_FDS_TRACE_LEVEL

initialization parameter as follows:

HS_FDS_TRACE_LEVEL=ON

Appendix D

Initialization Parameter File Syntax

D-2

SET

and

PRIVATE

are optional keywords. You cannot use either as an initialization

parameter name. Most parameters are needed only as initialization parameters, so

you usually do not need to use the

SET

PRIVATE

keywords. If you do not specify

either

SET

PRIVATE

, the parameter is used only as an initialization parameter for

the agent.

SET

specifies that, in addition to being used as an initialization parameter, the

parameter value is set as an environment variable for the agent process. Use

SET

for parameter values that the drivers or non-Oracle system need as environment

variables.

PRIVATE

specifies that the initialization parameter should be private to the agent

and should not be uploaded to the Oracle database. Most initialization parameters

should not be private. If, however, you are storing sensitive information like a

password in the initialization parameter file, then you may not want it uploaded

to the server because the initialization parameters and values are not encrypted

when uploaded. Making the initialization parameters private prevents the upload

from happening and they do not appear in dynamic performance views. Use

PRIVATE

for the initialization parameters only if the parameter value includes

sensitive information such as a user name or password.

SET PRIVATE

specifies that the parameter value is set as an environment variable

for the agent process and is also private (not transferred to the Oracle database,

not appearing in dynamic performance views or graphical user interfaces).

D.2 Oracle Database Gateway for Teradata Initialization

Parameters

The initialization file parameters that can be set for the Oracle Database Gateway for

Teradata are as follows:

• HS_DB_DOMAIN

• HS_DB_INTERNAL_NAME

• HS_DB_NAME

• HS_DESCRIBE_CACHE_HWM

• HS_LANGUAGE

• HS_LONG_PIECE_TRANSFER_SIZE

• HS_FDS_TRANSACTION_ISOLATION

• HS_OPEN_CURSORS

• HS_RPC_FETCH_REBLOCKING

• HS_RPC_FETCH_SIZE

• HS_TIME_ZONE

• HS_TRANSACTION_MODEL

• IFILE

• HS_FDS_TIMESTAMP_MAPPING

• HS_FDS_DATE_MAPPING

• HS_FDS_CONNECT_INFO

Appendix D

Oracle Database Gateway for Teradata Initialization Parameters

D-3

• HS_FDS_DEFAULT_OWNER

• HS_FDS_PROC_IS_FUNC

• HS_FDS_RECOVERY_ACCOUNT

• HS_FDS_RECOVERY_PWD

• HS_FDS_TRACE_LEVEL

• HS_FDS_TRANSACTION_LOG

• HS_FDS_FETCH_ROWS

• HS_IDLE_TIMEOUT

• HS_NLS_LENGTH_SEMANTICS

• HS_KEEP_REMOTE_COLUMN_SIZE

• HS_FDS_REMOTE_DB_CHARSET

• HS_FDS_SUPPORT_STATISTICS

• HS_FDS_ARRAY_EXEC

D.3 HS_DB_DOMAIN

Property Description

Default value

WORLD

Range of values

199

characters

Specifies a unique network sub-address for a non-Oracle system. The

HS_DB_DOMAIN

initialization parameter is similar to the

DB_DOMAIN

initialization parameter, described in

the Oracle Database Reference. The

HS_DB_DOMAIN

initialization parameter is required

if you use the Oracle Names server. The

HS_DB_NAME

and

HS_DB_DOMAIN

initialization

parameters define the global name of the non-Oracle system.

Note:

The

HS_DB_NAME

and

HS_DB_DOMAIN

initialization parameters must combine to

form a unique address in a cooperative server environment.

D.4 HS_DB_INTERNAL_NAME

Property

Description

Default value

01010101

Range of values

hexadecimal characters

Specifies a unique hexadecimal number identifying the instance to which the

Heterogeneous Services agent is connected. This parameter's value is used as part

of a transaction ID when global name services are activated. Specifying a nonunique

Appendix D

HS_DB_DOMAIN

D-4

number can cause problems when two-phase commit recovery actions are necessary

for a transaction.

D.5 HS_DB_NAME

Property Description

Default value

Range of values

characters

Specifies a unique alphanumeric name for the data store given to the non-Oracle

system. This name identifies the non-Oracle system within the cooperative server

environment. The

HS_DB_NAME

and

HS_DB_DOMAIN

initialization parameters define the

global name of the non-Oracle system.

D.6 HS_DESCRIBE_CACHE_HWM

Property Description

Default value

100

Range of values

4000

Specifies the maximum number of entries in the describe cache used by

Heterogeneous Services. This limit is known as the describe cache high water mark.

The cache contains descriptions of the mapped tables that Heterogeneous Services

reuses so that it does not have to re-access the non-Oracle data store.

If you are accessing many mapped tables, increase the high water mark to improve

performance. Increasing the high water mark improves performance at the cost of

memory usage.

D.7 HS_LANGUAGE

Property Description

Default value System-specific

Range of values

Any valid language name (up to

255

characters)

Provides Heterogeneous Services with character set, language, and territory

information of the non-Oracle data source. The value must use the following format:

language[_territory.character_set]

Note:

The globalization support initialization parameters affect error messages, the

data for the SQL Service, and parameters in distributed external procedures.

• Character Sets

Appendix D

HS_DB_NAME

D-5

• Language

• Territory

D.7.1 Character Sets

Ideally, the character sets of the Oracle database and the non-Oracle data source are

the same. In almost all cases,

HS_LANGUAGE

should be set exactly the same as Oracle

database character set for optimal character set mapping and performance. If they are

not the same, Heterogeneous Services attempts to translate the character set of the

non-Oracle data source to the Oracle database character set, and back again. The

translation can degrade performance. In some cases, Heterogeneous Services cannot

translate a character from one character set to another.

Note:

The specified character set must be a superset of the operating system

character set on the platform where the agent is installed.

As more Oracle databases and non-Oracle databases use Unicode as database

character sets, it is preferable to also run the gateway in Unicode character set. To

do so, you must set

HS_LANGUAGE=AL32UTF8

. However, when the gateway runs on

Windows, the Microsoft ODBC Driver Manager interface can exchange data only in

the double-byte character set, UCS2. This results in extra ratio expansion of described

buffer and column sizes. Refer to HS_FDS_REMOTE_DB_CHARSET for instruction

on how to adjust to correct sizes.

D.7.2 Language

The language component of the

HS_LANGUAGE

initialization parameter determines:

• Day and month names of dates

• AD, BC, PM, and AM symbols for date and time

• Default sorting mechanism

Note that Oracle does not determine the language for error messages for the

generic Heterogeneous Services messages (

ORA-25000

through

ORA-28000

). These

are controlled by the session settings in the Oracle database.

D.7.3 Territory

The territory clause specifies the conventions for day and week numbering, default

date format, decimal character and group separator, and ISO and local currency

symbols. Note that the level of globalization support between the Oracle database

and the non-Oracle data source depends on how the gateway is implemented.

Appendix D

HS_LANGUAGE

D-6

D.8 HS_LONG_PIECE_TRANSFER_SIZE

Property Description

Default value

64 KB

Range of values

Any value up to

2 GB

Sets the size of the piece of

LONG

data being transferred. A smaller piece size means

less memory requirement, but more round-trips to fetch all the data. A larger piece size

means fewer round-trips, but more of a memory requirement to store the intermediate

pieces internally. Thus, the initialization parameter can be used to tune a system

for the best performance, with the best trade-off between round-trips and memory

requirements, and network latency or response time.

D.9 HS_OPEN_CURSORS

Property Description

Default value

Range of values

to the value of Oracle's

OPEN_CURSORS

initialization parameter

Defines the maximum number of cursors that can be open on one connection to a

non-Oracle system instance.

The value never exceeds the number of open cursors in the Oracle database.

Therefore, setting the same value as the

OPEN_CURSORS

initialization parameter in the

Oracle database is recommended.

D.10 HS_RPC_FETCH_REBLOCKING

Property Description

Default value

Range of values

OFF

Controls whether Heterogeneous Services attempts to optimize performance of

data transfer between the Oracle database and the Heterogeneous Services agent

connected to the non-Oracle data store.

The following values are possible:

•

OFF

disables reblocking of fetched data so that data is immediately sent from agent

to server.

•

enables reblocking, which means that data fetched from the non-Oracle system

is buffered in the agent and is not sent to the Oracle database until the amount of

fetched data is equal or higher than the value of

HS_RPC_FETCH_SIZE

initialization

parameter. However, any buffered data is returned immediately when a fetch

indicates that no more data exists or when the non-Oracle system reports an error.

Appendix D

HS_LONG_PIECE_TRANSFER_SIZE

D-7

D.11 HS_RPC_FETCH_SIZE

Property Description

Default value

50000

Range of values

10000000

Tunes internal data buffering to optimize the data transfer rate between the server and

the agent process.

Increasing the value can reduce the number of network round-trips needed to transfer

a given amount of data, but also tends to increase data bandwidth and to reduce

latency as measured between issuing a query and completion of all fetches for the

query. Nevertheless, increasing the fetch size can increase latency for the initial fetch

results of a query, because the first fetch results are not transmitted until additional

data is available.

D.12 HS_TIME_ZONE

Property Description

Default value for

'[+|-]hh:mm'

Derived from the

NLS_TERRITORY

initialization parameter

Range of values for

'[+|-]hh:mm'

Any valid datetime format mask

Specifies the default local time zone displacement for the current SQL session. The

format mask, [+|-]hh:mm, is specified to indicate the hours and minutes before or after

UTC (Coordinated Universal Time—formerly Greenwich Mean Time). For example:

HS_TIME_ZONE = [+ | -] hh:mm

D.13 HS_TRANSACTION_MODEL

Property Description

Default Value

COMMIT_CONFIRM

Range of Values

COMMIT_CONFIRM

READ_ONLY

SINGLE_SITE

READ_ONLY_AUTOCOMMIT

SINGLE_SITE_AUTOCOMMIT

Specifies the type of transaction model that is used when the non-Oracle database is

updated by a transaction.

The following values are possible:

•

COMMIT_CONFIRM

provides read and write access to the non-Oracle database and

allows the gateway to be part of a distributed update. To use the commit-confirm

model, the following items must be created in the non-Oracle database:

– Transaction log table. The default table name is

HS_TRANSACTION_LOG

. A

different name can be set using the

HS_FDS_TRANSACTION_LOG

parameter. The

Appendix D

HS_RPC_FETCH_SIZE

D-8

transaction log table must be granted

SELECT

DELETE

, and

INSERT

privileges

set to public.

– Recovery account. The account name is assigned with the

HS_FDS_RECOVERY_ACCOUNT

parameter.

– Recovery account password. The password is assigned with the

HS_FDS_RECOVERY_PWD

parameter.

•

READ_ONLY

provides read access to the non-Oracle database.

•

SINGLE_SITE

provides read and write access to the non-Oracle database.

However, the gateway cannot participate in distributed updates.

•

READ_ONLY_AUTOCOMMIT

provides read only access to the non-Oracle database that

does not use logging.

•

SINGLE_SITE_AUTOCOMMIT

provides read and write access to the non-Oracle

database without logging. The gateway cannot participate in distributed updates.

Moreover, any update to the non-Oracle database is committed immediately.

D.14 IFILE

Property Description

Default value None

Range of values Valid parameter file names

Use the

IFILE

initialization parameter to embed another initialization file within the

current initialization file. The value should be an absolute path and should not contain

environment variables. The three levels of nesting limit do not apply.

See Also:

Oracle Database Reference

D.15 HS_FDS_TIMESTAMP_MAPPING

Property Description

Default Value

DATE

Range of Values

CHAR

DATE

TIMESTAMP

Syntax

HS_FDS_TIMESTAMP_MAPPING={CHAR|DATE|TIMESTAMP}

If set to

CHAR

, then non-Oracle target timestamp would be mapped to

CHAR(26)

. If set

DATE

(default), then non-Oracle target timestamp would be mapped to Oracle

DATE

If set to

TIMESTAMP

, then non-Oracle target timestamp would be mapped to Oracle

TIMESTAMP

Appendix D

IFILE

D-9

D.16 HS_FDS_DATE_MAPPING

Property Description

Default Value

DATE

Range of Values

DATE

CHAR

Syntax

HS_FDS_DATE_MAPPING={DATE|CHAR}

If set to

CHAR

, then non-oracle target date would be mapped to

CHAR(10)

. If set to

DATE

then non-Oracle target date would be mapped to Oracle date.

D.17 HS_FDS_CONNECT_INFO

Property Description

Default Value None

Range of Values Not applicable

HS_FDS_CONNECT_INFO

that describes the connection to the non-Oracle system.

The default initialization parameter file already has an entry for this parameter. The

syntax for

HS_FDS_CONNECT_INFO

for the gateway is as follows:

HS_FDS_CONNECT_INFO=host_alias:port_number[/database_name]

where,

host_alias

is the host alias name or IP address of the machine hosting the

Teradata database,

port_number

is the port number of the Teradata database server,

and

database_name

is the Teradata database name. The

database_name

variable is

optional.

This release supports IPv6 format, so you can enter IPv6 format in place of

hostname

but you need to wrap square brackets around the IPv6 specification.

For example,

HS_FDS_CONNECT_INFO=[2001:0db8:20c:f1ff:fec6:38af]:port_number/…

D.18 HS_FDS_DEFAULT_OWNER

Property Description

Default Value None

Range of Values Not applicable

The name of the table owner that is used for the non-Oracle database tables if an

owner is not specified in the SQL statements.

Appendix D

HS_FDS_DATE_MAPPING

D-10

Note:

If this parameter is not specified and the owner is not explicitly specified in

the SQL statement, then the user name of the Oracle user or the user name

specified when creating the database link is used.

D.19 HS_FDS_TRANSACTION_ISOLATION

Property Description

Default Value

SERIALIZABLE

Range of Values

{

READ_UNCOMMITTED READ_COMMITTED REPEATABLE_READ

SERIALIZABLE

}

Syntax

HS_FDS_ISOLATION_LEVEL={{READ_UNCOMMITTED|

READ_COMMITTED|REPEATABLE_READ|SERIALIZABLE}

HS_FDS_TRANSACTION_ISOLATION

specifies the isolation level that is used for the

transaction that the gateway opens on the non-Oracle database.

The isolation levels of

READ_UNCOMMITTED

READ_COMMITTED

REPEATABLE_READ

, and

SERIALIZABLE

are the four isolation levels defined in the SQL standard and adopted

by both ANSI and ISO/IEC. For additional information regarding them, see Oracle

Database Concepts.

Use caution when specifying an isolation level lower than the Oracle transaction

isolation level being used, as the gateway transaction will have different Preventable

Read Phenomena from what will occur in the Oracle database transaction.

D.20 HS_FDS_PROC_IS_FUNC

Property Description

Default Value

FALSE

Range of Values

TRUE

FALSE

Enables return values from functions. By default, all stored procedures and functions

do not return a return value to the user.

Note:

If you set this initialization parameter, you must change the syntax of the

procedure execute statement for all existing stored procedures to handle

return values.

Appendix D

HS_FDS_TRANSACTION_ISOLATION

D-11

D.21 HS_FDS_RECOVERY_ACCOUNT

Property Description

Default Value

RECOVER

Range of values Any valid user ID

Specifies the name of the recovery account used for the commit-confirm transaction

model. An account with user name and password must be set up at the non-

Oracle system. For more information about the commit-confirm model, see the

HS_TRANSACTION_MODEL

parameter.

The name of the recovery account is case sensitive.

D.22 HS_FDS_RECOVERY_PWD

Property Description

Default Value

RECOVER

Range of values Any valid password

Specifies the password of the recovery account used for the commit-confirm

transaction model set up at the non-Oracle system. For more information about the

commit-confirm model, see the

HS_TRANSACTION_MODEL

parameter.

The name of the password of the recovery account is case sensitive.

D.23 HS_FDS_TRACE_LEVEL

Property Description

Default Value

OFF

Range of values

OFF

DEBUG

Specifies whether error tracing is turned on or off for gateway connectivity.

The following values are valid:

•

OFF

disables the tracing of error messages.

•

enables the tracing of error messages that occur when you encounter

problems. The results are written by default to a gateway log file in LOG directory

where the gateway is installed.

•

DEBUG

enables the tracing of detailed error messages that can be used for

debugging.

Appendix D

HS_FDS_RECOVERY_ACCOUNT

D-12

D.24 HS_FDS_TRANSACTION_LOG

Property Description

Default Value

HS_TRANSACTION_LOG

Range of Values Any valid table name

Specifies the name of the table created in the non-Oracle system for logging

transactions. For more information about the transaction model, see the

HS_TRANSACTION_MODEL

parameter.

D.25 HS_FDS_FETCH_ROWS

Property Description

Default Value

100

Range of Values

Any integer between

and

1000

Syntax

HS_FDS_FETCH_ROWS=num

HS_FDS_FETCH_ROWS

specifies the fetch array size. This is the number of rows

to be fetched from the non-Oracle database and to return to Oracle database

at one time. This parameter will be affected by the

HS_RPC_FETCH_SIZE

and

HS_RPC_FETCH_REBLOCKING

parameters.

D.26 HS_IDLE_TIMEOUT

Property Description

Default Value 0 (no timeout)

Range of Values 0-9999 (minutes)

Syntax

HS_IDLE_TIMEOUT=num

This feature is only available for Oracle Net TCP protocol. When there is no activity for

a connected gateway session for this specified time period, the gateway session would

be terminated automatically with pending update (if any) rolled back.

D.27 HS_NLS_LENGTH_SEMANTICS

Property

Description

Default Value

BYTE

Range of Values

BYTE | CHAR

Syntax

HS_NLS_LENGTH_SEMANTICS = { BYTE | CHAR }

This release of gateway has Character Semantics functionality equivalent to

the Oracle Database Character Semantics, that is,

NLS_LENGTH_SEMANTICS

. When

HS_NLS_LENGTH_SEMANTICS

is set to

CHAR

, the

(VAR)CHAR

columns of Teradata are to

Appendix D

HS_FDS_TRANSACTION_LOG

D-13

be interpreted as having

CHAR

semantics. The only situation the gateway does not

honor the

HS_NLS_LENGTH_SEMANTICS=CHAR

setting is when both Oracle database and

the gateway are on the same multi-byte character set

D.28 HS_KEEP_REMOTE_COLUMN_SIZE

Property Description

Default Value

OFF

Range of Values

OFF | LOCAL | REMOTE |ALL

Syntax

HS_KEEP_REMOTE_COLUMN_SIZE = OFF | LOCAL |

REMOTE | ALL

Parameter type String

HS_KEEP_REMOTE_COLUMN_SIZE

specifies whether to suppress ratio expansion when

computing the length of

(VAR)CHAR

datatypes during data conversion from non-Oracle

database to the gateway, and then to the Oracle database. When it is set to

REMOTE

the expansion is suppressed between the non-Oracle database and the gateway.

When it is set to

LOCAL

, the expansion is suppressed between the gateway and

the Oracle database. When it is set to

ALL

, the expansion is suppressed from the

non-Oracle database to the Oracle database.

When the parameter is set, the expansion is suppressed when reporting the

remote column size, calculating the implicit resulting buffer size, and instantiating

in the local Oracle database. This has effect only for remote column size from

non-Oracle database to the Oracle database. If the gateway runs on Windows and

HS_LANGUAGE=AL32UTF8

, then you must not specify this parameter, as it would influence

other ratio related parameter operation. It has no effect for calculating ratio for data

moving from Oracle database to non-Oracle database through gateway during

INSERT

UPDATE

, or

DELETE

D.29 HS_FDS_REMOTE_DB_CHARSET

Property Description

Default Value None

Range of values Not applicable

Syntax

HS_FDS_REMOTE_DB_CHARSET

This parameter is valid only when

HS_LANGUAGE

is set to

AL32UTF8

and the gateway

runs on Windows. As more Oracle databases and non-Oracle databases use

Unicode as database character sets, it is preferable to also run the gateway in

Unicode character set. To do so, you must set

HS_LANGUAGE=AL32UTF8

. However,

when the gateway runs on Windows, the Microsoft ODBC Driver Manager

interface can exchange data only in the double-byte character set, UCS2.

This results in extra ratio expansion of described buffer and column sizes. To

compensate, the gateway can re-adjust the column size if

HS_FDS_REMOTE_DB_CHARSET

is set to the corresponding non-Oracle database character set. For example,

HS_FDS_REMOTE_DB_CHARSET=KO16KSC5601

Appendix D

HS_KEEP_REMOTE_COLUMN_SIZE

D-14

D.30 HS_FDS_SUPPORT_STATISTICS

Property Description

Default Value

TRUE

Range of values

{TRUE|FALSE}

Syntax

HS_FDS_SUPPORT_STATISTICS= {TRUE|FALSE}

We gather statistics from the non-Oracle database by default. You can

choose to disable the gathering of remote database statistics by setting the

HS_FDS_SUPPORT_STATISTICS

parameter to

FALSE

D.31 HS_FDS_ARRAY_EXEC

Property Description

Default Value

TRUE

Range of values

{TRUE|FALSE}

Syntax

HS_FDS_ARRAY_EXEC= {TRUE|FALSE}

If set to

TRUE

, the gateway will use array operations for insert, update, delete

statements containing binds against the remote data source. The array size is

determined by the value of the

HS_FDS_FETCH_ROWS

init parameter.

If set to

FALSE

, the gateway will not use array operations for insert, update, and delete

statements. Instead, a single statement will be issued for every value.

Appendix D

HS_FDS_SUPPORT_STATISTICS

D-15

Index

ALTER statement, B-1

Arithmetic operators, B-3

BYTE data type, A-1

BYTEINT data type, A-1

Case rules, 2-9

Case studies, 3-1

CHAR data type, A-1

character sets

Heterogeneous Services, D-6

COMMIT

restrictions, 2-13

Commit point site, 2-13

Comparison operators, B-3

CONNECT BY clause, 2-14

COPY command, 2-14

CREATE statement, B-1

Cursor loops

restrictions, 2-13

Data definition language, B-1

Data dictionary

views, C-3

Data type

BYTE, A-1

BYTEINT, A-1

CHAR, A-1

conversion, 2-11

DATE, A-1

DECIMAL, A-1

FLOAT, A-1

INTEGER, A-1

LONG, A-1

LONG VARCHAR, A-1

RAW, A-1

Data type (continued)

SMALLINT, A-1

TIME, A-1

TIMESTAMP, A-1

VARBYTE, A-1

VARCHAR, A-1

VARCHAR2, A-1

DATE data type, A-1

DDL statement, 2-13

DECIMAL data type, A-1

DELETE statement, 3-5, B-1, B-2

demonstration build SQL script, 3-2

Demonstration files, 3-2

Demonstration tables, 3-3

Demonstration tables build SQL script, 3-3

describe cache high water mark

definition, D-5

DROP statement, B-1

Error messages

error tracing, D-12

Errors

ORA-02070, 2-13

fetch array size, with HS_FDS_FETCH_ROWS,

D-13

FLOAT data type, A-1

Gateway

case studies, 3-1

pass-through feature, 2-2

supported functions, B-1

supported SQL syntax, B-1

globalization support

Heterogeneous Services, D-5

GRANT statement, B-1

Group functions, B-4

Index-1

Heterogeneous Services

defining maximum number of open cursors,

D-7

optimizing data transfer, D-7

setting global name, D-5

specifying cache high water mark, D-5

tuning internal data buffering, D-8

tuning LONG data transfer, D-7

Hexadecimal notation, 2-10

HS_DB_NAME initialization parameter, D-5

HS_DESCRIBE_CACHE_HWM initialization

parameter, D-5

HS_FDS_CONNECT_INFO, D-10

HS_FDS_DEFAULT_OWNER initialization

parameter, D-10

HS_FDS_FETCH_ROWS parameter, D-13

HS_FDS_PROC_IS_FUNC initialization

parameter, D-11

HS_FDS_TRACE_LEVEL initialization

parameter, D-12

enabling agent tracing, D-2

HS_FDS_TRANSACTION_ISOLATIONparamete

r, D-11

HS_FDS_TRANSACTION_LOG initialization

parameter, D-13

HS_IDLE_TIMEOUT, D-13

HS_KEEP_REMOTE_COLUMN_SIZE, D-14

HS_LANGUAGE initialization parameter, D-5

HS_LONG_PIECE_TRANSFER_SIZE

initialization parameter, D-7

HS_NLS_LENGTH_SEMANTICS, D-13

HS_OPEN_CURSORS initialization parameter,

D-7

HS_RPC_FETCH_REBLOCKING initialization

parameter, D-7

HS_RPC_FETCH_SIZE initialization parameter,

D-8

HS_TIME_ZONE initialization parameter, D-8

IFILE initialization parameter, D-9

Initialization parameter file

customizing, D-1

INSERT statement, 3-5, B-2

INTEGER data type, A-1

isolation level,

HS_FDS_TRANSACTION_ISOLATION,

D-11

Known restrictions, 2-12

Locking, database, 2-11

LONG data type, A-1

LONG VARCHAR data type, A-1

NVL function, 3-5

Objects, naming rules, 2-9

ORA-02070, 2-13

parameters

gateway initialization file

HS_FDS_FETCH_ROWS, D-13

HS_FDS_TRANSACTION_ISOLATION,

D-11

Pass-Through Feature, 3-6

Passing commands to database, 2-13

PL/SQL, 2-16

RAW data type, A-1

remote

DRDA database,

HS_FDS_TRANSACTION_ISOLATION,

D-11

ROLLBACK

restrictions, 2-13

ROWID, 2-14

savepoint support, 2-13

SELECT statement, 3-6, B-2, C-2

SMALLINT data type, A-1

SQL

statements,

HS_FDS_TRANSACTION_ISOLATION,

D-11

String functions, B-4

SUM function, 3-5

TIME data type, A-1

TIMESTAMP data type, A-1

transactional capability, 2-13

Index

Index-2

transactional integrity, 2-12

TRUNCATE statement, B-1

Two-phase commit, 2-13

UPDATE statement, 3-5, 3-6, B-2

VARBYTE data type, A-1

VARCHAR data type, A-1

VARCHAR2 data type, A-1

WHERE CURRENT OF clause, 2-14

Index

Index-3