Home Page Icon
Home Page
Table of Contents for
Cover Page
Close
Cover Page
by Thomas Coffing, Tom Coffing
Teradata Database Administration – The Fundamentals
Cover Page
Title Page
Copyright Page
About Tom Coffing
About Thomas Coffing III
Contents
Chapter 1 – Introduction and Good Advice
What is Parallel Processing?
Start Small and Think Big
Give your Enterprise the Tools they Need
Model the Business with ERwin
Educate the Business on the Business by Sharing the Model
Load your Models and have the SQL Built Automatically
Five Brilliant Pieces of Teradata (1 of 5) is MPP
Five Brilliant Pieces (2 of 5) are Tactical Queries
Five Brilliant Pieces (3 of 5) is a Traffic System
Five Brilliant Pieces (4 of 5) is Viewpoint
Five Brilliant Pieces (5 of 5) are Data Processing Options
Support Large Queries, but Monitor them Closely
Experiment and Improve Loading Data Strategies
Compress your Data with Multi-Value Compression
Separate your Production System from your Test System
Chapter 2 - Teradata Architecture Fundamentals the DBA must know
Parallel Architecture
The Teradata Architecture
All Teradata Tables are Spread across ALL AMPS
Teradata Systems can Add AMPs for Linear Scalability
AMPs and Parsing Engines (PE's) Live inside SMP Nodes
Each Node is Attached Via a Network to a Disk Farm
Two SMP Nodes Connected become One MPP System
There are Many Nodes in a Teradata Cabinet
This is the Visual you Want to Understand Teradata
Responsibilities of the DBA
Chapter 3 – The Primary Index is the Axis of all Teradata Systems
The Primary Index is Defined when the Table is CREATED
A Unique Primary Index (UPI)
Primary Index in the WHERE Clause - Single-AMP Retrieve
A Non-Unique Primary Index (NUPI)
Primary Index in the WHERE Clause - Single-AMP Retrieve
A Conceptual Example of a Multi-Column Primary Index
Primary Index in the WHERE Clause - Single-AMP Retrieve
A Conceptual Example of a Table with NO PRIMARY INDEX
A Full Table Scan is Likely on a Table with NO Primary Index
Table CREATE Examples with Four Different Primary Indexes
What Happens when you Forget the Primary Index?
Why Create a Table with no Primary Index (NoPI)?
Chapter 4 – A DBA's Best Friend - The Data Dictionary
The Data Dictionary Resides in User DBC
The DBC.DBCInfoV View
Querying the Data Dictionary
Using the Keyword USER
Restricted Views have an X at the End of their Name
The V is New with Teradata V12
The V and the Restricted X are Now often Combined
A Recap of what we have Learned so Far
The DBC.DatabasesV View
The DBC.Users View
The DBC.Tables View
Using DBC.Tables to Find out about Fallback
The DBC.Indices View
The DBC.Columns View
Clever Queries for the DBC.ColumnsV View
New V14 - The DBC.PartitioningConstraintsV View
The DBC.AccountInfo View
The DBC.AMPUsage View
Clearing out the DBC.AMPUsage Data
The DBC.AllTempTables
The DBC.Triggers
The DBC.All_RI_ChildrenV
DBC.SessionInfoV Information
DBC.LogonOffV
AllRoleRights, AllRightsV, UserRightsV and UserGrantedRightsV
The DBC.Profiles View
RoleMembers, RoleInfo, UserRoleRights and ProfileInfoVX,
Understanding that Space is Based on a Per-AMP Basis
Total Space for a Single Database or User
Using the Data Dictionary to See the Space for Everyone
Finding the Perm Percent used
Finding the Perm Percent used with a HAVING Clause
Finding the Perm Percent Left with a HAVING Clause
Creating a Macro for Perm Percent used with a Dynamic %
Orphaned Spool Files that Need to be Deleted
Finding Table Sizes
Finding Skew in the Tables in a Database
Finding Skew in a Table
Display the Distribution of a Column Per AMP
Your Users and Databases
DBC Tables used in the Collect Statistics Process
The DBC Table DBC.Next
DBA Advice - ClearPeakDisk to Reset Peak Space
DBA Advice – Clean out these Tables Periodically
The DBC.AssociationV View
The DBC.JournalsV View
DBC.Databases2V is for Unresolved Reference Constraints
The DBC.All_RI_ChildrenV for Inconsistent RI
The DBC.ShowColChecksV View
The DBC.ShowTblChecksV View
The DBC.PartitioningConstraintsV View
The DBC.AccessLogV View
The DBC.AccessLogV View for Today's Queries
The DBC.AccessLogV View Denials for Today
DBC.DBQLRulesV
DBC.QryLogV
DBC.QryLogSummaryV
ResUsage Macros
Executing the ResUsage Macro DBC.Resnode
The DBC.IdCol Table
Chapter 5 - How Teradata Tracks Objects
Teradata Assigns Each Object a Unique Numeric ID
The Table ID
The Table ID in Greater Detail
Looking at the TableID inside the Actual Cylinders
A More Detailed View of TableID inside the Actual Cylinders
The Blocks Below are all Associated with the Same Table
Bits, Bytes and More
Cylinder Sizes
Chapter 6 - Creating Users and Databases
Creating Users and Databases
Password Security Meanings
Now we have Two Users in the Teradata System
A Grant Statement so Others Create a Database or User
And so the Teradata Hierarchy Begins
Creating a Database
Users are Given Passwords while Database are not
Teradata Administrator can CREATE Users
The Modify User Statement
A Clever Way to Reset a User Password
Accounts and their Associated Priorities
Creating a User with Multiple Account Priorities
Self-Nicing to Change Account Priorities
Account String Expansion (ASE)
The DBC.AccountInfo View
The DBC.AMPUsage View
Account String Expansion (ASE) in Action
Test – Run Queries under all Accounts for TeraTom
The DBC.AMPUsage View
Chapter 7 - Profiles
Profiles
Getting Started for Profile Creation
Creating a Profile and a User
Password Security
Password Security Meanings
Creating a Profile and then Modifying a User
Quiz – What are the Profile Values?
Answer to Quiz – What are the Profile Values?
Quiz – What are the Profile Values after Null?
Answer to Quiz – What are the Profile Values after Null?
The DBC.ProfilesVX View
The DBC.ProfilesV View
The DBC.AccountInfoVX View
ProfileInfoVX, RoleMembers, RoleInfo and UserRoleRights
Teradata Administrator can CREATE Profiles (1 of 2)
Teradata Administrator can CREATE Profiles (2 of 2)
Dropping a Profile
The Effects of Dropping a Profile
Chapter 8 – Roles
Roles
Getting Started for Role Creation
Create a Role and then Assign that Role it's Access Rights
Create a User and Assign them a Default Role
A Role Vs. a Profile
Granting a Role to a Current User
Active Roles
Setting your Active Role to ALL
Roles and Valid Objects
Roles and Invalid Commands
Nesting of Roles
Nesting of Roles in Action (1 of 3)
Nesting of Roles in Action (2 of 3)
Nesting of Roles in Action (3 of 3)
Quiz – What Databases Does Mandy have Access to?
Answer – What Databases Does Mandy have Access to?
GRANT WITH ADMIN OPTION Command
REVOKE ADMIN OPTION FOR Command
RoleMembers, RoleInfo, UserRoleRights and ProfileInfoVX,
DBC Tables for AllRoleRights, AllRightsV, UserRightsV and UserGrantedRightsV
Chapter 9 - Access Rights
The Objects that Require Access Rights
Objects and Available Access Rights
A Few Examples to Get you Started
There are Three Types of Access Rights
There are Three Types of Access Rights
There are Three Types of Access Rights
A Dinner Invitation of Access Rights
One of the Problems with Access Rights
The Rights for SysDBA and TeraTom
The GRANT Statement
Create a Role and then Assign that Role it's Access Rights
GRANT to PUBLIC
GRANT to ALL DBC
GRANT using the ALL Keyword
GRANT Database Strategy for Users, Views and Tables
Inheriting Access Rights
GRANT at the Column Level
GRANT for the Ability to CREATE Secondary Indexes
Access Rights to CREATE Triggers
The REVOKE Command
DBC Tables for AllRoleRights, AllRightsV, UserRightsV and UserGrantedRightsV
The GIVE Statement
A DROP User can be Better than a GIVE Statement
Removing a Level in the Teradata Hierarchy
Chapter 10 - Collect Statistics
The Teradata Parsing Engine (Optimizer) is Cost Based
The Purpose of Collect Statistics
When Teradata Collects Statistics it Creates a Histogram
The Interval of the Collect Statistics Histogram
Histogram Quiz
Answers to Histogram Quiz
What to COLLECT STATISTICS on?
Why Collect Statistics?
How do you know if Statistics were Collected on a Table?
A Huge Hint that no Statistics have been Collected
The Basic Syntax for COLLECT STATISTICS
COLLECT STATISTICS Examples for a Better Understanding
The New Teradata V14 Way to Collect Statistics
COLLECT STATISTICS Directly from Another Table
Where Does Teradata Keep the Collected Statistics?
The Official Syntax for COLLECT STATISTICS
How to Recollect STATISTICS on a Table
Teradata Always Does a Random AMP Sample
Random Sample is Kept in the Table Header in FSG Cache
Multiple Random AMP Samplings
How a Random AMP Gets a Table Row Count
Random AMP Estimates for NUSI Secondary Indexes
USI Random AMP Samples are not Considered
There's no Random AMP Estimate for Non-Indexed Columns
A Summary of the PE Plan if no Statistics were Collected
Stale Statistics Detection and Extrapolation
Extrapolation for Future Dates
How to Copy a Table with Data and the Statistics
How to Copy a Table with NO Data and the Statistics
When to COLLECT STATISTICS using only a SAMPLE
Examples of COLLECT STATISTICS using only a SAMPLE
Examples of COLLECT STATISTICS for V14
How to Collect Statistics on a PPI Table on the Partition
Teradata V12 and V13 Statistics Enhancements
Teradata V14 Statistics Enhancements
Teradata V14 Summary Statistics
Teradata V14 MaxValueLength
Teradata V14 MaxIntervals
Teradata V14 Sample N Percent
Teradata V14.10 Statistics Collection Improvements
Teradata V14.10 Statistics Collection Threshold Examples
Teradata V14.10 AutoStats Feature
Teradata Statistics Wizard
Chapter 11 – Locking
The Four Major Locks of Teradata
The Read Lock
The Read Lock and Joins
The Write Lock
The Exclusive Lock
The Three Levels of Locking
Locking at the Row Hash Level
Locking at the Table Level
Locking at the Database Level
The Ongoing Battle between Read and Write Locks
Compatibility between Read Locks
Why Read Locks Wait on Write Locks
Why Write Locks Wait on Read Locks
The Access Lock is Different from the Other Locks
What is the Purpose of an Access Lock?
Locking Modifiers - Locking Row, Table or Database
All Views should Consider the Locking for Access Statement
What is a Dead Lock or a Deadly Embrace?
Pseudo Tables are Designed to Minimize Dead Locks
Pseudo Tables are Referenced in the Explain Plan
Incompatible Locks Wait on Each Other
The Checksum Lock of Teradata
The Nowait Option for Locking
The Automatic Locking for Access Button inside Nexus
Viewpoint Lock Viewer
Viewpoint Lock Viewer Lets you Configure your View
What is a Host Utility (HUT) Lock?
Chapter 12 – Protection Features
A List of the Protection Features
Transient Journal Protects the Transaction Integrity
The Transient Journal in Action
A Single Transaction could Involve all AMPs
The Secret to Turning off the Transient Journal
The Transient Journal's Write Ahead Logging (WAL)
A Node with 40 AMPs and 40 Dedicated FSG Caches
The Transient Journal's Write Ahead Logging (WAL)
Working Example of the Write Ahead Log (WAL)
The First Step in our Example of the Write Ahead Log (WAL)
The Second Step in our Example of the Write Ahead Log
The Third Step in our Example of the Write Ahead Log
The Fourth Step in our Example of the Write Ahead Log
The Last Step in our Example of the Write Ahead Log
Fallback to Protect against an AMP Failure
Fallback Clusters
AMPs in a Cluster are Physically Separated
The Reason AMPs in a Cluster are Physically Separated
The Price you Pay for Fallback
How to Create a Table with Fallback
How to Create a Table with no Fallback
How to Alter a Table to Add or Drop Fallback
What is a Virtual Disk?
Why Do AMPs Each have Four Physical Disks?
Is a Mirror Just Like Looking into a Mirror?
RAID 1 Mirroring – Redundant Array of Independent Disks
What Does RAID Protect?
How Does RAID Fail?
Do RAID and Fallback have a Connection?
What is a Clique?
If a Node Goes Down the AMPs Migrate within the Clique?
Does Teradata Reset during a Node Failure?
Four Node Cliques
Migrating AMPs in Four Node Cliques
The Hot Spare Node
The Hot Spare Node in Action
With a Hot Spare a Second Teradata Reset isn't Needed
A Node, It's AMPs and their Disks
How Cliques are Physically Defined
Cliques are Cabled so Migrating AMPs can Access their Disks
A Review of Fallback and Clusters
An Example of Fallback and Clusters
Quiz 1 – How Many Clusters Do you See?
Quiz 1 Answer – How Many Clusters Do you See?
Quiz 2 – How Many Cliques Do you See?
Quiz 2 Answer – How Many Cliques Do you See?
Quiz 3 – What have we Lost? Multiple Choice Answer
Quiz 3 Answer – What have we Lost? Multiple Choice Answer
Quiz 4 – What have we Lost? Multiple Choice Answer
Quiz 4 Answer – What have we Lost? Multiple Choice Answer
Quiz 5 – What have we Lost? Which Answer is False?
Quiz 5 Answer – What have we Lost? Which Answer is False?
Quiz 6 – What have we Lost? Pick Two True Answers
Quiz 6 Answer – What have we Lost? Pick Two True Answers
Summary of the Facts for Fallback, Clusters, and Cliques
Quiz 7 –How Many Virtual Disks (Vdisks) are in this System?
Quiz 7 Answer –How Many Virtual Disks are in this System?
Quiz 8 –How Many Physical Disks are in this System?
Quiz 8 Answer–How Many Physical Disks are in this System?
Quiz 9 – How Many Transient Journals in this System?
Quiz 9 Answer –How Many Transient Journals in this System?
Quiz 10 – How Many Transient Journals are Open?
Quiz 10 Answer – How Many Transient Journals are Open?
Quiz 11 – How Much Space?
Quiz 11 Answers – How Much Space?
Quiz 12 – How Much Space with Fallback?
Quiz 12 Answers – How Much Space with Fallback?
Quiz 13 – How Many Disks could we Lose with RAID 1?
Quiz 13 Answer – How Many Disks could we Lose?
Quiz 14 – How Many Disk Losses could Kill us?
Quiz 14 Answer – How Many Disk Losses could Kill us?
Quiz 15 – How Many AMPs could we Lose if Lucky?
Quiz 15 Answer – How Many AMPs could we Lose if Lucky?
Quiz 16 – How Many AMPs could we Lose if Unlucky?
Quiz 16 Answer – How Many AMPs could we Lose Unlucky?
The Permanent Journal
Difference between the Transient and the Permanent Journal
Difference between the before and after Permanent Journal
Full System Backup Compared to an after Journal
How Full System Backups Work with the after Journal
The Many Different Permanent Journal Options
Where is the Permanent Journal Stored?
Using Common Sense about Journal Locations
After Journals are Never Stored in the Same Node or Clique
What is a Dual after Journal?
What is a Dual before Journal?
What is a Journal?
Creating a Table with Fallback and a before and after Journal
Does Fallback Affect a Permanent Journal?
Permanent Journal Rules
Example 1: Permanent Journal Scenarios to Test the Rules
Example 2: Permanent Journal Scenarios to Test the Rules
Example 3: Permanent Journal Scenarios to Test the Rules
How to Create Database with a Permanent Journal
Creating Tables Under Different Journal Circumstances
Permanent Journal's Three Main Areas
The Current Journal Consists of the Active and Saved Areas
Permanent Journal Commands
Deleting a Permanent Journal
Some Great Advice for Maintaining the Permanent Journals
Recovery Using the Permanent Journals
The Journals View in DBC (DBC.Journals)
Archive Recovery Console (ARC)
Reasons you Might Utilize ARC
ARC Raising the BAR (Backup Archive Restore)
ARC Commands in Alphabetical Order
An ARC Example of an Archive and then a Restore
Chapter 13 – Top SQL Commands Cheat Sheet
SELECT all Columns from a Table and Sort
Select Specific Columns and Limiting the Rows
Changing your Default Database
Keywords that Describe you
Select TOP Rows in a Rank Order
A Sample Number of Rows
Getting a Sample Percentage of Rows
Find Information about a Database
Find Information about a Table
Using Aggregates
Performing a Join
Performing a Join using ANSI Syntax
Using Date, Time and Timestamp
Using Date Functions
Using the System Calendar
Using the System Calendar in a Query
Formatting Data
Using Rank
Using a Derived Table
Using a Subquery
Correlated Subquery
Using Substring
Basic CASE Statement
Advanced CASE Statement
Using an Access Lock in your SQL
Collect Statistics
CREATING a Volatile Table with a Primary Index
CREATING a Volatile Table that is Partitioned (PPI)
CREATING a Volatile Table that is Deleted after the Query
Finding the Typical Rows Per Value for Specific Column
Finding out how Much Space you have
How Much Space you have Per AMP
Finding your Space
Finding Space Skew in Tables in a Database
Finding the Number of Rows Per AMP for a Column
Finding Account Information
Ordered Analytics
Back Cover
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Next
Next Chapter
Title Page
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset