Customized Onsite Training

2
Days
  • Customized Content
  • For Groups of 5+
  • Online or On-location
  • Expert Instructors
Request Class or call 1-877-932-8228
Overview

In this Teradata Utilities training class, students will learn traditional utilities at the perfect level with detailed examples and explanations so they’re clear to understand.

This course is designed for IT professionals, Developers, and DBAs who have a desire to learn Teradata Utilities.

Goals
  1. Gain a grasp of all traditional utilities.
Outline
  1. Teradata Utilities Introduction
    1. The Teradata Utilities
    2. Block Level Utilities
    3. Row Level Utilities
    4. Fast Path Inserts Using Insert/Select
    5. Fast Path Deletes
    6. Freespace Percent
    7. Referential Integrity and Load Utility Solutions
    8. Teradata has a No Primary Index Table called a NoPI Table
    9. This is NOT Necessarily a NoPI Table
    10. NoPI Tables Spread rows across all-AMPs Evenly
    11. NoPI Tables used as Staging Tables for Data Loads
    12. NoPI Table Capabilities
    13. NoPI Table Restrictions
    14. Why Would a NoPI Table have a Row-ID?
  2. Collect Statistics
    1. The Teradata Parsing Engine (Optimizer) is Cost Based
    2. The Purpose of Collect Statistics
    3. When Teradata Collects Statistics, it creates a Histogram
    4. The Interval of the Collect Statistics Histogram
    5. Histogram Quiz
    6. Answers to Histogram Quiz
    7. What to COLLECT STATISTICS On?
    8. Why Collect Statistics?
    9. How do you know if Statistics were collected on a Table?
    10. A Huge Hint that No Statistics Have Been Collected
    11. The Basic Syntax for COLLECT STATISTICS
    12. COLLECT STATISTICS Examples for a better Understanding
    13. The New Teradata V14 Way to Collect Statistics
    14. COLLECT STATISTICS Directly From another Table
    15. Where Does Teradata Keep the Collected Statistics?
    16. The Official Syntax for COLLECT STATISTICS
    17. How to Recollect STATISTICS on a Table
    18. Teradata Always Does a Random AMP Sample
    19. Random Sample is Kept in the Table Header in FSG Cache
    20. Multiple Random AMP Samplings
    21. How a Random AMP gets a Table Row count
    22. Random AMP Estimates for NUSI Secondary Indexes
    23. USI Random AMP Samples are Not Considered
    24. There's No Random AMP Estimate for Non-Indexed Columns
    25. A Summary of the PE Plan if No Statistics Were Collected
    26. Stale Statistics Detection and Extrapolation
    27. Extrapolation for Future Dates
    28. How to Copy a Table with Data and the Statistics
    29. How to Copy a Table with NO Data and the Statistics
    30. When to COLLECT STATISTICS Using only a SAMPLE
    31. Examples of COLLECT STATISTICS Using only a SAMPLE
    32. Examples of COLLECT STATISTICS for V14
    33. How to Collect Statistics on a PPI Table on the Partition
    34. Teradata V12 and V13 Statistics Enhancements
    35. Teradata V14 Statistics Enhancements
    36. Teradata V14 Summary Statistics
    37. Teradata V14 MaxValueLength
    38. Teradata V14 MaxIntervals
    39. Teradata V14 Sample N Percent
    40. Teradata Statistics Wizard
  3. Table Create and Data Types
    1. Creating a Table with a Unique Primary Index
    2. Creating a Table with a Non-Unique Primary Index
    3. Creating a Table and forgetting to put in a Primary Index Clause
    4. Creating a Set Table
    5. Creating a Multiset Table
    6. Creating a Set Table that won't have a Duplicate Row Check
    7. Set Table with a Unique Constraint Eliminates the Duplicate Row Check
    8. Creating a Table with a Unique Secondary Index
    9. Creating a Table with a Multi-Column Primary Index
    10. Data Types
    11. Data Types Continued
    12. Data Types Continued
    13. Major Data Types and the number of Bytes they take up
    14. Making an exact copy a Table
    15. Making a NOT-So-Exact Copy a Table
    16. Copying a Table with a new Default Primary Index
    17. Troubleshooting Copying and Changing the Primary Index
    18. Copying only specific columns of a table
    19. Copying a Table with Data and Keeping the Statistics
    20. Copying a Table with No Data and Statistics
    21. Copying a table Structure with Zeroed Statistics
    22. Creating a Table with Fallback
    23. Creating a Table with No Fallback
    24. Creating a Table with a Before Journal
    25. Creating a table with a Dual Before Journal
    26. Creating a Table with an After Journal
    27. Creating a Table with a Dual After Journal
    28. Creating a Table with the Journal Keyword Alone
    29. Why use a Before Journal?
    30. Why Use an After Journal?
    31. Creating a Table with Customization of the Data Block Size
    32. Creating a Table with Customization on FREESPACE Percent
    33. Creating a QUEUE Table
    34. Example of how a Queue Table Works
    35. Example of how a Queue Table Works
    36. The Concept behind Partitioning a Table
    37. Creating a PPI Table with Simple Partitioning
    38. Creating a PPI Table with RANGE_N Partitioning per Month
    39. A Visual of One Year of Data with Range_N per Month
    40. An SQL Example explaining Range_N Partitioning per Month
    41. Creating a PPI Table with RANGE_N Partitioning per Day
    42. Creating a PPI Table with RANGE_N Partitioning per Week
    43. A Clever Range_N Option
    44. Creating a PPI Table with CASE_N
    45. NO CASE and UNKNOWN Partitions Together
    46. Combining Older Data and Newer Data in PPI
    47. Multi-Level Partitioning Combining Range_N and Case_N
    48. NON-Unique Primary Indexes (NUPI) in PPI
    49. PPI Table with a Unique Primary Index (UPI)
    50. Tricks for Non-Unique Primary Indexes (NUPI)
    51. Character Based PPI for RANGE_N
    52. Character-Based PPI for CASE_N
    53. Dates and Character-Based Multi-Level PPI
    54. TIMESTAMP Partitioning
    55. Using CURRENT_DATE to define a PPI
    56. ALTER to CURRENT_DATE the next year
    57. ALTER to CURRENT_DATE with Save
    58. Altering a PPI Table to Add or Drop Partitions
    59. Deleting a Partition
    60. Deleting a Partition and saving its contents
    61. Using the PARTITION Keyword in your SQL
    62. SQL for RANGE_N
    63. SQL for CASE_N
  4. Temporary Tables
    1. There are three types of Temporary Tables
    2. CREATING A Derived Table
    3. Naming the Derived Table
    4. Aliasing the Column Names in the Derived Table
    5. Most Derived Tables Are Used To Join To Other Tables
    6. Multiple Ways to Alias the Columns in a Derived Table
    7. Our Join Example with a Different Column Aliasing Style
    8. Column Aliasing Can Default for Normal Columns
    9. CREATING A Derived Table using the WITH Command
    10. Our Join Example With the WITH Syntax
    11. The Same Derived Query shown Three Different Ways
    12. Quiz - Answer the Questions
    13. Answer to Quiz - Answer the Questions
    14. Clever Tricks on Aliasing Columns in a Derived Table
    15. A Derived Table lives only for the lifetime of a single query
    16. An Example of Two Derived Tables in a Single Query
    17. WITH RECURSIVE Derived Table
    18. Defining the WITH Recursive Derived Table
    19. Looping Through the Recursive Derived Table
    20. Looping Through a Second Time
    21. Looping Through a Third Time
    22. Looping Through and Adding Nothing Ends the Loop
    23. Looping Through the WITH Recursive Derived Table
    24. Creating a Volatile Table
    25. You Populate a Volatile Table with an INSERT/SELECT
    26. The Three Steps to Use a Volatile Table
    27. Why Would You Use the ON COMMIT DELETE ROWS?
    28. The HELP Volatile Table Command Shows your Volatiles
    29. A Volatile Table with a Primary Index
    30. The Joining of Two Tables Using a Volatile Table
    31. You Can Collect Statistics on Volatile Tables
    32. The New Teradata V14 Way to Collect Statistics
    33. Four Examples of Creating a Volatile Table Quickly
    34. Four Advanced Examples of Creating a Volatile Table Quickly
    35. Creating Partitioned Primary Index (PPI) Volatile Tables
    36. Using a Volatile Table to Get Rid of Duplicate Rows
    37. Using a Simple Global Temporary Table
    38. Two Brilliant Techniques for Global Temporary Tables
    39. The Joining of Two Tables Using a Global Temporary Table
    40. CREATING A Global Temporary Table
  5. BTEQ – Batch Teradata Query
    1. BTEQ – Batch Teradata Query Tool
    2. How to Logon to BTEQ in Interactive Mode
    3. Running Queries in BTEQ in Interactive Mode
    4. BTEQ Commands vs BTEQ SQL Statements
    5. WITH BY Command for Subtotals
    6. WITH Command for a Grand Total
    7. WITH and WITH BY Together for Subtotals and Grand Totals
    8. How to Logon to BTEQ in a SCRIPT
    9. Running Queries in BTEQ through a Batch Script
    10. Running a BTEQ Batch Script through the Command Prompt
    11. Running a BTEQ Batch Script through the Run Command
    12. Using Nexus to Build Your BTEQ Scripts
    13. Using Nexus to Build Your BTEQ Scripts
    14. Using BTEQ Scripts to IMPORT Data
    15. What Keywords Mean in a BTEQ Script
    16. Creating a BTEQ IMPORT for a Comma Separated Value File
    17. Four Great Examples/Ways to Run a Teradata BTEQ Script
    18. BTEQ Export – Four types of Export Variations
    19. Creating a BTEQ Export Script in Record Mode
    20. Creating a BTEQ Export Script in Report Mode
    21. The Appearance of Record Mode vs Report Mode Data
    22. Using Report Mode to Create a Comma Separated Report
    23. Creating a BTEQ IMPORT for a Comma Separated Value File
    24. Using Multiple Sessions in BTEQ
    25. BTEQ Fast Path Inserts
    26. BTEQ Can Use Conditional Logic
    27. Using a BTEQ Export and Setting a Limit in a UNIX System
  6. FastLoad
    1. FastLoad
    2. Block Level Utility Limits
    3. FastLoad has Two Phases
    4. FastLoad Phase 1
    5. FastLoad Phase 2
    6. A Sample FastLoad Script Created by Nexus SmartScript
    7. Executing the FastLoad Script
    8. The Nexus SmartScript Easily Builds Your Utilities
    9. The Nexus SmartScript FastLoad Builder
    10. Create and Execute Your FastLoad Scripts with Nexus
    11. FastLoad to a NoPI Table
    12. FastLoad and CHECKPOINT
    13. Loading Multiple Input Files with FastLoad
    14. Valid Data Types That Can Be Used in a FastLoad
    15. A FastLoad that Converts Data Types
    16. A FastLoad that Uses the NULLIF Statement
    17. FastLoad and Referential Integrity Solutions
    18. The Output Report from FastLoad
    19. Recovering a FastLoad that has failed
    20. A BTEQ Export and then a FastLoad
    21. A FastExport and then a FastLoad Needs Indicators
  7. MultiLoad
    1. MultiLoad
    2. Block Level Utility Limits
    3. MultiLoad has Five Phases
    4. MultiLoad has IMPORT and DELETE Tasks
    5. A Sample MultiLoad Script Created by Nexus SmartScript
    6. Referential Integrity and Load Utility Solutions
    7. MultiLoad That Inserts and Updates from Two Different Files
    8. A MultiLoad Example That UPSERTs
    9. A MultiLoad DELETE MODE Example
    10. MultiLoad DELETE Rules
    11. Five Formats of MultiLoad Files
    12. A NoPI Table Does Not Work with MultiLoad
    13. Executing a MultiLoad Script
    14. The Output Report from MultiLoad
    15. Host Utility Locks (HUT Locks)
    16. Troubleshooting MultiLoad
  8. TPump
    1. TPump
    2. TPump is NOT a Block Level Utility and has No Limits
    3. Limitations of TPump
    4. A Sample TPump Script Created by Nexus SmartScript
    5. Executing a TPump Script
    6. TPump Begin Load Statement Options
    7. Five Formats of TPump Files
    8. TPump Script with Error Treatment Options
    9. TPump UPSERT Script
    10. The Output Report from TPump
    11. Did you know Tera-Tom was a world-class athlete?
  9. FastExport
    1. FastExport
    2. New Rules for Block Utilities
    3. A Sample FastExport Script Created by Nexus SmartScript
    4. FastExport by Default places Null Indicators in Output
    5. A Sample FastExport Script Created by Nexus SmartScript
    6. No Spool Options with FastExport
    7. FastExport with No Spool
    8. FastExport that Joins Two Tables
    9. FastExport Modes
    10. How to Eliminate Indicators in your FastExport Script
    11. Executing a FastExport Script
  10. Teradata Parallel Transport (TPT)
    1. What is TPT?
    2. TPT Producers Create Streams and Consumers Write Them
    3. The Four Major Operators of TPT
    4. TPT can read from multiple source files in Parallel
    5. TPT can have more Operators than Consumers
    6. TPT Operators and their Equivalent Load Utility
    7. How to Run a TPT Script
    8. Six Syntax Rules when Creating TPT Scripts
    9. TPT Scripts are divided into two major sections
    10. Three Required Define Statements in the Declarative Section
    11. The Major Keys to Building TPT Scripts
    12. Schemas
    13. The DDL Operator
    14. DDL Operator Example
    15. The SQL Selector Operator
    16. SQL_Selector Operator Export to Delimited File (1 of 3)
    17. SQL_Selector Operator Export to Delimited File (2 of 3)
    18. SQL_Selector Operator Export to Delimited File (3 of 3)
    19. Another SQL_Selector Operator Export (1 of 3)
    20. Another SQL_Selector Operator Export (2 of 3)
    21. Another SQL_Selector Operator Export (3 of 3)
    22. SQL Selector Example (1 of 3)
    23. SQL Selector Example (2 of 3)
    24. SQL Selector Example (3 of 3)
    25. Another SQL Selector Example (1 of 3)
    26. Another SQL Selector Example (2 of 3)
    27. Another SQL Selector Example (3 of 3)
    28. The Export Operator
    29. Export Operator to Formatted Flat File Example (1 of 3)
    30. Export Operator to Formatted Flat File Example (2 of 3)
    31. Export a Table to a Formatted Flat File with Indicators (3 of 3)
    32. Deferred Schema
    33. Export a Table to a Binary Flat File (1 of 2)
    34. Export a Table to a Binary Flat File (2 of 2)
    35. The Load Operator
    36. Load from Binary File (1 of 3)
    37. Load from Binary File (2 of 3)
    38. Load from Binary File (3 of 3)
    39. Data Connectors
    40. Another Import to Table from Binary File (1 of 3)
    41. Another Import to Table from Binary File (2 of 3)
    42. Another Import to Table from Binary File (3 of 3)
    43. Load Table from Flat File (1 of 2)
    44. Load Table from Flat File (2 of 2)
    45. Load another Table from a Flat File
    46. Update Operator
    47. Teradata V14.10 Extended MultiLoad Protocol (MLOADX)
    48. Update Operator Example (1 of 3)
    49. Update Operator Example (2 of 3)
    50. Update Operator Example (3 of 3)
    51. Another Update Operator Example (1 of 3)
    52. Another Update Operator Example (2 of 3)
    53. Another Update Operator Example (3 of 3)
    54. Stream Operator
    55. Stream Operator Example (1 of 3)
    56. Stream Operator Example (2 of 3)
    57. Stream Operator Example (3 of 3)
    58. Another Stream Operator Example (1 of 3)
    59. Another Stream Operator Example (2 of 3)
    60. Another Stream Operator Example (3 of 3)
    61. Easy Loader Example (tdload)
    62. TPT Utility Commands
    63. OS Command Operator
    64. Job Variables Example
    65. Include Statement (1 of 2)
    66. Include Statement (2 of 2)
    67. Operator Templates
    68. Using Operator Templates
    69. Operator Template Example
    70. Moving Data from Netezza to Teradata (1 of 2)
    71. Moving Data from Netezza to Teradata (2 of 2)
  11. Top SQL Commands Cheat Sheet
    1. SELECT All Columns from a Table and Sort
    2. Select Specific Columns and Limiting the Rows
    3. Changing your Default Database
    4. Keywords that describe you
    5. Select TOP Rows in a Rank Order
    6. A Sample number of rows
    7. Getting a Sample Percentage of rows
    8. Find Information about a Database
    9. Find information about a Table
    10. Using Aggregates
    11. Performing a Join
    12. Performing a Join using ANSI Syntax
    13. Using Date, Time and Timestamp
    14. Using Date Functions
    15. Using the System Calendar
    16. Using the System Calendar in a Query
    17. Formatting Data
    18. Using Rank
    19. Using a Derived Table
    20. Using a Subquery
    21. Correlated Subquery
    22. Using Substring
    23. Basic CASE Statement
    24. Advanced CASE Statement
    25. Using an Access Lock in your SQL
Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Class Prerequisites

Experience in the following is required for this Teradata class:

  • Basic Java Knowledge.

Experience in the following would be useful for this Teradata class:

  • Experience with Eclipse.
Preparing for Class

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

72,554

Students who have taken Live Online Training

15,169

Organization who trust Webucator for their training needs

100%

Satisfaction guarantee and retake option

9.42

Students rated our trainers 9.42 out of 10 based on 5,211 reviews

Contact Us or call 1-877-932-8228