- 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
- 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
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.