About Thomas Coffing III
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
- 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
About Thomas Coffing III
Thomas Coffing III is a Teradata Certified Master who has written several books on all aspects of Teradata. He is a world-renowned professional database administrator and application developer, and has been working in the data warehousing industry since 2001. His expertise also includes Oracle, Netezza, SQL Server, Neoview, DATAllegro, and DB2 systems.
Thomas has been a key developer of the Nexus Query Chameleon, considered by many to be the best query tool in the market today.
Thomas has worked for more than 50 fortune 500 companies in regards to EDW training and application development, and has trained some of the brightest database administrators in the industry. His previous clients include General Electric, Charles Schwab, Liberty Mutual, Meijer, Caterpillar, American Airlines, and JP Morgan Chase. Thomas also headlined the 2007 Neoview conference for HP in Phuket, Thailand.
Thomas is a Cincinnati native, husband, father, and a musician who plays the bass guitar and piano.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.