TERADATA DEV SYLLABUS

COMPLETE PRACTICAL and REAL-TIME ORIENTED TRAINING WITH PROJECTS

UNIQUE FEATURES OF VINAYTECHHOUSE?
  •           In detail subject with clear diagramatic explanation {Unique Style }.
  •           Full practical oriented classes with Real-time examples and situations.
  •           Certification based training and interview related practices.
  •           Exclusive project helps to under Teradata Project Environment .
  •          Various documents discussion (used in real-time) to work effectively.
TERADATA DEVELOPMENT SYLLABUS
  (TERADATA PART-1: DATABASE & SYSTEMS)
1)Teradata Warehouse and Competitiveness
v  Opportunities of Teradata in the enterprise.
v  What is Teradata-RDBMS/DWH & BI?
v  Teradata 15 & Other versions( 14,13.10,13,12)
v  Teradata History and Real time running versions.
v  Teradata View Point Features (Web based management tool) ***.
v  Descriptions on Golden Gate and Data Mover***
v  Teradata competitiveness’ and differences with other RDBMS
Discussion on Interview and Certification questions
2)Teradata Single ,Multi and Cloud System
v  Node Architecture(Shared Nothing)
v  SMP Architecture
v  MPP Architecture
v  Cloud Architecture (Teradata 14)
v  Discussion on real time no of AMPs and Nodes
Real time Project Configurations
3)Teradata Components and Explanation
v  PDE(Parallel Data Extension),
v  Vprocs (Virtual Processors)
v  TPA (Trusted Parallel Application)
v  PE(Parsing Engine)
v  AMP(Access Module Processing)
v  BYNETs (Board less, Board Oriented)
v  TDP (Teradata Directory Program)
v  CLI(Call Level Interface),
v  VSS (Virtual Storage System)***
Discussion on  Certification & Interview questions
Discussion on various organizations configurations.
4)Teradata Storage and Retrieval Architecture
Request Processing
v   a. Syntaxer b. Resolver
v  c. Security Module d. Optimizer
v  e. Step Generator f. GNC Apply g. Dispatcher
Hashing approach
v  Hash algorithm, Row Hash, Row ID
v  DSW, Hash Map , Hash Bucket, Reaching Vdisk

5) Teradata Indexes (Primary, Secondary)

        Primary Index
v  Unique Primary Index (UPI)
v  Non Unique Primary Index (NUPI)
v  Partitioned Primary Index (PPI) (Range_N, Case_N, Multi Valued PPI ***)
v  NOPI (No Primary Index)***
v  Full Uniform Distribution vs Skewed distribution.
v  Skewness and Skewfactor
Secondary Index
v  Unique Secondary Index (USI)
v  Non Unique Secondary Index(NUSI)
v  Value Ordered Secondary Index (VOSI)
v  Hash Index (HI)
v  Join Index (JI) and Types
Full Table Scan(FTS) - Execution process
Diagrammatic explanation, syntaxes and practical.
Performance improvement indexes and tuning them.
TERADATA PART-2:  TERADATA-SQL)

1) Teradata Server, Client and Pre-requisites

v  Server start, stop, pause in Linux & Windows
v  Client tools to operate [GUI, Wizard, CUI]
v  Teradata Sql-Assistant, BTEQ, Query Studio
v  Open Database Connectivity (ODBC), OLEDB
v  Working with Sql-Assistant, BTEQ
v  Transaction Modes [BTET, ANSI]
v  Single, Multi Statement request operations
v  Command, Query and Request Processing

Practical on this with case studies

2) Teradata DBA concepts

v  Databases creation, manipulation and removal
v  Users creation, manipulation and removal
v  Memory management (Spool, Perm and Temp)
v  Privileges management
v  Working with other memory areas
v  Working on DDL commands to implement
v  Working with Teradata Administrator tool
v  Working on ViewPoint Portal (Optional)

3) Teradata SQL Languages

v  Data Definition Language (DDL)
v   Data Manipulation Language (DML)
v  Data Control Language (DCL)
v  Stored Procedure Language (SPL)
v  ANSI commands (COMMIT,ROLLBACK,ABORT)
v  Teradata Extensions a. HELP b. SHOW c. EXPLAIN d. COLLECT STATISTICS

4) Data Manipulation Language (DML)

v  Retrieval of data from objects (SELECT)
v  Manipulation of data from objects
(Insert, Update, Delete, Merge, Upsert)
Real time usages and detailed practices

8) Other Components of table

v  Data Types (ANSI, Teradata)
v  Data Type Attributes
v  Column Storage Attributes(COMPRESS)
v  COMPRESS (Block, Row, Column)
v  Constraints
o    Primary / Unique / References / Check
v  Soft Referential Integrity(SOFT RI)
v  COLUMNAR FEATURE
Real time practices and performance improvements

9) Teradata Transactions

v  Implicit & Explicit Transaction
v  Interactive & Batch Modes

            Semantic Modes a. BTET b. ANSI

10) Conditional Expressions and Operators

v  Conditional Expressions (IN,NOT IN,LIKE, EXISTS,
BETWEEN AND, IS NULL etc…)
v  Arithmetic Operators and order of precedence
v  Logical Operators and order of precedence
v  Conditional Operators and order of precedence

11) Teradata Other Objects

v  Sub Queries :Simple , Nested & Correlated
v  Set Operations :Union , Union All ,Intersect , Except
v  Joins : Cross ,Inner ,Left ,Right and Full Joins
Detailed discussion on Real Time Queries and Tips
v  Views
v  Macros
v  Stored Procedures
v  Triggers
v  Cursors
Detailed discussion on Real Time Queries and Tips.

 

5) Data Definition Language (CREATE, ALTER, DROP)

v  Create/alter table with all possible options (Free space, Data Block Size)
Create different types of tables
v  Set table
v  Multi Set table
v  Derived table
v  Volatile table (VT)
v  Global Temporary table (GTT)
v  Error table (ET)
v  Queue
v  System Calendar
v  Temporal tables(TD 14)***
i. Valid Time ii. Transaction Time iii. Bi Temporal
Real time usages and detailed practices
Real time usages and detailed practices

6) Data Control Language (DBA Related Concepts)

v  GRANTing privileges (GRANT)
v  REVOKing privileges (REVOKE)
v  GIVing Privileges    (GIVE)
Real time usages and detailed practices

7) Locks, Data Recovery and Protection

v  Object Locks -Access, Read, Write, Exclusive, Hut
v  NO Wait ,Pseudo table lock
v  HSA,HSN (Hot Stand By AMP,NODE)
v  RAID1 ,RAID5 , Disk Arrays
v  Fall Back , AMP Clustering
v  Clique
v  Images (Single , Dual , Before, After)
v  Journals ( Recovery , Transient and Permanent Journals ,Write ahead logging)

12) Teradata Functions

Niladic Functions
Table Functions
Scalar Functions
Aggregate Functions
Oracle functions in Teradata
Otranslate, Greatest, Decode, NVL, Lpad etc…
ETL(Normal )Functions :
Format, Cast , Coalesce, Nullif ,Trim ,Substr, Position, Length, Chars, Upper, Lower, Sample, Random, Rank, Type, Named, Title, Index, Trim, Add_Months etc….
Advanced OLAP Functions (Ordered Analytical)
v  Moving SUM/AVG/DIFF
v  Cumulative SUM/AVG/DIFF
v  With, With By
v  Rank(), Qualify
v  Row_Number(), Qualify
v  Format
v  Extract
v  Top
v  Csum
Detailed discussion on Real Time Queries and Tips

13) Teradata Performance Tuning

v  Explain Request Modifier(EXPLAIN PLAN)
v  Collecting Statistics, Confidence Levels (High, ,Low, NO, JOIN), DIAGNOSTICS HELP STATS
v  Tuning SQL Queries & Utilities
v  Usage of PMON, Index & Statistics wizards
v  Various Indexes (Hash, Join ),
v  Teradata Join Strategies (Merge, Product ,Nested ,Row hash)
v  Skew Factor, Skewness and remedies
v  Spool space Issues
v  System Level Bottlenecks identification.



(TERADATA PART-3:  UTILITIES [ETL & ADMIN] )
Load and Unload Utilities & Tools

1) Teradata SQL ASSISTANT (SQLA)
v  Import and Export data
v  Working on various Options
2) Teradata BTEQ
v  Features, Command Sets, Modes, Formats
v  Scripts with Fixed, Vartext and Report.
v  Branching and Looping
v  Load/Un Load with ETL operations
v  Multi Statement Requests **
v  Table –Table data loading
v  Exception handling and examples
Questions and error handling

3) Teradata FASTLOAD

v  Features, Phases and Limitations of Fast Load
v  Advantages , Process, Error tables
v  Working with Fixed and Variable text scripts.
v  Real time Issues and resolving it.
Questions and error handling
4) Teradata MULTILOAD
v  Features, Phases ,Limitations of Multi Load
v  Tables and Tasks in Multi Load
v  INSERT/UPDATE/DELETE/UPSERT/RELEASE
v  SCDs implementation(Type1, Type2, Type3)
v  Multi file and Multi table loading
v  Real time Issues and solving it.
Questions and error handling

5) Teradata TPUMP

v  Features, Limitations, Process of TPump
v  Diff between MLOAD and Tpump
v  SCDs Implementation(Type1,Type2, Type3)
Real time & Sample scripts, Issues Resolution.

6) Teradata FASTEXPORT

v  Features, Process ,Limitations
v  Exporting data to Single, Multiple tables
v  Passing Parameters (Internal, External)
Real time usages & scripts.

7) TPT (Teradata Parallel Transporter) & TPT API

v  Load, Export, Update, Stream Operators
v  TPT Streaming Process
v  TPT Wizard Implementation
v  Implementing LOAD operator with multiple examples
v  Implementing UPDATE operator with multiple examples
v  Implementing STREAM operator with multiple examples
v  Implementing FILTER operator with multiple examples
v  Implementing STANDALONE operator with multiple examples
v  Implementing Data Connector Operator
v  Sessions and Instances
Real time usages & scripts and recommendations

8) Teradata OLELOAD

Moving data between Heterogeneous Sources
 (Flat Files, Excel Files and Databases)

9) Teradata other utilities and tools (over view)

v  Teradata Backup Archive and Recovery Solutions
v  Teradata Performance Monitor (PMON)
v  TERADATA Administrator
v  TERADATA Index WIZARD
v  TERADATA statistics wizard
v  Teradata System Emulation tool (TSET)
v  Teradata Viewpoint (portals and portlets)



No comments:

Post a Comment