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
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
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
39. Teradata V14 Sample N Percent
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
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
2. Block Level Utility Limits
6. A Sample FastLoad Script Created by Nexus SmartScript
8. The Nexus SmartScript Easily Builds Your Utilities
9. The Nexus SmartScript FastLoad Builder
11. FastLoad to a NoPI Table
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
2. Block Level Utility Limits
5. A Sample MultiLoad Script Created by Nexus SmartScript
6. Referential Integrity and Load Utility Solutions
8. A MultiLoad Example That UPSERTs
9. A MultiLoad DELETE MODE Example
11. Five Formats of MultiLoad Files
12. A NoPI Table Does Not Work with MultiLoad
14. The Output Report from MultiLoad
15. Host Utility Locks (HUT Locks)
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
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)
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
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)
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
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
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
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.