Cover image for Teradata Database Administration – The Fundamentals

Teradata Database Administration – The Fundamentals

Author Thomas Coffing , Tom Coffing
Release Date: 2013/09/01
ISBN: 9781940540160
Topic:
0%
23
Chapters
0-1
Hours read
0k
Total Words
    Start Reading Now    
       Add to Wishlist       
View table of contents

Book Description

This is the first of a series of 3 DBA books. The most important aspect of being a DBA is understanding the landscape as well as having strong fundamentals so you can do your job. That is what this book lays out. You'll understand how Teradata works, you'll understand the environment and the data base. You'll understand your roles and profiles. You are set to be an excellent DBA by reading this book.

Table of Contents

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