Transcription
Oracle Database Gateway forTeradataUser's Guide19cE96407-01April 2019
Oracle Database Gateway for Teradata User's Guide, 19cE96407-01Copyright 2002, 2019, Oracle and/or its affiliates. All rights reserved.Primary Author: Rhonda DayContributing Authors: Vira Goorah, Juan Pablo Ahues-Vasquez, Peter Castro, Charles Benet, Peter Wong,Govind LakkojuThis software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense 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 forinteroperability, is prohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou 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 onbehalf 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 installed on the hardware, and/or documentation, delivered to U.S. Government end users are"commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agencyspecific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of theprograms, including any operating system, integrated software, any programs installed on the hardware,and/or documentation, shall be subject to license terms and license restrictions applicable to the programs.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 thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron,the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced MicroDevices. 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 expresslydisclaim all warranties of any kind with respect to third-party content, products, and services unless otherwiseset forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not beresponsible 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.
ContentsPreface12AudienceixDocumentation AccessibilityixRelated DocumentsxConventionsxIntroduction to the Oracle Database Gateway for TeradataOverview of the Oracle Database Gateway for Teradata1-1About Heterogeneous Services Technology1-1About Oracle Database Gateway for Teradata1-2Teradata Gateway Features and RestrictionsRemote Insert Rowsource2-1Using the Pass-Through Feature2-2Executing Stored Procedures and Functions2-2Return Values and Stored Procedures2-3Result Sets and Stored Procedures2-3OCI Program Fetching from Result Sets in Sequential Mode2-5PL/SQL Program Fetching from Result Sets in Sequential Mode2-6CHAR Semantics2-7Multi-byte Character Sets Ratio Suppression2-7IPv6 Support2-7Gateway Session IDLE Timeout2-8Database Compatibility Issues for Teradata2-8Schema Considerations2-8Naming Rules2-8Rules for Naming Objects2-8Case Sensitivity2-9Data Types2-9Binary Literal Notation2-10Data Type Conversion2-10iii
QueriesRow Selection2-10Empty Bind Variables2-11LockingKnown Restrictions2-112-11Transactional Integrity2-11Transaction Capability2-12COMMIT or ROLLBACK in PL/SQL Cursor Loops Closes Open Cursors2-12Pass-Through Feature2-12Bind Variables for Date Columns2-12SQL Syntax2-13WHERE CURRENT OF Clause2-13CONNECT BY Clause2-13ROWID2-13EXPLAIN PLAN Statement2-13SQL*Plus2-13Database Links2-13Known Problems32-102-14Teradata LONG VARCHAR Data Type2-14Schema Names and PL/SQL2-14Data Dictionary Views and PL/SQL2-15Case StudiesCase Descriptions3-1Installation Media Contents3-1Demonstration Files3-2Demonstration Requirements3-2Creating Demonstration Tables3-2Demonstration Table Definitions3-3Demonstration Table Contents3-3Case 1: Simple Queries3-4Case 2: A More Complex Query3-4Case 3: Joining Teradata Tables3-4Case 4: Write Capabilities3-5DELETE Statement3-5UPDATE Statement3-5INSERT Statement3-5Case 5: Data Dictionary Query3-5Case 6: The Pass-Through Feature3-5UPDATE Statement3-5iv
SELECT StatementAData Type ConversionBSupported SQL Syntax and FunctionsSupported SQL B-2Oracle FunctionsB-2Functions Not Supported by TeradataB-2Functions Supported by TeradataB-2Arithmetic OperatorsB-3Comparison OperatorsB-3Group FunctionsB-3String FunctionsB-3Other FunctionsB-3Data DictionaryTeradata System Catalog TablesC-1Accessing the Gateway Data DictionaryC-1Direct Queries to Teradata TablesC-2Supported Views and TablesC-2Data Dictionary MappingC-3Default Column ValuesC-4Gateway Data Dictionary DescriptionsC-4ALL CATALOGC-4ALL COL COMMENTSC-4ALL CONS COLUMNSC-5ALL CONSTRAINTSC-5ALL IND COLUMNSC-6ALL INDEXESC-6ALL OBJECTSC-8ALL TAB COLUMNSC-9ALL TAB COMMENTSC-10ALL TABLESC-10ALL USERSC-11ALL VIEWSC-12v
DDBA CATALOGC-12DBA COL COMMENTSC-12DBA OBJECTSC-13DBA TAB COLUMNSC-13DBA TAB COMMENTSC-14DBA TABLESC-15DICT COLUMNSC-16DICTIONARYC-16DUALC-17USER CATALOGC-17USER COL COMMENTSC-17USER CONS COLUMNSC-17USER CONSTRAINTSC-18USER IND COLUMNSC-18USER INDEXESC-19USER OBJECTSC-20USER TAB COLUMNSC-21USER TAB COMMENTSC-22USER TABLESC-22USER USERSC-24USER VIEWSC-24Initialization ParametersInitialization Parameter File SyntaxD-1Oracle Database Gateway for Teradata Initialization ParametersD-2HS DB DOMAIND-3HS DB INTERNAL NAMED-4HS DB NAMED-4HS DESCRIBE CACHE HWMD-4HS LANGUAGED-4Character SetsD-5LanguageD-5TerritoryD-5HS LONG PIECE TRANSFER SIZED-6HS OPEN CURSORSD-6HS RPC FETCH REBLOCKINGD-6HS RPC FETCH SIZED-7HS TIME ZONED-7HS TRANSACTION MODELD-7IFILED-8vi
HS FDS TIMESTAMP MAPPINGD-8HS FDS DATE MAPPINGD-9HS FDS CONNECT INFOD-9HS FDS DEFAULT OWNERD-9HS FDS TRANSACTION ISOLATIOND-10HS FDS PROC IS FUNCD-10HS FDS RECOVERY ACCOUNTD-11HS FDS RECOVERY PWDD-11HS FDS TRACE LEVELD-11HS FDS TRANSACTION LOGD-12HS FDS FETCH ROWSD-12HS IDLE TIMEOUTD-12HS NLS LENGTH SEMANTICSD-12HS KEEP REMOTE COLUMN SIZED-13HS FDS REMOTE DB CHARSETD-13HS FDS SUPPORT STATISTICSD-14HS FDS ARRAY EXECD-14Indexvii
List of TablesA-1Data Type Mapping and RestrictionsA-1C-1Oracle Data Dictionary View Names and Teradata EquivalentsC-3C-2ALL CATALOGC-4C-3ALL COL COMMENTSC-4C-4ALL CONS COLUMNSC-5C-5ALL CONSTRAINTSC-5C-6ALL IND COLUMNSC-6C-7ALL INDEXESC-6C-8ALL OBJECTSC-8C-9ALL TAB COLUMNSC-9C-10ALL TAB COMMENTSC-10C-11ALL TABLESC-10C-12ALL USERSC-11C-13ALL VIEWSC-12C-14DBA CATALOGC-12C-15DBA COL COMMENTSC-12C-16DBA OBJECTSC-13C-17DBA TAB COLUMNSC-13C-18DBA TAB COMMENTSC-14C-19DBA TABLESC-15C-20DICT COLUMNSC-16C-21DICTIONARYC-16C-22DUALC-17C-23USER CATALOGC-17C-24USER COL COMMENTSC-17C-25USER CONS COLUMNSC-17C-26USER CONSTRAINTSC-18C-27USER IND COLUMNSC-18C-28USER INDEXESC-19C-29USER OBJECTSC-20C-30USER TAB COLUMNSC-21C-31USER TAB COMMENTSC-22C-32USER TABLESC-22C-33USER USERSC-24C-34USER VIEWSC-24viii
PrefaceThis manual describes the Oracle Database Gateway for Teradata, which enablesOracle client applications to access Teradata data through Structured QueryLanguage (SQL). The gateway, with the Oracle database, creates the appearance thatall data resides on a local Oracle database, even though the data can be widelydistributed.This preface covers the following topics: Audience Documentation Accessibility Related Documents ConventionsAudienceThis manual is intended for Oracle database administrators who perform the followingtasks: Installing and configuring the Oracle Database Gateway for Teradata Diagnosing gateway errors Using the gateway to access Teradata dataNote:You should understand the fundamentals of Oracle Database Gatewaysand the UNIX based platforms before using this guide to install oradminister the gateway.Documentation AccessibilityFor information about Oracle's commitment to accessibility, visit the OracleAccessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx acc&id docacc.Access to Oracle SupportOracle customers that have purchased support have access to electronic supportthrough My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx acc&id info or visit http://www.oracle.com/pls/topic/lookup?ctx acc&id trsif you are hearing impaired.ix
PrefaceRelated DocumentsFor more information, see these Oracle resources: Oracle Database New Features Guide Oracle Call Interface Programmer's Guide Oracle Database Administrator's Guide Oracle Database Development Guide Oracle Database Concepts Oracle Database Performance Tuning Guide Oracle Database Error Messages Oracle Database Globalization Support Guide Oracle Database Reference Oracle Database SQL Language Reference Oracle Database Net Services Administrator's Guide SQL*Plus User's Guide and Reference Oracle Database Heterogeneous Connectivity User's Guide Oracle Database Security GuideConventionsThe following text conventions are used in this document:ConventionMeaningboldfaceBoldface type indicates graphical user interface elements associatedwith an action, or terms defined in text or the glossary.italicItalic type indicates book titles, emphasis, or placeholder variables forwhich you supply particular values.monospaceMonospace type indicates commands within a paragraph, URLs, codein examples, text that appears on the screen, or text that you enter.x
1Introduction to the Oracle DatabaseGateway for TeradataOracle Database Gateways provide the ability to transparently access data residing ina non-Oracle system from an Oracle environment. The following topics briefly coversHeterogeneous Services, the technology that the Oracle Database Gateway forTeradata is based on.To get a good understanding of generic gateway technology, Heterogeneous Services,and how Oracle Database Gateways fit in the picture, reading the Oracle DatabaseHeterogeneous Connectivity User's Guide first is highly recommended.Topics: Overview Heterogeneous Services Technology Oracle Database GatewaysOverview of the Oracle Database Gateway for TeradataHeterogeneous data access is a problem that affects a lot of companies. A lot ofcompanies run several different database systems. Each of these systems stores dataand has a set of applications that run against it. Consolidation of this data in onedatabase system is often hard - in large part because many of the applications that runagainst one database may not have an equivalent that runs against another. Until suchtime as migration to one consolidated database system is made feasible, it isnecessary for the various heterogeneous database systems to interoperate.Oracle Database Gateways provide the ability to transparently access data residing ina non-Oracle system from an Oracle environment. This transparency eliminates theneed for application developers to customize their applications to access data fromdifferent non-Oracle systems, thus decreasing development efforts and increasing themobility of the application. Applications can be developed using a consistent Oracleinterface for both Oracle and Teradata.Gateway technology is composed of two parts: a component that has the generictechnology to connect to a non-Oracle system, which is common to all the non-Oraclesystems, called Heterogeneous Services, and a component that is specific to the nonOracle system that the gateway connects to. Heterogeneous Services, in conjunctionwith the Oracle Database Gateway agent, enables transparent access to non-Oraclesystems from an Oracle environment.About Heterogeneous Services TechnologyHeterogeneous Services provides the generic technology for connecting to non-Oraclesystems. As an integrated component of the database, Heterogeneous Services canexploit features of the database, such as the powerful SQL parsing and distributedoptimization capabilities.1-1
Chapter 1About Oracle Database Gateway for TeradataHeterogeneous Services extend the Oracle SQL engine to recognize the SQL andprocedural capabilities of the remote non-Oracle system and the mappings required toobtain necessary data dictionary information. Heterogeneous Services provides twotypes of translations: the ability to translate Oracle SQL into the proper dialect of thenon-Oracle system as well as data dictionary translations that displays the metadata ofthe non-Oracle system in the local format. For situations where no translations areavailable, native SQL can be issued to the non-Oracle system using the pass-throughfeature of Heterogeneous Services.Heterogeneous Services also maintains the transaction coordination between Oracleand the remote non-Oracle system, such as providing the two-phase commit protocolto ensure distributed transaction integrity, even for non-Oracle systems that do notnatively support two-phase commit.See Also:Oracle Database Heterogeneous Connectivity User's Guide for moreinformation about Heterogeneous Services.About Oracle Database Gateway for TeradataThe capabilities, SQL mappings, data type conversions, and interface to the remotenon-Oracle system are contained in the gateway. The gateway interacts withHeterogeneous Services to provide the transparent connectivity between Oracle andnon-Oracle systems.The gateway must be installed on a machine running either the Teradata database orthe Teradata client. This machine can be the same machine as the Oracle database oron the same machine as the Teradata database or on a third machine as astandalone. Each configuration has its advantages and disadvantages. The issues toconsider when determining where to install the gateway are network traffic, operatingsystem platform availability, hardware resources and storage.1-2
2Teradata Gateway Features andRestrictionsAfter the gateway is installed and configured, you can use the gateway to accessTeradata 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 ProblemsRemote Insert RowsourceA remote insert rowsource feature allows remote insert requiring local Oracle data towork through the Oracle database and Oracle Database Gateway. This functionalityrequires that the Oracle database and the Oracle Database Gateway to be version12.2 or later.By Oracle Database design, some distributed statement must be executed at thedatabase link site. But in certain circumstances, there is data needed to execute thesequeries that must be fetched from the originating Oracle Database. Underhomogeneous connections, the remote Oracle database would call back the sourceOracle 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 backfunctions, or data, that can only be provided by the Oracle instance that issued thequery. In general, these kinds of statements are not something that can be supportedthrough 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 statementcontaining SQL functions or statements that needs to be executed at theoriginating Oracle database.2-1
Chapter 2Using the Pass-Through FeatureThese SQL functions include USER, USERENV, and SYSDATE; and involve theselection of data from the originating Oracle database. Any SQL statement that involves a table in Oracle database, and a LONG or LOBcolumn in a remote table.An example of a remote INSERT statement that can work through the remote insertrowsource feature is as follows:INSERT INTO gateway table@gateway link select * from local table;Using the Pass-Through FeatureThe gateway can pass Teradata commands or statements from the application directlyto the Teradata database using the DBMS HS PASSTHROUGH package.Use the DBMS HS PASSTHROUGH package in a PL/SQL block to specify the statement tobe passed to the Teradata database, as follows:DECLAREnum rows INTEGER;BEGINnum rows : DBMS HS PASSTHROUGH.EXECUTE IMMEDIATE@TERA('command');END;/Where command cannot be one of the following: BEGIN TRANSACTION BT COMMIT END TRANSACTION ET ROLLBACKThe DBMS HS PASSTHROUGH package supports passing bind values and executingSELECT statements.See Also:Oracle Database PL/SQL Packages and Types Reference and Chapter 3,Features of Oracle Database Gateways, of Oracle Database HeterogeneousConnectivity User's Guide for more information about theDBMS HS PASSTHROUGH package.Executing Stored Procedures and FunctionsUsing the procedural feature, the gateway can execute stored procedures that aredefined in the Teradata database. It is not necessary to relink the gateway or definethe procedure to the gateway, but the procedure's access privileges must permitaccess by the gateway.2-2
Chapter 2Executing Stored Procedures and FunctionsStandard 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 IN/OUT arguments only Return value mode: Have a return value for all stored procedures Resultset mode: Out values are available as last result setReturn Values and Stored ProceduresBy 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 initializationparameter file.See Also:Initialization Parameters for information about both editing the initializationparameter file and the HS FDS PROC IS FUNC parameter.No
This manual describes the Oracle Database Gateway for Teradata, which enables Oracle client applications to access Teradata data through Structured Query Language (SQL). The gateway, with the Oracle database, creates the appearance that all data resides on a local Oracle database, even though the data can be widely distributed.
