Introduction to Teradata Warehouse. Teradata Database Release V2R5.1 Teradata Warehouse Release 7.1

298 Pages·2003·1.334 MB·English

Teradata Database
Introduction to Teradata
Warehouse
Teradata Database Release V2R5.1
Teradata Warehouse Release 7.1
B035-1091-083A
November 2003
The product described in this book is a licensed product of NCR Corporation.
BYNET is an NCR trademark registered in the U.S. Patent and Trademark Office.
CICS, CICS/400, CICS/600, CICS/ESA, CICS/MVS, CICSPLEX, CICSVIEW, CICS/VSE, DB2, DFSMS/MVS, DFSMS/
VM, IBM, NQS/MVS, OPERATING SYSTEM/2, OS/2, PS/2, MVS, QMS, RACF, SQL/400, VM/ESA, and VTAM are
trademarks or registered trademarks of International Business Machines Corporation in the U. S. and other countries.
DEC, DECNET, MICROVAX, VAX and VMS are registered trademarks of Digital Equipment Corporation.
HEWLETT-PACKARD, HP, HP BRIO, HP BRIO PC, and HP-UX are registered trademarks of Hewlett-Packard Co.
KBMS is a trademark of Trinzic Corporation.
INTERTEST is a registered trademark of Computer Associates International, Inc.
MICROSOFT, MS-DOS, MSN, The Microsoft Network, MULTIPLAN, SQLWINDOWS, WIN32, WINDOWS,
WINDOWS 2000, and WINDOWS NT are trademarks or registered trademarks of Microsoft Corporation.
SAS, SAS/C, SAS/CALC, SAS/CONNECT, and SAS/CPE are registered trademarks of SAS Institute Inc.
SOLARIS, SPARC, SUN and SUN OS are trademarks of Sun Microsystems, Inc.
TCP/IP protocol is a United States Department of Defense Standard ARPANET protocol.
TERADATA and DBC/1012 are registered trademarks of NCR International, Inc.
UNICODE is a trademark of Unicode, Inc.
UNIX is a registered trademark of The Open Group.
X and X/OPEN are registered trademarks of X/Open Company Limited.
YNET is a trademark of NCR Corporation.
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 NCR CORPORATION (NCR) 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 NCR intends to
announce such features, functions, products, or services in your country. Please consult your local NCR 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. NCR 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 e-mail: [email protected]
or write:
Information Engineering
NCR Corporation
100 North Sepulveda Boulevard
El Segundo, CA 90245-4361
U.S.A.
Any comments or materials (collectively referred to as “Feedback”) sent to NCR will be deemed non-confidential. NCR
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, NCR 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 © 1999–2003, NCR Corporation
All Rights Reserved
Preface
Supported Software Release
This book supports Teradata® Database Release V2R5.1 and Teradata®
Warehouse Release 7.1.
Changes to This Book
This book includes the following changes to support the current release:
Date Description
November 2003 Changed the title from Introduction to Teradata RDBMS to
Introduction to Teradata Warehouse.
Reorganized the book, placing chapters into sections so that
information is easier to find.
Added the following features to the appropriate chapters:
(cid:127) Hot Standby Nodes
(cid:127) Disk I/O Integrity Checking
(cid:127) SQL Functions
(cid:127) User-Defined Functions
(cid:127) Encryption
(cid:127) Database Object Use Count
Added information about:
(cid:127) Teradata Tools and Utilities
(cid:127) Teradata Meta Data Services
Updated information in existing sections. This information is
marked by change bars in the margins.
Introduction to Teradata Warehouse i
Preface
Date Description
December 2002 Chapter 2
(cid:127) Moved the following:
“Data Communications” section to Chapter 6 and updated
the features in the section
“Database Management Tools” to Chapter 9
(cid:127) Rewrote the following section:
“Disk Arrays”
(cid:127) Added the Cylinder Read feature to “The Teradata File
System” section
Chapter 3
(cid:127) Added the following new features:
“Referential Constraints”
“Batch Referential Integrity”
(cid:127) Added information about rows and columns to:
“Derived Tables”
“Tables, Rows, and Columns”
(cid:127) Moved the index information to the new Chapter 5: “Data
Distribution and Access Methods.”
ii Introduction to Teradata Warehouse
Preface
Date Description
December 2002 Chapter 4
(continued)
(cid:127) Added the following new section:
“What Are Macros”
“Table Constraints”
“Default Database”
“Identity Column”
(cid:127) Updated the following sections:
“SQL Data Types”
“Data Type Attributes”
“Data Manipulation”
“SELECT Statement and Set Operators”
“What Are Teradata Stored Procedures”
(cid:127) Moved the following sections to Chapter 9: “Database
Management and Analysis Tools”:
“Basic Teradata Query Utility”
“Teradata RDBMS Preprocessor2”
Chapter 5
(cid:127) Created Chapter 5: “Data Distribution and Access Methods”
and consolidated index information in this chapter.
(cid:127) Added the following new feature:
“Partitioned Primary Indexes”
“Sparse Join Indexes”
(cid:127) Updated the following sections:
“Join Indexes”
“Hashing”
“Index Specification”
“Strengths and Weaknesses of Various Types of Indexes”
“Secondary Indexes”
Chapter 6
(cid:127) Updated the following section:
“Summary of Data Dictionary Views”
Chapter 7
(cid:127) Updated the following sections:
(cid:127) “Stored Procedures as SQL Applications”
(cid:127) “The EXPLAIN Statement”
(cid:127) “Data Communications”
(cid:127) “CLIv2 for Channel-Attached Systems”
(cid:127) “CLIv2 for Network-Attached Systems”
(cid:127) “Other Types of Data Communications”
Introduction to Teradata Warehouse iii
Preface
Date Description
December 2002 Chapter 8
(continued)
(cid:127) Added new chapter:
Chapter 8: “International Language Support”
Chapter 9
(cid:127) Added a new chapter titled, Chapter 9: “Database
Management and Analysis Tools.” The chapter contains
updated descriptions of the database management tools from
Chapter 2 and the new database query analysis tools in
release V2R5.0.
(cid:127) Added the following new tools:
“Priority Scheduler Administrator”
“Teradata Index Wizard”
“Database Query Log”
(cid:127) Added the following sections that were previously in Chapter
4: “Structured Query Language (SQL)”:
“Basic Teradata Query Utility”
“Teradata RDBMS Preprocessor2”
(cid:127) Updated the following sections:
“Query Session”
“Teradata Dynamic Query Manager”
“Teradata SQL Assistant”
Chapter 10
(cid:127) Updated the following sections:
“Vproc Migration”
“Fallback Tables”
“Journaling”
“Teradata Archive/Recovery”
“BYNETs”
“RAID Disk Units”
Chapter 11
(cid:127) Deleted Chapter 11 “System Maximum Capacities”. You can
find this information in:
Teradata RDBMS SQL Reference, Volume 1
Chapter 11 is now titled: Chapter 11: “Concurrency Control and
Transaction Recovery”
(cid:127) Updated the following section:
“Deadlocks and Deadlock Resolution”
Chapter 12
(cid:127) Added the following sections:
“User-Level Password Attributes”
iv Introduction to Teradata Warehouse
Preface
Date Description
December 2002 Chapter 13
(continued)
(cid:127) Updated the following sections:
“How to Create Databases”
“Account String Expansion”
“Account Performance Groups”
“Summary Mode”
“How to Control Collection and Logging of ResUsage Data”
(cid:127) Added the following new section:
“Roles and Profiles”
June 2001 Added the following features and enhancements:
(cid:127) Single Sign On
(cid:127) Hash Index
(cid:127) UTF-8 character set support
(cid:127) Integrated Database Query Manager
(cid:127) Resource Check Tool
(cid:127) 128 K data block support
(cid:127) Increased number of global and volatile temporary tables
Updated the following features:
(cid:127) Stored procedures
(cid:127) Indexes
(cid:127) Backup utilities
September 2000 (cid:127) Updated glossary. Removed references to UNISYS and
KBMS/Intellect. Replaced reference to Nomad2 to NOMAD.
Introduction to Teradata Warehouse v
Preface
Date Description
June 2000 (cid:127) COLLECT STATISTICS: Collected statistics are now stored in
a spool table so that you can collect statistics at the same time
you execute queries against the table.
(cid:127) Fallback: You can now define join index subtables with
fallback.
(cid:127) Online Analytical Processing (OLAP): The operation of OLAP
sampling is now optimized in the file system by only
accessing the data blocks that contain the target row positions
instead of scanning all the data blocks.
(cid:127) PDE Tools Utility (NT Only): A new PDE tools utility allows
you to run the ctl, DBS control, DIP, and Vproc Manager
utilities from any TPA or non-TPA or from the AWS. You can
also start, reset, or stop the PDEs.
(cid:127) EXPLAIN: The size of the EXPLAIN text is now unlimited.
(cid:127) Stored Procedures: Teradata has developed the Teradata
Stored Procedures (TDSP) feature.
April 1999 (cid:127) A new virtual BYNET driver has eliminated the need for the
vnet driver on systems that have no BYNET hardware.
(cid:127) Addition of the use of triggers with SQL statements.
(cid:127) Addition of a join index to improve performance
(cid:127) Internationalization of Kanji characters
(cid:127) Increase in the maximum number of vprocs to 16k
(cid:127) Increase in the row size to 64 K
(cid:127) Addition of OLAP features for statistical functions, extended
date/calendar capability, and sampling.
(cid:127) Addition of Timestamping to the Data Dictionary.
vi Introduction to Teradata Warehouse
Preface
About This Book
About This Book
Purpose
This book provides an introduction to the Teradata Warehouse covering:
(cid:127) Teradata Database and Teradata Tools and Utilities
(cid:127) Teradata Database architecture and the relational model
(cid:127) Applications and data communications
(cid:127) Data definitions and data manipulation using Structured Query Language
(SQL)
(cid:127) System administration and security
Audience
This book is intended for users who interface with the Teradata Warehouse.
Such individuals may include database users or administrators.
How This Book Is Organized
This book contains the following chapters:
Chapter 1: “Teradata Warehouse” provides an introduction to the data
warehouse concepts.
Section 1: “Teradata Warehouse Overview” contains the following chapters:
Chapter 2: “Teradata Warehouse Overview” describes the components of the
Teradata Warehouse.
Chapter 3: “Teradata Database Architecture” describes the Teradata Database
system hardware and its design.
Chapter 4: “International Language Support” describes the language support
capabilities of the Teradata Database.
Section 2: “The Teradata Database Structure” contains the following chapters:
Chapter 5: “Structured Query Language (SQL)” provides information about
how to use SQL to manipulate data in the Teradata Database.
Chapter 6: “Application Development” provides information about developing
applications for the Teradata Database.
Chapter 7: “The Teradata Database Model” contains basic information about
the tables, rows, and columns that make up the database model.
Chapter 8: “Data Distribution and Access Methods” provides information
about distributing data to and retrieving data from the Teradata Database.
Introduction to Teradata Warehouse vii
Preface
About This Book
Chapter 9: “Data Dictionary” describes the structure and content of the system
tables in the Data Dictionary.
Chapter 10: “Teradata Meta Data Services Services” provides information about
Teradata Meta Data Services, which allows you to store, access, and
administrate metadata on the Teradata Database.
Chapter 11: “Other Database Objects” provides more information about views,
macros, stored procedures, and triggers.
Section 3: “Teradata Database System Operation” contains the following
chapters:
Chapter 12: “Normalization and Referential Integrity” describes the following:
(cid:127) How normalization reduces complex data to simpler, stable data structures
(cid:127) How referential integrity protects data
Chapter 13: “Data Communication Between Client and Teradata Database”
describes how the client and the Teradata Database exchange information.
Chapter 14: “Reliability” describes how fault-tolerant hardware and software
increase the reliability of the Teradata Database.
Section 4: “Management and Monitoring” contains the following chapters:
Chapter 15: “Concurrency Control and Transaction Recovery” describes the
mechanisms that prevent concurrently operating sessions from damaging the
data that resides within the Teradata Database.
Chapter 16: “Database Management and Analysis Tools” describes the tools
that you can use to manage the hardware and software that make up the
Teradata Database.
Chapter 17: “Security and Integrity” describes how to prevent unauthorized
access to the information in the Teradata Database.
Chapter 18: “System Administration” describes space allocation, roles and
profiles, accounting, and maintenance capabilities on the Teradata Database.
Chapter 19: “System Monitoring” describes the various aspects of monitoring
the Teradata Database, including the monitoring tools used to track system
performance.
Prerequisites
To gain an understanding of Teradata Warehouse, you should be familiar with
the following:
(cid:127) Basic computer technology
(cid:127) System hardware
(cid:127) Teradata Tools and Utilities
viii Introduction to Teradata Warehouse