Contents
Chapter 1 – Teradata Basics and Data Warehouse Concepts
Passing Your Teradata Certification Tests
The Basics of a Single Computer
Teradata Parallel Processes Data
All Teradata Tables are spread across ALL AMPS
Teradata Systems can Add AMPs for Linear Scalability
Understand that Teradata can scale to incredible size
AMPs and Parsing Engines (PEs) 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
The Boardless BYNET and the Physical BYNET
This is the Visual You Want to Know in order to Understand Teradata
Features That Are Unique To Teradata
The Three Teradata V14 Platforms and Their Operating System
The Five Stages of Data Warehouse Evolution
The Evolution (Four Stages) of Data Processing
A Distributed Architecture vs. a Centralized Architecture
The Eight Types of Objects in Teradata
Relational Models vs. Enterprise Models
The Two Methods of Processing Rows of Data
LAN Connections for Network Attached Client
Mainframe Connections to Teradata
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
Service Level Goals and Service Level Agreements
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
An EXPLAIN that shows a Full Table Scan
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 3 – Hashing of the Primary Index
The Hash Map Determines which AMP will own the Row
The Hash Map Determines which AMP will own the Row
Placing rows on the AMP Continued
A Review of the Hashing Process
Non-Unique Primary Indexes have Skewed Data
The Row Hash and Uniqueness Value make up the Row-ID
A Row-ID Example for a Unique Primary Index
A Row-ID Example for a Non-Unique Primary Index (NUPI)
Two Reasons why each AMP Sorts their rows by the Row-ID
AMPs sort their rows by Row-ID to Group like Data
AMPs sort their rows by Row-ID to do a Binary Search
Table CREATE Examples with four different Primary Indexes
Null Values all Hash to the Same AMP
A Unique Primary Index (UPI) Example
A Non-Unique Primary Index (NUPI) Example
A Multi-Column Primary Index Example
A No Primary Index (NoPI) Example
Chapter 4 - Partition Primary Index (PPI) Tables
The Concept behind Partitioning a Table
Creating a PPI Table with Simple Partitioning
A Visual Display of Simple Partitioning
An SQL Example that explains Simple Partitioning
Creating a PPI Table with RANGE_N Partitioning per Month
A Visual of One Year of Data with Range_N per Month
An SQL Example explaining Range_N Partitioning per Month
A Partition # and Row-ID = Row Key
An AMP Stores its Rows Sorted in only Two Different Ways
Creating a PPI Table with RANGE_N Partitioning per Day
A Visual of Range_N Partitioning Per Day
An SQL Example that explains Range_N Partitioning per Day
Creating a PPI Table with CASE_N
A Visual of Case_N Partitioning
An SQL Example that explains CASE_N Partitioning
Number of PPI Partitions Allowed
How many partitions do you see?
NO CASE and UNKNOWN Partitions Together
A Visual of Case_N Partitioning
Multi-Level Partitioning Combining Range_N and Case_N
A Visual of Multi-Level Partitioning
The SQL on a Multi-Level Partitioned Primary Index
Altering a PPI Table to Add or Drop Partitions
Deleting a Partition and saving its contents
Creating a Unique Secondary Index (USI)
What is in a Unique Secondary Index (USI) Subtable?
A Unique Secondary Index (USI) Subtable is hashed
A Primary Index Query vs. a Unique Secondary Index Query
How the Parsing Engine uses the USI Subtable
Creating a Non-Unique Secondary Index (NUSI)
What is in a Unique Secondary Index (USI) Subtable?
Non- Unique Secondary Index (NUSI) Subtable is AMP Local
How the Parsing Engine uses the NUSI Subtable
Quiz – Answer the Tough USI Questions
Answer to Quiz – Answer the Tough USI Questions
A Picture with a Base Table, USI, and NUSI Subtable
Quiz – Tough Questions on the USI and NUSI Subtables
Answer – Tough Questions on the USI and NUSI Subtables
A Query Using an USI Only Moves Two Blocks
A Query Using A NUSI Always Uses All AMPs
Two Non-Unique Secondary Indexes (NUSI) on a Table
How the PE Decides on the NUSI or the Full Table Scan
Primary Index vs. Secondary Index
What are the Big Four Tactical Queries?
Columnar Tables have NO Primary Index
NoPI Tables Spread rows across all-AMPs Evenly
NoPI Tables used as Staging Tables for Data Loads
What does a Columnar Table look like?
Comparing Normal Table vs. Columnar Tables
Example of Columnar CREATE Statement
Columnar can move just One Container to Memory
Containers on AMPs match up perfectly to rebuild a Row
Indexes can be used on Columns (Containers)
Indexes can be used on Columns (Containers)
Single-Column vs. Multi-Column Containers
Comparing Normal Table vs. Columnar Tables
Columnar Row Hybrid CREATE Statement
Columnar Row Hybrid Query Example
Review of Row-Based Partition Primary Index (PPI)
Visual of Row Partitioning (PPI Tables) by Month
CREATE Statement for both Row and Column Partition
Visual of Row Partitioning (PPI Tables) and Columnar
How to Load into a Columnar Table
Auto Compress in Columnar Tables
Auto Compress Techniques in Columnar Tables
When and When NOT to use Columnar Tables
When your System Arrives, there is only User named DBC
First Assignment is to create another User just under DBC
Perm Space is for Permanent Tables
Spool Space is work space that builds a User’s Answer Sets
Spool Space is in an AMP’s Memory and on its Disk
Users are Assigned Spool Space Limits
What is the Purpose of Spool Limits?
Why did my query Abort and say “Out of Spool”?
How can Skewed Data cause me to run “Out of Spool”?
Why did my Join cause me to run “Out of Spool”?
What does my system look like when it first arrives?
DBC owns all the PERM Space in the system on day one
DBC’s First Assignment is Spool Space
DBC’s 2nd Assignment is to CREATE Users and Databases
The Teradata Hierarchy Continues
Differences between PERM and SPOOL
What are Similarities between a DATABASE and a USER?
What is the Difference between a DATABASE and a USER?
Objects that take up PERM Space
A Series of Quizzes on Adding and Subtracting Space
Chapter 8 – The User Environment
DBC is the only user when the system first arrives
DBC will Create Databases and Give them Space
DBC will create some initial Users
A Typical Teradata Environment
What are Similarities between a DATABASE and a USER?
Create a Role and then Assign that Role Its Access Rights
Create a User and Assign them a Default Role
There are Three Types of Access Rights
Description of the Three Types of Access Rights
ProfileInfoVX, RoleMembers, RoleInfo and UserRoleRights
Accounts and their Associated Priorities
Creating a User with Multiple Account Priorities
Account String Expansion (ASE)
Creating a Multi-Table Join Index
Outer Join Multi-Table Join Index
Visual of a Left Outer Join Index
Compressed Multi-Table Join Index
A Visual of a Compressed Multi-Table Join Index
Creating a Single-Table Join Index
Conceptual of a Single Table Join Index on an AMP
Single Table Join Index Great For LIKE Clause
Single Table Join Index with Value Ordered NUSI
Compressed Single-Table Join Index
New Aggregate Join Index (Teradata V14.10)
A Global Multi-Table Join Index
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
Exceptions to the ORDER BY Rule inside a View
Views sometimes CREATED for Formatting or Row Security
CREATING Views for Complex SQL such as Joins
WHY certain columns need Aliasing in a View
Updating Data in a Table through a View
Maintenance Restrictions on a Table through a View
CREATING and EXECUTING a Simple Macro
Multiple SQL Statements inside a Macro
Passing an INPUT Parameter to a Macro
Troubleshooting a Macro with INPUT Parameters
Troubleshooting a Macro with INPUT Parameters
An UPDATE Macro with Two Input Parameters
Executing a Macro with Named (Not Positional) Parameters
Chapter 13 – Data Modeling Techniques
The Four Stages of Modeling for Teradata
The Logical Model can be loaded inside Nexus
Primary Keys Compared to Foreign Keys
First, Second and Third Normal Form
Quiz – Choose that Normalization Technique
Answer to Quiz – Choose that Normalization Technique
Quiz – What Normalization Is It Now?
Answer to Quiz – What Normalization Is It Now?
The Employee_Table and Department_Table Can Be Joined
The Employee_Table and Department_Table Join SQL
Chapter 14 – Things to Consider
Multi-Statement Transaction in BTEQ
Teradata Mode Default is a Set Table
An Example of a Duplicate Row Error
Creating a Set Table with a Unique Primary Index
Creating a Set Table with a Unique Secondary Index
ANSI mode defaults to a Multiset Table
Using a Volatile Table to Get Rid of Duplicate Rows
You Populate a Volatile Table with an INSERT/ SELECT
The Three Steps to Use a Volatile Table
Why Would You Use the ON COMMIT DELETE ROWS?
The HELP Volatile Table Command Shows your Volatiles
A Volatile Table with a Primary Index
The Joining of Two Tables Using a Volatile Table
Creating a Secondary Index on a Volatile Table
You Can Collect Statistics on Volatile Tables
The New Teradata V14 Way to Collect Statistics
CREATING A Global Temporary Table
Using a Simple Global Temporary Table
Two Brilliant Techniques for Global Temporary Tables
The Joining of Two Tables Using a Global Temporary Table
Chapter 15 – 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
Clearing Out the DBC.AMPUsage Data
User Defined Compression Techniques
Lossy and Lossless Compression
Temperature-Based Block Level Compression
The Teradata 2690 has Hardware Compression
Row-Level Compression Utilizing a Compressed Join Index
A Visual of a Compressed Multi-Table Join Index
Important Information about Multi-Value Compression (MVC)
Presence Bytes are also used for Multi-Value Compression
A MVC Compression Example that Compresses Two Values
A MVC Compression Example that Compresses Three Values
Quiz – Name that MVC Compression Value
The Next Important Concept in MVC Compression
Quiz – Can you Fill in the MVC Compression Values?
Answer – Can you Fill in the MVC Compression Values?
The Multi-Value Compression (MVC) Cost vs. the Savings
The Cost List of MVC Compression
Auto Compress in Columnar Tables
Auto Compress Techniques in Columnar Tables
Columnar Tables Automatically Compress unless NO AUTO COMPRESS
Chapter 17 – 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
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
Database Administration (DBA) on Teradata
The First Time you Login to Viewpoint
The Add Content Menu for Monitoring
The Add Content Menu for Tools
The Add Content Menu for Trend Reporting
How the Page looks after you add your first Content
The Fundamentals of Viewpoint Pages and Portals
Adding Multiple Portals to a Single Page
All Portals to Their Individual Tab
Three Levels of Workload Management
Pre-execution, Query Execution, and Post-execution
Query Management compared to Workload Management
The Active Workload Management Concept
What is the Secret Sauce for Query Management?
There are Four Types of Query Rules
Common Sense Examples of Filters and Throttles
When Creating Workloads the “WHO” is your Foundation
After the “WHO” comes the “WHERE”
After the “WHO” and the “WHERE” comes the “WHAT”
Pre-execution, Query Execution, and Post-execution
The Concept of a Resource Partition
The Clever Idea behind Resource Partitioning
The Brilliant Idea behind Resource Partitioning
The Concept of Resource Partitions and Weights?
The Concept of a Workload in a Resource Partition
How to Configure Priority Scheduler
The Three Areas of the Workload Designer
How the Area of Workload Designer are Used
Chapter 20 - Teradata Load Utilities Introduction
BTEQ – Batch Teradata Query Tool
How to Logon to BTEQ in Interactive Mode
Running Queries in BTEQ in Interactive Mode
BTEQ Commands vs BTEQ SQL Statements
How to Logon to BTEQ in a SCRIPT
Running Queries in BTEQ through a Batch Script
Running a BTEQ Batch Script through the Command Prompt
Running a BTEQ Batch Script through the Run Command
Using Nexus to Build Your BTEQ Scripts
Using Nexus to Build Your BTEQ Scripts
A Sample FastLoad Script Created by Nexus SmartScript
The Nexus SmartScript Easily Builds Your Utilities
The Nexus SmartScript FastLoad Builder
Create and Execute Your FastLoad Scripts with Nexus
MultiLoad has IMPORT and DELETE Tasks
A Sample MultiLoad Script Created by Nexus SmartScript
TPump is NOT a Block Level Utility and has No Limits
A Sample TPump Script Created by Nexus SmartScript
A Sample FastExport Script Created by Nexus SmartScript
FastExport by Default places Null Indicators in Output
A Sample FastExport Script Created by Nexus SmartScript
TPT Producers Create Streams and Consumers Write Them
The Four Major Operators of TPT
TPT can read from multiple source files in Parallel
TPT can have more Operators than Consumers
TPT Operators and their Equivalent Load Utility
Chapter 21 – Teradata Tools and Everything Active
Teradata Visual Explain Utility
The Active Workload Management Concept
Chapter 22 – Practice Test Questions to Build Your Confidence
Chapter 23 – Lessons with Tera-Tom Video Guide
Video 1 - Teradata Basics - The Architecture
Video 2 - Hashing the Primary Index
Video 3 - The Cold Hard Teradata Facts
Video 4 -Inside the Amps’ Disc
Video 5 - PPI (Partitioned Primary Index Tables)
Video 8 - How Teradata Joins Tables Together
Video 9- Protection Features (1/3)
Video 10- Protection Features (2/3)
Video 11- Protection Features (3/3)
Video 12- Collect Statistics (1 of 2)
Video 13- Collect Statistics (2 of 2)
Teradata SQL Video 1 - The Basics of SQL
Teradata SQL Video 2 – Building Your SQL Knowledge
Teradata SQL Video 3 - Aggregates
Teradata SQL Video 6 – Temporary Tables (Derived)
Teradata SQL Video 7 – Volatile and Global Temporary Tables
Teradata SQL Video 8 – Ordered Analytic Functions
Teradata SQL Video 9 – Advanced Ordered Analytics
Teradata SQL Video 10 - Subqueries
Teradata SQL Video 11 – Substring and Positioning
Teradata SQL Video 12 – Data Interrogation
Teradata SQL Video 14 - Macros
Teradata SQL Video 15 – Stored Procedures