Teradata Database Database Administration Release 15.10 B035-1093-151K April 2018 The product or products described in this book are licensed products of Teradata Corporation or its affiliates. Teradata, Aster, BYNET, Claraview, DecisionCast, IntelliBase, IntelliCloud, IntelliFlex, QueryGrid, SQL-MapReduce, Teradata Decision Experts, "Teradata Labs" logo, Teradata ServiceConnect, and Teradata Source Experts 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. Amazon Web Services, AWS, Amazon Elastic Compute Cloud, Amazon EC2, Amazon Simple Storage Service, Amazon S3, AWS CloudFormation, and AWS Marketplace are trademarks of Amazon.com, Inc. or its affiliates in the United States and/or other countries. AMD Opteron and Opteron are trademarks of Advanced Micro Devices, Inc. Apache, Apache Avro, Apache Hadoop, Apache Hive, 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. CENTOS is a trademark of Red Hat, Inc., registered in the U.S. and other countries. Cloudera and CDH are trademarks or registered trademarks of Cloudera Inc. in the United States, and in jurisdictions throughout the world. Data Domain, EMC, PowerPath, SRDF, and Symmetrix are either registered trademarks or trademarks of EMC Corporation in the United States and/or other countries. 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, IBM Spectrum Protect, and z/OS are trademarks or 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 of Quest Software, Inc. 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. SAP is the trademark or registered trademark of SAP AG in Germany and in several other countries. SAS and SAS/C are trademarks or registered trademarks of SAS Institute Inc. Sentinel® is a registered trademark of SafeNet, Inc. SPARC is a registered trademark of SPARC International, Inc. 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. Veritas, the Veritas Logo and NetBackup are trademarks or registered trademarks of Veritas Technologies LLC or its affiliates in the U.S. 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-2018 by Teradata. All Rights Reserved. Preface Purpose This book covers the basics of Teradata database administration, giving you the essential tasks and the procedures for doing them. Chapters in this book are either designed for all DBAs or split tasks into two major groupings indicated in the chapter title: those for application DBAs, who are concerned with the design and performance of queries, and administrative DBAs, who are responsible for the day-to-day operation and maintenance of the data warehouse. This book provides more details on topics of interest primarily to database administrators. Where complete syntax is necessary or in-depth details are required, this book may point you to other books with more extensive information. Audience This book is intended for database administrators (DBAs). It is also useful for system operators. Supported Software Releases and Operating Systems This book supports Teradata® Database 15.10.x Teradata Database 15.10.x is supported on: • SUSE Linux Enterprise Server 10 SP3 (cid:129) SUSE Linux Enterprise Server 11 SP1 (cid:129) SUSE Linux Enterprise Server 11 SP3 (Release 15.10.01 and later) Teradata Database client applications support other operating systems. Prerequisites You should have some knowledge of the following: (cid:129) Relational database concepts (cid:129) Teradata SQL (cid:129) The Teradata Database configuration Database Administration 3 Preface Changes to This Book You might find it helpful to review the following publications: (cid:129) Introduction to Teradata (cid:129) Database Design (cid:129) SQL Fundamentals The following publications are directly related to the information in this book and you may find it helpful to have them available for reference: (cid:129) SQL Data Definition Language (cid:129) Utilities (cid:129) Security Administration (cid:129) Data Dictionary (cid:129) The suite of Teradata Tools and Utilities client documents Changes to This Book Release Description Teradata Database Release 15.10 Reflected table schema changes in several DBQL topics: April 2018 (cid:129) Updated the instructions and examples in Shredding the Data in the DBQLXMLLockTbl. (cid:129) Replaced Output of Shredding. (cid:129) Added a new topic: Investigating Deadlocks: Querying Shredded_LockTbl. Teradata Database Release 15.10 Added information about the following target tables used in shredding the lock plan information in DBQLXMLLockTbl: September 2017 (cid:129) Shredded_LockTbl: blocking transaction lock shredding target table (cid:129) Shredded_TransTbl: blocked transaction lock shredding target table Teradata Database Release 15.10 (cid:129) Updated information on mainframe channel and network connections. July 2015 (cid:129) The WITH option of END QUERY LOGGING is deprecated. (cid:129) Specified that you cannot log use count information for account strings. (cid:129) Added the QryLogParamJSON view. Changed the QryLogParam view output to CLOB type. (cid:129) Added step names and their explanations for DBQLStepTbl. (cid:129) The Recovery Manager commands LIST ROLLBACK TABLES and LIST CANCEL ROLLBACK TABLES now include DBQL and ResUsage tables. (cid:129) Updated the description of the RollbackPriority DBS control field. 4 Database Administration Preface Additional Information Release Description Teradata Database Release 15.10 (cid:129) Moved the data compression chapter from this book to Database Design. June 2015 (cid:129) Reflected that PDE tool mboxchk replaces dbschk in detecting database hangs and slowdowns. (cid:129) Incorporated Trusted Sessions, CheckTable Enhancements, MLOADX Enhancements, Load Isolation, Secure Zones, Columnar Enhancements, DSA: Allow Restore Jobs To Complete Even When Out of Space, SQL Equivalents of Ferret Output, Support Audit Trail of DBS Control GDO, New AWT Resource Throttle, NO COLUMNS suboption to the OBJECT logging option of BEGIN QUERY LOGGING, and Combining Multiple DBQL XML Plan Rows in One Document. (cid:129) Added expanded deadlock information and revised shredding instructions to the DBQLXMLLockTbl section. (cid:129) Noted that Teradata logs a warning if the number of crashdumps saved on a node is near the limit. (cid:129) Removed documentation on troubleshooting a snapshot dump. The Onsite System and Dump Analysis tool is for Teradata Customer Support only. (cid:129) Removed references to 30-character limits for object names. Teradata Database supports object names up to 128 characters long in most cases. For more information on object naming see SQL Fundamentals. 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. Database Administration 5 Preface Product Safety Information URL Description www.teradata.com The Teradata home page provides links to numerous sources of information about Teradata. Links include: (cid:129) Executive reports, white papers, 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 delivers training 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. 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 teradata- [email protected]. Product Safety Information This document may contain information addressing product safety practices related to data or property damage, identified by the word Notice. A notice indicates a situation which, if not avoided, could result in damage to property, such as equipment or data, but not related to personal injury. Example: Notice: Improper use of the Reconfiguration utility can result in data loss. Teradata Database Optional Features This book may include descriptions of the following optional Teradata Database features and products: (cid:129) In-Memory Optimization (cid:129) Teradata Columnar (cid:129) Teradata Row Level Security (cid:129) Teradata Secure Zones (cid:129) Teradata Temporal 6 Database Administration Preface Teradata Database Optional Features (cid:129) Teradata Virtual Storage (VS) You may not use these features without the appropriate licenses. The fact that these features may be included in product media or downloads, or described in documentation that you receive, does not authorize you to use them without the appropriate licenses. Contact your Teradata sales representative to purchase and enable optional features. Database Administration 7 Preface Teradata Database Optional Features 8 Database Administration Table of Contents Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Supported Software Releases and Operating Systems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Prerequisites . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3 Changes to This Book. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4 Additional Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5 Product Safety Information. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6 Teradata Database Optional Features. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .6 Chapter 1: Setting Up Your Administrative Environment: All DBAs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 Logging in to the Operating System . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 About Root Logons . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 About Tdtrusted Logons. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27 About User DBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Setting Up Teradata Administrator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Set Up the Teradata Administrator GUI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Set Up the Data Directory for Teradata Administrator . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Setting Up the Database Administrator User. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Creating the DBADM Profile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Creating User DBADMIN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Granting Database Privileges to User DBADMIN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Creating a Spool Reserve Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Guidelines for Reserving Minimum Spool Space. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Using Viewpoint Alerts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Setting Up Alert Actions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Creating Alerts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Setting Up Teradata Viewpoint Alerts for Space Usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 About the Global Default Parameters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 Database Administration 9 Table of Contents Chapter 2: About Databases and Users in Teradata: All DBAs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45 About Databases and Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45 Space Used by Databases and Users. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45 Creating Versus Owning Objects in the Hierarchy. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46 About Creating a Database or User . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47 The System Users . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48 System User DBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .48 System User SYSTEMFE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .49 System User SYSADMIN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .49 Other System Databases and Users. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .50 Recommended Hierarchy . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51 Secure Zones Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51 Chapter 3: Working with Databases: All DBAs . . . . . . . . . . . . . . . . . .53 About Database Creation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53 Best Practices for Database Creation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .53 Creating the Tables Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .54 Creating the Views Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .56 Working with Table Access Privileges for Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .59 Dropping a Database or User. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60 Transferring Ownership with GIVE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .60 Chapter 4: Working with Tables and Views: Application DBAs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63 Choosing a Primary Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63 Guidelines for Choosing Primary Index Columns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .63 Unique and Nonunique Primary Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65 Row Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .65 No Primary Index (NoPI) Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .66 Relationship of Primary Indexes, Primary Keys, and Foreign Keys . . . . . . . . . . . . . . . . . .66 Creating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .67 Converting Existing Database Tables to Teradata Tables . . . . . . . . . . . . . . . . . . . . . . . . . .67 Considerations for Defining Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .69 Creating Tables in Teradata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .73 Copying a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .78 10 Database Administration