Introduction to the (cid:226) (cid:226) Teradata RDBMS for UNIX Version 2 Release 2.1.0 BD10-4955-B Introduction to the Teradata® RDBMS for UNIX® Version 2 Release 2.1 BD10-4955-B 01.00.00 May 29, 1998 The product described in this book is a licensed product of NCR Corporation. BYNET is a registered trademark of Teradata Corporation CICS, CICS/ESA, CICS/VS, DATABASE2, DB2, IBM, MVS/ESA, MVS/XA, QMS, RACF, SQL/DS, VM/XA, and VTAM are trademarks or registered trademarks of International Business Machines Corporation in the U. S. and other countries. DBC/1012 is a registered trademark of Teradata Corporation. DEC, VAX, MicroVax, and VMS are registered trademarks of Digital Equipment Corporation. EXCELAN is a trademark of Excelan, Incorporated. HEWLETT-PACKARD is a registered trademark of Hewlett-Packard Company, INTELLECT and KBMS are trademarks of Trinzic Corporation. INTERTEST is a registered trademark of Computer Associates International, Inc. ISO is a trademark of International Standards Organization. MICROSOFT, MS-DOS, DOS/V, Windows, Windows 95, and Windows NT are registered trademarks of Microsoft Corporation. SABRE is a trademark of Seagate Technology, Inc. SAS and SAS/C are registered trademarks of SAS Institute Inc. SUN and SUN OS are trademarks of Sun Microsystems, Incorporated. TCP/IP protocol is a United States Department of Defense Standard ARPANET protocol. TERADATA is a registered trademark of Teradata Corporation. UNIX is a registered trademark of UNIX System Laboratories. YNET is a registered trademark of Teradata Corporation. X/Open and the X device are trademarks of X/Open Company Limited. XNS is a trademark of Xerox Corporation. It is the policy of NCR Corporation (NCR) to improve products as new technology, components, software, and firmware become available. NCR, therefore, reserves the right to change specifications without prior notice. All features, functions, and operations described herein may not be marketed by NCR in all parts of the world. In some instances, photographs are of equipment prototypes. Therefore, before using this document, consult with your NCR representative or NCR office for information that is applicable and current. To maintain the quality of our information products, we need your comments on the accuracy, clarity, organization, and value of this book. Please complete the User Feedback Form and mail or e-mail the form to: [email protected] Information Engineering NCR Corporation 100 North Sepulveda Boulevard El Segundo, CA 90245-4361 U.S.A. Copyright © 1998 By NCR Corporation Dayton, Ohio U.S.A. All Rights Reserved Printed in U.S.A. Preface About This Book About This Book Note: The name of the Teradata Database System (DBS) has been changed to the Teradata Relational Database Management System (RDBMS) to more accurately reflect the true nature of the product. This change will take place over a period of time in documentation, product names, and screen displays. In the meantime, all occurrences of “Teradata Database System,” “Teradata DBS,” or “DBS” should be read as referring to the “Teradata Relational Database Management System.” This book provides an introduction to the Teradata RDBMS for Purpose UNIX. This book is intended for anybody who uses the Teradata RDBMS Audience for UNIX. This book contains thirteen chapters, one appendix and a glossary: How This Book Is Organized Chapter 1, “Overview,” introduces the Teradata RDBMS, including its design philosophy and goals, its shared information architecture, and its scalability. Chapter 2, “Teradata RDBMS Architecture,” introduces the hardware and software architecture that supports the Teradata RDBMS, including both client and server software. System 3500, System 4500, and System 5100 (WorldMark) hardware is described. Chapter 3, “The Relational Model,” presents an overview of the relational model for database management, including an introduction to normalization and a brief discussion of Teradata RDBMS macros. Chapter 4, “Data Definition,” describes the data definition capabilities of Teradata SQL, the Structured Query Language, including how to create, change, and delete databases, tables, indexes, and macros. Chapter 5, “Data Manipulation,” describes the data manipulation capabilities of Teradata SQL, including the SELECT, INSERT, UPDATE, and DELETE statements. Chapter 6, “Views,” introduces the concept of the view, emphasizing that views are virtual, not base tables. The chapter also describes why views are the recommended means by which to present base table information to end users. Introduction to the Teradata RDBMS for UNIX i Preface About This Book Chapter 7, “Data Dictionary,” describes the Data Dictionary (DD), the system catalog for the Teradata RDBMS. The DD includes definitions for the database objects, user characteristics, and much more. Chapter 8, “Application Development,” introduces application development in the Teradata RDBMS environment, including the use of embedded SQL and CLI calls in client programming languages. Chapter 9, “Fault Tolerance,” describes fault tolerance in the Teradata RDBMS, including both hardware and software elements. Chapter 10, “Concurrency Control and Recovery,” introduces the topic of concurrency control and transactions. Object locking, serializability of transactions, and the two-phase commit protocol for distributed databases are among the subjects described. Chapter 11, “Security and Integrity,” discusses security and integrity in the Teradata RDBMS environment. Chapter 12, “System Administration,” introduces system administration of the Teradata RDBMS. Topics include user and space allocation, accounting, monitoring, and server-resident utilities. Chapter 13, “Operating and Configuration Specifications,” describes the capacities of and requirements for the Teradata RDBMS. Appendix A, “How the Teradata RDBMS for UNIX Differs from the Teradata RDBMS for TOS,” describes the differences between Version 1 and Version 2 Teradata database management systems. The “Glossary” defines frequently used terms in the Teradata RDBMS environment. You should be familiar with basic computer technology, NCR Prerequisites system hardware, the Teradata RDBMS, the system console environment, and X Windows. It may be helpful to review the following books: l Introduction to Teradata RDBMS for UNIX l Teradata RDBMS for UNIX Support Utilities Reference ii Introduction to the Teradata RDBMS for UNIX Preface Changes to This Book Changes to This Book Changes made to the Introduction to the Teradata RDBMS for UNIX are focused on DR maintenance and include: Join Index Join Index represents a new type of indexing structure. For introductory DR 37060 information on Join Index see page 4-10 and page 4-17. For general information on Join Index, see the Teradata RDBMS for UNIX V2R2.1 Base System Release Definition and Transmittal Document. For usage information see the section on Join Index in the Teradata RDBMS for UNIX Database Design and Administration Manual. RFC to provide ESCON This DR addresses changes to the mainframe channel mainframe physical connection to the connectivity Teradata server. Pages in this document that are impacted: page 2-3, page 2-4, DCR 7030 page 2-5 and page 2-11. Hash Join Hash Join is an alternative join scheme and is introduced on page 5-13. DR 39131 Decimal 18 Default is The increase of the maximum Decimal Regression Problem value for TotalDigits from 15 to 18 has caused regression problems some DR 39789 customer applications and third party vendor processes. This DR is addressed in page 4-2 and page 4-2. Introduction to the Teradata RDBMS for UNIX iii Preface Changes to This Book Minor wording changes Corrected on page 2-11. include: DR 41272 Teradata RDBMS Year Throughout this reference, there is 2000 Qualification frequent mention of the DATE parameter in a 2-digit year format DR 38139 ‘YY/MM/DD’. Teradata RDBMS V2R2.1 introduces the use of a system-wide default called the CenturyBreak parameter which the RDBMS software will use to internally convert 2-digit dates (‘YY’) to the correct 4-digit date (‘XXYY’). This new parameter is a new general field in the DBS control record. For more information on the CenturyBreak parameter see Chapter 14, “Setting Up, Creating, and Modifying the Database Structure,” of the Teradata RDBMS for UNIX Database Design and Administration Manual. iv Introduction to the Teradata RDBMS for UNIX Preface List of Acronyms List of Acronyms The following acronyms, listed in alphabetical order, are used in this book: 1NF First Normal Form 2NF Second Normal Form 2PC Two-Phase Commit 3NF Third Normal Form 4NF Fourth Normal Form 5NF Fifth Normal Form AMP Access Module Process ANSI American National Standards Institute API Application Programming Interface ASCII American Standard Code for Information Interchange ASF2 Archive Storage Facility 2 AWS Administrative Workstation BCNF Boyce-Codd Normal Form BTEQ Basic Teradata Query Facility CICS Customer Information Control System CLIv2 Call-Level Interface, Version 2 CMS Conversational Monitor System CNS Console Subsystem DB2 DATABASE 2 DBC Database Computer DBS Database System DBW Database Window DD Data Dictionary DDL Data Definition Language DML Data Manipulation Language EBCDIC Extended Binary Coded Decimal Interchange Code FIPS Federal Information Processing Standards Introduction to the Teradata RDBMS for UNIX v Preface List of Acronyms IBM International Business Machines Corporation I/O Input/Output JBOD Just a Bunch Of Disks MOSI Micro Operating System Interface MPP Massively Parallel Processing MTDP Micro Teradata Director Program MVS Multiple Virtual Storage NIST National Institute of Standards and Technology NUPI Nonunique Primary Index NUSI Nonunique Secondary Index ODBC Open Database Connectivity OLTP On Line Transaction Processing OS/VS Operating System/Virtual Storage PDE Parallel Database Extensions PE Parsing Engine PJ/NF Projection-Join Normal Form PP2 Preprocessor2 RAID Redundant Array of Inexpensive Disks RI Referential Integrity SMP Symmetric Multi-Processing SQL Structured Query Language SQL/DS Structured Query Language/Data System TCP/IP Transmission Control Protocol/Internet Protocol TDP Teradata Director Program TOS Teradata Operating System TPA Trusted Parallel Application TS/API Transparency Series/Application Program Interface TSO Time Sharing Option UPI Unique Primary Index USI Unique Secondary Index VM/CMS Virtual Machine/Conversational Monitor System vi Introduction to the Teradata RDBMS for UNIX Preface List of Acronyms VM/SP Virtual Machine/System Product VS Virtual Storage Introduction to the Teradata RDBMS for UNIX vii