Teradata Data Warehouse Appliance 2700 Implementation Guide

320 Pages·2013·2.46 MB·English

by  Teradata Information Engineering

Teradata Data Warehouse Appliance 2700
Implementation Guide
Release 14.0 SLES 11
B035-1196-063K
August 2013
The product or products described in this book are licensed products of Teradata Corporation or its affiliates.
Teradata, Active Data Warehousing, Active Enterprise Intelligence, Applications-Within, Aprimo, Aprimo Marketing Studio, Aster, BYNET,
Claraview, DecisionCast, Gridscale, MyCommerce, Raising Intelligence, Smarter. Faster. Wins., SQL-MapReduce, Teradata Decision Experts,
"Teradata Labs" logo, "Teradata Raising Intelligence" logo, Teradata ServiceConnect, Teradata Source Experts, "Teradata The Best Decision Possible"
logo, The Best Decision Possible, WebAnalyst, and Xkoto are trademarks or registered trademarks of Teradata Corporation or its affiliates in the
United States and other countries.
Adaptec and SCSISelect are trademarks or registered trademarks of Adaptec, Inc.
AMD Opteron and Opteron are trademarks of Advanced Micro Devices, Inc.
Apache, Apache Hadoop, Hadoop, and the yellow elephant logo are either registered trademarks or trademarks of the Apache Software Foundation
in the United States and/or other countries.
Apple, Mac, and OS X all are registered trademarks of Apple Inc.
Axeda is a registered trademark of Axeda Corporation. Axeda Agents, Axeda Applications, Axeda Policy Manager, Axeda Enterprise, Axeda Access,
Axeda Software Management, Axeda Service, Axeda ServiceLink, and Firewall-Friendly are trademarks and Maximum Results and Maximum
Support are servicemarks of Axeda Corporation.
Data Domain, EMC, PowerPath, SRDF, and Symmetrix are registered trademarks of EMC Corporation.
GoldenGate is a trademark of Oracle.
Hewlett-Packard and HP are registered trademarks of Hewlett-Packard Company.
Hortonworks, the Hortonworks logo and other Hortonworks trademarks are trademarks of Hortonworks Inc. in the United States and other
countries.
Intel, Pentium, and XEON are registered trademarks of Intel Corporation.
IBM, CICS, RACF, Tivoli, and z/OS are registered trademarks of International Business Machines Corporation.
Linux is a registered trademark of Linus Torvalds.
LSI is a registered trademark of LSI Corporation.
Microsoft, Active Directory, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States
and other countries.
NetVault is a trademark or registered trademark of Quest Software, Inc. in the United States and/or other countries.
Novell and SUSE are registered trademarks of Novell, Inc., in the United States and other countries.
Oracle, Java, and Solaris are registered trademarks of Oracle and/or its affiliates.
QLogic and SANbox are trademarks or registered trademarks of QLogic Corporation.
Quantum and the Quantum logo are trademarks of Quantum Corporation, registered in the U.S.A. and other countries.
Red Hat is a trademark of Red Hat, Inc., registered in the U.S. and other countries. Used under license.
SAS and SAS/C are trademarks or registered trademarks of SAS Institute Inc.
SPARC is a registered trademark of SPARC International, Inc.
Symantec, NetBackup, and VERITAS are trademarks or registered trademarks of Symantec Corporation or its affiliates in the United States and
other countries.
Unicode is a registered trademark of Unicode, Inc. in the United States and other countries.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Other product and company names mentioned herein may be the trademarks of their respective owners.
THE INFORMATION CONTAINED IN THIS DOCUMENT IS PROVIDED ON AN "AS-IS" BASIS, WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESS OR IMPLIED, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR
NON-INFRINGEMENT. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION
MAY NOT APPLY TO YOU. IN NO EVENT WILL TERADATA CORPORATION BE LIABLE FOR ANY INDIRECT, DIRECT, SPECIAL, INCIDENTAL,
OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS OR LOST SAVINGS, EVEN IF EXPRESSLY ADVISED OF THE POSSIBILITY OF
SUCH DAMAGES.
The information contained in this document may contain references or cross-references to features, functions, products, or services that are not
announced or available in your country. Such references do not imply that Teradata Corporation intends to announce such features, functions,
products, or services in your country. Please consult your local Teradata Corporation representative for those features, functions, products, or
services available in your country.
Information contained in this document may contain technical inaccuracies or typographical errors. Information may be changed or updated
without notice. Teradata Corporation may also make improvements or changes in the products or services described in this information at any time
without notice.
To maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document.
Please email: [email protected].
Any comments or materials (collectively referred to as "Feedback") sent to Teradata Corporation will be deemed non-confidential. Teradata
Corporation will have no obligation of any kind with respect to Feedback and will be free to use, reproduce, disclose, exhibit, display, transform,
create derivative works of, and distribute the Feedback and derivative works thereof without limitation on a royalty-free basis. Further, Teradata
Corporation will be free to use any ideas, concepts, know-how, or techniques contained in such Feedback for any purpose whatsoever, including
developing, manufacturing, or marketing products or services incorporating Feedback.
Copyright © 2000 – 2013 by Teradata Corporation. All Rights Reserved.
Preface
Purpose
This guide describes the minimum tasks required to implement database operations on the
Teradata Data Warehouse Appliance 2700, including:
(cid:129) Configuring the system and its connected clients
(cid:129) Monitoring the health of system hardware and handling common problems
(cid:129) Converting existing database table definitions and queries to Teradata SQL
(cid:129) Moving data from an existing database into Teradata Database
(cid:129) Creating Teradata Database users, roles and profiles
(cid:129) Granting database access privileges, logging access attempts, and auditing security logs
(cid:129) Monitoring and managing database activity, performing periodic maintenance, and using
performance tuning features
Reference information tables appear at the end of many procedures that provide links to
additional capabilities and options.
Audience
This guide is intended for first time users of Teradata Database:
(cid:129) Planners
(cid:129) Administrators
(cid:129) System programmers
Supported Software Release
This books supports Teradata Database 14.0 running in a SLES 11 environment on a Teradata
Data Warehouse Appliance 2700 and Teradata clients running Teradata Tools and Utilities
14.10.
Teradata Data Warehouse Appliance 2700 3
Preface
Changes to This Guide
Changes to This Guide
This guide includes the following changes to support the current release:
Release Description
Teradata Database 14.0 on Initial release
SLES 11
August 2013
Additional Information
URL Description
www.info.teradata.com/ Use the Teradata Information Products Publishing Library
site to:
(cid:129) View or download a manual:
1 Under Online Publications, select General Search.
2 Enter your search criteria and click Search.
(cid:129) Download a documentation CD-ROM:
1 Under Online Publications, select General Search.
2 In the Title or Keyword field, enter CD-ROM, and
click Search.
(cid:129) Order printed manuals:
Under Print & CD Publications, select How to Order.
www.teradata.com The Teradata home page provides links to numerous
sources of information about Teradata. Links include:
(cid:129) Executive reports, case studies of customer experiences
with Teradata, and thought leadership
(cid:129) Technical information, solutions, and expert advice
(cid:129) Press releases, mentions and media resources
www.teradata.com/t/TEN/ Teradata Customer Education designs, develops and
delivers education that builds skills and capabilities for
our customers, enabling them to maximize their Teradata
investment.
https://tays.teradata.com Use Teradata @ Your Service to access Orange Books,
technical alerts, and knowledge repositories, view and join
forums, and download software patches.
developer.teradata.com/ Teradata Developer Exchange provides articles on using
Teradata products, technical discussion forums, and code
downloads.
4 Teradata Data Warehouse Appliance 2700 Implementation Guide
Preface
References to Linux and Windows
To maintain the quality of our products and services, we would like your comments on the
accuracy, clarity, organization, and value of this document. Please e-mail: teradata-
[email protected].
References to Linux and Windows
This guide makes various references to “Linux” and “Windows.”
(cid:129) The Teradata Data Warehouse Appliance 2700 runs on SUSE Linux Enterprise Server 11,
Service Pack 1, which is referred to in the text as “Linux.”
(cid:129) Although Teradata Database supports several client operating systems, this guide assumes
that Teradata clients run a supported version of Microsoft Windows. The general term
“Windows” is used except in cases where a reference is dependent on a specific version of
Microsoft Windows.
Teradata Data Warehouse Appliance 2700 Implementation Guide 5
Preface
References to Linux and Windows
6 Teradata Data Warehouse Appliance 2700 Implementation Guide
Table of Contents
Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Supported Software Release. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3
Changes to This Guide. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4
Additional Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4
References to Linux and Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5
Chapter 1: Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Organization of This Book. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Database Implementation Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Chapter 2: System Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Hardware Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Teradata Data Warehouse Appliance 2700 Cabinet Configurations . . . . . . . . . . . . . . . . 22
Cabinet Component Descriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
System Scalability. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Customer Network and Mainframe Connection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Hardware Data Protection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Teradata Database Software Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Parallel Database Extensions (PDE). . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Teradata Database Software . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Chapter 3: Setting Up System Management . . . . . . . . . . . . . . . . . . . 45
SMWeb Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Configuring a Workstation for Use with SMWeb. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
Setting Up an Internet Explorer Browser on a Windows Workstation . . . . . . . . . . . . . . 47
Setting Up a Firefox Browser . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
Teradata Data Warehouse Appliance 2700 Implementation Guide 7
Table of Contents
Setting Up Software on a Workstation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48
Accessing the SMWeb Home Page . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48
Navigating to the SMWeb Home Page from the SOV CMIC . . . . . . . . . . . . . . . . . . . . . . .48
Navigating to the SMWeb Home Page from a Non-SOV CMIC . . . . . . . . . . . . . . . . . . . .49
Setting Up User Access to SMWeb Applications and Web Pages . . . . . . . . . . . . . . . . . . . . . . .49
Adding a User, Enhanced User, or Service User. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50
Setting Up Server Management Command Line Interface Utilities. . . . . . . . . . . . . . . . . . . . . .51
Setting Up SMWeb CLI Utilities on a Windows Workstation . . . . . . . . . . . . . . . . . . . . . .51
Setting Up SMWeb CLI Utilities on a Linux Workstation. . . . . . . . . . . . . . . . . . . . . . . . . .52
Setting Up an Alert Subscription. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52
Working with SMClient Default Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53
Setting Up SMClient Default Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53
SMClient Default Views Used to Monitor the System. . . . . . . . . . . . . . . . . . . . . . . . . . . . .54
Monitoring System Health with SMClient. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63
Monitoring the Alert Viewer for System Alerts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63
Determining When to Take Action to Fix a Problem. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .64
Working with Customer-Replaceable Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
Preparing to Replace a Problem Component . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
Opening a Maintenance Window. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65
Closing a Maintenance Window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66
Determining Whether Your Action Solved the Problem. . . . . . . . . . . . . . . . . . . . . . . . . . .66
Opening an Incident. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .67
When to Open an Incident. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .67
Gathering Needed Supporting Information for an Incident. . . . . . . . . . . . . . . . . . . . . . . .67
Opening Incidents Through Teradata@YourService. . . . . . . . . . . . . . . . . . . . . . . . . . . . .68
Chapter 4: Setting Up Teradata Clients . . . . . . . . . . . . . . . . . . . . . . . . . . .71
Required Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71
About Client Software Installation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71
Supported Platforms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .71
Installation Guidelines . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72
Standard Client Software Bundle. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72
User Documentation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72
Client Software Installation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .72
Installing Third-Party Software. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73
Installing Microsoft .NET Framework. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73
Installing Third-Party Software for Teradata Parallel Transporter Base . . . . . . . . . . . . . .74
Installing Third Party Software for Teradata Parallel Transporter Wizard . . . . . . . . . . . .74
Installing Third-Party Software for Teradata WebSphere MQ Access Module. . . . . . . . .74
8 Teradata Data Warehouse Appliance 2700 Implementation Guide
Table of Contents
Installing Teradata Tools and Utilities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Using the Latest Software Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Downloading the Latest Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Installing the Latest Updates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Downloading the Windows Help Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Configuring Network Name Resolution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
Creating Environment Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Working with ODBC Data Sources. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Setting up Teradata as an ODBC Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Setting Up an External ODBC Data Source. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Restarting the Client. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Setting Up Teradata Clients to Access Teradata Viewpoint . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Requirements for Viewpoint Clients . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Chapter 5: Setting up Administrative Users . . . . . . . . . . . . . . . . . . . . 85
Implementation Task Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Configuring Default Administrative Settings. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Setting Up the Teradata Administrator GUI. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
Defining the Data Directory . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Setting Global Default Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Enabling Lock Logging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Changing the PasswordExpire Parameter . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Allocating Space for System-Level Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88
About Database Space . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
About System-Generated Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Required System-Level Space Allocation Tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Creating the Spool Space Reserve. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Assigning Perm Space to Crashdumps. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Setting Up the Database Administrator User. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Required Database Administrator Setup Tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Creating the DBADM Profile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
Creating User DBADMIN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Granting Database Privileges to User DBADMIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Setting Up Database Administrator Access to Teradata Viewpoint . . . . . . . . . . . . . . . . 102
Chapter 6: Creating the Database Structure . . . . . . . . . . . . . . . . . . 105
Database Implementation Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Teradata Data Warehouse Appliance 2700 Implementation Guide 9
Table of Contents
About the Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .105
Working with Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .105
About Top Level Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .105
Creating the Tables Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .106
Creating the Views Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .108
Working with Table Access Privileges for Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111
Working with Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112
About Teradata Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .112
Table Implementation Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .113
Converting Existing Database Tables to Teradata Tables . . . . . . . . . . . . . . . . . . . . . . . . .113
About Primary Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .115
Choosing a Primary Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .117
Creating Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .118
Validating Primary Index Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .123
Working with Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .123
Creating Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .124
Working with the Basic Teradata Query Utility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .128
Starting BTEQ in Interactive Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .128
Using BTEQ Scripts to Create Database Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .129
Chapter 7: Converting Queries to Teradata SQL . . . . . . . . . . . . . . .131
Converting Data Types to Teradata Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .132
Converting Built-in SQL Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .132
About Oracle-Equivalent Teradata UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .133
Installing Oracle-Equivalent Teradata UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .133
Using Oracle-Equivalent Teradata UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Translating Built-in SQL Functions to Teradata SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . .134
Converting Hierarchical Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .135
Converting Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .136
Checking the Use of NULLs in Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
Converting Nested Aggregate Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .137
Converting External Tables to Table Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .138
Converting Queue Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .138
Converting Queries to Set Processing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .139
Converting PL/SQL Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .141
General Considerations for Using Teradata SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .142
10 Teradata Data Warehouse Appliance 2700 Implementation Guide