Teradata Wiki

Columnar

2016-06-30T09:30:00.001+05:30

The purpose of a Columnar(NoPI) table is to spread the rows evenly across the AMPs. This is why a NoPI table is often used as a staging table.

Columnar Table Fundamentals

Columnar Tables must be a NoPI Table so No Primary Index (NoPI).
The NoPI brings even distribution to the table.
Columnar Tables allow Columns to be Partitioned.
An AMP still holds the entire row, but partitions vertically.
Columns are placed inside their own individual Container.
All Containers have the same amount of rows in the exact order.
Single Columns or Multi-Columns can be placed inside containers.
Each container looks like a small table for I/O purposes.
Add up all the containers and you rebuild the row.
Columnar Tables make sense when users query only certain columns.
When a row is deleted it is NOT Physically Deleted but marked deleted

Normal table vs columnar table
The two tables above contain the same Employee data, but one is a columnar table. Employee_Normal has placed 3 rows on each AMP with 5 columns. The other table Employee_Columnar has 5 Containers each with one column.

Add caption

Example
CREATE Table Employee
(
Emp_Id Integer
,Dept_Id Integer
,First_Name Varchar(20)
,Last_Name Char(20)
,Salary Decimal (10,2)
)
No Primary Index
PARTITION BY COLUMN;

Columnar table

This AMP is assigned 3 Employee Rows
All AMPs hold 3 different Employee Rows also
Each Row has 5 Columns
This Columnar Table partitions in 5 separate containers
Each container has a relative row number (1, 2, 3)
Each container has the exact same number of rows

NoPI Table Capabilities:

Are always Multi-Set Tables
Have Secondary Indexes (USI or NUSI)
Have Join Indexes
Be Volatile or Global Temporary Tables
Can COLLECT STATISTICS
Be FALLBACK Protected
Have Triggers
Be Large Objects (LOBs)
Have Primary Key Foreign Key Constraint

NoPI Table Restrictions

No Primary Indexes allowed
No SET Tables
No Partition Primary Index (PPI) tables
No Queue Tables
No Hash Indexes
No Identity Columns
No Permanent Journaling
Can't be the Target Table for any UPDATE, UPSERT or MERGE-INTO Statements

Examples of Columnar tables

Multi-Columnar

CREATE Table Employee
(
Emp_Id Integer
,Dept_Id Integer
,First_Name Varchar(20)
,Last_Name Char(20)
,Salary Decimal (10,2)
)
No Primary Index
PARTITION BY COLUMN
(Emp_Id
,Dept_id
(,First_name, Last_name, Salary));

Row Hybrid Columnar

CREATE Table Employee
(
Emp_Id Integer
,Dept_Id Integer
,First_Name Varchar(20)
,Last_Name Char(20)
,Salary Decimal (10,2)
)
No Primary Index
PARTITION BY COLUMN
(Emp_Id No Auto Compress
,Dept_id
(,First_name, Last_name, Salary)
No Auto Compress);

Columnar Partitions

Crete Table Order_table_PPI_Col
(Order_no integer not null
,Customer_no integer
,Order_date date
,Order_total decimal(10,2)
)
NO PRIMARY INDEX
PARTITION BY(Column
,Range_N(Order_date between date '2015-01-01 AND date '2015-12-31'
EACH INTERVAL '1' Month));

Creating Tables

2015-09-08T06:16:00.001+05:30

Creation Type	SQL
Creating a Table with a Unique Primary Index	CREATE TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX (EMP_NO);
Creating a Table with a Non-Unique Primary Index	CREATE TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) PRIMARY INDEX(EMP_NO);
Creating a Table without entering a Primary Index	CREATE TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) )
Creating a Table with NO Primary Index	CREATE TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) NO PRIMARY INDEX;
Creating a Set Table	CREATE SET TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) PRIMARY INDEX(EMP_NO);
Creating a Multiset Table	CREATE MULTISET TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) PRIMARY INDEX(EMP_NO);
Creating a Set Table with a Unique Primary Index	CREATE SET TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO);
Creating a Set Table with a Unique Secondary Index	CREATE SET TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) PRIMARY INDEX(EMP_NO) UNIQUE INDEX(DEPT_NO);
Creating a Table with an UPI and USI	CREATE SET TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO) UNIQUE INDEX(DEPT_NO);
Creating a Table with a Multicolumn Primary Index	CREATE TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) PRIMARY INDEX(EMP_NO,DEPT_NO,SALARY);
Creating a Unique Secondary Index (USI) after a table is created	CREATE UNIQUE INDEX IDXNAME(EMP_NO) ON EMP_TABLE ;
Creating a Value-Ordered NUSI	CREATE INDEX(DEPT_NO) ORDER BY VALUES ON EMP_TABLE ;
Making an exact copy a Table with data	CREATE TABLE EMP_TABLE_BACKUP AS EMP_TABLE WITH DATA;
Making an exact copy a Table with out data	CREATE TABLE EMP_TABLE_BACKUP AS EMP_TABLE WITH NO DATA;
Copying a Table	CREATE TABLE EMP_TABLE_BACKUP AS (SELECT * FROM EMP_TABLE ) WITH DATA;
Copying only specific columns of a table	CREATE TABLE EMP_TABLE_BACKUP AS (SELECT EMP_NO ,DEPT_NO ,SALARY FROM EMP_TABLE ) WITH DATA;
Copying aTable and Keeping the Statistics	CREATE TABLE EMP_TABLE_BACKUP AS EMP_TABLE WITH DATA AND STATS;
Copying a Table without data but with Statistics	CREATE TABLE EMP_TABLE_BACKUP AS EMP_TABLE WITH NO DATA AND STATS ;
Creating a Table with Fallback	CREATE TABLE EMP_TABLE, FALLBACK ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with No Fallback	CREATE TABLE EMP_TABLE, NO FALLBACK ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with a Before Journal	CREATE TABLE EMP_TABLE ,NO FALLBACK ,BEFORE JOURNAL ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with a Dual Before Journal	CREATE TABLE EMP_TABLE ,NO FALLBACK ,DUAL JOURNAL ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with an After Journal	CREATE TABLE EMP_TABLE ,NO FALLBACK ,AFTER JOURNAL ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with a Dual After Journal	CREATE TABLE EMP_TABLE ,NO FALLBACK ,DUAL AFTER JOURNAL ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with a Journal Keyword Alone	CREATE TABLE EMP_TABLE ,NO FALLBACK ,JOURNAL ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with Customization of the Data Block Size	CREATE TABLE EMP_TABLE ,FALLBACK ,DATABLOCKSIZE= 21248 BYTES ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Table with Customization with FREESPACE Percent	CREATE TABLE EMP_TABLE ,FALLBACK ,DATABLOCKSIZE= 21248 BYTES ,FREESPACE = 20 PERCENT ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) UNIQUE PRIMARY INDEX(EMP_NO) ;
Creating a Columnar Table	CREATE TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) NO PRIMARY INDEX PARTITION BY COLUMN;
Creating a Columnar Table with Multi-Column Containers	CREATE TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) NO PRIMARY INDEX PARTITION BY COLUMN ( EMP_NO, DEPT_NO, (FIRST_NAME,LAST_NAMESALARY));
Creating a Columnar Table with a Row Hybrid	CREATE TABLE EMP_TABLE ( EMP_NO INTEGER ,DEPT_NO INTEGER ,FIRST_NAME VARCHAR(20) ,LAST_NAME CHAR(20) ,SALARY DECIMAL(10,2) ,ADDRESS VARCHAR(100) ) NO PRIMARY INDEX PARTITION BY COLUMN (EMP_NO NO AUTO COMPRESS,DEPT_NO ,ROW (FIRST_NAME,LAST_NAMESALARY)NO AUTO COMPRESS);
Creating a Columnar Table with both Row and Column Partitions	CREATE TABLE ORDER_TABLE ( ORDER_NO INTEGER ,CUSTMER_NO INTEGER ,ORDER_DATE DATE ,ORDER_TOTAL DECIMAL(10,2) ) NO PRIMARY INDEX PARTITION BY (COLUMN, RANGE_N (ORDER_DATE BETWEEN DATE '2015-01-01' AND '2015-06-30' EACH INTERVAL '1' MONTH)) ;

TPT in deatail

2015-09-01T10:16:00.000+05:30

Teradata Parallel Transporter supports the following types of SQL statements:

Data Manipulation Language (DML): Insert, Update, Delete, Upsert, Merge, and Select
Data Control Language (DCL): Give, Grant, and Revoke
Data Definition Language (DDL): Create, Drop, Alter, Modify, Delete Database, Delete User, and Rename

OS Commands

Use the OS Command operator to send commands to the operating system on the client system

Delimited Data

Delimited data are variable-length text records with each field or column separated by one or more delimiter characters. Delimited data are also known as VARTEXT.

Use the Data Connector operator to read or write delimited data

Large Object Data Types

There are two kinds of large object data type:

• Character large object (CLOB)

• Binary large object (BLOB)

• Three operators support the CLOB and BLOB data types.

• The Inserter operator can insert CLOB and BLOB data types into a Teradata table

• The Selector operator can export CLOB and BLOB data types from a Teradata table

• The Data Connector operator can read/write CLOB and BLOB data types from/to a file.

Selecting the wrong operator to process the CLOB or BLOB data type terminates the job.

TPT script Structure

Building TPT Scripts

TPT uses a SQL-like scripting language for extract, basic transformation, and load functions. This easy-to-use language is based on SQL, making it familiar to most database users. All operators use the same scripting language. This represents an improvement over the individual utilities, each of which has its own unique scripting language. A single script can be used to define multiple operators and schemas to create complex extracting and loading jobs.

There are only a few statements that are needed to build a TPT script. A quick look at the basic statements can be seen here:

DEFINE JOB

Defines the overall job and packages together all following DEFINE and APPLY statements.

DEFINE SCHEMA

Defines the structure of a data object in terms of columns of specific data types. A given schema definition can be used to describe multiple data objects. Also, multiple schemas can be defined in a given script.

DEFINE OPERATOR

Defines a specific TPT operator to be used in this job.

DEFINE DBMS

Defines an instance of a database server to be used in this job.

APPLY

A processing statement used to initiate a TPT load, update, or delete operation.

Generally TPT script has two sections

1) Declarative section

2) Executable section

Declarative section—all schema and operator definitions

Define the job name for the script

Provide the description of job

Schema Definition

Operator Definition #1

….

Operator Definition #n

Executable section - -

Specifies all sql statements processing that is extract, load, filter, delete and update done by Apply definition

It also requires a job Variable file.

The structure of TPT script is as mentioned below

Script example

DEFINE JOB FILE_LOAD

DESCRIPTION 'Load a Teradata table from a file'

(

/* TPT declaration section/*

DEFINE SCHEMA Trans_n_Accts_Schema

(

Account_Number VARCHAR(50),

Trans_Number VARCHAR(50),

Trans_Date VARCHAR(50),

Trans_ID VARCHAR(50),

Trans_Amount VARCHAR(50)

);

DEFINE OPERATOR DDL_OPERATOR

TYPE DDL

ATTRIBUTES

(

VARCHAR PrivateLogName = 'ddl_log',

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

VARCHAR ErrorList = '3807'

);

DEFINE OPERATOR FILE_READER

TYPE DATACONNECTOR PRODUCER

SCHEMA Trans_n_Accts_Schema

ATTRIBUTES

(

VARCHAR PrivateLogName = 'dataconnector_log',

VARCHAR DirectoryPath = @jobvar_datafiles_path,

VARCHAR FileName = 'accounts.txt',

VARCHAR Format = 'Delimited',

VARCHAR OpenMode = 'Read',

VARCHAR TextDelimiter = '|'

);

DEFINE OPERATOR LOAD_OPERATOR

TYPE LOAD

SCHEMA *

ATTRIBUTES

(

VARCHAR PrivateLogName = 'load_log',

VARCHAR TdpId = @jobvar_tdpid,

VARCHAR UserName = @jobvar_username,

VARCHAR UserPassword = @jobvar_password,

VARCHAR TargetTable = @jobvar_tgt_dbname || '.Trans',

VARCHAR LogTable = @jobvar_wrk_dbname || '.LG_Trans',

VARCHAR ErrorTable1 = @jobvar_wrk_dbname || '.ET_Trans',

VARCHAR ErrorTable2 = @jobvar_wrk_dbname || '.UV_Trans'

);

STEP Setup_Tables

(

/* TPT Executation section/*

APPLY

('DROP TABLE ' || @jobvar_wrk_dbname || '.ET_Trans;'),

('DROP TABLE ' || @jobvar_wrk_dbname || '.UV_Trans;'),

('DROP TABLE ' || @jobvar_tgt_dbname || '.Trans;'),

('CREATE TABLE ' || @jobvar_tgt_dbname

|| '.Trans (Account_Number VARCHAR(50),

Trans_Number VARCHAR(50),

Trans_Date VARCHAR(50),

Trans_ID VARCHAR(50),

Trans_Amount VARCHAR(50));')

TO OPERATOR (DDL_OPERATOR);

);

STEP Load_Trans_Table

(

APPLY

('INSERT INTO ' || @jobvar_tgt_dbname || '.Trans(Account_Number,

Trans_Number,

Trans_Date,

Trans_ID,

Trans_Amount)

VALUES(:Account_Number,

:Trans_Number,

:Trans_Date,

:Trans_ID,

:Trans_Amount);')

TO OPERATOR (LOAD_OPERATOR[2])

SELECT * FROM OPERATOR (FILE_READER[2]);

);

Note: Example, Job Variables can be maintained in separate variables file or we can pass directly

Job execution

tbuild -f <script file name> -z <checkpoint interval>

The -z option sets the checkpoint interval to the number of seconds specified.

SET CHECKPOINT INTERVAL 160 SEC

SET CHECKPOINT INTERVAL 12 MINUTES

The checkpoint interval can be specified in a job script between the last DEFINE statement

and the APPLY statement(s).

tbuild

We have seen the tbuild command in many of the previous examples. This command is used to initiate a TPT job. The following key options may be used with tbuild:

-f Specifies the filename to be used as input.

-u Specifies job variable values which are to be applied.

-z Specifies a checkpoint interval to be used for the client side.

-s Specifies that job execution is to start at a specific job step.

-v Specifies that job attributes are to be read from an external file.

-l Specifies latency interval - how often to flush stale buffers.

-n Specifies that the job should continue, even if a step return code is greater than 4

Note: If the checkpoint interval is specified both in the job script and with the tbuild -z command option, the -z option takes precedence.

Troubleshooting a Failed Job

Common Job Failures and Remedies

There are two categories of job failures. The evaluation and correction of each type of failure must be handled differently:

• Some jobs fail at launch, during execution of the tbuild statement, but before the initial job step have run.

• Some jobs launch successfully, and one or more job steps may execute successfully, but thejob fails to run to completion.

The following sections describe common errors encountered by Teradata PT jobs.

When the Job Fails to Begin Running.
When a job is launched but fails to begin execution, the associated errors appear in the public log. Errors are detected according to the launch sequence:

1. Teradata PT first processes the options specified in the tbuild command. If it detects tbuild command errors, the job stops.

Error types encountered: tbuild command errors

2 If Teradata PT encounters no tbuild command errors, it then parses the job script and creates a parallel job execution plan that will perform the operations specified in the APPLY

statement(s) in the job script.

Errors types encountered:

Pre processor errors -- Incorrect use of job variables or the INCLUDE directive.
Job script compilation errors -- Syntactic and semantic errors.

3.Only when script compilation is successful and the execution plan has been generated does the Teradata PT allocate resources for and launch the various internal tasks required to execute the job plan

Errors types encountered: System resource errors

The following common types of tbuild errors may occur at job launch:

User errors
executing the tbuild command
Script compiler errors
System resource errors
semaphore errors
socket errors
shared memory errors

Teradata15 features

2014-12-05T07:34:00.002+05:30

Teradata 15.0 has come up with many new exciting features and enhanced capabilities.
This post provides brief descriptions of the new features in this release in database level.

JavaScript Object Notation:
JSON (JavaScript Object Notation) is new functionality to support the storage and processing of data into Teradata database.

A JSON data type, its stores the data in JSON document or JSON records in relational format.
JSON data can be used for all methods, functions, and procedures for processing, shredding, and publishing.
The size of JSON documents up to 16MB
JSONPath support provides simple traversal and regular expressions with wildcards to filter and navigate complex JSON documents.

Teradata QueryGrid:
Teradata database now able to connect Hadoop with this QueryGrid so it’s called as Teradata Database-to-Hadoop also referred as Teradata-to-Hadoop connector.

It provides a SQL interface for transferring data between Teradata Database and remote Hadoop hosts.
Import Hadoop data into a temporary or permanent Teradata table.
Export data from temporary or permanent Teradata tables into existing Hadoop tables.
Create or drop tables in Hadoop from Teradata Database.
Reference tables on the remote hosts in SELECT and INSERT statements.
Select Hadoop data for use with a business tool.
Select and join Hadoop data with data from independent data warehouses for analytical use.

XML Data Type:
Teradata also provides a new data type which stores and process the XML data.
It supports methods, functions, stored procedures for all operations like parsing, validation, transformations and Query.
The XML type stores values up to 2GB in size.

Sample Table creation

CREATE TABLE EMP_TABLE (
   EMPID INTEGER,
   EMPName VARCHAR(256),
   EMPXML XML )
PRIMARY INDEX (EMPID);

EMPXML is an XML type column.

DBQL Show Parameters

A parameterized query used to place parameters, and the parameter values are provided in a separate statement at time of execution.

The main purpose is to distinguishes between code and data. Also avoids attackers from changing the query by inserting SQL commands.

Two new Data Dictionary tables are

DBC.DBQLParamTbl
DBC.DBQLParamTbl logs

Light-Weight Redistribution:

The Light-Weight Redistribution(LWR) also referred to as the Single Sender Redistribution (SSR). With this feature, the Teradata Database optimizer can switch from an all-AMP row redistribution to a few AMP row redistribution.

While executing the query, Teradata optimizer determines a query step is eligible for SSR, then the retrieved rows are redistributed by hash code to receiver AMP. In this case the number of AMPs is few. Without SSR all rows are normally redistributed across all AMPs.

Below chart shows the overall enhancements in Teradata 15 release.

Big Data & Analytics	JSON Integration
	Scripting and Language Support
	Table Operator Enhancements
	3D Geospatial Data Type
	Geospatial Performance Enhancements
	SQL-H Enhancements
Performance	Light-Weight Redistribution
Performance	Software Efficiency Improvements
Ecosystem	SQL Interface For ShowBlocks
	TASM/TIWM - Classification by Datablock Selectivity
	Utility Statistics Logging
	TASM - Global Weights
	Unity Callback Support
	TASM - Virtual Partitions Enhancement
	TIWM - Operating Periods For Appliance
Quality	Smart Retry During Parsing
	HSN Health
	New PI On Access Rights Table
	Unresponsive Node Isolation
	Onsite System and Dump Analysis
	DBQL - Show Parameters
Industry Compatibility	Sequenced Aggregate Enhancements and Aggregate JI for Temporal
	1MB Phase 2
	ANSI Temporal Syntax Support
	Teradata Directory Manager

DATE Functions

2014-06-07T22:21:00.000+05:30

As part of Domain Specific Functions Teradata database 14 has released new DATE functions.
Below re list of function.

Function	Description
LAST_DAY	Return date of the last day of the month that contains timestamp value
NEXT_DAY	returns first weekday named by day_value that is later than the date specified by date/timestamp value
NUMTODSINTERVAL	convert a numeric value into an INTERVAL DAY(4) TO SECOND(6) value
NUMTOYMINTERVAL	Convert a numeric value into an INTERVAL YEAR(4) TO MONTH value
TO_DSINTERVAL	Convert a string value into an INTERVAL DAY(4) TO SECOND(6) value.
TO_YMINTERVAL	Convert a string value into an INTERVAL YEAR(4) TO MONTH value.
MONTHS_BETWEEN	Return the number of months between two date/timestamp values.
OADD_MONTHS	Add a specified date/timestamp value to a specified number of months and return the resulting date.
TO_DATE	Convert a string into a DATE value via a format string.
TO_TIMESTAMP	Convert a string into a TIMESTAMP value via a format string.
TO_TIMESTAMP_TZ	Convert a string into a TIMESTAMP WITH TIME ZONE value via a format string.
TRUNC	Returns a DATE value with the time portion truncated to the unit specified by a format string.
ROUND	Returns a DATE value with the time portion rounded to the unit specified by a format string.

Examples:

SELECT LAST_DAY (DATE);
-----------------------
LAST_DAY(Date)
2014-06-30

SELECT NEXT_DAY(DATE '2014-06-10' , 'FRIDAY');
----------------------------------------------
NEXT_DAY(2014-06-10,'FRIDAY')
2014-06-13

SELECT NUMTODSINTERVAL(86405,'SECOND'), NUMTOYMINTERVAL(100, 'MONTH' );
----------------------------------------------------------------------------------------------------------------------
NUMTODSINTERVAL(86405,'SECOND')               NUMTOYMINTERVAL(100,'MONTH')
1 00:00:05.000000                                                    8-04

SELECT TO_DSINTERVAL('150 08:30:00') , TO_YMINTERVAL( '2-11') ;
----------------------------------------------------------------------------------------------
TO_DSINTERVAL('150 08:30:00')                            TO_YMINTERVAL('2-11')
150 08:30:00.000000                                                       2-11
The above functions can be helpful while adding to any date columns.

samples
SELECT ORDER_DATE, ORDER_DATE + TO_YMINTERVAL('02-11') FROM ORDERS;
SELECT EMP_ID, LAST_NAME FROM EMP_TABLE WHERE HIRE_DATE + TO_DSINTERVAL('100 00:00:00')

SELECT MONTHS_BETWEEN(DATE'2014-06-01', DATE'2014-02-01');
-------------------------------------------------------------
MONTHS_BETWEEN(2014-06-01,2014-02-01)
4.00

SELECT OADD_MONTHS (DATE '2014-04-15', 2), OADD_MONTHS (DATE '2008-02-29', 1);
---------------------------------------------------------------------------------------------------------------------
OADD_MONTHS(2014-04-15,2)                              OADD_MONTHS(2008-02-29,1)
2014-06-15                                             2008-03-31
Since 29 is the last day in February, March 31 is returned since 31 is the last day in March

SELECT TRUNC(CAST('2014/06/05' AS DATE), 'D') (FORMAT 'yyyy-mm-dd');
----------------------------------------------------------------------------------------------------
TRUNC('2014/06/05','D')
2014-06-01
The date was rounded to the first day of that week.

SELECT ROUND(CAST('2003/09/20' AS DATE), 'RM') (FORMAT 'yyyy-mm-dd');
----------------------------------------------------------------------------------------------------------
ROUND('2003/09/20','RM')
2003-10-01
Since the day is greater than or equal to 16, the date is rounded to the beginning of the next month.

Numeric Functions

2014-05-29T09:23:00.001+05:30

Teradata 14 has released many Domain Specific Functions. Now we are discussing about new Numeric functions which are equivalent to Oracle.
The following numeric functions are available in this Teradata 14 release.

Name	Description
*SIGN*	It returns the sign of a value
*TRUNC*	It will truncate a numeric value
*ROUND*	It will round a numeric value
*GREATEST*	It return the highest value from a list of given values
*LEAST*	It return the lowest value from a list of given values
*TO_NUMBER*	It convert a string to a number via a format string
*CEILING*	It return the smallest integer not less than the input parameter
*FLOOR*	It return the largest integer equal to or less than the input parameter

Example for all functions.

SELECT
SIGN(-123)                                         as SIGN
, TRUNC (32.976)                                as TRUNC
, ROUND(345.175)                              as ROUND
, GREATEST(12,56,10.1, 155.6)          as GREATEST
, LEAST (12,56,10.1, 155.6)                 as LEAST
, TO_NUMBER ('4769.96', '9999.99') as TO_NUMBER
, CEIL( 5.4)                                          as CEIL
, FLOOR(3.86)                                      as FLOOR
;

Regular Expression Functions

2014-05-22T21:23:00.004+05:30

In this Teradata 14 has released many domain specific function added NUMERIC data type, String functions and many of the functions supports regular expressions. These new functions are designed to be compatible to Oracle.
Here we are going to discuss about the following domain specific regular expression functions.

REGEXP_SUBSTR
REGEXP_REPLACE
REGEXP_INSTR
REGEXP_SIMILAR
REGEXP_SPLIT_TO_TABLE

Now will discuss in detail of each function below

REGEXP_SUBSTR
This function extracts a substring from a source string that matches a regular expression pattern.

General Syntax:
REGEXP_SUBSTR (source_string, regexp_string, position_arg);

position_arg = (occurance_org, match_arg)

REGEXP_SUBSTR Function Example:

SELECT REGEXP_SUBSTR ('God Bless Mummy God Bless Daddy', 'bless', 1, 2, 'i')

In the above example
source_string = God Bless Mummy God Bless Daddy
regexp_string = Bless
position_arg = Find the second occurrence of the string that matches regular expression. 'i' indicates case insensitive.

REGEXP_REPLACE
This function replaces portions of the source string parameter that match a regular expression pattern with a replace string.

General Syntax:
REGEXP_SUBSTR (source_string, regexp_string, replace_string);

replace_string=(position_arg,occurance_org, match_arg)

REGEXP_REPLACE Function Examples:

SELECT REGEXP_REPLACE ('Hello World World', 'world', 'My', 1, 1,'i');
In this we are replacing the 1st occurrence of 'World' with 'My' by ignoring case specific.

SELECT REGEXP_REPLACE ('Godís Love is so wonderful.', 'God's', 'Mother', 1, 1, 'c');

In this we are replacing the 1st occurrence of 'God's' with 'Mother' by considering with case specific with 'c'.

SELECT REGEXP_REPLACE('I love the buzz-buzz buzzing of the bee', 'buzz', 'BUZZ', 1, 2, 'c');
In this we are replacing the 2nd occurrence of 'buzz' with 'BUZZ' by considering the case specific with 'c'.

SELECT REGEXP_REPLACE ('ABCD123-$567xy','[^0-9]*','',1,0,'i');
In this we are replacing the any character or symbols with NULL. So it results only Numeric values.

REGEXP_INSTR
This function Search the source string for a match to a regular expression pattern and return the beginning or ending position of that match.

General Syntax:
REGEXP_SUBSTR (source_string, regexp_string, position_arg)

position_argt = (occurance_org,return_opt, match_arg)

return_opt returns 0 = function returns the beginning position of the match (default).
1 = function returns the end position

REGEXP_INSTR Function Examples:

SELECT REGEXP_INSTR('Happy Birthday to you', 'Happy Birthday', 1, 1, 0, 'c');
It returns 1

SELECT REGEXP_INSTR('Happy Birthday to you', 'Happy Birthday', 1, 1, 1, 'c');
It returns 15

REGEXP_SIMILAR
This function compares a source string to a regular expression and returns an integer value.
1 (true) if the entire string matches regexp_arg
0 (false) if the entire string does not match regexp_arg

General Snytax:
REGEXP_SIMILAR(source_string, regexp_string, match_arg)

REGEXP_INSTR Function Examples:

SELECT    Emp_Name
FROM    Emp_Table
WHERE    REGEXP_SIMILAR (emp_name, '(Smith B(i|y)rd)|| (John B(i|y)rd)','c') = 1;

It returns the names of employees that match
Smith Byrd
Smith Bird
John Bird
John Byrd

REGEXP_SPLIT_TO_TABLE
This table function splits a source string into a table of strings using a regular expression as the delimiter.

General Snytax:
REGEXP_SPLIT_TO_TABLE (source_string, regexp_string, match_arg)

REGEXP_SPLIT_TO_TABLE Function Examples:

CREATE TABLE split_table_latin(id integer, src varchar(100) character set latin, pattern varchar(100) character set latin, match varchar(100) character set latin);

INSERT into split_table_latin(3, 'The2134quick234brown234fox987jumps8743over342the03487lazy089734red972384dog2343.', '[0-9]*','c');

Then the following SELECT statement:

SELECT * from table ( regexp_split_to_table(split_table_latin.src,split_table_latin.pattern,split_table_latin.match)returns (res varchar(100) character set latin)) as t1;

Returns a table with the following rows

The
quick
brown
fox
jumps
over
the
lazy
red
dog
.

This all about the Regular Expression Functions. I hope you enjoyed while learning.

A to Z Performance Tuning

2014-05-16T09:02:00.004+05:30

A) Explain the EXPLAIN: Check for EXPLAIN plan to see how exactly Teradata will be executing the query. Try to understand basic keywords in Explain Plan like confidence level, join strategy used, re-distribution happening or not.

B) Collect STATS: The stats of the columns used join conditions should updated. Secondary Indexes without proper STATS can be of little or no help. Check for STATS status of the table.

C) Use Proper PI: If the Primary index is not properly defined in any one or all of the tables in the query. Check if the PI of target table loaded is unique.

D) Use PPI: If there is Partition Primary Index created on a table, try to use it. If you are not using it in filter condition, it will degrade the performance.

E) No FUNCTIONS in Conditions: Try to avoid using function in join conditions. Ex Applying COALESCE or TRIM etc causes high CPU consumption.

F) Use PPI: If Partition Primary Index is defined in tables try to use it. If you are not using it in filter condition, it will degrade the performance.

G) Same column DATA TYPES: Define same data type for the joining columns.

H) Avoid IN clause in filter conditions: When there can be huge number of values in where conditions, better option can be to insert such values in a volatile table and use volatile table with INNER JOIN in the main query.

I) Use Same PI in Source & Target: PI columns also can help in saving the data into disk .If the Source and Target have the same PI, data dump can happen very efficiently form source to target.

J) Collect STATS on VOLATILE table: Collect stats on volatile tables where required can save AMPCPU. Remove stats if already present where it is not getting used.
If the volatile table contains UNIQUE PI, then go for sample stats rather than full stats.

K) DROPPING volatile tables explicitly: Once volatile tables is no more required you can drop those. Donít wait for complete procedure to be over. This will free some spool space immediately and could prove to be very helpful in avoiding No More Spool Space error.

L) NO LOG for volatile tables: Create volatile tables with NO LOG option.

M) Check DBQL Stats: Keep your performance stats accessible. Target the most AMPCPU consuming query first.

N) UPDATE clause: Do not write UPDATE clause with just SET condition and no WHERE condition. Even if the Target/Source has just one row, add WHERE clause for PI column.

O) DELETE & INSERT: Sometimes replacing UPDATE with DELETE & INSERT can save good number of AMPCPU. Check if this holds good for your query.

P) Query SPLITS: Split queries into several smaller queries logically and use volatile tables with proper PI.

Q) Try MSR: If same target table is loaded multiple times, try MSR for several sections. This will speed the final MERGE step into target table and you may see good CPU gain.

R) Try OLAP Functions: Check if replacing co-related sub query with OLAP function may result in AMPCPU saving.

S) Avoid DUPLICATE data: If the join columns in the tables involved in the query have duplicates. Use Distinct or Group by, load into a volatile table, collect stats and use the volatile table.

T) Use Proper JOINS: If joins used, donít use right outer, left or full joins where inner joins is sufficient.

U) User proper ALIAS: Check the aliases in the joins. Small mistake could lead to a product join.

V) Avoid CAST: Avoid unnecessary casting for DATE columns. Once defined as DATE, you can compare date columns against each other even when they are in different format. Internally, DATE is stored as INTEGER. CAST is required mainly when you have to compare VARCHAR value as DATE.

W) Avoid UDF: Most of the functions are available in Teradata for data manipulations. So avoid User Defined Functions

X) Avoid FULL TABLE SCAN: Try to avoid FTS scenarios like SI should be defined on the columns which are used as part of joins or Alternate access path. Collect stats on SI columns else there are chances where optimizer might go for FTS even when SI is defined on that particular column

Y) Avoid using IN/NOT IN: For large list of values, avoid using IN /NOT IN in SQLs. Write large list values to a temporary table and use this table in the query

Z) Use CONSTANTS: Use constants to specify index column contents whenever possible, instead of specifying the constant once, and joining the tables. This may provide a small savings on performance.

Top SQL Commands

2014-04-23T08:51:00.000+05:30

SQL to changing the default Database
DATABASE EMP_DATA_BASE;

SQL to find Information about a Database
HELP DATABASE EMP_DATA_BASE;

SQL to get Sample number of rows
SELECT * FROM EMP_TBL SAMPLE 10;

SQL to get a sample Percentage of rows
SELECT * FROM EMP_TBL SAMPLE .50;

SQL to find information about a Table
SHOW TABLE EMP_TBL;

SQL to Use an Access Locked Table
LOCKING ROW FOR ACCESS SELECT * FROM EMP_TBL;

SQL Keywords that describe you
SELECT DATABASE, USER, SESSION,
ACCOUNT,
PROFILE,
ROLE;"
"SELECT DATE,
CURRENT_DATE,
TIME,
CURRENT_TIME,
CURRENT_TIMESTAMP;

SQL to Use Aggregates functions
SELECT TOP 10 STUDENT_NO, FIRST_NAME, LAST_NAME, CLASS_CODE
FROM STUDENT_TBL
ORDER BY GRADE DESC;"
"SELECT DEPT_NO
,MAX(SALARY) AS ""MAXIMUM""
,MIN(SALARY) AS ""MINIMUM""
,AVG(SALARY) AS ""AVERAGE""
,SUM(SALARY) AS ""SUM""
,COUNT(*) AS ""COUNT""
FROM EMP_TBL
GROUP BY DEPT_NO
ORDER BY DEPT_NO;

SQL to Select TOP Rows in a Rank Order
SELECT TOP 10 STUDENT_NO
,FIRST_NAME
,LAST_NAME
,CLASS_CODE
FROM STUDENT_TBL
ORDER BY GRADE DESC;

SQL Using Date, Time and Timestamp
SELECT
CALENDAR_DATE
,DAY_OF_WEEK
,DAY_OF_MONTH
,DAY_OF_YEAR
,DAY_OF_CALENDAR
,WEEKDAY_OF_MONTH
,WEEK_OF_MONTH
,WEEK_OF_YEAR
,WEEK_OF_CALENDAR
,MONTH_OF_QUARTER
,MONTH_OF_YEAR
,MONTH_OF_CALENDAR
,QUARTER_OF_YEAR
,QUARTER_OF_CALENDAR
,YEAR_OF_CALENDAR
FROM SYS_CALENDAR.CALENDAR;

SQL to Find out how much Space a USER have
SELECT
USERNAME
,CREATORNAME
,PERMSPACE
,SPOOLSPACE
,TEMPSPACE
,LASTALTERNAME
,LASTALTERTIMESTAMP
FROM DBC.USERS
WHERE USERNAME='USER';

SQL to find how much Space left Per AMP in database
SELECT
VPROC
,DATABASENAME
,ACCOUNTNAME
,MAXPERM
,MAXSPOOL
,MAXTEMP
FROM DBC.DISKSPACE
WHERE DATABASENAME='EMP_DB'   ;

SQL to finding USER Space
SELECT
MAX(MAXPERM)
,MAX(MAXSPOOL)
,MAX(MAXTEMP)
FROM DBC.DISKSPACE
WHERE DATABASENAME='USER' ;

SQL to find Space Skew in Tables in a Database
SELECT VPROC
,CAST(TABLENAME AS CHAR(20))
,CURRENTPERM
,PEAKPERM
FROM DBC.TABLESIZEV
WHERE DATABASENAME='USER'
ORDER BY TABLENAME, VPROC;

SQL to Find Table Skew
SELECT
TABLENAME,
SUM(CURRENTPERM) /(1024*1024) AS CURRENTPERM,
(100 - (AVG(CURRENTPERM)/MAX(CURRENTPERM)*100)) AS SKEWFACTOR
FROM
DBC.TABLESIZE
WHERE DATABASENAME=
AND TABLENAME =
GROUP BY 1;

SQL to Find AMP Skew
SELECT DATABASENAME
,TABLENAME
,VPROC
,CURRENTPERM
,PEAKPERM
FROM DBC.TABLESIZE
WHERE
DATABASENAME=
AND
TABLENAME=
ORDER BY VPROC ;

SQL to find number of rows per AMP for a Column
SELECT HASHAMP(HASHBUCKET( HASHROW(EMP_NO))) AS ""AMP"" , COUNT(*)
FROM EMP_TABLE
GROUP BY 1
ORDER BY 1;

SQL to Identify duplicate records
SELECT COLUMN1, COLUMN2, COLUMN3, COUNT(*)
FROM
DATABASE.TABLE
GROUP BY COLUMN1, COLUMN2, COLUMN3
HAVING COUNT(*) >1;

SQL to Delete Duplicate records
CREATE TABLE TABLE1_BACKUP AS (SELECT * FROM TABLE1 QUALIFY ROW_NUMBER() OVER (PARTITION BY COLUMN1 ORDER BY COLUMN1 DESC )=1) WITH DATA;
DELETE FROM TABLE1;
INSERT INTO TABLE1 SELECT * FROM TABLE_BACKUP;

SQL below to find TOP Databases by space occupied
SELECT
DatabaseName
,MAX(CurrentPerm) * (HASHAMP()+1)/1024/1024 AS USEDSPACE_IN_MB
FROM DBC.DiskSpace
GROUP BY DatabaseName
ORDER BY USEDSPACE_IN_MB DESC;

SQL to find TOP Tables by space occupied
SELECT DATABASENAME
,TABLENAME
,SUM(CurrentPerm)/1024/1024 AS TABLESIZE_IN_MB
FROM DBC.TableSize
GROUP BY DATABASENAME,TABLENAME
ORDER BY TABLESIZE_IN_MB DESC;

SQL to find out list of nodes
SELECT DISTINCT NODEID FROM DBC.RESUSAGESPMA;

SQL to find Account Information
SELECT * FROM DBC.AccountInfoV ORDER BY 1;

COLLECT STATISTICS

2014-03-26T08:37:00.000+05:30

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

DBC.Indexes (for multi-column indexes only)
DBC.TVFields (for all columns and single column indexes)
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.

These are the latest enhancements of COLLECT STATISTICS.

HASH Functions

2014-03-21T21:48:00.000+05:30

There are four types of hashing functions are available in Teradata.

HASHROW
HASHAMP
HASHBUCKET
HASHBAKAMP

HASHROW Function
Basic Synatx for HASHROW is
HASHROW( [ < data-column-value > […, < data-column-value >…] ] )

Example

SELECT    HASHROW (NULL)     AS NULL_HASH
   ,HASHROW('Anil')        AS Name_HASH
                 ,HASHROW()                 AS NO_HASH ;

The Query will give same results if you ran again and again.
The HASHROW function produces the 32-bit binary Row Hash that is stored as part of the data row.
It returns maximum of 4,294,967,295 unique values.
The values range from 0 to FFFFFFFF.

General example is
SELECT HASHROW(Emp_no) AS HASHROW_VALUE FROM Emp_table;

Result:
HASHROW_VALUE
------------
717A8724

HASHBUCKET Function

Basic Syntax for HASHBUCKET is

HASHBUCKET( [ ] )

Example
SELECT HASHBUCKET(NULL) AS NULL_BUCKET
,HASHBUCKET() AS NO_BUCKET;

The HASHBUCKET function produces 16bit binary Hash Bucket that is used with the Hash Map to determine the AMP that should store and retrieve the data row
The values range from 0 to 1,048,575,not counting the NULL as a potential result.

General Example
SELECT COUNT(*) AS NBR_ROWS
               ,HASHBUCKET(HASHROW (Emp_No)) AS Bucket_No
       FROM Emp_Table
                GROUP BY 2 ;

NBR_ROWS	Bucket_No
1	388963
1	1560021
1	683238
1	53025
1	1877998
1	795490
1	410221
1	1048185
1	804626
1	317398

HASHAMP Function
Basic Synatx for HASHAMP is
HASHAMP( )

Example
SELECT HASHAMP(NULL) AS NULL_BUCKET
,HASHAMP () AS NO_BUCKET;

The HASHAMP function returns the identification number of the primary AMP for any Hash Bucket number.
When no value is passed to the HASHAMP function, it returns a number that is one less than the number of AMPs in the current system configuration

General Example

SELECT Emp_no
       ,HASHBUCKET(HASHROW(Emp_no)) AS Bucket_No
       ,HASHAMP(HASHBUCKET(HASHROW(Emp_no))) AS AMP_No
       FROM Emp_Table
       ORDER BY 1;

Emp_no	Bucket_No	AMP_No
1777	973955	120
4972	93325	41
7524	540321	56
21048	957958	69
21048	957958	69
24402	707988	65
26346	9707	71
27470	471190	15
29157	613408	25
29775	452863	120

HASHBAKAMP Function
Basic Syntax for HASHBAKAMP is

HASHBAKAMP ( )

ExampleSELECT HASHBAKAMP (NULL) AS NULL_BUCKET
,HASHBAKAMP () AS NO_BUCKET;

The HASHBAKAMP function returns the identification number of the Fallback AMP for any Hash Bucket number

General Example
SELECT Emp_no
       ,HASHBUCKET(HASHROW(Emp_no)) AS Bucket_No
       ,HASHBAKAMP(HASHAMP(HASHBUCKET(HASHROW(Emp_no)))) AS BAK_AMP_No
       FROM Emp_Table
       ORDER BY 1;

Emp_no	Bucket_No	BAK_AMP_No
2226	439931	88
6467	977645	94
24115	222558	92
24402	707988	94
25316	277218	94
25499	290771	95
29405	528628	93
40115	944038	95
40117	892602	86
40564	410014	85

Explain Plan

2014-03-19T21:27:00.001+05:30

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").

Hope you understood about EXPLAIN.

CUBE Function

2014-02-08T17:03:00.002+05:30

I hope you all familiar with ROLLUP Function now. If not please read before get into CUBE Function.

The CUBE function can be called as Superset of RollUP function.
Because It returns the same result set as ROLLUP along with provides some additional information.
Instead producing a 'hierarchy' of results, CUBE looks at all permutations of the specified dimensions.

Let take an example , Produce a 'CUBE' report showing employee salaries across the dimensions of department and manager.

SELECT DEPT_NO AS DEPTNO
,MGR_EMP_NO AS MANAGER
,SUM(SALARY_AMOUNT) AS SUMSAL
FROM EMPLOYEE
WHERE DEPT_NO < 402
GROUP BY CUBE (DEPT_NO,MGR_EMP_NO)
ORDER BY 1,2;

DEPTNO	MANAGER	SUMSAL

?	?	591925	(all depts, all managers)

?	801	286750	(all depts, one manager)

?	1003	207725

?	1019	58700

?	1025	38750

100	?	100000	(one dept, all managers)

100	801	100000	(one dept, one manager)

201	?	73450	(one dept, all managers)

201	801	34700

201	1025	38750

301	?	116400

301	801	57700

301	1019	58700

302	?	56500

302	801	56500

401	?	245575

401	801	37850

401	1003	207725

If you observe there are four additional rows (green) are included in the output.
These four rows represent the totals by manager.This report shows all possible permutations of the two specified dimensions.

Reversing CUBE Parameters:
What happens when we flip the sequence of the CUBE parameters? Consider the following:

SELECT DEPT_NO AS DEPTNO
,MGR_EMP_NO AS MANAGER
,SUM(SALARY_AMOUNT) AS SUMSAL
FROM EMPLOYEE
WHERE DEPT_NO < 402
GROUP BY CUBE (MGR_EMP_NO,DEPT_NO)
ORDER BY 1,2;

Yes, The output is exactly returns same.There is no difference in output becacuse CUBE function looks at all permutations of the specified parameters, order of the arguments doesn't matter - unlike ROLLUP where it does matter.

we can use the GROUPING and COALESCE functions to make the report more readable.

SELECT CASE GROUPING (department_number)
WHEN 1 THEN 'All Depts'
ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno
,CASE GROUPING (manager_employee_number)
WHEN 1 THEN 'All Managers'
ELSE manager_employee_number END AS Mngr
,SUM(salary_amount) AS SumSal
FROM employee
WHERE department_number < 402
GROUP BY CUBE (manager_employee_number,department_number)
ORDER BY 1,2;

Deptno	Mngr	SumSal

100	801	100000

100	All Managers	100000

201	801	34700

201	1025	38750

201	All Managers	73450

301	801	57700

301	1019	58700

301	All Managers	116400

302	801	56500

302	All Managers	56500

401	801	37850

401	1003	207725

401	All Managers	245575

All Depts	801	286750

All Depts	1003	207725

All Depts	1019	58700

All Depts	1025	38750

All Depts	All Managers	591925

As more dimensions are added to the cube, more permutations and thus more rows will result.

If n is the number of dimensions in a cube, then 2**n represents the number of permutations generated:
2 dimensions - 4 permutations
3 dimensions - 8 permutations
4 dimensions - 16 permutations
etc.
Example

Produce a 'cube' report showing employee salaries across the three dimensions of department, manager and job code for department numbers less than 302.

SELECT CASE GROUPING (department_number)
WHEN 1 THEN 'All Depts'
ELSE (COALESCE (department_number,'Null Dept') ) END AS Deptno

,CASE GROUPING (manager_employee_number)
WHEN 1 THEN 'All Managers'
ELSE manager_employee_number END AS Mngr

,CASE GROUPING (job_code)
WHEN 1 THEN 'All Job Codes'
ELSE (COALESCE (job_code,'Null Job Code') ) END AS Job

,SUM(salary_amount) AS SumSal
FROM employee
WHERE department_number < 302
GROUP BY CUBE (manager_employee_number,department_number, job_code)
ORDER BY 1,2;

Deptno	Mngr	Job	SumSal
100	801	111100	100000
100	801	All Job Codes	100000
100	All Managers	All Job Codes	100000
100	All Managers	111100	100000
201	801	211100	34700
201	801	All Job Codes	34700
201	1025	All Job Codes	38750
201	1025	222101	38750
201	All Managers	211100	34700
201	All Managers	All Job Codes	73450
201	All Managers	222101	38750
301	801	All Job Codes	57700
301	801	311100	57700
301	1019	All Job Codes	58700
301	1019	312101	29450
301	1019	312102	29250
301	All Managers	312102	29250
301	All Managers	311100	57700
301	All Managers	All Job Codes	116400
301	All Managers	312101	29450
All Depts	801	All Job Codes	192400
All Depts	801	111100	100000
All Depts	801	211100	34700
All Depts	801	311100	57700
All Depts	1019	312101	29450
All Depts	1019	All Job Codes	58700
All Depts	1019	312102	29250
All Depts	1025	All Job Codes	38750
All Depts	1025	222101	38750
All Depts	All Managers	All Job Codes	289850
All Depts	All Managers	311100	57700
All Depts	All Managers	211100	34700
All Depts	All Managers	111100	100000
All Depts	All Managers	312101	29450
All Depts	All Managers	312102	29250
All Depts	All Managers	222101	38750
All Depts	All Managers	312102	29250
All Depts	All Managers	222101	38750

To help interpret the output, use the following color-coding scheme:

(department,manager, job_code)       Each unique combo of dept/mgr/job
(department, manager)                        Each unique combo of dept/mgr
(department, job_code)                        Each unique combo of dept/job
(manager, job_code)                             Each unique combo of mgr/job
(department)                                         All depts
(manager)                                             All mgrs
(job_code)                                             All jobs
( )                                                       Grand Total

Teradata Facts

2014-01-30T21:40:00.003+05:30

Below are some of the hard facts about Teradata Database and Limitations between versions.

Each AMP holds a portion of the rows for every table in the system.
Each Table has a Primary Index that is Unique or Non-Unique
The Hash Map Determines which AMP will own the Row
A Unique Primary Index Spreads the Data Evenly
The AMP Adds a Uniqueness Value to Create the Row-ID
Each AMP Sorts Their Tables by the Row-ID
A Non-Unique Primary Index Skews the Data
Unique Primary Index Queries are a Single AMP Retrieve
A Non-Unique Primary Index is also a Single AMP Retrieve
Teradata has a No Primary Index Table called a NoPI Table
There are Normal Tables and then There are Partitioned Tables
Partitioning is Designed to Eliminate the Full Table Scan
A Partition # and Row-ID = Row Key
An AMP Stores its Rows Sorted in only Two Different Ways
AMPs Moves Their Data Blocks into Memory to Read/Write
Most Taxing thing for an AMP is Moving Blocks into Memory
Rows are Stored in Data Blocks which are stored in Cylinders
Rows for an AMP Stored Inside a Data Block in a Cylinder
An AMP's Master Index is Used to Find the Right Cylinder
There is One Master Index and Thousands of Cylinder Indexes
The Row Reference Array is always in Row-ID order
The maximum block size (before a split) was 255 sectors (127.5 K), but now it is 2047 sectors (1 MB).
A Block Splits into Two Blocks at Maximum Block Size.
Once the block grows to the maximum size, then Teradata will split the single block into two separate blocks. The default for the split is 2047 sectors or 1 MB.
The maximum for all Teradata V14 data block splits are 2047 sectors or 1 MB, but the defaults differ based on whether or not it is an enterprise class system, or an appliance
Each Table has a 48-bit TableID

Differences in Teradata vesrions V12, V13 and V14

	Versions
	12	13	14
Max Vdisk Size	1.26 TB	1.26 TB	7.2 TB
Max Block Size (sectors)	255	255	255
Max Table Header Size	128 KB	1 MB	1 MB
Max Databases/Users	4.2 Billion	4.2 Billion	4.2 Billion
Max Partitions	65,535	65,535	9.2 * 10 18
Max Partition Levels	15	15	62
Max spool tables per query	2048	2048	2048
Max Tables in a Join	64	128	128
Max columns in a table	2048	2048	2048
Max columns in an index	64	64	64
Max SQL request size	1 MB	1 MB	1 MB
Max AMPs and PEs	16,384	16,384	32,720

ROLLUP Function

2014-01-06T21:51:00.003+05:30

The teradata ROLLUP function is used, when ever an aggregation is required across all levels of a hierarchy within a single dimension.

Lets take geography is a dimension. You might have 3 levels of hierarchy - city, county and state. You may wish to produce sum of revenue amounts across all three levels.

we can produce below samples

Produce a sum of revenue amounts across all three levels.
Aggregate at the lowest level (city) and 'rollup' those aggregations to the next level (county) and finally rollup to the top level (state).
Provide a grand total across all three levels.

All of these are easily done with the ROLLUP feature.

Example1:
Using the GROUP BY function, produce a total of salaries by department for department numbers less than 402.

SELECT department_number
,SUM(salary_amount)
FROM employee WHERE department_number < 402
GROUP BY 1
ORDER BY 1;

department_number	Group Sum(salary_amount)
100	100000
201	73450
301	116400
302	56500
401	245575

ROLLUP Function - One Level
Using the ROLLUP function, produce salary totals by department.

SELECT department_number
,SUM(salary_amount)
FROM employee
WHERE department_number < 402
GROUP BY ROLLUP (department_number)
ORDER BY 1;

department_number	Group Sum(salary_amount)
?	591925
100	100000
201	73450
301	116400
302	56500
401	245575

There is an additional row with a '?' department number. This row represents the rollup of all department salaries which is the top (and only) level of the hierarchy in this case. The '?' does not represent a null, rather it represents the 'total' of all department salaries.

ROLLUP Function - Two Levels

Produce a hierarchy of total salaries by department within manager.

SELECT manager_employee_number AS Mgr
,department_number AS Dept
,SUM(salary_amount) AS SumSal
FROM employee
WHERE department_number < 402
GROUP BY ROLLUP (manager_employee_number,department_number)
ORDER BY 1,2;

Mgr	Dept	SumSal
?	?	591925	(all mgrs,all depts,all emps)
801	?	286750	(mgr 801,all depts, 5 emps)
801	100	100000	(mgr 801,dept 100, 1 emp)
801	201	34700	(mgr 801,dept 201, 1 emp)
801	301	57700	(mgr 801,dept 301, 1 emp)
801	302	56500	(mgr 801,dept 302, 1 emp)
801	401	37850	(mgr 801,dept 401, 1 emp)
1003	?	207725	(mgr 1003,all depts, 6 emps)
1003	401	207725	(mgr 1003,dept 401, 6 emps)
1019	?	58700	(mgr 1019,all depts, 2 emps)
1019	301	58700	(mgr 1019,dept 301, 2 emps)
1025	?	38750	(mgr 1025,all depts, 1 emp)
1025	201	38750	(mgr 1025,dept 201, 1 emp)

Three separate aggregations taking place in the preceding query.

Totals for each dept/manager combo (black).
This is represented as the set '(manager,department )' and is the bottom of the hierarchy.

Totals for each manager across all departments (red).
This is represented as the set '(manager )' which is the middle of the hierarchy.

A total for all depts across all managers (blue).
This is represented as the set '( )' and is the top of the hierarchy.

ROLLUP WITH NULL
As previously indicated, the row with the '?' represents the grand total of all salaries across all departments.
But what if there was a department number with a null? How would we distinguish between it and a 'total' row?

Add an employee with a null department and a null job code:

INSERT INTO employee VALUES
(1050,801,NULL,NULL,'LaCoste','Jason',780415,480816,60000.00);

SELECT department_number
,SUM(salary_amount)
FROM employee
GROUP BY ROLLUP (department_number)
ORDER BY 1;

department_number	Group Sum(salary_amount)
?	651925
?	60000
100	100000
201	73450
301	116400
302	56500
401	245575

While it may seem obvious that the row with the larger sum will represent the 'total' row,
a function exists which permits the report to be more easily read.

Teradata 14 Certification Details

2013-12-27T21:25:00.002+05:30

The Teradata 14 Certification is the most current release of Teradata Certification exams offered globally through authorized Prometric Testing Centers.

Teradata 14 Certification Details

Exam	Title	Duration
TE0-141	Teradata 14 Basics exam	1 hr 30 min
TE0-142	Teradata 14 SQL exam	1 hr 45 min
TE0-143	Teradata 14 Physical Design and Implementation	2 hrs
TE0-14B	Teradata 14 Bridge from Teradata 12*	2 hrs 30 min
TE0-144	Teradata 14 Database Administration exam	2 hrs 15 min
TE0-145	Teradata 14 Solutions Development exam	2 hrs
TE0-146	Teradata 14 Enterprise Architecture exam	1 hr 45 min
TE0-147	Teradata 14 Comprehensive Mastery exam**	3 hrs 20 min

NEW! *Teradata 14 Bridge Exam This exam is designed to transition or “bridge” qualifying candidates from the Teradata 12 Certification track to the Teradata 14 Certification track. This eligibility based exam is available only to candidates that have achieved any of the following Teradata 12 Certifications: Certified Technical Specialist, Database Administrator, Solutions Developer or Enterprise Architect. The Teradata 14 Bridge exam replaces the need to retake the core Teradata Certification exams: Basics (TE0-141), SQL (TE0-142), and Physical Design and Implementation (TE0-143) once you have achieved the prerequisite Teradata 12 Certification(s).

Teradata 12 Certification Details

PLEASE NOTE: The following Teradata 12 Certification Exams (TE0-121 through TE0-126*) will remain in market through September 30, 2014. After that time, the exams will be discontinued but the certifications will continue to be supported. If you are new to the TCPP program, we strongly encourage you to begin with the Teradata 14 Basics exam on the Teradata 14 Certification Track.

The Teradata 12 Qualifying Exam for V2R5 Masters (TE0-12Q*) and the Teradata 12 Comprehensive Mastery Exam (TE0-127*) will remain in market until Q1 2015.

Exam	Title	Duration
TE0-121	Teradata 12 Basics exam	2 hrs
TE0-122	Teradata 12 SQL exam	2 hrs 15 min
TE0-123	Teradata 12 Physical Design and Implementation	2 hrs
TE0-124	Teradata 12 Database Administration exam	2 hrs 15 min
TE0-125	Teradata 12 Solutions Development exam	3 hrs
TE0-126	Teradata 12 Enterprise Architecture exam	2 hrs 45 min
TE0-127	Teradata 12 Comprehensive Mastery exam*	3 hrs 30 min
TE0-12Q	Teradata 12 Qualifying Exam for V2R5 Masters*	2 hrs 45 min

How to schedule exams at Prometric:

Schedule appointments on-line: Your correct and valid e-mail address will be required each time you schedule an exam. To begin registration, go to: www.prometric.com/Teradata

Or contact a Prometric Regional Registration Center by telephone:

Candidates in the USA & Canada may call Toll Free: 1-877-887-6868

Candidates in all other countries, go to www.prometric.com/teradata and choose “contact numbers” on the left navigation bar

* In India only- NIIT testing sites do not accept discount/promotional codes directly at the site. If you plan to register onsite, please visit the Prometric website and choose “Locate a Test Center” to find an alternative testing center.

Additional Information about your Prometric Registration:

Exam Cost: Teradata exam fees will vary based on the exam taken and your location. Exam fees will display after you choose your exam title during the on-line registration process. You may pay Prometric with a credit card or exam voucher.

Testing ID: Prometric assigns a unique alpha-numeric Testing ID to you during your first exam registration. Use the same Prometric testing ID each time you register for a Teradata exam. Your correct and valid e-mail address will be required each time you schedule an exam.

Official Name: Ensure that your entire first and last name is spelled correctly as it appears on your official Government-issued photo ID.

Cancellation: Prometric’s 24-hour cancellation and no-show policies will be in effect for exam appointments.

Re-Take Policy: Re-take violations are considered Security violations. To avoid re-take violations, please refer to the re-take policy prior to re-scheduling a failed exam.

Exam Completion and Results: Upon completing a Teradata exam at a Prometric Testing Center, Prometric will provide a printed hard copy “Notice of Exam Completion”. Keep this document for information you will need to retrieve your “Notice of Exam Results”

Architecture

2013-11-30T20:13:00.000+05:30

Teradata relies on three architectural components.

Teradata Architecture

Parsing Engine (PE)
The Parsing Engine (PE) is a component that interprets SQL requests, receives input records, and passes data. It sends the messages through the BYNET to the AMPs.

BYNETs
The BYNET act as message-passing layer. It decides which AMP should receive a message.

Access Module Processor (AMP)
The AMP is a virtual processor (vproc) designed to managing a portion of the entire database.
It performs all database management functions such as sorting, aggregating, and formatting data.
The AMP receives data from the PE, formats rows, and distributes them to the disk storage units it controls. The AMP also retrieves the rows requested by the Parsing Engine.

Disks
Disks are disk drives associated with an AMP that store the data rows.

FAST EXPORT

2013-11-29T21:58:00.000+05:30

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

FastExport EXPORTS data from Teradata.
FastExport only supports the SELECT statement.
Choose FastExport over BTEQ when Exporting Data of more than half a million+ rows
FastExport supports multiple SELECT statements and multiple tables in a single run
FastExport supports conditional logic, conditional expressions, arithmetic calculations, and data
conversions.
FastExport does NOT support error files or error limits.
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;

.EXPORT 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

Rank Function

2013-11-29T16:22:00.000+05:30

The teradata RANK function permits a column to be ranked, either based on high or low order, against other rows in the answer set. You may specify the order of ranking by use of the ORDER BY clause within the RANK function. Descending order will provide traditional ranking wherein the largest value is assigned a rank of one. Ascending order will associate the largest value with the highest rank value (i.e., typically a value > 1).

The syntax for the RANK function is:

RANK( ) OVER (ORDER BY sales DESC);

Example
Show the ranking of product sales for store 1001.

SELECT storeid
,prodid
,sales
,RANK( ) OVER (ORDER BY sales DESC) AS "Rank"
FROM salestbl
WHERE storeid = 1001;

Result

Storeid prodid sales Rank
----------------------------------------------------------------
1001 F 150000.00 1
1001 A 100000.00 2
1001 C 60000.00 3
1001 D 35000.00 4

Things To Notice:
WHERE clause qualifies rows to be ranked.
When the order of sales is DESC, the highest sales amount is rank #1

Example
Show the lowest ranking of product sales for store 1001.

SELECT storeid
,prodid
,sales
,RANK( ) OVER (ORDER BY sales ASC) AS "Rank"
FROM salestbl
WHERE storeid = 1001;

Storeid prodid sales Rank
----------------------------------------------------------------
1001 D 35000.00 1
1001 C 60000.00 2
1001 A 100000.00 3
1001 F 150000.00 4

Things To Notice:
When the order of sales is ASC, the lowest sales amount is rank #1
Rank #1 always appears at top of list unless overridden.

Rankings With Qualification

The QUALIFY clause allows restriction of which rankings will be output in the final result.
The QUALIFY clause allows restriction of which rankings will be output in the final result.

QUALIFY performs like the HAVING clause by requesting a specific range in the output.

Example
Get top 3 sales - any product in any store:

SELECT storeid
,prodid
,sales
,RANK( ) OVER (ORDER BY sales DESC)
AS "Ranking"
FROM salestbl
QUALIFY Ranking <= 3;

Storeid prodid sales Rank
----------------------------------------------------------------
1001 F 150000.00 1
1001 A 100000.00 2
1003 B 65000.00 3

Things To Notice:
QUALIFY shows the ranking for the top 3 sales amounts only.

The RANK function produces an ordered sequence. ORDER BY can override the normal sequencing.

Example
Reverse the ranking sequence in the previous example.

SELECT storeid
,prodid
,sales
,RANK( ) OVER (ORDER BY sales DESC) AS "Ranking"
FROM salestbl
QUALIFY Ranking <= 3
ORDER BY 4 DESC;

Storeid prodid sales Rank
----------------------------------------------------------------
1003 B 65000.00 3
1001 A 100000.00 2
1001 F 150000.00 1

Things To Notice:
The ORDER BY clause in the SELECT statement may always be used to control the final order of the result set.
When the order of sales is DESC, the highest sales amount is always rank #1
After the ranking is applied, the output results are produced based on the ORDER BY sequence in the SELECT statement, if specified.

Ranking With PARTITION BY

The PARTITION BY clause may be used in conjunction with a RANK function to change the scope of the ranking. Without a PARTITION BY clause, the scope defaults to the RANK column.

Whereas the RANK( ) ORDER BY clause controls the default sort key, the PARTITION BY clause adds another level of sort to the output.

Example
Get the top three selling products in each store.

SELECT storeid
,prodid
,sales
,RANK( ) OVER (PARTITION BY storeid
ORDER BY sales DESC) AS "Ranking"
FROM salestbl
QUALIFY Ranking <= 3;

Storeid prodid sales Rank
----------------------------------------------------------------
1001 F 150000.00 1
1001 A 100000.00 2
1001 C 60000.00 3
1002 A 40000.00 1
1002 C 35000.00 2
1002 D 25000.00 3
1003 B 65000.00 1
1003 D 50000.00 2
1003 A 30000.00 3

Things To Notice:
PARTITION BY clause controls scope, i.e., rank sales within store.
Without PARTITION BY, scope would default to sales only.
QUALIFY the ranking for the top 3 sales amounts per store.
Sort sequence of output is sales descending.
Due to PARTITION BY, sort is by sales (DESC) within store (ASC).

Note -- no aggregation is done in this query.

Interview Questions Part-1

2013-11-27T19:59:00.000+05:30

What are the different types of locks in Teradata?

Exclusive Lock:
Exclusive locks are only applied to databases or tables, never to rows.
They are the most restrictive type of lock; all other users are locked out.
Exclusive locks are used rarely, most often when structural changes are being made to the database.

Read Lock:
Read locks are used to ensure consistency during read operations.
Several users may hold concurrent read locks on the same data, during which no modification of the data is permitted.

Write Lock:Write locks enable users to modify data while locking out all other users except readers not concerned about data consistency (Access lock readers).
Until a Write lock is released, no new read or write locks are allowed.

Access Lock:Access locks can be specified by users who are not concerned about data consistency.
The use of an access lock allows for reading data while modifications are in process. Access locks are designed for decision support on large tables that are updated only by small single row changes. Access locks are sometimes called stale read locks, i.e. you may get stale data that hasnot been updated

Lock types are automatically applied based on the SQL command:
SELECT -->applies a Read lock
UPDATE --> applies a Write lock
CREATE TABLE --> applies an Exclusive lock

=========================================================================

What is multiple statement processing?
Multiple statement processing increases the performance when loading into large tables. All statements are sent to parser simultaneously and all statements are executed parallel.
In sql Assistance, once you place all queries and press F5. All will be executed parallel. Make sure each sql statement ends semicolon(;).

=========================================================================

What is TENACITY and SLEEP ?
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.

=========================================================================

What is soft Referential Integrity and Batch Referential Integrity?
Soft Referential Integrity:
It provides a mechanism to allow user-specified Referential Integrity (RI) constraints that are not
enforced by the database.
Enables optimization techniques such as Join Elimination.

Batch Referential Integrity:
Tests an entire insert, delete, or update batch operation for referential integrity. If insertion, deletion, or update of any row in the batch violates referential integrity, then parsing engine software rolls back the entire batch and returns an abort message.

=========================================================================

What Teradata statistics ?

Statistics collection is essential for the optimal performance of the Teradata query optimizer.

The query optimizer relies on statistics to help it determine the best way to access data.

Statistics also help the optimizer ascertain how many rows exist in tables being queried and predict how many rows will qualify for given conditions.

Lack of statistics, or out-dated statistics, might result in the optimizer choosing a less-than-optimal method for accessing data tables.

Points:

1: Once a collect stats is done on the table(on index or column) where is this information stored so that the optimizer can refer this?

Ans: Collected statistics are stored in DBC.TVFields or DBC.Indexes. However, you cannot query these two tables.

2: How often collect stats has to be made for a table that is frequently updated?

Answer: You need to refresh stats when 5 to 10% of table's rows have changed. Collect stats could be pretty resource consuming for large tables. So it is always advisable to schedule the job at off peak period and normally after approximately 10% of data changes.

3: Once a collect stats has been done on the table how can i be sure that the optimizer is considering this before execution ? i.e; until the next collect stats has been done will the optimizer refer this?

Ans: Yes, optimizer will use stats data for query execution plan if available. That's why stale stats is dangerous as that may mislead the optimizer.

=========================================================================

What is a HOT AMP?

When the workload is not distributed across all the AMPs, only a few AMPs end up overburdened with the work. This is a hot AMP condition.

This typically occurs when the volume of data you are dealing with is high and

(a). You are trying to retrieve the data in a TERADATA table which is not well distributed across the AMPs on the system (bad Primary Index)

(b). When you are trying to join on column with highly non unique values

(c). When you apply the DISTINCT operator on a column with highly non unique values

=========================================================================

What are different SPACES available in Teradata?

There are mainly 3 types of spaces available.

Perm Space

Temp Space

Spool space

Perm Space :All databases have a defined upper limit of permanent space. Permanent space is used for storing the data rows of tables. Perm space is not pre-allocated. It represents a maximum limit.

Spool Space :All databases also have an upper limit of spool space. If there is no limit defined for a particular database or user, limits are inherited from parents.

Theoretically, a user could use all unallocated pace in the system for their query. Spool space is temporary space used to hold intermediate query results or formatted answer sets to queries. Once the query is complete, the spool space is released.

Example: You have a database with total disk space of 100GB. You have 10GB of user data and an additional 10GB of overhead. What is the maximum amount of spool space available for queries?

Answer: 80GB. All of the remaining space in the system is available for spool

Temp Space :The third type of space is temporary space. Temp space is used for Global and Volatile temporary tables, and these results remain available to the user until the session is terminated. Tables created in temp space will survive a restart.

=========================================================================

How Does Teradata Store Rows?

Teradata uses hash partitioning and distribution to randomly and evenly distribute data across all AMPs.
The rows of every table are distributed among all AMPs - and ideally will be evenly distributed among all AMPs.
Each AMP is responsible for a subset of the rows of each table.
Evenly distributed tables result in evenly distributed workloads.

=========================================================================

Which one will take care when an AMP goes down?

Down amp recovery journal will start when AMP goes down to restore the data for the down amp.

Fall back is like it has redundant data,if one amp goes down in the cluster also it wont affect your queries.the query will use data from fall back rows.the down amp wont be updated use the data from fall back.

=========================================================================

Which one will take care when a NODE goes down?

In the event of node failure, all virtual processors can migrate to another available node in the clique.

All nodes in the clique must have access to the same disk arrays

=========================================================================

What is the use of EXPLIN plan?

The EXPLAIN facility allows you to preview how Teradata will execute a requested query. It returns a summary of the steps the Teradata RDBMS would perform to execute the request.

EXPLAIN also discloses the strategy and access method to be used, how many rows will be involved, and its cost in minutes and seconds. Use EXPLAIN to evaluate a query performance

and to develop an alternative processing strategy that may be more efficient. EXPLAIN works on any SQL request. The request is fully parsed and optimized, but not run. The complete plan is returned to the user in readable English statements.

EXPLAIN provides information about locking, sorting, row selection criteria, join strategy and conditions, access method, and parallel step processing. EXPLAIN is useful for performance tuning, debugging, pre-validation of requests, and for technical training.

=========================================================================

What COALESCE function?

The newer ANSI standard COALESCE can also convert a NULL to a zero. However, it can convert a NULL value to any data value as well.
The COALESCE searches a value list, ranging from one to many values, and returns the first Non-NULL value it finds.
At the same time, it returns a NULL if all values in the list are NULL.

To use the COALESCE, the SQL must pass the name of a column to the function. The data in the column is then compared for a NULL. Although one column name is all that is required, normally more than one column is normally passed to it. Additionally, a literal value, which is never NULL, can be returned to provide a default value if all of the previous column values are NULL

The syntax for the COALESCE follows:

SELECT COALESCE ( [, ] )
,( COALESCE([,] ) )
FROM GROUP BY 1 ;
=========================================================================

When will you create PPI and when will you create secondary indexes?

Partitioned Primary Indexes are Created so as to divide the table onto partitions based on Range or Values as Required. This is effective for Larger Tables partitioned on the Date and integer columns. There is no extra Overhead on the System (no Spl Tables Created ect )

Secondary Indexes are created on the table for an alternate way to access data. This is the second fastest method to retrieve data from a table next to the primary index. Sub tables are created.

PPI and secondary indexes do not perform full table scans but they access only a defined st of data in the AMP's.

=========================================================================
When you chose primary index and when will you choose secondary index?
Primary index will be chosen at the time of table creation. This will help us in data distribution, data retrieval and join operations.
Secondary indexes can be created and dropped at any time. They are used as an alternate path to access data other than the primary index.

Related Posts:
Interview Questions Part-2 Interview Questions Part-3

Interview Questions Part-2

2013-11-26T20:33:00.000+05:30

Diff between database and user?
Both may own objects such as tables, views, macros, procedures, and functions. Both users and databases may hold privileges.
However, only users may log on, establish a session with the Teradata Database, and submit requests.

A user performs actions where as a database is passive. Users have passwords and startup strings; databases do not. Users can log on to the Teradata Database, establish sessions, and submit SQL statements; databases cannot.

Creator privileges are associated only with a user because only a user can log on and submit a CREATE statement. Implicit privileges are associated with either a database or a user because each can hold an object and an object is owned by the named space in which it resides

=========================================================================
When a PI is not mentioned on a table, how will Teradata consider the PI for that table?

If you don't specify a PI at table create time then Teradata must chose one. For instance, if the DDL is ported from another database that uses a Primary Key instead of a Primary Index, the CREATE TABLE contains a PRIMARY KEY (PK) constraint. Teradata is smart enough to know that Primary Keys must be unique and cannot be null. So, the first level of default is to use the PRIMARY KEY column(s) as a UPI.
If the DDL defines no PRIMARY KEY, Teradata looks for a column defined as UNIQUE. As a second level default, Teradata uses the first column defined with a UNIQUE constraint as a UPI.
If none of the above attributes are found, Teradata uses the first column defined in the table as a NON-UNIQUE PRIMARY INDEX (NUPI).

=========================================================================
What is covered query in Teradata?

If a SELECT query covers all the columns that are defined in the JOIN INDEX as join columns, such type of queries are called as COVERED query.
Multi-Column NUSI Columns used as a Covered Query
=========================================================================
What is the use of WITH CHECK OPTION in Teradata?
In Teradata, the additional key phase: WITH CHECK OPTION, indicates that the WHERE clause conditions should be applied during the execution of an UPDATE or DELETE against the view. This is not a concern if views are not used for maintenance activity due to restricted privileges.

=========================================================================
What is identity column?
Teradata has a concept of identity columns on their tables beginning around V2R6.x. These columns differ from Oracle's sequence concept in that the number assigned is not guaranteed to be sequential. The identity column in Teradata is simply used to guaranteed row-uniqueness.
Example:
CREATE MULTISET TABLE MyTable
(
ColA INTEGER GENERATED BY DEFAULT AS IDENTITY
(START WITH 1
INCREMENT BY 20)
ColB VARCHAR(20) NOT NULL
)
UNIQUE PRIMARY INDEX pidx (ColA);
Granted, ColA may not be the best primary index for data access or joins with other tables in the data model. It just shows that you could use it as the PI on the table.

=========================================================================
How to implement UPSERT logic in Teradata using SQL?

We have MERGE-INTO option available in Teradata data which works as an UPSERT logic in teradata. If the condition matches then update else insert.

Example: MERGE into dept_table1 as Taregt

USING (SELECT dept_no, dept_name, budget

FROM dept_table where dept_no = 20) Source

ON (Target.dept_no = 20)

WHEN MATCHED then

UPDATE set dept_name = ëBeing Renamedí

WHEN NOT MATCHED then

INSERT (dept_no, dept_name, budget)

VALUES (source.dept_no, source.dept_name, source.budget);

=========================================================================
What is value ordered NUSI?
When we define a value ordered NUSI on a column the rows in the secondary subtable get sorted based on the secondary index value. The columns should be of integer or date type.
This is used for range queries and to avoid full table scans on large tables.

=========================================================================
What are the DBQL tables.

Database Query Log tables are the tables present in DBC database which store the history of all the operations performed on the tables present in the databases.
The history could get very large so these tables should be purged when the data is no longer needed.

=========================================================================
What is the difference between sample and top?

The Sampling function (SAMPLE) permits a SELECT to randomly return rows from a Teradata database table.
It allows the request to specify either an absolute number of rows or a percentage of rows to return. Additionally, it provides an ability to return rows from multiple samples.

SELECT * FROM student_course_table SAMPLE 5;

The TOP clause is used to specify the number of records to return. The TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance.

SELECT TOP 2 * FROM EMP.

=========================================================================
What is difference between ZEROIFNULL and NULLIFZERO ?
The ZEROIFNULL function: will pass zero when data coming as null
The NULLIFZERO function: will pass null when data coming as zero.

=========================================================================
What is Range_N?
Range_N is defined on a partition primary index to specify the range of values of a column that should be assigned to a partition.
The number of partitions = the number of ranges specified + no case + unknown
no case ñ> if the value does not belong to any range
unknown -> for the values like nulls, spaces etc

=========================================================================
What is Casting in Teradata ?

It will convert the data type
The casting is similar to DDL:
CAST('02/03/2009-01:25:11' AS TIMESTAMP FORMAT 'MM/DD/YYYY-HH:MI:SS')
=========================================================================
Can we do collect stats on a table when the table is being updated?
No.We never collect stats on a table when the table is being updated. It will go for dead lock.

=========================================================================
When will you go for hash index?

A hash index organizes the search keys with their associated pointers into a hash file structure.
We apply a hash function on a search key to identify a bucket, and store the key and its associated pointers in the bucket (or in overflow buckets).
Strictly speaking, hash indices are only secondary index structures, since if a file itself is organized using hashing, there is no need for a separate hash index structure on it.

=========================================================================
Difference between Global and Volatile Temporary Table? Where they can be used and how they can be used?

Global Temporary Tables

Materialize in users temporary space
Base definitions are permanent in Data Dictionary tables
Definitions can be materialized by any user with the necessary DML privileges
Can be defined for COLLECT STATISTICS
Can survive a Teradata Database restart
Up to 2000 materialized tables per session
Materialized instance is local to a session
If not dropped manually, instance is dropped automatically at session end
An instance an be dropped manually any time during a session with DROP TEMPORARY TABLE
Materialized contents are not shareable with other sessions
A very large number of global tables can prolong logoff, because materialized global tables are dropped automatically at session end.

Volatile Tables

Occupy space in users SPOOL allocation
Definitions are kept in cache and go away at session end or during a Teradata Database restart
Private to the session in which they are created
Can be defined for COLLECT STATISTICS(TD14 feature)
Do not survive a Teradata Database reset
Up to 1000 tables per session
Local to a session
If not dropped manually, dropped automatically at session end
Can be dropped manually any time during a session with DROP TABLE
Contents are not sharable with other sessions
A very large number of volatile tables can prolong logoff, because volatile tables are dropped automatically at session end.

Related Posts:
Interview Questions Part-1 Interview Questions Part-3

Interview Questions Part-3

2013-11-25T20:37:00.000+05:30

What are the advantages of partitioned tables:
1)They provide efficient searches by using partition elimination at the various levels or combination of levels.
2)They reduce the I/O for range constraint queries
3)They take advantage of dynamic partition elimination
4)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.)
5)The Primary Index may be either a UPI or a NUPI; a NUPI allows local joins to other similar entities
6)Row hash locks are used for SELECT with equality conditions on the PI columns.
7)Partitioned tables allow for fast deletes of data in a partition.
8)They allow for range queries without having to use a secondary index.
9)Specific partitions maybe archived or deleted.
10)May be created on Volatile tables; global temp tables, base tables, and non-compressed join indexes.
11)May replace a Value Ordered NUSI for access.

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

=========================================================================
What are the Disadvantages of partitioned tables
1)Rows in a partitioned table are 2 bytes longer.
2)Access via the Primary Index may take longer.
3)Full table joins to a NPPI table with the same PI may take longer.
=========================================================================
Difference between Set and Multiset Table?
The SET table does not allow duplicate rows while the multiset table allow duplicate rows. For a SET table, to enhance the performance and to enforce uniqueness, a UPI or a USI can be created on a particular column. For a multiset table, a NUPI or NUSI can be created on a particular column to enhance performance.

Characteristics of SET tables:
Logically correct Implementation
Automatic duplicate row elimination

Characteristics of MULTISET tables:

No duplicate row check overhead
Data integrity becomes a user responsibility

=========================================================================What are the Advantages and Disadvantages of multi-column PI?
As many as 64 columns may be included in an index. Multi-column Primary Indexes may provide better distribution.

Advantage
*More columns = more uniqueness
*Distinct value increases.
*Rows per value decreases.
*Selectivity increases.

Disadvantage
*More columns = less usability
*PI can only be used when values for all PI columns are provided in SQL statement.
*Partial values do not generate a useful hash result.
=========================================================================
What is Value List Compression? how would you implement compression ?
The Value list compression cannot compress an existing column but it can be used to add a compressed column to a table.
Use the ALTER TABLE statement to compress columns and reduce the number of I/O operations. Consider the following:
*Set the column default value to most frequent value.
*Compress to the default value.
*This is especially useful for sparsely populated columns.
*Overhead is not high.
*The I/O savings correlates to the percentage of data compressed out of a row.

Value List Compression (VLC) provides the Teradata database with the capacity to support multiple value compression for fixed width columns. When you specify a values or values, the system suppresses any data matching the compress value from a row. This saves disk space. Smaller physical row size results in less data blocks and fewer I/Os and improved overall performance.

Because VLC allows you to specify a list of compress values for a column, the system suppresses data when one of the specified values exists in the column. Up to 255 distinct values (plus NULL) may be compressed per fixed-width column.

VLC improves performance as follows:
*Reduces the I/O required for scanning tables when the tables have compressible values in their columns.
*Reduces disk space because rows are smaller.
*Permits joins to look up the tables to be eliminated.
*Improves data loading because more rows may fit into one data block after compression is applied.

=========================================================================
What have you done to resolve the running out of spool problem?
To resolve running out of spool problem,
1) The column involved in the where clause/joining column is an index column or not, should be checked .
2) Also whether statistics have been collected on the index column. If no statistics have been collected then collect stats on it.
3) Check maxspool and peak spool from dbc.diskspace per amp basis and check for data skew.
4) Also check for improper join condition or join condition missing.
4) Despite of the steps taken, the running out of spool problem comes then explicitly increase the spool space.

=========================================================================
What are the 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

=========================================================================
What are the limitations of Multi load?
1)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.

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

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

4)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.

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

6)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
=========================================================================
Explain the phases in Multiload?

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.
=========================================================================
What are the limitations of Tpump?

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.

=========================================================================
How many types of error tables in Fast Load?

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.
=========================================================================
What is Quantiles?

Quantiles are used to divide a number of rows into a number of partitions of roughly equal size. The most common quantile is the percentile which is based on a value of 100. It is also not uncommon to have quartiles (based on 4), tertiles (based on 3) or deciles (based on 10). Notice that by default, both the quantile column and the quantile value itself will be output in ascending sequence.

Aggregations may not be combined with OLAP functions, thus a derived or temporary table is sometimes necessary to produce the aggregation before the function is applied. The standard form of the quantile function is:

QUANTILE (quantile_constant,sortlist)

SELECT employee_number, salary_amount,
QUANTILE (100, employee_number,salary_amount )
FROM employee
QUALIFY QUANTILE (100, employee_number) < 25;

=========================================================================
What is WIDTH_BUCKET ?

The WIDTH_BUCKET function assigns partition numbers to values associated with a column or expression, based on a specified range and based on the number of partitions desired.
Like many OLAP and aggregate functions, WIDTH_BUCKET is used to extract statistical information from sample populations.
The format of the function is as follows:
WIDTH_BUCKET (column_expr,lower_bound, upper_bound ,partition_count)

=========================================================================
What is Random function?

The RANDOM function may be used to generate a random number between a specified range.
RANDOM (Lower limit, Upper limit) returns a random number between the lower and upper limits inclusive. Both limits must be specified, otherwise a random number between 0 and approximately 4 billion is generated.

SEL DEPARTMENT_NUMBER,RANDOM(1,9) FROM EMPLOYEE;

limitations:
RANDOM is non-ANSI standard
RANDOM may be used in a SELECT list or a WHERE clause, but not both
RANDOM may be used in Updating, Inserting or Deleting rows
RANDOM may not be used with aggregate or OLAP functions
RANDOM cannot be referenced by numeric position in a GROUP BY or ORDER BY clause
=========================================================================
Related Posts:
Interview Questions Part-1 Interview Questions Part-2

TPT

2013-10-25T00:06:00.002+05:30

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.

Producer or READ Operator
Filter Operator or TRANSFORM Operator
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

TPUMP

2013-10-23T21:22:00.004+05:30

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

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

LOGTABLE EMPDB.EMP_TPUMP_LOG;

LOGON TDDB/USERNAME,PWD;

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

.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 INFILE C:\TEMP\TPUMP_FLAT_FILE.txt;

LAYOUT RECLAYOUT
APPLY EMP_INS

.END LOAD;
.LOGOFF;

MULTI LOAD

2013-10-23T21:19:00.004+05:30

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:

Setting up a Logtable
Logging onto Teradata
Identifying the Target, Work and Error tables
Defining the INPUT flat file
Defining the DML activities to occur
Naming the IMPORT file
Telling MultiLoad to use a particular LAYOUT
Telling the system to start loading
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;