Introduction to Teradata Warehouse. Teradata Database Release V2R5.1 Teradata Warehouse Release 7.1

Introduction to Teradata Warehouse
Preface
	Supported Software Release
	Changes to This Book
	About This Book
	List of Acronyms
	Technical Information on the Web
Contents
Chapter 1: Teradata Warehouse
	What Is a Data Warehouse
	The Next Step for the Data Warehouse
		Strategic Queries
		Tactical Queries
		Teradata Warehouse
Section 1:
Teradata Warehouse Overview
Chapter 2: Teradata Warehouse Overview
	What Is the Teradata Database
		Attachment Methods
		How to Communicate with the Teradata Database Using SQL
	Purpose in Development
	Shared Information Architecture
	Teradata Database Server Software
	Parallel Upgrade Tool
	What Are Teradata Tools and Utilities
	For More Information
Chapter 3: Teradata Database Architecture
	SMP and MPP Machines
		The BYNET
		Boardless BYNET
	Disk Arrays
		Logical Units
		Pdisks and Vdisks
	Cliques
	Hot Standby Nodes
	Virtual Processors
		Parsing Engine
		Access Module Processor
		AMP Clusters
	Parsing Engine Request Processing
		The Dispatcher
		The AMPs
		Example: SQL Statement
	Parallel Database Extensions
		Trusted Parallel Applications
		PDE and MPP Systems
		Start and Stop PDE
	The Teradata File System
		Cylinder Read
		Disk I/O Integrity Checking
	Workstation Types and Available Platforms
		System Console
		Administration Workstation
	Teradata Database Window
		How the Database Window Communicates with Teradata Database
		Running DBW
	For More Information
Chapter 4: International Language Support
	Character Set Overview
		What Is a Repertoire
		Character Representation
	External and Internal Character Sets
		Character Data Translation
		What Teradata Database Supports
	Teradata Database Character Data Storage
		Internal Server Character Sets
		User Data
		System Dictionary Data
	Language Support Modes
		Default Character Set for User Data
		Character Set for System Dictionary Data
		Character Set for Dictionary Data Other Than Object Names
	Standard Language Support Mode
		LATIN Character Set
		Compatible Languages
	Japanese Language Support Mode
		Advantages of Storing System Dictionary Data Using KANJI1
		Advantages of Storing User Data Using UNICODE
	Extended Support
	For More Information
Section 2:
The Teradata Database Structure
Chapter 5: Structured Query Language (SQL)
	Why SQL
	What is SQL
		Data Definition Language
		Data Control Language
		Data Manipulation
	SQL Data Types
		Teradata and ANSI-Compliant Data Types
		Data Type Attributes
	Statement Punctuation
	SQL Statements and Requests
	The SELECT Statement
		SELECT Statement and Set Operators
		SELECT Statement and Joins
	SQL Functions
		Scalar Functions
		Aggregate Functions
		Ordered Analytical Functions
	User-Defined Functions
		Creating User-Defined Functions
		SQL Statements Related to Functions
	Cursors
	For More Information
Chapter 6: Application Development
	Types of SQL Development
		Explicit SQL Development
		Implicit SQL Development
	Embedded SQL Applications
		What Is Embedded SQL
		How Does an Application Program Use Embedded SQL
		Supported Languages and Platforms
	Macros as SQL Applications
		SQL Used to Create a Macro
		Macro Usage
		SQL Used to Modify a Macro
		SQL Used to Delete a Macro
	Teradata Stored Procedures as SQL Applications
		SQL Used to Create Stored Procedures
		Stored Procedure Example
		SQL Used to Execute a Stored Procedures
	The EXPLAIN Statement
		How Is EXPLAIN Useful
		EXPLAIN With Simple Join Index Example
	Third-Party Development
		TS/API Products
		Compatible Third-Party Software Products
		Performance Monitor/Application Programming Interface
	For More Information
Chapter 7: The Teradata Database Model
	What is a Relational Model
	What is a Relational Database
		Set Theory and Relational Database Terminology
	Tables, Rows, and Columns
		Table Constraints
		Permanent and Temporary Tables
		Global Temporary Tables
		Volatile Temporary Tables
		Derived Tables
		Rows and Columns
	For More Information
Chapter 8: Data Distribution and Access Methods
	Teradata Database Indexes
	Primary Indexes
		Primary Index Characteristics
		How Are Primary Keys and Primary Indexes Related
	Partitioned Primary Indexes
		Non-partitioned Primary Indexes
		How Do Partitioned and Non-Partitioned Primary Indexes Compare
	Secondary Indexes
		Secondary Index Subtables
		How Do Primary and Secondary Indexes Compare
	Join Indexes
		Single-Table Join Indexes
		Multi-Table Join Indexes
		Aggregate Join Indexes
		Sparse Join Indexes
	Hash Indexes
	Index Specification
		Creating Indexes
		Strengths and Weaknesses of Various Types of Indexes
	Hashing
	Identity Column
	For More Information
Chapter 9: Data Dictionary
	What is the Data Dictionary
		Data Dictionary Content
		What Is in a Data Dictionary Table
	Teradata Database Data Dictionary Views
		What Is in a View
		Why Use Views
	Who Uses Data Dictionary Views
	SQL Access to the Data Dictionary
	For More Information
Chapter 10: Teradata Meta Data Services Services
	What Is Metadata
	Types of Metadata
	Teradata Meta Data Services
		Creating the Teradata Meta Data Repository
		Connecting to the Teradata Meta Data Repository
	For More Information
Chapter 11: Other Database Objects
	What Are Views
		SQL Statements Related to Views
		Restrictions on Using Views
	What Are Teradata Stored Procedures
		Why Use Stored Procedures
		Elements of a Teradata Stored Procedure
	What Are Macros
		SQL Statements Related to Macros
		Single-User and Multi-User Macros
		Macro Processing
	What Are Triggers
		Types of Triggers
		When Do Triggers Fire
		ANSI-Specified Order
		Trigger Functions
		SQL Statements Related to Triggers
		Elements of a Trigger
		Restrictions on Triggers
	For More Information
Section 3:
Teradata Database System Operation
Chapter 12: Normalization and Referential Integrity
	Normalization
		Normal Forms
		Relational Database Terminology
	First, Second, and Third Normal Forms
		First Normal Form
		Second Normal Form
		Third Normal Form
		Advantages of Normalization
	Boyce-Codd Normal Form and Higher Normal Forms
		Boyce-Codd Normal Form
		Fourth Normal Form
		Fifth Normal Form
	Referential Integrity
		Referential Integrity in the Teradata Database
		Referential Integrity Terminology
		Referencing (Child) Table
		Referenced (Parent) Table
		Why Is Referential Integrity Important
	Referential Integrity Constraints
		Referential Constraints
		Batch Referential Integrity
		Rules for Referential Integrity Constraints
		Referential Constraint Checks
	For More Information
Chapter 13: Data Communication Between Client and Teradata Database
	Attachment Methods
	CLIv2 for Channel-Attached Systems
		What CLIv2 for Channel-Attached Clients Does
		Teradata Director Program
		Server
	CLIv2 for Network-Attached Systems
		What CLIv2 for Network-Attached Clients Does
		Micro Teradata Director Program
		Micro Operating System Interface
	Other Types of Data Communications
		WinCLI
		ODBC
		JDBC
	For More Information
Chapter 14: Reliability
	Software Fault Tolerance
		Vproc Migration
		Fallback Tables
		AMP Clusters
		One-Cluster Configuration
		Smaller Cluster Configuration
		Journaling
		Teradata Archive/Recovery
		Table Rebuild Utility
	Hardware Fault Tolerance
	For More Information
Section 4:
Management and Monitoring
Chapter 15: Concurrency Control and Transaction Recovery
	What is Concurrency Control
	What is Recovery
	Concept of a Transaction
		Definition of a Transaction
		Definition of Serializability
		Transaction Semantics
	ANSI Mode Transactions
		BEGIN TRANSACTION/END TRANSACTION Statements
		Roll Back an ANSI Transaction
	Teradata Mode Transactions
		BEGIN TRANSACTION/END TRANSACTION Statements
		Roll Back a Teradata Mode Transaction
	Concept of a Lock
		Overview of Teradata Database Locking
		Why Do Database Management Systems Require Locking
		Lock Levels
		Levels of Locks Types
		Automatic Database Lock Levels
		Deadlocks and Deadlock Resolution
	Host Utility Locks
		HUT Lock Types
		HUT Lock Characteristics
	System and Media Recovery
		System Restarts
		Transaction Recovery
		Down AMP Recovery
	Two-Phase Commit Protocol
		Definition of Participant
		Definition of Coordinator
	For More Information
Chapter 16: Database Management and Analysis Tools
	Teradata Tools and Utilities - Archive Utilities
		Teradata Archive/Recovery Utility
		Open Teradata Backup
	Teradata Tools and Utilities - Data Load and Export Utilities
		Teradata MultiLoad
		Teradata FastLoad
		Teradata Parallel Data Pump
		Teradata FastExport Utility
	Database Management Tools
		Teradata Database - Active Session and Configuration
	System Resource Management
		Teradata Database - Ferret Utility
		Teradata Database - Priority Scheduler Utility
		Teradata Tools and Utilities - Teradata Statistics Wizard
		Teradata Database - Teradata Dynamic Query Manager
	Teradata Database - Teradata MultiTool
	Database Query Analysis Tools
	Teradata Tools and Utilities - Teradata Index Wizard
		What Can the Teradata Index Wizard Do
		Demographics
	Teradata Database - Query Capture Facility
		QCD Schema Improvement
		Teradata Index Wizard Support
	Teradata Tools and Utilities - Teradata Visual Explain
	Teradata Database - Database Query Log
	Teradata Database - Target-Level Emulation
	Teradata Tools and Utilities - Teradata System Emulation Tool
	Teradata Database - Database Object Use Count
	Query Facilities
	Teradata Tools and Utilities - Basic Teradata Query Utility
		BTEQ Support
		BTEQ Communication
	Teradata Tools and Utilities - Teradata SQL Assistant
	Teradata Tools and Utilities - Preprocessor2
	For More Information
Chapter 17: Security and Integrity
	Security and Integrity
	System Integrity
	System Security
	Resource Access Control
		User Identifiers
		Client Identifiers
		Logon Policies
		TDP Security
		Single Sign On
	Encryption
		Network Data Encryption
		Logon Encryption and the Teradata Gateway
	Security Features
		Password Attributes
		User-Level Password Attributes
		DBC.DBase Table
	SQL Used to Control Logon
		Data Access Control
		Ownership and Implicit Rights
		System Views for Access Information
	Security Policies and Physical Access Control
		Principle Considerations of a Security Policy
		Key Implementation Elements of a Security Policy
	Auditing and Accountability
	For More Information
Chapter 18: System Administration
	Space Allocation for Databases and Users
		Databases and Users
		How to Create a Finance and Administration Database
		How to Create Databases
		How to Create Users
	Roles and Profiles for Users
	Accounting
		Session Management
		Establishing a Session
		Logon Operands
		Session Requests
		Account String Expansion
		Account Performance Groups
	Maintenance Utilities
	For More Information
Chapter 19: System Monitoring
	Teradata Manager
	System and Configuration Status
	Resource Usage Monitoring
		Resource Usage Tables and Views
		Resource Usage Data Categories
		Resource Usage Data Handling
		Resource Usage Macros
		How to Control Collection and Logging of Resource Usage Data
		Summary Mode
	Performance Monitoring
		The TDPTMON
		System Management Facility
		The Performance Monitor/Application Interface
	For More Information
Index