TERADATA

2567 Satisfied Learners

Best Teradata Training in Pune India

Today all databases are failed for Big Data warehouse. Teradata is only database which
sustain in market because of it speed and linear scalability. Most of the global airlines
using teradara .good number of global financial organizations are using teradara. Most
of the global retailers use teradata

 Duration of Training: 8 weekends

Teradata Syllabus

Teradata Basics

The Teradata Architecture
The Teradata Architecture
The Parsing Engine
The AMPs
Born to be parallel
The BYNET
Watch the Tera-Tom Video on Architecture
Collecting Statistics
Parsing Engine uses Statistics for the Plan
Columns and Indexes to Collect Statistics On
A Scalable Architecture
VProcs
Nodes and MPP
Watch the Tera-Tom Video on How AMPS store and process rows in Parallel

1. Logical Modeling

Logical Modeling – Primary and Foreign Keys
A Normalized Data Warehouse
Normalization
Dimensional Modeling also called Star Schema 

2. The Primary Index

Physical Modeling – The Primary Index
Two Types of Primary Indexes (UPI or NUPI)
Unique Primary Index (UPI)
Non-Unique Primary Index (NUPI)
Multi-Column Primary Indexes
When do you define the Primary Index?
Watch the Tera-Tom Video on Primary Indexes

 

3. How Teradata Distributes And Retrieves Data
How Teradata Distributes and Retrieves Rows
Hashing the Primary Index Value
The Hash Map
Laying a Row onto the Proper AMP
Retrieving a Row by way of the Primary Index
Placing Non-Unique Primary Indexes (NUPI) Rows
Non-Unique Primary Indexes Continued
Placing (NUPI) Rows Continued
Retrieving (NUPI) Rows
Even Distribution with an UPI
Uneven Distribution with a NUPI
Unacceptable Distribution with a NUPI
Big Trouble – A Picture of a Full Table Scan
A Visual for Data Layout
Watch the Tera-Tom Video on Data Placement
Bonus Video – Tera-Tom Video on the Row-ID
Test your Teradata Primary Index Knowledge

 

4. Secondary Indexes
Secondary Indexes
The Secondary Index Subtable
USI – Always a Two-AMP Operation
USI Summary
Watch the Tera-Tom Video on Unique Secondary Indexes (USI)
NUSI Sub table is AMP-Local
NUSI Recap
Secondary Index Summary
Watch the Tera-Tom Video on Non-Unique Secondary Indexes (NUSI)

 

5. Partitioned Tables
A Table used for our Partitioning Example
Range Queries
Why we had to perform a Full Table Scan
A Partitioned Table
A Partitioned Table
One Year of Orders Partitioned
Fundamentals of Partitioning
Add the Partition to the Row-ID for the Row Key
Multi-Level Partitioning
Partitioning Rules
Watch the Tera-Tom Video on Partitioning

 

6. Space
The most Powerful USER
DBC owns all the Disk Space
DBC Example of 1000 GBs
DBC will first CREATE a USER or a DATABASE
Teradata is Hierarchical
Only two Objects can Receive PERM Space
PERM, SPOOL and TEMP Space
Spool is like a Speed Limit
All Space is calculated on a Per AMP Basis
Watch the Tera-Tom Video on Space

 

7. Join Indexes
Introduction to a Join Index
Join Index Fundamentals
A Multi-Table Join Index Example
Multi-Table Join Index Picture
Single-Table Join Index
Aggregate Join Index
Join Index Details to Remember
Watch the Tera-Tom Video on the Parody Teradata Query Search-Off

 8. Data Protection

Data Protection
Transaction Concept
Transient Journal
RAID 1 – Mirroring
Cliques
VProcs Migrate when a Node Fails
Cliques – An 8-Node Example
Hot Standby Nodes
Hot Standby Nodes in Action
FALLBACK Protection
How Fallback Works
Fallback Clusters Exercise
Fallback Clusters
The Six Rules of Fallback
Down AMP Recovery Journal (DARJ)
Permanent Journal
Permanent Journal Rules

 9. Locking

The Four Locks of Teradata
Teradata has 3 levels of Locking
Locking Modifiers – The Access Lock
Locks and their compatibility 

 
B) Course No :2 Teradata SQL
Syllabus

 

1.Fundamental Sql Using Select
Fundamental Structured Query Language (SQL)
Basic SELECT Command
WHERE Clause
Compound Comparisons (AND / OR)
Impact of NULL on Compound Comparisons
Using NOT in SQL Comparisons
Multiple Value Search (IN)
Using NOT IN
Using Quantifiers Versus IN
Multiple Value Range Search (BETWEEN)
Character String Search (LIKE)
Derived Columns
Creating a Column Alias Name
AS
NAMED
Naming conventions
Breaking Conventions
ORDER BY
TOP Rows Option
DISTINCT Function

2. On-Line Help And Show Commands
HELP commands
SET SESSION command
SHOW commands
EXPLAIN
Adding Comments
ANSI Comment
Teradata Comment
User Information Functions
ACCOUNT Function
DATABASE Function
SESSION Function

 

3. Data Conversions
Data Conversions
Data Types
CAST
Implied CAST
Formatted Data
Formatted Data for Day-Month-Year
Tricking the ODBC to Allow Formatted Data
TITLE Attribute for Data Columns
Transaction Modes
Case Sensitivity of Data
CASESPECIFIC
LOWER Function
UPPER Function

 

4. Aggregation
Aggregate Processing
Math Aggregates
The SUM Function
The AVG Function
The MIN Function
The MAX Function
The COUNT Function
Aggregates
Use of HAVING
Using the DISTINCT Function with Ao Performance Opportunities

 

5. Subquery Processing
Subquery
Using NOT IN
Using Quantifiers
Qualifying Table Names and Creating a Table Alias
Qualifying Column Names
Creating an Alias for a Table
Correlated Subquery Processing
Correlated Subquery To Find Duplicate Values
EXISTS

 

6. Join Processing
Join Processing
Original Join Syntax
Product Join
Newer ANSI Join Syntax
INNER JOIN
OUTER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN
Self Join
Alternative JOIN / ON Coding
Adding Residual Conditions to a Join
INNER JOIN
OUTER JOIN
OUTER JOIN Hints
Parallel Join Processing
Join Index Processing

 

7. Date And Time Processing
ANSI Standard DATE Reference
INTEGERDATE
ANSIDATE
DATEFORM
System Level Definition
User Level Definition
Session Level Declaration
DATE Processing
ADD_MONTHS
ANSI TIME
EXTRACT
Implied Extract of Day, Month and Year
ANSI TIMESTAMP
TIME ZONES
Setting TIME ZONES
Using TIME ZONES
Normalizing TIME ZONES
DATE and TIME Intervals
Using Intervals
INTERVAL Arithmetic with DATE and TIME
CAST Using Intervals
OVERLAPS
System Calendar

 

8. Character String Processing
Transforming Character Data
CHARACTERS Function
CHARACTER_LENGTH Function
OCTET_LENGTH Function
TRIM
SUBSTRING
SUBSTR
SUBSTRING and Numeric Data
POSITION
INDEX
SUBSTRING and POSITION or INDEX Used Together
Concatenation of Character Strings

 

9. Set Operators
Set Operators
Considerations for Using Set Operators
INTERSECT
UNION
EXCEPT
MINUS
Using Multiple Set Operators in a Single Request

 

10. Data Manipulation
Data Maintenance
Considerations for Data Maintenance
Safeguards
INSERT Command
Using Null for DEFAULT VALUES
INSERT / SELECT Command
Fast Path INSERT / SELECT
UPDATE Command
Fast Path UPDATE
DELETE Command
Fast Path DELETE
UPSERT
MERGE
ANSI Vs Teradata Transactions
Performance Issues with Data Maintenance
Impact of FALLBACK on Row Modification
Impact of PERMANENT JOURNAL Logging on Row Modification
Impact of Primary Index on Row Modification
Impact of Secondary Indices on Row Modification

 

11. Data Interrogation
Data Interrogation
NULLIFZERO
NULLIF
ZEROIFNULL
COALESCE
CASE
Flexible Comparisons within CASE
Comparison Operators within CASE
CASE for Horizontal Reporting
Nested CASE Expressions
CASE used with the other DML
Using CASE to avoid a join

 

12. View Processing
Views
Reasons to Use Views
Considerations for Creating Views
Creating and Using VIEWS
TOP Command allows ORDER BY in the View
Deleting Views
Modifying Views
Modifying Rows Using Views
DML Restrictions when using Views
INSERT using Views
UPDATE or DELETE using Views
WITH CHECK OPTION
Locking and Views

 

13. Data Definition Language
Creating Tables
Table Considerations
Maximum Columns per Table
Table Naming Conventions
CREATE TABLE
Column Data Types
Specifying the Database in a CREATE TABLE Statement
PRIMARY INDEX considerations
CREATE TABLE AS
Table Type Specifications of SET VS MULTISET
SET and MULTISET Tables
Protection Features
FALLBACK
Permanent Journal
BEFORE Journal
AFTER Journal
Internal Storage Options
DATABLOCKSIZE
FREESPACE PERCENTAGE
QUEUE Tables
Partitioned Primary Index (PPI)
SQL for Partitioned Primary Index (PPI)
Adding and Dropping Partitions
Column Attributes
Constraints
UNIQUE Constraint
CHECK Constraint
Referential Integrity (RI) Constraint
Defining Constraints at the Column level
Defining Constraints at the Table Level
Utilizing Default Values for a Table
Secondary Indices
CREATE TABLE to Copy an existing table
Altering a Table
Modifying the Primary Index Partitioning
Revalidating the Primary Index
Dropping a Table
Dropping a Table versus Deleting Rows
Renaming a Table
Creating Secondary via CREATE INDEX
Join Index
Collecting Statistics
Hashing Functions
HASHROW
HASHBUCKET
HASHAMP
HASHBAKAMP

 

14. Temporary Tables
Temporary Tables
Temporary Table Choices
Derived Tables
Derived Tables Using a Non-Recursive WITH
Derived Tables Using a Recursive WITH
Volatile Temporary Tables
Global Temporary Tables
GLOBAL Temporary Table Examples
General Practices for Temporary use Tables

3) Course No 3: TERADATA UTILITIES

 
1. BTEQ

Logging Onto Bteq Interactively
Logging Onto Bteq In A Script
Using Bteq To Submit Queries In Interactive Mode
The Brilliant Bteq With By Statement
Bteq Can Provide Grand Totals
The Four Types Of Bteq Exports
A Bteq Export Script
A Bteq Export Script In Report Mode
Output For Record Mode And Report Mode
Using Report Mode To Export A Csv File
Importing From A Csv File
Two Ways To Run A Bteq Script
Exporting Large Files In A Unix Mp-Ras System
Bteq Import Script
Importing A Comma Separated Csv File
Multiple Sessions In Bteq
Fast Path Inserts Using Bteq
Cool Commands In Bteq – Sidetitles
Using Bteq Conditional Logic With Errorcode
Bteq Commands
Sequence Control Commands
Format Control Commands

 

2. Fastload

Maximum amount of block utilities has changed!
Fastload has two phases
Acquisition phase 1
Fastload application phase 2
A sample fastload script
Referential integrity and load utility problems
Two ways to execute a fastload script
Building, executing or scheduling fastload with nexus
Building a fastload with nexus smartscript
The smartscript fastload builder
Executing fastload from the nexus chameleon
Output report from fastload
Recovering when fastload fails
Valid data types used in fastload
Fasload commands

3. Multiload
Maximum amount of block utilities has changed!
The five phases of multiload
A sample multiload script
Referential integrity and multiload problems
Supported input formats
A multiload that inserts and updates using
Two different input files
A multiload that inserts and updates using
Two different input files continued
A multiload that upserts
A multiload that upserts continued
A multiload that deletes (step 1 of 3)
A multiload that deletes (step 2 of 3)
A multiload that deletes (step 3 of 3)
Multiload imports won‘t work with nopi tables
Two easy ways to run a multiload
Building, executing or scheduling multiload with nexus
Building a multiload with nexus smartscript
The smartscript multiload builder
Executing your multiload script
The output report from multiload
Utility locks
Trouble shooting and restarting multiload
4. TPUMP
Limitations of tpump
A sample tpump script
Two easy ways to run a tpump
Building, executing or scheduling tpump with nexus
Building a tpump with nexus smartscript
The smartscript tpump builder
Executing your tpump script
Supported input formats
Tpump script with error treatment options
A tpump script that uses two input data files
A tpump upsert sample script
Fundamentals of the .begin statement
Pack
Rate
Serialize
Tpump commands and parameters
.Begin load parameters unique to tpump
Monitoring tpump
Handling errors in tpump using the error table
Common error codes and what they mean
Restarting tpump
Tpump and multiload comparison chart

5. Fastexport

Maximum amount of block utilities has changed!
How fastexport works
Fastexport supported operating systems
Fastexport
Fastexport enhancement – no spool
A fastexport in its simplest form
Fastexport script that joins two tables
Fastexport modes and formats
How indicators can hurt your fastload
How you can omit indicators in your fastexport
Create a fastload with indicators
A fastexport script using binary mode
Fastexport support and task commands
Task commands
Fastexport supported sql commands

 

 

Our Courses

Drop A Query