Enquiry Now !      

best TeraData Training Pune | TeraData Institutes in Pune | Technogeeks

    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

fdsfsd
TeraData


Enquiry Now !