BTEQ

BTEQ

Batch TEradata Query (BTEQ) is pronounced Bee-Teeeek.
BTEQ was the first utility and query tool for Teradata. BTEQ can be used as a Query tool, to load data a row at a time into Teradata and to export data off of Teradata a row at a time.

The features of BTEQ:

• BTEQ can be used to submit SQL in either a batch or interactive environment.
• BTEQ gives the outputs in a report format, where Queryman outputs data in a format more like a spreadsheet.
• As said Bteq is an excellent tool for importing and exporting data.

There are mainly 4 types of BTEQ Exports.

Export DATA
This is set by .EXPORT DATA.
Generally, users will export data to a flat file format.This is called Record Mode or DATA mode.
If the data has no headers or white space between the data contained in each column and the data is written to the file in a normal format.

Export INDICDATA
This is set by .EXPORT INDICDATA.
This mode is used to export data with extra indicator bytes to indicate NULLs in column for a row.

Export REPORT
This is set by .EXPORT REPORT
In this mode the output of BTEQ export would return the column headers for the fields, white space, expanded packed or binary data.
Its just looks like a report with column headers and data.

Export DIF
This called as Data Interchange Format, which allows users to export data from Teradata to be directly utilized for spreadsheet applications like Excel, FoxPro and Lotus.

Below is the example of BTEQ IMPORT Script.We are taking data form a flat file from C:\TEMP\EMPDATA.txt and importing records into Empdb.Emp_Table.

.LOGON USERNAME/PWD;

.IMPORT DATA FILE=C:\TEMP\EMPDATA.txt, SKIP=2

.QUIET ON  
.REPEAT*

USING (EMP_NUM    INTEGER
      ,EMP_NAME   VARCHAR(20)
      ,EMP_PHONE  VARCHAR(10)
      )
INSERT INTO Empdb.Emp_Table
VALUES( :EMP_NUM
       ,:EMP_NAME
       ,:EMP_PHONE
       );
       
.QUIT
.LOGOFF

Below is a review of those commands for the above example.

• QUIET ON limits BTEQ output to reporting only errors and request processing statistics.
• REPEAT * causes BTEQ to read a specified number of records or until EOF. The default is one record. Using REPEAT 100 would perform the loop 100 times.
• The USING defines the input data fields and their associated data types coming from the host.

FAST EXPORT

FAST EXPORT

FastExport ,the name itself is spells to exports data from Teradata to a Flat file. But BTEQ also does the same thing.The main difference is BTEQ exports data in rows and FastExport exports data in 64K blocks. So if its required to load data with lightning speed Fast export is the best choice.

FastExport is a 64K block utility it falls under the limit of 15 block utilities. That means that a system can’t have more than a combination of 15 FastLoads, MultiLoads, and FastExports.

Basic fundamentals of FastExport

1. FastExport EXPORTS data from Teradata.
2. FastExport only supports the SELECT statement.
3. Choose FastExport over BTEQ when Exporting Data of more than half a million+ rows
4. FastExport supports multiple SELECT statements and multiple tables in a single run
5. FastExport supports conditional logic, conditional expressions, arithmetic calculations, and data
6. conversions.
7. FastExport does NOT support error files or error limits.
8. FastExport supports user-written routines INMODs and OUTMODs

Sample fast export Script

.LOGTABLE Empdb.Emp_Table_log;
.LOGON TD/USERNAME,PWD;

BEGIN EXPORT
SESSIONS 12;

.IMPORT OUTFILE C:\TEMP\EMPDATA.txt
 FORMAT BINARY;

SELECT EMP_NUM    (CHAR(10)),
      ,EMP_NAME   (CHAR(50)),
      ,SALARY     (CHAR(10)),
      ,EMP_PHONE  (CHAR(10))
FROM Empdb.Emp_Table;
      )

.END EXPORT;
.LOGOFF;

FastExport Modes
FastExport has two modes: RECORD or INDICATOR
RECORD mode is the default, but you can use INDICATOR mode if required.
The difference between the two modes is INDICATOR mode will set the indicator bits to 1 for column values containing NULLS.

FastExport Formats
FastExport can export data in below formats

• FASTLOAD
• BINARY
• TEXT
• UNFORMAT

FASTLOAD

FASTLOAD

Fastload, the name itself telling that loads data in a fast way.That means it loads huge amount of data from flat file into EMPTY tables.

Manily FastLoad was developed to load millions of rows into empty Teradata tables so it is fast.
FastLoad will create a Teradata session for each AMP in order to maximize parallel processing.This gives awesome performance in loading data.

There are more reasons why FastLoad is so fast. Below are limitations of Fast load.
1) No Secondary Indexes are allowed on the Target Table: Usually UPI and NUPI are used in Teradata to distribute the rows evenly across the AMPs.Secondary indexes are stored in a subtable block and many times on a different AMP from the data row.

2)No Referential Integrity is allowed: The Referential Integrity defined on a table would take more system checking to prevent referential constraints.

3)No Triggers are allowed at load time: Fast load focused on data load with high speed. So triggers not allowed.

4)Duplicate Rows (in Multi-Set Tables) are not supported: Multiset tables are allowed duplicate data. Fastload can load the data into multiset tables but duplicate rows are discarded.

5)No AMPs may go down (i.e., go offline) while FastLoad is processing: The down AMP must be repaired before the load process can be restarted

6)No more than one data type conversion is allowed per column: Data type conversion cause high resource utilization on the system

Fastload requires mainly three components

Log table
Log table stores the processing record information during load.This table contains one row for every FastLoad running on the system

Empty Target table
As mentioned earlier target tables should be empty.

Error tables(two)
Each FastLoad requires two error tables. These are automatically created during run. These will populated only errors occurred during the load.

The first error table is for any translation errors or constraint violations
For example, if a column is defined as integer but the data from source the data is coming in CHAR format.i.e wrong data.

The second error table is for errors caused by duplicate values for Unique Primary Indexes.


FastLoad Has Two Phases:

FastLoad divides its job into two phases, both designed for speed.

Phase 1 or Acquisition Phase
The primary purpose of phase 1 is to get data from host computer into Teradata System.
The data moves in 64 K blocks and is stored in worktables on the AMPs.
The data is not stored in the correct AMP.

Phase 2 or Application Phase.
Once the data is moved from the server, each AMP will hash its worktable rows.
Each each row transfers to the worktables where they permanently resides
Rows of a table are stored on the disks in data blocks

Simple fastload Script:

1. Logging onto Teradata
2. Defining the Teradata table that you want to load (target table)
3. Defining the INPUT data file
4. Telling the system to start loading
5. Telling the system to to insert data into final target.
6. End session.

LOGON TDDB/USERNAME,PWD;

CREATE TABLE EMPDB.EMP_TABLE
( EMP_NUM     INTEGER
,DEPT_NUM     SMALLINT
,FIRST_NAME   CHAR(20)
,LAST_NAME    VARCHAR(20)
,SALARY       DECIMAL(8,2)
UNIQUE PRIMARY INDEX(EMP_NUM);

DEFINE  EMP_NUM    (INTEGER)
       ,DEPT_NUM   (SMALLINT)
       ,FIRST_NAME (CHAR(20))
       ,LAST_NAME  (VARCHAR(20))
       ,SALARY     (DECIMAL(8,2))
FILE=C:\TEMP\EMP_FILE.txt;

BEGIN LOADING EMPDB.EMP_TABLE
ERRORILES EMPDB.EMP_ERR1, EMPDB.EMP_ERR2
CHECKPOINT 10000000;

INSERT INTO EMPDB.EMP_TABLE
VALUES
( :EMP_NUM
  ,:DEPT_NUM
  ,:FIRST_NAME
  ,:LAST_NAME
  ,:SALARY );

END LOGOFF;
LOGOFF;

when can we RESTART fastlaod and cannot?

You Cannot RESTART FastLoad if

• The Error Tables are DROPPED
• The Target Table is DROPPED
• The Target Table is CREATED

You Can RESTART FastLoad

• The Error Tables are NOT DROPPED in the script
• The Target Table is NOT DROPPED in the script
• The Target Table is NOT CREATED in the script
• You have defined a checkpoint

Below are fast load commands used for creating fastload scripts:

AXSMOD to specify an access module (e.g., OLE-DB provider) that provides data to the FastLoad utility on network-attached client systems.

SESSIONS max min to specify the number of sessions. max = maximum number of sessions that will be logged on.

ERRLIMIT to control a runaway error condition, such as a mis-definition of the input data.
Specify the maximum number of error records you want to occur before the system issues an ERROR and terminates the load.

TENACITY to specify the number of hours FastLoad will try to establish a connection. The default is no tenacity.
The statement must be placed before LOGON.

SLEEP to specify the number of minutes FastLoad waits before retrying a logon.
The default is 6 minutes. The statement must be placed before LOGON.

MULTILOAD

MULTILOAD

MultiLoad has the capability to load multiple tables at one time from either a LAN or Channel environment. That why its names as MULTI LOAD.

The data load can perform multiple types of DML operations, including INSERT, UPDATE, DELETE and UPSERT on up to five (5) empty or populated target tables at a time.

Limitations of Multiload
Unique Secondary Indexes are not supported on a Target Table:Like FastLoad, MultiLoad does not support Unique Secondary Indexes (USIs). But unlike FastLoad, it does support the use of Non-Unique Secondary Indexes (NUSIs) because the index subtable row is on the same AMP as the data row.

Referential Integrity is not supported: The Referential Integrity defined on a table would take more system checking to prevent referential constraints.

Triggers are not supported at load time: Disable all the Triggers prior to using it.

No concatenation of input files is allowed: It could impact are restart if the files were concatenated in a different sequence or data was deleted between runs.

No Join Indexes: All the join indexes must be dropped before running a MultiLoad and then recreate them after the load is completed

Will not process aggregates, arithmetic functions or exponentiation:If you need data conversions or math, you might be better off using an INMOD to prepare the data prior to loading it.

Multiload requires mainly Four components

Log Table:Log table stores the processing record information during load.This table contains one row for every Multiload running on the system.

Work Table(s): MultiLoad will automatically create one worktable for each target table. Usually in IMPORT mode multiload could have one or more work tables and in DELETE moode you have ony one. The Purpose of work tables are 1) to perform DM tasks 2) APPLYing the input data to the AMPs.

Error Tables: Like Fastload, Multiload also two error tables
The first Error Table (ET). It contains all translation and constraint errors that may occur while the data is being acquired from the source(s)
The second Uniqueness Violation (UV) table that stores rows with duplicate values for Unique Primary Indexes (UPI).

Target table: Target tables can have data. Multiload can load the data where target table alredy loaded.

MultiLoad Has Five IMPORT Phases:

Phase 1: Preliminary Phase : Ita Basic setup phase.Its used for several preliminary set-up activities for a successful data load.
Phase 2: DML Transaction Phase: All the SQL Data Manipulation Language (DML) statements are sent  to Teradata database as Multilaod supports multiple DML functions.
Phase 3: Acquisition Phase: Once the setup completes the PE's plan stored on each AMP.Then Locks the table headers and the actual input data will also be stored in the worktable.
Phase 4: Application Phase: In this phase all DML opreations are applied on target tables.
Phase 5: Cleanup Phase: Table locks will be released and all the intermediate work tables will be dropped.

MultiLoad has full RESTART capability in all of its five phases of operation.

Sample Multiload Script:

The script on the following page follows these steps:

1. Setting up a Logtable
2. Logging onto Teradata
3. Identifying the Target, Work and Error tables
4. Defining the INPUT flat file
5. Defining the DML activities to occur
6. Naming the IMPORT file
7. Telling MultiLoad to use a particular LAYOUT
8. Telling the system to start loading
9. Finishing loading and logging off of Teradata

LOGTABLE  EMPDB.EMP_TABLE_LOG;

LOGON TDDB/USERNAME,PWD;

.BEGIN IMPORT MLOAD
TABLES EMPDB.EMP_TABLE
WORK TABLES EMPDB.EMP_WT
ERROR TABLE EMPDB.EMP_ET
            EMPDB.EMP_UV;

LAYOUT FILECOLDESC1;

.FIELD EMP_NUM  * INTEGER ;
.FIELD SALARY   * DECIMAL(8,2);

.DML LABLE EMP_UPD;
UPDATE EMPDB.EMP_TABLE
SET SALARY=:SALARY
WHERE EMP_NUM=:EMP_NUM;

.IMPORT INFILE C:\TEMP\MLOAD_FLAT_FILE.txt
LAYOUT FILECOLDESC1 
APPLY EMP_UPD;

.END MLOAD;
.LOGOFF;

TPUMP Utility

TPUMP

TPump is shortened name for Teradata Parallel Data Pump . As learned Fastload and Multiload are loads huge volume of data. But TPump loads data one row at a time, using row hash locks..
Because it locks at this level,and not at the table level like MultiLoad,TPump can make many simultaneous, or concurrent, updates on a table.

TPump performs Inserts,Upadtes,Deletes and Upserts from Flat filed to populated Teradata tables at ROW LEVEL.

TPump supports

• Secondary Indexes
• Referential Integrity
• Triggers
• Join indexes
• Pumpdata in at varying rates.

Tpump also have limitations.

• No concatenation of input data files is allowed.
• TPump will not process aggregates, arithmetic functions or exponentiation.
• The use of the SELECT function is not allowed.
• No more than four IMPORT commands may be used in a single load task.
• Dates before 1900 or after 1999 must be represented by the yyyy format for the year portion of the date, not the default format of yy.
• On some network attached systems, the maximum file size when using TPump is 2GB.
• TPump performance will be diminished if Access Logging is used.

Tpump supports One Error Table.The error table does the following:

• Identifies errors
• Provides some detail about the errors
• Stores a portion the actual offending row for debugging

Like the other utilities, a TPump script is fully restartable as long as the log table and error tables are not dropped

A Sample TPump Script

1. The script on the following page follows these steps:
2. Setting up a Logtable
3. Logging onto Teradata
4. Identifying the Target, Work and Error tables
5. Defining the INPUT flat file
6. Defining the DML activities to occur
7. Naming the IMPORT file
8. Telling TPump to use a particular LAYOUT
9. Telling the system to start loading
10. Finishing and log off of Teradata

LOGTABLE  EMPDB.EMP_TPUMP_LOG;

LOGON TDDB/USERNAME,PWD;

BEGIN LOAD
PACK 5
RATE 10
ERROR TABLE EMPDB.TPUMPERROR;

.LAYOUT RECLAYOUT;

.FIELD    EMP_NUM         * INTEGER;
.FIELD    DEPT_NUM       * SMALLINT;
.FIELD    FIRST_NAME    * CHAR(20);
.FIELD    LAST_NAME     * VARCHAR(20);
.FIELD    SALARY            * DECIMAL(8,2);

.DML LABEL EMP_INS;
INSERT INTO EMPDB.EMP_TABLE
(EMP_NUM, DEPT_NUM, FIRST_NAME, LAST_NAME, SALARY)
VALUES 
(:EMP_NUM, :DEPT_NUM, :FIRST_NAME, :LAST_NAME, :SALARY)

.IMPORT INFINE C:\TEMP\TPUMP_FLAT_FILE.txt;

LAYOUT RECLAYOUT
APPLY EMP_INS

.END LOAD;
.LOGOFF;

Teradata Parallel Transporter (TPT)

The Teradata Parallel Transport (TPT) utility is combination of BTEQ, FastLoad, MultiLoad, Tpump, and FastExport utilities. So TPT can perform

• Insert data to tables
• Export data from tables
• Update tables

TPT works around the concept of Operators and Data Streams.

TPT

In the following diagram showing you mainly three components.

1. Producer or READ Operator
2. Filter Operator or TRANSFORM Operator 
3. Consumer Operator or WRITE Operator

The Producer Operator performs read the Queues, Files, Relational Databases, and Non-Relational Sources.
The Filter Operators Transforms data from INMODs, WHERE Clauses, APPLY Filters, and User-Defined functions.
The Consumer Operator performs INSERTS (Load), Updates, SQL Inserts, and Tpump like Streams.

How to Run a TPT Script

The easiest way to run a TPT script is to use the TBuild utility. You first create your script and then run TBuild, passing
TBuild the script name to run.

In the below example just create a sample script called Scriptname.txt(this is not complete script)
Then run by using TBuild –f command.

1) Create a script

DEFINE JOB CREATE_SOURCE_EMP_TABLE

                          (

DEFINE OPERATOR DDL OPERATOR 

DESCRIPTION 'TPT DDL OPERATOR'

                 TYPE DDL

             ATTRIBUTES

                        (

VARCHAR TDPID = 'LOCALTD',

VARCHAR USERNAME ='DBC', 

VARCHAR PASSWORD ='DBC',

);

2) Using the command prompt run TBuild command

   TBuild -f C:\Temp\Scriptname.txt

Temporary tables

Temporary tables

Teradata has implemented three types of temporary tables.

• Derived tables
• Volatile temporary tables
• Global temporary tables

What is the reasons to implement Temporary Tables? 

Temporary tables are an additional tool that assist us in performing SQL operations that either:
  >Might not be possible against a normalized table, or
  >May require multiple SQL statements to complete.

They are some very useful for demoralizations such as:
  >Summary tables.
  >Repeating groups.

And they may also be used for intermediate results which are needed:
  >Frequently, or
  >On an on-going basis.

Derived Tables:
Below are the characteristics of Derived Tables:

• Derived tables are local to the query that means it exists only for the duration of the query.
• The table is automatically discarded once the query is done.
• Spooled rows, which populate the table, are also discarded when query finishes
• It is incorporated into SQL query syntax
• There is no data dictionary involvement 

Below is the example for Derived tables.

SELECT T.PROD_ID, T.SUM_SALES, RANK(T.SUM_SALES)
FROM (SELECT PROD_ID, SUM(SALES) FROM SALES_TBL
         GROUP BY 1) AS T(PROD_ID, SUM_SALES)
QUALIFY RANK(SUM_SALES)<=3; 

In the above query T is the Derived Table.PROD_ID and SUM_SALES are the derived columns.


Volatile Temporary Tables:
Below are the characteristics of Volatile Temporary Tables

• Volatile Temporary Tables are exists throughout the entire session, not just a single query
• It must be explicitly created using the CREATE VOLATILE TABLE syntax. 
• It is discarded automatically at the end of the session or can be dropped manually anytime.
• There is no data dictionary involvement. 
• Can be used with multiple queries in the session.
• Volatile Tables are designed for  performance improvement.
• Can do collect statistics.

Below is the example for Volatile tables.

CREATE VOLATILE TABLE vt_deptsal, LOG
(deptno SMALLINT
,avgsal   DEC(9,2)
,maxsal   DEC(9,2)
,minsal   DEC(9,2)
,sumsal   DEC(9,2)
,empcnt   SMALLINT)
ON COMMIT PRESERVE ROWS; 

ON COMMIT DELETE ROWS means the data is deleted when the query is committed.This statement allows us to use the volatile table again for other queries in the session

Limitations on Volatile Tables:

• CREATE/DROP INDEX
• ALTER TABLE
• GRANT/REVOKE privileges
• DELETE DATABASE/USER (does not drop volatile tables)

Global Temporary Tables:
Below are the characteristics of Global Temporary Tables

• By using CREATE GLOBAL TEMPORARY command we used to create. 
• The main difference between a global temporary table and a volatile temporary table is that the global table definition stored in data dictionar(DD)
• Each instance of a global temporary table is local to a session
• Materialized tables are dropped automatically at the end of the session
• A user can materialize up to 2,000 global tables per session.
• COLLECTing STATISTICS can be possible on Global Temporary tables 
• Secondary indexes can be added to a Global Temporary tables

Below is the example for Global Temporary Tables

CREATE SET GLOBAL TEMPORARY TABLE PED.gt_deptsal
,NO FALLBACK,   LOG
  (
  deptno SMALLINT,
  avgsal DECIMAL(9,2),
  maxsal DECIMAL(9,2),
  minsal DECIMAL(9,2),
  sumsal DECIMAL(9,2),
  empcnt SMALLINT)
PRIMARY INDEX (deptno)
ON COMMIT PRESERVE ROWS; 

The ON COMMIT DELETE ROWS clause is the default.. If you want to use the command ON COMMIT PRESERVE ROWS, you must specify that in the CREATE TABLE statement

Performance Guide 3

COLLECT STATISTICS

The Purpose of  COLLECT STATISTICS is to gather and store demographic data for one or more columns or indices of a table or join index.In this process it collects data and stores the summary in the Data Dictionary (DD) inside USER DBC.
The optimizer uses this synopsis data to generate efficient table access and join plans.

Below are the statistics will collect

• The number of rows in the table
• The average row size
• Information on all Indexes in which statistics were collected
• The range of values for the column(s) in which statistics were collected
• The number of rows per value for the column(s) in which statistics were collected
• The number of NULLs for the column(s) in which statistics were collected

What to COLLECT STATISTICS On?

Do NOT COLLECT Statistics on all columns and indexes because it takes too much space.

• Primary Index of a Join Index
• Secondary Indexes defined on any join index
• Non-indexed columns used in joins
• The Unique Primary Index of small tables (less than 1,000 rows per AMP)
• All Non-Unique Primary Indexes and All Non-Unique Secondary Indexes
• Join index columns that frequently appear on any additional join index columns that frequently appear in WHERE search conditions
• The Unique Primary Index of small tables (less than 1,000 rows per AMP)
• Columns that frequently appear in WHERE search conditions or in the WHERE clause of joins

Syntax for COLLECT STATISTICS

COLLECT STATISTICS on COLUMN ;
COLLECT STATISTICS on INDEX ();

Examples for a better Understanding

COLLECT STATISTICS on Emp_Table ;
COLLECT STATISTICS on Emp_Table COLUMN Dept_no ;
COLLECT STATISTICS on Emp_Table COLUMN(Emp_no, Dept_no);
COLLECT STATISTICS on Emp_Table INDEX Emp_no ;
COLLECT STATISTICS on Emp_Table INDEX (First_name, Last_name);

The New Teradata V14 Way to Collect Statistics

COLLECT STATISTICS COLUMN(First_Name, Last_Name) 

                                         ,COLUMN(First_Name)
                                         ,COLUMN(Dept_No) ON Employee_Table;

With the latest approach, Only a single table scan is required instead multiple table scan like old approach.

Teradata keeps all Collect Statistics information in DBC in the Data Dictionary tables. Those are

1. DBC.Indexes (for multi-column indexes only)
2. DBC.TVFields (for all columns and single column indexes)
3. DBC.StatsTbl (Teradata V14 and beyond)

Collect Statistics on a PPI Table on the Partition

COLLECT STATISTICS on Order_Table_PPI COLUMN PARTITION

The main reasons to collect stats on Partiton are

• PE will give better plan for PPI tables
• It helps on  Partition Elimination on Range Queries.
• Mainly helpful when a table has a lot of empty partitions.

Teradata V14 Statistics Enhancements

1) There is a SUMMARY option to collect table-level statistics
Table-level statistics known as "summary statistics" are collected whenever column or index statistics are collected.

SHOW SUMMARY STATISTICS VALUES ON Employee_Table;

One critical advantage is that the optimizer now uses summary stats to get the most up-to-date row count from the table in order to provide more accurate extrapolations

2) SYSTEM SAMPLE option allows the system to determine the sampled system percentage.
SAMPLE n PERCENT allows you to specify sampling at the individual statistics collection level rather than at the system level
COLLECT STATISTICS    USING Sample 20 Percent   COLUMN (Last_Name) ON Employee_Table;

3) Statistics are stored in DBC.StatsTbl to reduce access contention and improve performance.

4) New views DBC.StatsV, DBC.ColumnStatsV, DBC.MultiColumnStatsV, and IndexStatsV.

5) SHOW STATISTICS statement reports detailed statistics in plain text or XML formatting.

6) MaxValueLength
Teradata only placed the first 16 bytes in the statistics before Version 14 but now the default is 25 bytes.
COLLECT STATISTICS USING MAXVALUELENGTH 50  COLUMN (Item_Name)  ON Shipping_Table  

7) MaxIntervals 

Whenever you collected statistics, Teradata did a full table scan on the values, sorted them, and then placed them into 200 intervals. Now, the default is 250 intervals

8)Expressions can now be specified in COLLECT STATS statements

COLLECT STATISTICS USING SYSTEM SAMPLE COLUMN (EXTRACT(MONTH FROM Order_Date), Order_Total) AS Month_Plus_Total ON Order_Table;Also use Substr(), MOD, CONCATENATION, Format Conversion expressions like Uppercase, Lowercase, Data type Conversions, CASE-Expressions, BEGIN and END expressions of PERIOD types.

9)AutoStats feature
Teradata V14.10 Autostats identifies and collects missing statistics needed and detects stale statistics for refreshing. If statistics are not being used by optimizer, they will be removed.
A new repository of a system supplied database named TDSTATS stores metadata for all stats collections. This is created by a new process called DIPSTATS.

10) External stored procedures (XSPs) perform the stats management process.

Performance Guide 2

Explain Plan

What is EXPLAIN?
EXPLAIN statement is the Parsing Engines (PE’s) plan to the AMPs.A step-by-step
analysis of the queries being executed in the database.
The EXPLAIN facility provides an "English" translation of the plan the SQL Optimizer develops to service a request.

The EXPLAIN is an excellent facility for the following activities:
• Determining Access Paths
• Validating the use of indexes
• To determine locking profiles
• Showing Triggers and Join Index access
• Estimate the query runtime

Sample Syntax for Explain is.
EXPLAIN Select Emp_ID, Emp_Name from Emp_Table ;

If we pass an explain command in front of a query statement or Simply Press F6 key, there are a several estimated confidence messages that the optimizer will relay to the user is

High Confidence - Statistics available on an Index or Column.
Low Confidence - Random sampling of INDEX, or Statistics available butAND/OR condition exists.
No Confidence - Random sampling based on AMP row count. No statistics are collected.

Understanding the EXPLAIN Terminology you should know the Keywords.

Keyword	Explanation
Locking Pseudo Table	Serial lock on a symbolic table. Every table has one. Used to prevent deadlocks situations between users.
Locking table for	Indicates that an ACCESS, READ, WRITE, or EXCLUSIVE lock has been placed on the table
Locking rows for	Indicates that an ACCESS, READ, or WRITE, lock is placed on rows read or written
Do an ABORT test	Guarantees a transaction is not in progress for this user
All AMPs retrieve	All AMPs are receiving the AMP steps and are involved in providing the answer set
By way of an all rows scan	Rows are read sequentially on all AMPs
By way of primary index	Rows are read using the Primary index column(s)
By way of index number	Rows are read using the Secondary index – number from HELP INDEX
BMSMS	Bit Map Set Manipulation Step, alternative direct access technique when multiple NUSI columns are referenced in the WHERE clause
Residual conditions	WHERE clause conditions, other than those of a join
Eliminating duplicate rows	Providing unique values, normally result of DISTINCT, GROUP BY or subquery
Where unknown comparison will be ignored	Indicates that NULL values will not compare to a TRUE or FALSE. Seen in a subquery using NOT IN or NOT = ALL because no rows will be returned on ignored comparison.
Nested join	The fastest join possible. It uses a UPI to retrieve a single row after using a UPI or a USI in the WHERE to reduce the join to a single row.
Merge join	Rows of one table are matched to the other table on common domain columns after being sorted into the same sequence, normally Row Hash
Product join	Rows of one table are matched to all rows of another table with no concern for domain match
ROWID join	A very fast join. It uses the ROWID of a UPI to retrieve a single row after using a UPI or a USI in the WHERE to reduce the join to a single row.
Duplicated on all AMPs	Participating rows for the table (normally smaller table) of a join are duplicated on all AMPS
Hash redistributed on all AMPs	Participating rows of a join are hashed on the join column and sent to the same AMP that stores the matching row of the table to join
SMS	Set Manipulation Step, result of an INTERSECT, UNION, EXCEPT or MINUS operation
Last use	SPOOL file is no longer needed after the step and space is released
Built locally on the AMPs	As rows are read, they are put into SPOOL on the same AMP
Aggregate Intermediate Results computed locally	The aggregation values are all on the same AMP and therefore no need to redistribute them to work with rows on other AMPs
Aggregate Intermediate Results computed globally	The aggregation values are not all on the same AMP and must be redistributed on one AMP, to accompany the same value with from the other AMPs

Below are the some examples

Example for FULL TABLE SCAN    

EXPLAIN SELECT * FROM Emp_Table;

1. First, we lock a distinct SQL_CLASS."pseudo table" for read on a RowHash to prevent global deadlock for SQL_CLASS.Emp_Table.

2. Next, we lock SQL_CLASS.Emp_Table for read.

3. We do an all-AMPs RETRIEVE step from SQL_CLASS.Emp_Table by way of an all-rows scan with no residual conditions into Spool 1 (group_amps), which is built locally on the AMPs. The size of Spool 1 is estimated with low confidence to be 6 rows (342 bytes). The estimated time for this step is 0.03 seconds.

4. Finally, we send out an END TRANSACTION step to all AMPs involved in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.03 seconds.

If you see all-AMPs RETRIEVE by way of an all-rows scan in your plan, that means teradata is doing a Full Table Scan. So it is reading every row in the table.


Explain Example – Unique Primary Index (UPI)
 EXPLAIN SELECT * FROM Emp_Table WHERE Emp_No = 123000;

 1. First, we do a single-AMP RETRIEVE step from SQL_CLASS.Emp_Table by way of the unique primary index "SQL_CLASS.Emp_Table.Emp_No = 123000" with no residual conditions. The estimated time for this step is 0.01 seconds.
-> The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.01 seconds.

In the Emp_table, Emp_no is the Unique Primary Index. So USI used in where clause. So it gives Single-AMP retrieve by way of the Unique Primary Index. This is the fastest query!

Explain Example – Non-Unique Primary Index (NUPI)
 EXPLAIN SELECT * FROM Sales_Table WHERE Product_ID = 1000;

 1. First, we do a single-AMP RETRIEVE step from SQL_CLASS.Sales_Table by way of the primary index"SQL_CLASS.Sales_Table.Product_ID = 1000" with no residual conditions into Spool 1 (one-amp), which is built locally on that AMP. The size of Spool 1 is estimated with low confidence to be 2 rows (66 bytes). The estimated time for this step is 0.02 seconds.
-> The contents of Spool 1 are sent back to the user as the result of statement 1. The total estimated time is 0.02 seconds.

Explain Example – Unique Seconday Index (USI)
Explain SELECT * FROM Emp_Table WHERE
First_name="Sachin" and
Last_name=""tendulkar"
1. First, we do a two-AMP RETRIEVE step from SQL_CLASS.Emp_Table by way of unique index # 12 "SQL_CLASS.Emp_Table.Last_name = 'tendulkar ' , SQL_CLASS.Emp_Table.First_name = 'Sachin'" with no residual conditions. The estimated time for this step is 0.01 seconds.
->The row is sent directly back to the user as the result of statement 1. The total estimated time is 0.01 seconds.

Explain Example – Redistributed to All-AMPs
 
  EXPLAIN SELECT Emp.*, Dept**
         FROM Emp_Table as Emp
           INNER JOIN
          Department_Table as Dept
          ON Emp.Dept_No = Dept.Dept_No;

 4. We do an all-AMPs RETRIEVE step from SQL_CLASS.Emp by way of an all-rows scan with a condition of ("NOT (SQL_CLASS.Emp.Dept_No IS NULL)") into Spool 2 (all_amps), which is redistributed by the hash code of (SQL_CLASS.Emp.Dept_No) to all AMPs. Then we do a SORT to order Spool 2 by row hash. The size of Spool 2 is estimated with low confidence to be 6 rows (294 bytes). The estimated time for this step is 0.01 seconds.
 
5 We do an all-AMPs JOIN step from SQL_CLASS.Dept by way of a RowHash match scan, which is joined to Spool 2 (Last Use) by way of a RowHash match scan. SQL_CLASS.Dept and Spool 2 are joined using a merge join, with a join condition of ("Dept_No = SQL_CLASS.Dept.Dept_No").

Partitioned Primary Index (PPI )

Partitioned Primary Index

Partitioned Primary Index (PPI) is an indexing mechanism in Teradata Database.

• PPI is used to improve performance for large tables when you submit queries that specify a range constraint.
• PPI allows you to reduce the number of rows to be processed by using partition elimination.
• PPI will increase performance for incremental data loads, deletes, and data access when working with large tables with range constraints

Lets take Order_Table, where we have both January and February dates in column Order_Date.

The Order_Table spread across the AMPs.Notice that January and February dates are mixed on every AMP in what is a random order. This is because the Primary Index is Order_Number.

When we apply Range Query, that means it uses the keyword BETWEEN.

The BETWEEN keyword in Teradata means find everything in the range BETWEEN this date and this other date. We had no indexes on Order_Date so it is obvious the PE will command the AMPs to do a Full Table Scan. To avoid full table scan, we will Partition the table.

After Partitioned Table,
The example of AMPs on the top of the page. This table is not partitioned.

The example of AMPs on the bottom of the page. This table is partitioned.

Each AMP always sorts its rows by the Row-ID in order to do a Binary Search on Primary Index queries.

Notice that the rows on an AMP don‘t change AMPs because the table is partitioned. Remember it is the Primary Index alone that will determine which AMP gets a row. If the table is partitioned then the AMP will sort its rows by the partition.

Now we are running our Range Query on our Partitioned Table,each AMP only reads from one partition. The Parsing Engine will not to do full table scan. It instructs the AMPs to each read from their January Partition. You Partition a Table when you CREATE the Table.

A Partitioned Table is designed to eliminate a Full Table Scan, especially on Range Queries.

Types of partitioning:

RANGE_N Partitioning

Below is the example for RANGE_N Partition by day.
CREATE TABLE ORDER_TABLE

(

ORDER_NO INTEGER NOT NULL,

CUST_NO INTERGER,

ORDER_DATE DATE,

ORDER_TOTAL DECIMAL(10,2)

)

PRIMARY INDEX(ORDER_NO)

PARTITION BY RANGE_N

(ORDER_DATE BETWEEN

DATE '2012-01-01' AND DATE '2012-12-31'

EACH INTERVAL '7' DAY);

Case_N Partitioning
CREATE TABLE ORDER_TABLE

(

ORDER_NO INTEGER NOT NULL,

CUST_NO INTERGER,

ORDER_DATE DATE,

ORDER_TOTAL DECIMAL(10,2)

)

PRIMARY INDEX(ORDER_NO)

PARTITION BY CASE_N

(ORDER_TOTAL < 1000,

 ORDER_TOTAL < 2000,

 ORDER_TOTAL < 5000,

 ORDER_TOTAL < 10000,

 ORDER_TOTAL < 20000,

 NO CASE, UNKNOWN);

The UNKNOWN Partition is for an Order_Total with a NULL value. The NO CASE Partition is for partitions that did not meet the CASE criteria.

For example, if an Order_Total is greater than 20,000 it wouldn‘t fall into any of the partitions so it goes to the NO CASE partition.

Multi-Level Partitioning:
You can have up to 15 levels of partitions within partitions.

CREATE TABLE ORDER_TABLE

(

ORDER_NO INTEGER NOT NULL,

CUST_NO INTERGER,

ORDER_DATE DATE,

ORDER_TOTAL DECIMAL(10,2)

)

PRIMARY INDEX(ORDER_NO)

PARTITION BY (RANGE_N

(ORDER_DATE BETWEEN

DATE '2012-01-01' AND DATE '2012-12-31'

EACH INTERVAL '1' DAY)

CASE_N (ORDER_TOTAL < 5000,

 ORDER_TOTAL < 10000,

 ORDER_TOTAL < 15000,

 ORDER_TOTAL < 20000,

 NO CASE, UNKNOWN));

 Character Based Partitioning(New Feature V13.10) :
There are four new data types available for Character Based PPI. They are CHAR, VARCHAR, GRAPHIC, and VARGRAPHIC.

Example for RANGE Based Character PPI

  

 CREATE TABLE EMP_TBL

 (

 EMP_NO INTEGER NOT NULL,

 DEPT_NO INTEGER,

 FIRST_NAME CHAR(20),

 LAST_NAME VARCHAR(20),

 SALARY DECIMAL(10,2),

 ) PRIMARY INDEX(EMP_NO)

 PARTITION BY RANGE_N

(LAST NAME BETWEEN ( 'A ','B ','C ','D ','E ','F ','G ','H ',

'I ','J ','K ','L ','M ','N ','O ','P ','Q ','R ','S ','T ',

'U ','V ','W ','X ','Y ','Z ' AND 'ZZ',UNKNOWN));

Example for CASE Based Character PPI

CREATE TABLE PRODUCT_TBL

(PRODUCT_ID INTEGER NOT NULL

,PRODUCT_NAME CHAR(30)

,PRODUCT_COST DECIMAL(10,2)

,PRODUCT_DESCRIPTION VARCHAR(100)

)PRIMARY INDEX(PRODUCT_ID)

PARTITION BY CASE_N

(PRODUCT_NAME < 'Apples'

PRODUCT_NAME < 'Bananas'

PRODUCT_NAME < 'Cantaloupe'

PRODUCT_NAME < 'Grapes'

PRODUCT_NAME < 'Lettuce'

PRODUCT_NAME < 'Mangos'

PRODUCT_NAME >='Mangos' and <='Tomatoes');

Ex-Query: Find all Products between Apples and Grapes?

Ans: SELECT * FROM PRODUCT_TBL WHERE PRODUCT_NAME BETWEEN 'Apples' and 'Grapes';

Partitioning Rules:

• A table can have up to 65,535 Partitions.
• Partitioning never determines which AMP gets row.
• Partitioning determines how an AMP will sort the row on its own.
• Table can have up to 15 levels of partitions.
• A table cannot have an UPI as the Primary Index if the Partition table does not include PI.
• Total 3 forms of Partitioning Simple, RANGE and CASE.

Advantages of partitioned tables:

• They provide efficient searches by using partition elimination at the various levels or combination of levels.
• They reduce the I/O for range constraint queries
• They take advantage of dynamic partition elimination
• They provide multiple access paths to the data, and an MLPPI provides even more partition elimination and more partitioning expression choices, (i.e., you can use last name or some other value that is more readily available to query on.)
• The Primary Index may be either a UPI or a NUPI; a NUPI allows local joins to other similar entities
• Row hash locks are used for SELECT with equality conditions on the PI columns.
• Partitioned tables allow for fast deletes of data in a partition.
• They allow for range queries without having to use a secondary index.
• Specific partitions maybe archived or deleted.
• May be created on Volatile tables; global temp tables, base tables, and non-compressed join indexes.
• May replace a Value Ordered NUSI for access.

Disadvantages of partitioned tables: 
•Rows in a partitioned table are 2 bytes longer.
•Access via the Primary Index may take longer.
•Full table joins to a NPPI table with the same PI may take longer.  

Fallback

Fallback

Fallback is a Teradata Database feature that protects data in the case of an AMP vproc failure.
Fallback protection can be done at the table or database level.
It is especially useful in applications that require high availability.

Fallback protects your data by storing a second copy of each row of a table on a different AMP in the same cluster.
If an AMP fails, the system accesses the Fallback rows to meet requests.
Fallback provides AMP fault tolerance at the table level.
With Fallback tables, if one AMP fails, all data is still available. Users may continue to use Fallback tables without any loss of access to data.

During table creation or after a table is created, you may specify whether or not the system should keep a Fallback copy of the table.
If Fallback is specified, it is automatic and transparent.

Fallback guarantees that the two copies of a row will always be on different AMPs. If either AMP fails, the alternate row is still available on the other AMP.

 Fallback will give effect on you system mainly on
 Space:Need extra space for Fallback-protected tables in addition to the original database size.
 RAID protection of Fallback-protected tables

Performance:Need twice the disk space for storage and twice the I/O required for INSERTs, UPDATEs, and DELETEs of rows in Fallback protected tables. The Fallback option does not require any extra I/O for SELECTS, as the system will read from one copy or the other, and the Fallback I/O will be performed in parallel with the primary I/O so there is no performance hit.

Fallback benefits:

• A level of protection beyond RAID disk array protection.
• Can be specified on a table-by-table basis to protect data requiring the highest availability.
• Permits access to data while an AMP is off-line.
• Automatically restores data that was changed during the AMP off-line period. 

How It Works: 
Fallback is accomplished by grouping AMPs into clusters.
When a table is defined as Fallback-protected, the system stores a second copy of each row in the table on a "Fallback AMP" in the AMP cluster.

Below is a cluster of four AMPs. Each AMP has a combination of Primary and Fallback data rows:
Primary Data Row: A record in a database table that is used in normal system operation.
Fallback Data Row: The online backup copy of a Primary data row that is used in the case of an AMP failure.

Write: Each Primary data row has a duplicate Fallback row on another AMP. The Primary and Fallback data rows are written in parallel.

Read: When an AMP is down with a table that is defined as Fallback, Teradata will access the Fallback copies of the rows.

More Clusters: The diagram below shows how Fallback data is distributed among multiple clusters.

How It Handles Failures

If two physical disks fail in the same RAID 5 rank or RAID 1 mirrored pair, the associated AMP vproc fails. Fallback protects against the failure of a single AMP in a cluster.
If two AMPs in a cluster fail, the system halts and must be restarted manually, after the AMP is recovered by replacing the failed disk(s).

Reads: When an AMP fails, the system reads all rows it needs from the remaining AMPs in the cluster. If the system needs to find a Primary row from the failed AMP, it reads the Fallback copy of that row, which is on another AMP.

Writes: A failed AMP is not available, so the system cannot access any of that AMP's disk space. Copies of its unavailable primary rows are available as Fallback rows on the other AMPs in the cluster, and are updated there.

Replacement: Repairing the failed AMP requires replacing the failed physical disks and bringing the AMP online. Once the AMP is online, the system uses the Fallback data on the other AMPs to automatically reconstruct data on the newly replaced disks. 

Hot Sandby Node (HSN)

Hot Standby Nodes

Hot Standby Nodes

A Hot Standby Node (HSN) is a node that is a member of a clique that is not configured (initially) to execute any Teradata vprocs. If a node in the clique fails, the AMPs from the failed node move to the hot standby node. The performance degradation is 0%.

When the failed node is recovered/repaired and restarted, it becomes the new hot standby node. A second restart of Teradata is not needed.

Characteristics of a hot standby node are:

• A node that is a member of a clique. 
• Does not normally participate in the trusted parallel application (TPA). 
• Can be brought into the configuration when a node fails in the clique. 
• Helps with unplanned outages. 
• Eliminates the need for a restart to bring a failed node back into service. 

Notice in our picture that our first node is down, but that the AMPs and PEs migrated to our Hot Standby Node!

Cliques

Cliques

A clique (pronounced, "kleek") is a group of nodes that share access to the same disk arrays. Each multi-node system has at least one clique. The cabling determines which nodes are in which cliques -- the nodes of a clique are connected to the disk array controllers of the same disk arrays.

 
In the event of a node failure, cliques provide for data access through vproc migration. When a node resets, the following happens to the AMPs:

• When the node fails, the Teradata Database restarts across all remaining nodes in the system.
• The vprocs (AMPs) from the failed node migrate to the operational nodes in its clique.
• The PE vprocs will migrate as follows: LAN attached PEs will migrate to other nodes in the clique. Channel attached PEs will not migrate. While that node remains down, that channel connection is not available.
• Disks managed by the AMP remain available and processing continues while the failed node is being repaired.

 
Cliques in a System
 
Vprocs are distributed across all nodes in the system. Multiple cliques in the system should have the same number of nodes.

The diagram below shows three cliques. The nodes in each clique are cabled to the same disk arrays. The overall system is connected by the BYNET. If one node goes down in a clique the vprocs will migrate to the other nodes in the clique, so data remains available. However, system performance decreases due to the loss of a node. System performance degradation is proportional to clique size.

Types Of Journals

Recovery Journal
The Teradata Database uses Recovery Journals to automatically maintain data integrity in the case of:
*An interrupted transaction (Transient Journal)
*An AMP failure (Down-AMP Recovery Journal)

Recovery Journals are created, maintained, and purged by the system automatically, so no DBA intervention is required. Recovery Journals are tables stored on disk arrays like user data is, so they take up disk space on the system.

Transient Journal 
A Transient Journal maintains data integrity when in-flight transactions are interrupted (due to aborted transactions, system restarts, and so on). Data is returned to its original state after transaction failure.

A Transient Journal is used during normal system operation to keep "before images" of changed rows so the data can be restored to its previous state if the transaction is not completed. This happens on each AMP as changes occur. When a transaction is started, the system automatically stores a copy of all the rows affected by the transaction in the Transient Journal until the transaction is committed (completed). Once the transaction is complete, the "before images" are purged. In the event of a transaction failure, the "before images" are reapplied to the affected tables and deleted from the journal, and the "rollback" operation is completed.

Down-AMP Recovery Journal 
The Down-AMP Recovery Journal allows continued system operation while an AMP is down (for example, when two disk drives fail in a rank or mirrored pair). A Down-AMP Recovery Journal is used with Fallback-protected tables to maintain a record of write transactions (updates, creates, inserts, deletes, etc.) on the failed AMP while it is unavailable.

The Down-AMP Recovery Journal starts automatically after the loss of an AMP in a cluster, Any changes to the data on the failed AMP are logged into the Down-AMP Recovery Journal by the other AMPs in the cluster. When the failed AMP is brought back online, the restart process includes applying the changes in the Down-AMP Recovery Journal to the recovered AMP. The journal is discarded once the process is complete, and the AMP is brought online, fully recovered.

Permanent Journals
Permanent Journals are an optional feature used to provide an additional level of data protection. You specify the use of Permanent Journals at the table level. It provides full-table recovery to a specific point in time. It can also reduce the need for costly and time-consuming full-table backups.

Permanent Journals are tables stored on disk arrays like user data is, so they take up additional disk space on the system. The Database Administrator maintains the Permanent Journal entries (deleting, archiving, and so on.)

How Permanent Journals Work

A Database (object) can have one Permanent Journal.

When you create a table with Permanent Journaling, you must specify whether the Permanent Journal will capture:
*Before images -- for rollback to "undo" a set of changes to a previous state.
*After images -- for rollforward to "redo" to a specific state.

You can also specify that the system keep both before images and after images. In addition, you can choose that the system captures:
*Single images (the default) -- this means that the Permanent Journal table is not Fallback protected.
*Dual images -- this means that the Permanent Journal table is Fallback protected.

The Permanent Journal captures images concurrently with standard table maintenance and query activity. The additional disk space required may be calculated in advance to ensure adequate resources. Periodically, the Database Administrator must dump the Permanent Journal to external media, thus reducing the need for full-table backups since only changes are backed up rather than the entire database.