Chapter 1 — The Rules of Data Warehousing
Teradata: Brilliant by Design
The Teradata Parallel Architecture
A Logical View of the Teradata Architecture
The Parsing Engine (PE)
The Parsing Engine in Detail
The Request and Respond Parcel
The Parsing Engine Knows All
The Access Module Processors (AMPs)
AMP Worker Tasks (AWT)
The BYNET
A Visual for Data Layout
Teradata is a shared nothing Architecture
Teradata has Linear Scalability
How Teradata handles data access
The PE uses Statistics to come up with the Plan
When there are NO Statistics Collected on a Table
Teradata Cabinets, Nodes, VPROCs, and Disks
A Node and its Memory Allocations
Each PE has a Plan Library called RTS Cache
LAN Connection for Network Attached Clients
Mainframe Connection to Teradata
Sessions and Session Pools
Teradata Configuration Utilities
Config and Reconfig
Chapter 2 — Teradata Space
How Permanent Space is Given
The Teradata Hierarchy
Reserving Permament Space for Spool
How Spool Space is Calculated
A Spool Space Example
PERM, SPOOL and TEMP Space
Space Allocation Review
DBC.DiskSpace[x] View
Using DBC.DiskSpace[x] View
DBC.TableSize[x] View
DBC.AllSpace[x] View
Comparing the Results
DBC.DatabaseSpace Table
Resetting Peak Values
MP Disks have Cylinders and Data Blocks
Full Cylinder Read
Table Header
Each Table is given a Table ID
How Data Blocks are Dynamically Built
Data Blocks
How Teradata Finds a Row of Data
The Master Index
The Cylinder Index
Cylinder Index Changes
How Teradata Writes to an AMP
Writing to Data Blocks of Equal Length
When a Data Block is Not Big Enough for a Write
How Teradata Allocates Blocks
Block and Row Definitions
Large Row versus Oversized Row
Defragmentation
When a Cylinder becomes Full
Another quiz on Perm and Spool Space
Chapter 3 — DBC Data Dictionary Tables
Data Dictionary Directory
DD/D
The Parsing Engine has Data Dictionary Cache
Master User DBC
Data Dictionary Directory Tables
System Views
Configure Dictionary Views
Restricted Views
Restricted Views
Accessing Restricted Views
Selecting Information about Created Objects
DBC View Definitions
Children View
Databases View
Users View
Tables View
Columns View
Indices View
IndexConstraints View
AllTempTables View
Triggers View
Timestamps in the Data Dictionary
Finding Table Names Using the LIKE Command
Finding Table Names in a Particular Database
Using the Keyword USER on DBC Views
Using DBC.AMPUsage
Using DBC.TableSize
Keeping Track of Logons and Logoffs
Chapter 4 — Access Rights, Roles and Profiles
Automatic, Implicit, and Explicit Rights
Automatic, Implicit, and Explicit Rights
Tools for Finding Access Rights
The GRANT Statement
The GRANT Command
The REVOKE Statement
The REVOKE Command
GRANT and REVOKE Commands Using the Role Format
Roles
Roles and Profiles Defined
Advantages of using Roles
Access Rights without the use of Roles
Access Rights with the use of a Role
Utilizing Roles
Creating Roles
Setting and MODIFYING Roles
Nesting Roles
DBC.RoleInfo and DBC.RoleInfo[X]
AllRoleRights and UserRoleRights Views
GRANT PUBLIC Implementation Change
Profiles
Impact of Profiles on Users
CREATING PROFILES
MODIFYING PROFILES
Password Attributes
DBC.ProfileInfo and DBC.ProfileInfoX
Chapter 5 — DBS Control
DBS Control Record - General Fields
DBS Control Record – V2R6.1 General Fields
CHECKSUM LEVELS
Chapter 6 — Query Analysis and Tools
Access Logging
Setting Up Access Logging
BEGIN LOGGING Statement
END LOGGING Statment
DBC.AccLogRules View
DBC.AccessLog View
Teradata Administrator and Access Logging
Using Teradata Administrator to Start/Stop Access Logging
Using Teradata Administrator to Start/Stop Access Logging Sessions
Database Query Log (DBQL)
Security and Permisisons
The Default Row
DBC.DBQLogTbl
The BEGIN QUERY LOGGING Statement
Using the BEGIN QUERY LOGGING statement
BEGIN QUERY LOGGING WITH Options
Using the WITH EXPLAIN Option
Using the WITH OBJECTS Option
Using the WITH SQL Option
Using the WITH STEPINFO Option
Using the WITH ALL Option
BEGIN QUERY LOGGING LIMIT Options
LIMIT SQLTEXT
Using the LIMIT SQL Option
LIMIT SUMMARY
Using the LIMIT SUMARY Option
LIMIT THRESHOLD
END QUERY LOGGING Statement
DBQL Tables and Views
Defining Rules for DBQL
DBC.DBQLRules View
Exercise: Recreating Query Logging Statements
Using Teradata Administrator to Recreate/Stop an Active Query Logging Session
Statistics Wizard
Index Wizard
TSET
EXPLAIN - Full Table Scan
EXPLAIN - UNIQUE PRIMARY INDEX (UPI)
EXPLAIN - NON-UNIQUE PRIMARY INDEX (NUPI)
EXPLAIN – UNIQUE SECONDARY INDEX (USI)
EXPLAIN – NON-UNIQUE SECONDARY INDEX
EXPLAIN – What is a Psuedo Lock?
EXPLAIN – Confidence Levels
EXPLAIN – Execute the following steps in Parallel
EXPLAIN – Redistributed by Hash Code
EXPLAIN – Duplicated on All AMPs
EXPLAIN – JOIN INDEX
EXPLAIN – BMSMS Bit Mapping
EXPLAIN – PPI Tables and Partitions
EXPLAIN – Group_AMPs, SORT, Eliminating Duplicate Rows, and No Residual Conditions
EXPLAIN – Last Use, End Transaction, and Computed Globally
Teradata Visual Explain Utility
Chapter 7 — Teradata Protection Features
How the Transient Journal Works
FALLBACK Protection
How Fallback Works
Fallback Clusters
Fallback Exercises with Clusters
Fallback – Performance Vs Protection
Fallback – Performance Vs Protection (Answers)
Down AMP Recovery Journal (DARJ)
Redundant Array of Independent Disks (RAID)
Cliques
Cliques — A two node example
Cliques — A four node example
Permanent Journal Rules
Some Permanent Journal Possibilities
Creating a Permanent Journal
Assigning Tables to a Permanent Journal
Create Table Examples with Permanent Journals
Where do Before Journals keep their Rows?
Where do After Journals keep their Rows?
Each Permanent Journal is made up of 3 Areas
Restoring Data from a Permanent Journal
DBC Views that show the Permanent Journals
Chapter 8 — Starting and Stopping Teradata
Restarts of the Teradata Database
Automatic Restarts
Hardware Failures
Critical Database Errors
Restarting in UNIX
Restarting the DB Window (UNIX Only)
Startup and Recovery
Transaction Recovery
Performing Online and Offline Catch-up
Chapter 9 — Databases, Users and Accounting
Creating a User
Modifying and Deleting a USER
Teradata Administrator – Tools Menu
Teradata Administrator – Tools Menu
Specifying Account Priorities
Using Account IDs with Logon
Dynamically Changing an Account ID
Account String Expansion
System Accounting
System Accounting Views
DBC.AcountInfo[x] View
DBC.AMPUsage View
Chapter 10 — Views and MACROS
Creating Views
Creating Simple VIEWs and Views that Join Tables
How to Change a VIEW Using REPLACE
How to Drop a VIEW
View Aggregation
Using “Locking for Access” in Views
You can UPDATE Tables through Views
Restricting UPDATE rows WITH CHECK OPTION
Creating Macros
Creating a MACRO
Macros that Use Parameters
Changing a MACRO Using REPLACE
How to Execute a MACRO
How to Drop a MACRO
Chapter 11 — System Access Control Levels
Teradata Password Encryption
TDP Exits
Host Logon Processing
GRANT/REVOKE LOGON Statement
Session Related Views
DBC.SessionInfo View
DBC.LogOnOff View
DBC.LogonRules View
Data Access Information Views
AccLogRules Views
AccessLog Views
Chapter 12 — Priority Scheduler
Priority Scheduler Partition Hierarchy
Priority Scheduler Hierarchy Definitions
Allocation Groups
Resource Partition 0 User Access
Scheduling Policies
Performance Periods
CPU Usage Limits with Priority Scheduler
TDQM
Chapter 13 — Teradata Manager
Teradata Manager Applications
Teradata Dashboard — New Feature
Teradata Manager Service
Teradata Manager Service
Starting Teradata Manager
Enable Data Collection
Chapter 14 — Monitoring Tools - Performance Monitor - Overview
Performance Monitor
Getting PMON Started
PMON Main Window
PMON Sessions Screen
Viewing Session Status
Session Status Report Descriptions
Teradata Administrator (WinDDI)
The Alert Facility and Viewer
The Alert Viewer
Locking Logger
Locking Logger Functions:
Xperfstate
Teradata Manager Remote Console
Chapter 15 — Teradata Remote Console Utilities
Executing Utilities Through Various Locations
SMP and Database Window Utilities
CNSTool and CNSTerm
Teradata MultiTool for Windows 2000
Starting the Database Window (DBW)
Database Window via MultiTool
GET CONFIG Command
Query Configuration Utility
QRYCONFIG
QRYSESSN
RCVMANAGER (Recovery Manager)
RCVMANAGER Commands
Cancel a Rollback
SHOWLOCKS
VPROCMANAGER
FERRET UTILITY (File Reconfiguration Tool)
Defragment Commands
SHOWSPACE
SHOWBLOCKS
PACKDISK
Freeing Space with Defragment and Packdisk
SCANDISK
Checktable Utility
Table Rebuild
Chapter 16 — Resource Usage Data
ResUsage Collection and Logging
Filling the ResUsage Tables
Specify Tables with xctl or ctl Utility
Resource Usage Logging
Setting Resource Logging - DBW
Collection Costs
Resource Usage Tables
Resource Usage Views
ResNode Macros
RSSMon
Teradata System Emulation Tool
Chapter 17 — Archiving Fundamentals
The ARC Utility
The Two Stages of Archive and Recovery
Using ARC
ARC Statements
RESTORE Vs. Fastload
Restart Log
Restarting ARC
Invoking Archive
Invoking Archive - Continued
Session Control
Multiple Sessions
The ARCHIVE Statement
Archiving – Command Options
Types of Archives
Archiving Selected Partitions of a PPI Table
ANALYZE Statement
Specific AMP Archive
Cluster Level Archive
Options for Indexes
Group Read Lock Option
Archiving DBC
Chapter 18 — Restore Operations
Restore Related Statements
The Restore Statement
Restoring Examples
RESTORE Example and Output
Restoring Data
The RESTORE Statement
EXCLUDE TABLE:
Restoring Selected Partitions of PPI Table
COPY Statement
Copying Partitioned Data
Copying Objects
BUILD Statement
The RELEASE LOCK Statement
REVALIDATE REFERENCES
Revalidate References Output<insert diagram>
Recovery Control Data Dictionary Views