Contents
Chapter 1 – Introduction and Good Advice
Give your Enterprise the Tools they need
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
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
Chapter 3 – The Primary Index is the Axis of all Teradata Systems
The Primary Index is defined when the table is CREATED
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
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
Using DBC.Tables to find out about Fallback
Clever Queries for the DBC.ColumnsV View
New V14 - The DBC.PartitioningConstraintsV View
Clearing Out the DBC.AMPUsage Data
AllRoleRights, AllRightsV, UserRightsV and UserGrantedRightsV
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 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 Skew in the Tables in a Database
Display the Distribution of a Column per AMP
DBC Tables used in the Collect Statistics Process
DBA Advice - ClearPeakDisk to Reset Peak Space
DBA Advice – Clean out these Tables Periodically
DBC.Databases2V is for Unresolved Reference Constraints
The DBC.All_RI_ChildrenV for Inconsistent RI
The DBC.PartitioningConstraintsV View
The DBC.AccessLogV View for Today's Queries
The DBC.AccessLogV View Denials for Today
Executing the ResUsage Macro DBC.Resnode
Chapter 5 - How Teradata Tracks Objects
Teradata Assigns each Object a Unique Numeric 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
Chapter 6 - Creating Users and Databases
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
Users are Given Passwords While Database are Not
Teradata Administrator Can CREATE Users
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)
Account String Expansion (ASE) in Action
Test – Run queries Under All Accounts for TeraTom
Getting Started for Profile Creation
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?
ProfileInfoVX, RoleMembers, RoleInfo and UserRoleRights
Teradata Administrator Can CREATE Profiles (1 of 2)
Teradata Administrator Can CREATE Profiles (2 of 2)
The Effects of Dropping a Profile
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
Granting a Role to a Current User
Setting Your Active Role to ALL
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
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
Create A Role and then Assign that Role It's Access Rights
GRANT Database Strategy for Users, Views and Tables
GRANT for the Ability to CREATE Secondary Indexes
Access Rights to CREATE Triggers
DBC Tables for AllRoleRights, AllRightsV, UserRightsV and UserGrantedRightsV
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
What to COLLECT STATISTICS On?
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
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.10 Statistics Collection Improvements
Teradata V14.10 Statistics Collection Threshold Examples
Teradata V14.10 AutoStats feature
The Four Major Locks of Teradata
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 Automatic Locking for Access Button inside Nexus
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
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
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
Do RAID and Fallback have a Connection?
If a Node goes down the AMPs migrate within the Clique?
Does Teradata Reset during a Node Failure?
Migrating AMPs in Four Node Cliques
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 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?
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 Before Journal?
Creating a Table with Fallback and a Before and After Journal
Does Fallback Affect a Permanent Journal?
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
Some Great Advice for Maintaining the Permanent Journals
Recovery Using the Permanent Journals
The Journals View in DBC (DBC.Journals)
Archive Recovery Console (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
Select TOP Rows in a Rank Order
Getting a Sample Percentage of rows
Find Information about a Database
Find information about a Table
Performing a Join using ANSI Syntax
Using Date, Time and Timestamp
Using the System Calendar in a Query
Using an Access Lock in your SQL
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 Space Skew in Tables in a Database