Database Administration

591 Pages·2017·3.35 MB·English

by  Teradata Information Engineering

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