Teradata Topics Learn

Tera Data : I. Teradata Parallel Architecture Teradata Introduction Teradata Architecture Teradata Components Parsing Engine Processor (PEP or PE) Access Module Processor (AMP) Message Passing Layer (BYNET) A Teradata Database CREATE / MODIFY DATABASE Parameters Teradata Users { CREATE | MODIFY } DATABASE or USER (in common) { CREATE | MODIFY } USER (only) Symbols Used in this Book DATABASE Command Use of an Index Primary Index Secondary Index II. - 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 DISTINCT Function III. On-line HELP and SHOW Commands HELP commands SHOW commands EXPLAIN Adding Comments ANSI Comment Teradata Comment User Information Functions ACCOUNT Function DATABASE Function SESSION Function IV. Data Conversions Data Conversions Data Types CAST Implied CAST Formatted Data Tricking the ODBC to Allow Formatted Data TITLE Attribute for Data Columns Transaction Modes Case Sensitivity of Data CASESPECIFIC LOWER Function UPPER Function V. Aggregation Aggregate Processing Math Aggregates The SUM Function The AVG Function The MIN Function The MAX Function The COUNT Function Aggregates and Derived Data GROUP BY Limiting Output Values Using HAVING Statistical Aggregates The KURTOSIS Function The SKEW Function The STDDEV_POP Function The STDDEV_SAMP Function The VAR_POP Function The VAR_SAMP Function The CORR Function The COVAR Function The REGR_INTERCEPT Function The REGR_SLOPE Function

Using GROUP BY Use of HAVING Using the DISTINCT Function with Aggregates Aggregates and Very Large Data Bases (VLDB) Potential of Execution Error GROUP BY versus DISTINCT Performance Opportunities VI. 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 EXISTS VII. Subquery 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 VIII. 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 IX. 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 X. OLAP Functions On-Line Analytical Processing (OLAP) Functions OLAP Functions Cumulative Sum Using the CSUM Function Cumulative Sum with Reset Capabilities Using CSUM and GROUP BY Generating Sequential Numbers with CSUM Moving Sum Using the MSUM Function Moving Sum with Reset Capabilities Using MSUM and GROUP BY Moving Average Using the MAVG Function Moving Average with Reset Capabilities Using MAVG and GROUP BY Moving Difference Using the MDIFF Function Moving Difference with Reset Capabilities Using MDIFF and GROUP BY Cumulative and Moving SUM Using SUM / OVER Cumulative Sum with Reset Capabilities SUM Using SUM and OVER / PARTITION BY Moving Average Using AVG / OVER Moving Average with Reset Capabilities Using AVG and OVER / ROWS and PARTITION BY Moving Linear Regression Using the MLINREG Function Partitioning Data Using the QUANTILE Function QUALIFY to Find Products in the top Partitions Ranking Data using RANK QUALIFY to Find Top Best or Bottom Worse RANK with Reset Capabilities Using RANK with GROUP BY Internal RANK operations Sampling Rows using the SAMPLE Function RANDOM Number Generator Function XI. SET Operators

Set Operators Considerations for Using Set Operators INTERSECT UNION EXCEPT MINUS Using Multiple Set Operators in a Single Request XII. 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 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 XIII. 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 XIV. View Processing Views Reasons to Use Views Considerations for Creating Views Creating and Using VIEWS 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 XV. Macro Processing Macros CREATE MACRO REPLACE MACRO EXECUTE MACRO DROP MACRO Generating SQL from a Macro XVI. Transaction Processing What is a Transaction Locking Transaction Modes Comparison Chart Setting the Transaction Mode Teradata Mode Transactions ANSI Mode Transactions Aborting Teradata Transactions Aborting ANSI Transactions XVII. Reporting Totals and Subtotals Totals and Subtotals Totals (WITH) Subtotals (WITH…BY) Multiple Subtotals on a Single Break Multiple Subtotal Breaks XVIII. 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 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 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 CREATE TABLE to Copy an existing table Altering a Table Dropping a Table Dropping a Table versus Deleting Rows Renaming a Table Using Secondary Indices Join Index Collecting Statistics Hashing Functions HASHROW HASHBUCKET HASHAMP HASHBAKAMP Conclusion XIX. Temporary Tables Temporary Tables Creating Interim or Temporal Tables Temporary Table Choices Derived Tables Volatile Temporary Tables Global Temporary Tables GLOBAL Temporary Table Examples General Practices for Temporary use Tables XX. Trigger Processing Triggers Terminology Logic Flow CREATE TRIGGER Syntax Row Trigger Statement Trigger ‘BEFORE’ Trigger ‘AFTER’ Trigger ‘INSTEAD OF’ Trigger Cascading Triggers Sequencing Triggers XXI. Stored Procedures Teradata Stored Procedures CREATE PROCEDURE Stored Procedural Language (SPL) Statements BEGIN / END Statements Establishing Variables and Data Values DECLARE Statement to Define Variables SET to Assign a Data Value as a Variable Status Variables Assigning a Data Value as a Parameter Additional SPL Statements CALL Statement IF / END IF Statement LOOP / END LOOP Statements LEAVE Statement

WHILE / END WHILE Statement FOR / END FOR Statements ITERATE Statement PRINT Statement Exception Handling DECLARE HANDLER Statement DML Statements Using Column and Alias Names Comments and Stored Procedures Commenting in a Stored Procedure Commenting on a Stored Procedure On-line HELP for Stored Procedures HELP on a Stored Procedure HELP on Stored Procedure Language (SPL) REPLACE PROCEDURE DROP PROCEDURE RENAME PROCEDURE SHOW PROCEDURE TERADATA UTILITIES I. INTRODUCTION AN INTRODUCTION TO THE TERADATA UTILITIES Considerations for using Block at a Time Utilities Considerations for using Row at a Time Utilities II. BTEQ AN INTRODUCTION TO BTEQ Why it is called BTEQ? Logging on to BTEQ USING BTEQ TO SUBMIT QUERIES Submitting SQL in BTEQ’s Interactive Mode Submitting SQL in BTEQ’s Batch Mode USING BTEQ CONDITIONAL LOGIC USING BTEQ TO EXPORT DATA BTEQ EXPORT EXAMPLE USING RECORD (DATA) MODE BTEQ EXPORT EXAMPLE USING FIELD (REPORT) MODE BTEQ IMPORT EXAMPLE DETERMINING OUT RECORD LENGTHS BTEQ RETURN CODES BTEQ COMMANDS Session Control Commands File Control Commands Sequence Control Commands Format Control Commands III. FASTEXPORT AN INTRODUCTION TO FASTEXPORT Why it is called “FAST”Export How FastExport Works FastExport Fundamentals FastExport Supported Operating Systems Maximum of 15 Loads FASTEXPORT SUPPORT AND TASK COMMANDS Support Environment Commands (see Support Environment chapter for details)

Task Commands FASTEXPORT SUPPORTED SQL COMMANDS SQL Commands A FASTEXPORT IN ITS SIMPLEST FORM SAMPLE FASTEXPORT SCRIPT FASTEXPORT MODES AND FORMATS FastExport Modes FastExport Formats A FASTEXPORT SCRIPT USING BINARY MODE IV. FASTLOAD AN INTRODUCTION TO FASTLOAD Why it is called “FAST”Load How FastLoad Works FastLoad Has Some Limits Three Key Requirements for FastLoad to Run Maximum of 15 Loads FASTLOAD HAS TWO PHASES PHASE 1: Acquisition PHASE 2: Application FASTLOAD COMMANDS A FASTLOAD EXAMPLE IN ITS SIMPLEST FORM SAMPLE FASTLOAD SCRIPT CONVERTING DATA TYPES WITH FASTLOAD A FASTLOAD CONVERSION EXAMPLE WHEN YOU CANNOT RESTART FASTLOAD WHEN YOU CAN RESTART FASTLOAD WHAT HAPPENS WHEN FASTLOAD FINISHES RESTARTING FASTLOAD: A MORE IN-DEPTH LOOK How the CHECKPOINT Option Works Restarting with CHECKPOINT Restarting without CHECKPOINT — Preferred Method USING INMODS WITH FASTLOAD V. MULTILOAD DAN INTRODUCTION TO MULTILOAD Why it is called “Multi”Load Two MultiLoad Modes: IMPORT and DELETE Block and Tackle Approach MultiLoad Imposses Limits Error Tables, Work Tables and Log Tables Supported Input Formats MULTILOAD HAS FIVE IMPORT PHASES Phase 1: Preliminary Phase Phase 2: DML Transaction Phase Phase 3: Acquisition Phase Phase 4: Application Phase Phase 5: Clean Up Phase MULTILOAD COMMANDS Two Types of Commands PARAMETERS FOR .BEGIN IMPORT MLOAD PARAMETERS FOR .BEGIN DELETE MLOAD A SIMPLE MULTILOAD IMPORT SCRIPT MULTILOAD IMPORT SCRIPT ERROR TREATMENT OPTIONS FOR THE .DML LABEL COMMAND AN IMPORT SCRIPT WITH ERROR TREATMENT OPTIONS

A IMPORT SCRIPT THAT USES TWO INPUT DATA FILES REDEFINING THE INPUT A SCRIPT THAT USES REDEFINING THE INPUT A DELETE MLOAD SCRIPT USING A HARD CODED VALUE A DELETE MLOAD SCRIPT USING A VARIABLE AN UPSERT SAMPLE SCRIPT WHAT HAPPENS WHEN MULTILOAD FINISHES MultiLoad Statistics MultiLoad Output File [STEVE/MIKE we need a Sample Output] Troubleshooting MultiLoad Errors — More on the Error Tables RESTARTING MULTILOAD RELEASE MLOAD: WHEN YOU DON’T WANT TO RESTART MULTILOAD MULTILOAD AND INMODS HOW MULTILOAD COMPARES WITH FASTLOA VI. TPUMP AN INTRODUCTION TO TPUMP Why It Is Called “TPump” TPump Has Many Unbelievable Abilities TPump Has Some Limits Supported Input Formats TPUMP COMMANDS AND PARAMETERS LOAD Parameters IN COMMON with MultiLoad .BEGIN LOAD Parameters UNIQUE to TPump A SIMPLE TPUMP SCRIPT — A LOOK AT THE BASICS TPUMP SCRIPT WITH ERROR TREATMENT OPTIONS TPUMP OUTPUT STATISTICS A TPUMP SCRIPT THAT USES TWO INPUT DATA FILES A TPUMP UPSERT SAMPLE SCRIPT MONITORING TPUMP HANDLING ERRORS IN TPUMP USING THE ERROR TABLE One Error Table Common Error Codes and What They Mean RESTARTING TPUMP TPUMP AND MULTILOAD COMPARISION CHART VII. INMOD PROCESSING WHAT IS OF AN INMOD HOW AN INMOD WORKS CALLING AN INMOD FROM FASTLOAD WRITING AN INMOD Writing for Fastload Writing for MultiLoad, TPump and FastExport MIGRATING AN INMOD WRITING A NOTIFY ROUTINE SAMPLE INMOD VIII. OUTMOD PROCESSING WHAT IS OF AN OUTMOD HOW AN OUTMOD WORKS CALLING AN OUTMOD FROM FASTEXPORT WRITING AN OUTMOD WRITING A NOTIFY ROUTINE SAMPLE OUTMOD

IX. SUPPORT ENVIRONMENT THE TERADATA UTILITIES AND THE SUPPORT ENVIRONMENT THE SUPPORT ENVIRONMENT COMMANDS Alphabetic Command List Required Operational Command List Creating Teradata Sessions Terminating a Teradata Sessions Optional Command List Accepting a Parameter Value(s) Establishing the Default Date Format Displaying an Output Message Comparing Variable Data Routing Messages Running Commands from a File Setting Variables to Values Running a System Command USING A VARIABLE IN A SCRIPT THE SUPPORT ENVIRONMENT SYSTEM VARIABLES SUPPORT ENVIRONMENT EXAMPLE