What is the function of Teradata EXPLAIN Command ?

The Teradata EXPLAIN command can be added to the front of the SQL statement and while execution it gives a detailed analysis for particular statement. The EXPLAIN command can be used to estimate what are the indexes to be used,how much time a query will take and how many rows a query will be return. This command can be used as a useful debug tool to resolve problems with long processing times.

Teradata HELP TABLE Command

The Teradata HELP TABLE command displays the information regarding name, data type, and comment (if applicable), of all columns in particular table:


HELP TABLE Customer_Service.emp;

ColumnName	Type	Comment
emp_number	I	System assigned identification
mgr_emp_number	I
dept_number	I	Department employee works in
job_code	I	Job classification designation
l_name	CF	Employee surname
f_name	CV	Employee given name
hire_date	DA	Date employee was hired
birth_date	DA
sal_amt	D	Annual compensation amount

Data type Representations are as follows:

Type                Description

BF                     BYTE
BV                    VARBYTE

CF                     CHARACTER FIXED

CV                    CHARACTER VARIABLE

DA                    DATE

I1                     BYTEINT

I2                     SMALLINT

AT                     TIME

TS                     TIMESTAMP

D                      DECIMAL

I                       INTEGER

What is BTEQ in Teradata?

It is client s/w that resides on network or channel-attached host. After starting BTEQ,you can log on to Teradata using a TDPid (Teradata Director Program id) with your user id and password. The TDPid identifies the instance of TD you are going to access.

Use of BTEQ (Basic Teradata Query program) is to submit SQL queries to Teradata Database. It works like a interface b/w query and TD.

Teradata SQL basic commands

Teradata basic SQL commands commonly are divided into three categories:

1.Data Definition Language (DDL): - We can define and create database objects such as tables, macros, views, databases and users etc by using DDL commands.

SQL statement	Function
CREATE	To define a table, macro,view, index, trigger or stored procedure.
DROP	To remove a table, view, macro, index, trigger or stored procedure.
ALTER	To change table structure or protection definition.

          

2.Data Manipulation Language (DML): - Work with the data including tasks such as inserting data rows into a table and updating an existing row or performing queries on the data by using the DML  commands. 

SQL statement	Function
SELECT	To select data from one or more tables.
INSERT	To place a new row into a table.
UPDATE	To change data values in one or more existing rows.
DELETE	To remove one or more rows from a table.

3.Data Control Language (DCL): - Here we can perform the administrative tasks such as granting and revoking privileges to database objects or controlling ownership of those objects by using DCL  commands.

SQL statement	Function
GRANT	To give user privileges.
REVOKE	To remove user privileges.
GIVE	To transfer database ownership.

What are set tables and multiset tables in Teradata?

If the table is specified as SET , table do not have duplicate rows.

If the table is specified as MULTISET , table will be having duplicate rows.

Data Distribution in Teradata

Here we can see how Teradata performs Data distribution.


PEs are assigned either to LAN connections or to channel connections (e.g., IBM Mainframe). Data will be always stored by the AMPs in the form of 8-bit ASCII. If the input is in any form like EDCDIC, the PE converts it to ASCII before any hashing data and data distribution takes place.

A USER may have a HOST or COLLATION = MULTINATIONAL, EBCIDC, ASCII. If  COLLATION = EBCDIC or the HOST is an EBCDIC host , then the AMPs convert the data from ASCII to EBCDIC before doing any sorts or comparisions. MULTINATIONAL collation allows sites to create their own collation file. Otherwise all sorts and comparisons use the ASCII collating sequence.

Teradata do not have concept of pre allocated table space. The rows of all tables are distributed randomly across all AMPs after hashing and then randomly within the space available on the selected AMP.Data distribution is  directly dependens on the hash value of the primary index.

AMP means Access Module Processor
PE means Parsing Engine.

Types of Joins in Teradata

Whenever we need to retrieve data from two or more tables we can use Joins.The output of join is matching records from both the tables. 

In Teradata database we have Optimizer to determine which type of join strategy to be used and type of indexes to be used.

In General, Most common types of joins are the following.

Inner join (Self join)

Outer Join (Left outer join, Right outer join, Full outer join)

Cross join (Cartesian product join)

What is the difference between GROUP BY and DISTINCT?

Both generates the same query plan.

Use DISTINCT with few duplicates
Use GROUP BY with many duplicates 

By using DISTINCT, we can find unique rows and eliminate duplicate rows in the table. 

Eg:- Select Distinct(ColumnName) from tablename;

Whenever we are using aggregate operators like min, max, sum, count etc we can use GROUP BY .


Eg:-Select empid,min(salary),max(salary),sum(salary) from emp group by empid;

What is Join? Explian about Inner and Outer Joins?

We can use Join condition whenever we need to retrieve data from two (or) more tables.

Join can be used in either Where or From clause. But Join specification in From clause is recommended. We can also use Having and Where clause for filtering the data.

Joins can be classified into following types :

1.Inner Join (Equi Join & Natural Join)

2.Outer Join (Left Outer Join, Right Outer Join & Full Join)

3.Self Join

4.Cross Join

Inner Join: To retrieve matched records we can use comparison operators (i.e. =, < >).Whenever we need to get the matched records from two tables,this type of Join is used. 

Inner Join Also call as Natural Join or Equi Join.

Syntax:

Select emp.emp_id, emp. sal, ph.phone_no 

from employee emp Inner Join phone ph

on emp.emp_id = ph. emp_id

where emp.sal > 2000

Outer Join : To retrieve matched and unmatched records from two tables we can use Outer Join. Unmatched rows will be displayed as NULL from both tables.

Outer Join can be classified as: Left Outer Join , Right Outer Join and Full Outer Join.

Left Outer Join: All matched and unmatched records from left table will be displayed and unmatched records will be displayed as NULL from left table.But from right table only matched records will be displayed as it is and unmatched records will not be displayed.

Syntax:

Select emp.emp_id, emp. sal, ph.phone_no 

from employee emp Left Join phone ph

on emp.emp_id = ph.emp_id

where emp.sal > 2000 ;

Right Outer Join: All Records from right table will be displayed either matched or unmatched and unmatched records will be displayed as NULL from right table.But from left table only matched records will be displayed as it is and unmatched records will not be displayed.

Syntax:

Select emp.emp_id, emp. sal, ph.phone_no 

from employee emp Right Join phone ph

on emp.emp_id = ph.emp_id

where emp.sal > 2000 ;

Full Outer Join:  All matched and unmatched records from both left table and right table will be displayed and unmatched records will be displayed as NULL from both the tables.

Cross Join: This Join returns all rows from the left table, each and every row from the left table is in combination with all rows from the right table. Cross join also called  as Cartesian Product Join.

Syntax:

SELECT au.au_fname, au.au_lname, p.pub_name 

FROM authors AS au INNER JOIN publishers AS p 

ON au.city = p.city AND au.state = p.state 

ORDER BY au.au_lname ASC, au.au_fname ASC ;

Self Join: A table can be joined to itself is a self-join. 

For example, we can use a self-join to find out the authors in New Zealand who live in the same ZIP Code area.

SELECT au.au_fname, au.au_lname, a.au_fname, au2.au_lname FROM authors au INNER Jauthors a 

ON au.zip = a.zip 

WHERE au.city = ' New Zealand' 

ORDER BY au.au_fname ASC, au.au_lname ASC ;

What is the difference between Global temporary tables and Volatile temporary tables?

Global Temporary tables (GTT):-

1. Whenever we create GTT, its definition is stored into Data Dictionary.

2. Whenever we insert data into GTT,data is stored into temp space. So definition of the table will be active until we can delete using the drop table statement and data remains active up to end of the session only. 

3. We can Collect statistics on Global temporary tables.

Volatile Temporary tables (VTT) :-

1. Whenever we create VTT, its Definition is stored into System cache.

2. Whenever we insert data into VTT, data is stored into spool space.So table definition and data both are remains active only up to session end only. 
3. We cannot able to collect statistics on volatile tables.

Do we collect stats on volatile table?

Yes. We can collect stats on volatile tables from TD 13 ,But in earlier versions it is not possible to collect stats on volatile table.

Teradata ALTER Table function

We can alter the column without dropping the table by using the following syntax:

Syntax for adding new column name to table

ALTER TABLE <tablename> ADD <new_col>  <data_type>;

Syntax to modify a column datatype length

ALTER TABLE <Table_Name>

ADD <Existing_Column_Name> <New_Data_Type(New_Length)>

Here are the results while applying above syntax for changing column's data type:

3558: Cannot alter the specified attribute(s) for f_name.

VARCHAR ( from VARCHAR(20) TO VARCHAR(22)) - successfully executed

INTEGER ( FROM INTEGER TO DECIMAL(15,0)) - gives syntax error - 3558

CHAR ( FROM CHAR (5) TO CHAR (7)) -  syntax error - 3558

TIMESTAMP ( FROM TIMESTAMP(0) TO TIMESTAMP(1) )- syntax error - 3558

DECIMAL (FROM DECIMAL(15,0) TO DECIMAL(15,1) )- syntax error - 3558

It proves that these procedure is limited to VARCHAR.

Why MultiLoad Utility supports only Non Unique Secondary Index(NUSI) in the Target Table ?

Whenever we define a Secondary index, an Secondary index subtable will be created.In case of UPI, when they go for has distribution subtable is in one AMP and actual data row pointed by USI subtable is in another AMP. So the AMPs need to communicate, which is not supported by Multiload. But in case of NUSI, the subtable and the references of the actual data will store in the same AMP hence AMPs no need to communicate here.

So in case of NUSI, AMPs work in parallal and hence Mload supports that.

Difference b/w v2r5 and v2r6 ?

V2R6 included the new features like triggered stored procedures, queue tables, Multi statements request,Stored procedure Grammar Merge but these features are not available in V2r5.

How to handle nulls in Teradata?

Use  NULL in insert directly or use nullifzero,zeroifnull in select statement.

On which column will you take primary index?

1.Frequently using column.

2.Column that is not changing regularly (Not at all changing values /rarely)

3.By default Teradata database treats first column as PI

Teradata database can distibute the rows based on the uniqueness of the primary index. So generally the primary index is on the unique column should be recommended.

Teradata Objects

Table:- Table is a two dimensional structure.It is having both rows and columns.

View:- View is a predefined set of one or more tables (or) views.

Macro:- Macro is predefind set one or more DML statements. It is Used for simplification of execution of frequently used DMLs .

Triggger:- Trigger it is a set of sql statements usually associated with table or columns of table and when column changes, the trigger fires effectively while executing the SQL statements.

Stored procedure:- Stored procedure means set of SQL statements invoked by using single SQL call.

If a node in the clique fails then other node in the same clique has to do the failed node's job or other node in a different clique will do the job?

If a node fails in clique, other node in the same clique can do the work of the failed node job.

How to know if a table is locked ?

You can find it by using Show locks utility in Teradata Manager Tool.

You can use Teradata Manager -> Administrator -> Database Console Utility -> Show locks

LIKE Command in Teradata

We can see LIKE clause function below.


Suppose if you want to find out particular word or character is present in the starting of the string or ending of the string or middle of the string, You can use Like clause to narrow down the result set.

Syntax of LIKE clause:-


SELECT {*|<ColumnName>}

FROM <TableName>

WHERE <column name> LIKE [ALL | ANY] ‘[% | _] CharString [% | _]’

Here  ‘_’ means single character and ‘%’ means any no.of characters,.

If data value contains underscore and you need to display underscore as a value in the result set , use ESCAPE operator to treat the underscore as a value and not as a wildcard character. 

SELECT f_name, l_name

FROM employee

WHERE lname LIKE (‘S\_%’) ESCAPE ‘\’ 

The above query would find results like S_456, S_mon, Note that the underscore is present at the second position of the string.

Here, we can use any character whatever we like.

For example, both of following statements would return the same results.

SELECT f_name, l_name

FROM employee

WHERE  lname LIKE  (‘S@_%’) ESCAPE ‘@’ 

SELECT f_name, l_name

FROM employee

WHERE  lname LIKE (‘S$_%’) ESCAPE ‘$’