What are the different return codes(severity errors) in Teradata utilities?

Ans:

There are 3 basic return codes (severity errors) in teradata utilities.

0 - success

4 - Warning

8 - User error

12 - System error
16 - system error   


Please note that apart from this there are separate error codes for each of the error  returned from sql queries.

What is explain in teradata?

What is explain in teradata?

The EXPLAIN facility is a teradata extension that provides you with an "ENGLISH" translation of the

steps chosen by the optimizer to execute an SQL statement. It may be used on any valid teradata database with a preface called "EXPLAIN".

The following is an example:-

EXPLAIN select last_name first_name FROM employees;

1. The EXPLAIN parses the SQL statement but does not execute it.
2. This provides the designer with an "execution strategy".
3. The execution strategy provides what an optimizer does but not why it chooses them.
4. The EXPLAIN facility is used to analyze all joins and complex queries.

How to make sure Optimizer chooses NUSI over Full Table Scan (FTS)?

Answer:

A optimizer would prefer FTS over NUSI, when there are no Statistics defined on NUSI columns.
It would prefer scanning for full table instead of going for Subtables in NUSI  since optimized does not have information about subtables of NUSI

It is always suggested to collect statistics whenever NUSI columns are defined on the table.

Verify whether index is being used by checking in Explain plan.

When should the statistics be collected?

Ans:

Here are some excellent guidelines on when to collect statistics:

·       All Non-Unique indices

·       Non-index join columns

·       The Primary Index of small tables

·       Primary Index of a Join Index

·       Secondary Indices defined on any join index

·       Join index columns that frequently appear on any additional join index columns that frequently appear in WHERE search conditions

·       Columns that frequently appear in WHERE search conditions or in the WHERE clause of joins.

How to find out list of indexes in Teradata?

How to find out list of indexes in Teradata?

IndexType	Description
P	Nonpartitioned Primary
Q	Partitioned Primary
S	Secondary
J	join index
N	hash index
K	primary key
U	unique constraint
V	value ordered secondary
H	hash ordered ALL covering secondary
O	valued ordered ALL covering secondary
I	ordering column of a composite secondary index
M	Multi column statistics
D	Derived column partition statistics
1	field1 column of a join or hash index
2	field2 column of a join or hash index

SELECT

databasename, tablename, columnname, indextype, indexnumber, indexname

FROM  

dbc.indices

ORDER BY

databasename,  tablename, indexnumber;

How does indexing improve query performance?

Answers:

Indexing is a way to physically reorganize the records to enable some frequently used queries to run faster.

The index can be used as a pointer to the large table. It helps to locate the required row quickly and then return it back to the user.

Or

The frequently used queries need not hit a large table for data. They can get what they want from the index itself. - cover queries.

Index comes with the overhead of maintenance. Teradata maintains its index by itself. Each time an insert/update/delete is done on the table the indexes will also need to be updated and maintained.

Indexes cannot be accessed directly by users. Only the optimizer has access to the index.

What is the difference between MultiLoad & Fastload interns of Performance?

Answers:

If you want to load, empty table then you use the fastload, so it will very useful than the MultiLoad ,because fastload performs the loading of the data in 2phase and its no need a work table for loading the data .

So it is faster as well as it follows the below steps to load the data in the table

Phase1 - It moves all the records to the entire AMP first without any hashing

Phase2 - After giving end loading command, Amp will hashes the record and send it to the appropriate AMPS.

MultiLoad:

It does the loading in the 5 phases

Phase1 - It will get the import file and checks the script

Phase2 - It reads the record from the base table and store in the work table

Phase3 - In this acquisition phase it locks the table header

Phase4 - In the DML operation will done in the tables

Phase5 - In this table locks will be released and work tables will be dropped.

What is a clique?

What is a clique?

A clique is a set of Teradata nodes that share a common set of disk arrays which are connected in daisy chain network to each disk array controller.

Cliques provide data accessibility if a node fails for any reason,  Proms are distributed across all nodes in the system. Large multiple node systems will have clique mechanisms associated with them

How Teradata makes sure that there are no duplicate rows being inserted when it’s a SET table?

Answers:

Teradata will redirect the new inserted row as per its PI to the target AMP (on the basis of its row hash value), and if it find same row hash value in that AMP (hash synonyms) then it start comparing the whole row, and find out if duplicate.

If it’s a duplicate it silently skips it without throwing any error.

How to view every column and the columns contained in indexes in Teradata?

Answers:

Following query describes each column in the Teradata RDBMS

SELECT * FROM DBC.TVFields;

Following query describes columns contained in indexes in the Teradata RDBMS

SELECT * FROM DBC.Indexes;

How to select first N Records in Teradata?

Answers:

To select N records in Teradata you can use RANK function. Query syntax would be as follows

SELECT BOOK_NAME, BOOK_COUNT, RANK(BOOK_COUNT) A FROM LIBRARY QUALIFY A <= 10;

Where does TD store transient journal?

Ans:

In perm space -> dbc.transientjournal

But that special table can grow over dbc's perm limit until the whole system runs out of perm space.

What are the advantages and dis-advantages of secondary Indexes?

Answer:

Advantages:

1. A secondary index might be created and dropped dynamically

2.A table may have up to 32 secondary indexes.

3. Secondary index can be created on any column. .Either Unique or Non-Unique

4. It is used as alternate path or Least frequently used cases.  ex. defining SI on non indexed column can improve the performance, if it is used in  join or filter condition of a given query.

5. Collecting Statistics on SI columns make sure Optimizer choses SI if it is better than doing Full Table Scans

Disadvantages

1. Since Sub tables are to be created, there is always an overhead for additional spaces.

2. They require additional I/Os to maintain their sub tables.

3. The Optimizer may, or may not, use a NUSI, depending on its selectivity.

4. If the base table is Fallback, the secondary index sub table is Fallback as well.

5. If statistics are not collected accordingly, then the optimizer would go for Full Table Scan.

What are the difference types of temporary tables in Teradata?

Answers:

a.       Global temporary tables

b.       Volatile temporary tables

c.       Derived tables

Global Temporary tables (GTT) –

1. When they are created, its definition goes into Data Dictionary.

2. When materialized data goes in temp space.

3. That's why, data is active up to the session ends, and definition will remain there up-to its not dropped using Drop table statement. If dropped from some other session then its should be Drop table all;

4. You can collect stats on GTT.

5. Defined with the CREATE GLOBAL TEMPORARY TABLE sql

Volatile Temporary tables (VTT) -

1. Local to a session (deleted automatically when the session terminates)

2. Table Definition is stored in System cache .A permanent table definition is stored in the DBC data dictionary database (DBC.Temptables) .

3. Data is stored in spool space.

4. That’s why; data and table definition both are active only up to session ends.

5. No collect stats for VTT.If you are using volatile table, you can not put the default values on column level (while creating table)

6. Created by the CREATE VOLATILE TABLE sql statement

Derived tables

1 Derived tables are local to an SQL query.

2 Not included in the DBC data dictionary database, the definition is kept in cache.

3 They are specified on a query level with an AS keyword in an sql statement

How do you find out number of AMP's in the Given system

Answer

1.running following query in queryman

Select HASHAMP () +1;

2. We can find out complete configuration details of nodes and amps in configuration screen of Performance monitor

How many error tables are there in fload and Mload and what is their significance/use?

Answers: 

Fload uses 2 error tables

ET TABLE 1: where format of data is not correct.

ET TABLE 2: violations of UPI

It maintains only error field name, error code and data parcel only.

Mload also uses 2 error tables (ET and UV), 1 work table and 1 log table

1. ET TABLE - Data error

MultiLoad uses the ET table, also called the Acquisition Phase error table, to store data errors found during the acquisition phase of a MultiLoad import task.

2. UV TABLE - UPI violations

MultiLoad uses the UV table, also called the Application Phase error table, to store data errors found during the application phase of a MultiLoad import or delete task

Apart from error tables, it also has work and log tables

3. WORK TABLE - WT

Mload loads the selected records in the work table

4. LOG TABLE

A log table maintains record of all checkpoints related to the load job, it is essential/mandatory to specify a log table in mload job. This table will be useful in case you have a job abort or restart due to any reason.

What are set tables and multiset tables in Teradata?Explain with an appropriate example?

Answers:
1) Set tables cannot accept duplicate at row level not Index or key level.
Example of rows for set table:
R1 c2 c3 c4 ..cn
1 2 3 4 ... 9

Accepted
1 2 3 4 ... 9
Duplicate is Rejected
2 1 2 4 ... 9
3 2 4 4 ... 9
4 3 4 4 ... 9

2) Multi set Tables can accept
duplicate at row level not Index or key level.Exmaple of rows for multi set table:
R1 c2 c3 c4 .. cn
1 2 3 4 ... 9
1 2 3 4 ... 9
Duplicate is Accepted
2 1 2 4 ...9
3 2 4 4 ...9
3 2 4 4 ...9
Duplicate is Accepted

What is residual condition in explain plan ?

What is residual condition in explain plan ?
Answer

It is a condition which help u to reduce the number used for join condition. Residual condition does not help in locating a row

Why are AMPs and PEs called as vprocs ?

Answer

AMPs and PEs are implemented as “virtual processors - vprocs”. 
They run under the control of PDE and their number is software configurable.
AMPs are associated with “virtual disks – vdisks” which are associated with logical units (LUNs) within a disk array

Answer2:
Vprocs:Virtual process From PE to AMP (This is the network root via MSP(message passing layer),The processing data will store in Disks(These are Physical disks),Each Amp have too many P.disks,to migrate these P.disks The Bynet Network maintains Virtual disks.These V.disks will responsible for data migration.hence they are called as Virtual Process(VPROCS).

What is Teradata Virtual storage?

Answer

This concept is introduced in TD12. It does the following tasks
- maintains information on frequency of data access 
- tracks data storage task on physical media
- migrating frequently used data to fast disks and less frequently used data to slower disks
- allocating cyclinders from storage to individual AMPs

Which is faster – MultiLoad delete or Delete command?

Answer

MultiLoad delete is faster then normal Delete command, since the deletion happens in data blocks of 64Kbytes, where as delete command deletes data row by row.  Transient journal maintains entries only for Delete command since Teradata utilities doesn’t support Transient journal loading.

What do High confidence, Low confidence and No confidence mean in EXPLAIN plan?

Answer

Explain gives the execution strategy means what are the different steps that the query will go through.
HIGH CONFIDENCE:  Statistics are collected.
LOW CONFIDENCE:   Statistics are not collected.  But the where condition is having the condition on indexed column. Then estimations can be based on sampling.
NO CONFIDENCE: Statistics are not collected and the condition is on non indexed column.

What is the default join strategy in Teradata???

Answer
# 1
The Teradata Cost based optimizer will decide the join strategy based on the optimum path. The common strategies followed are from Merge, Hash & Nested Join

Answer 
# 2 
Three strategies followed by optimizer are:
1. Duplication of rows of one table to every amp
--> This one is opted by optimizer when the non-PI column is on a small table.
2. Redistribution of the non PI rows of the table to the amp containing the matching PI row amp.
--> This one is opted when the tables are large. The non PI column is in one table is redistributed to the amp containing the matching PI.
3. Redistribute both the rows of the table by hash values.
--> This is chosen when the join is on a column that is not the PI in either table. This will require the most spool space.

How many types of Skew exist?

Answer

If you utilized unequally TD resources (CPU,AMP,IO,Disk and etc) this is called skew exists. Major are 3 types of skews (CPU skew, AMP/Data skew, IO Skew).

-Data skew?
When data is not distributed equally on all the AMPs.
-Cpu skew?
Who is taking/consuming more CPU called cpu skew.
-IO skew?
Who perform more IO Operation? Resulting in IO Skew

Can you load multiple data files for same target table using Fastload?

Answer:

Yes, we can Load a table using multiple datafiles in Fastload.

Before giving "end loading" statement user can define file path and use insert sql for multiple source files and give "end loading" statement at the end

How many codd's rules are satisfied by teradata database?

How many codd's rules are satisfied by teradata database?
Answers:
12 codd’s rules

Explain about Skew Factor?

Answers:

The data distribution of table among AMPs is called Skew Factor . Generally For Non-Unique PI we get duplicate values so the more duplicate vales we get more the data have same row hash so all the same data will come to same amp, it makes data distribution inequality,One amp will store more data and other amp stores less amount of data, when we are accessing full table,
The amp which is having more data will take longer time and makes other amps waiting which leads processing wastage In this situation (unequal distribution of data)we get Skew Factor HighFor this type of tables we should avoid full table scans.
ex:AMP0 AMP110000(10%) 9000000(90%)in this situation skew factor is very high 90%

What is use of compress in teradata?Explain?

Answers:

Compression is used to Minimize the table size, for example when the table size is increasing anonymously We can use Compression to reduce the size of the table
Conditions:1.Compression can be declared at the time of table creation2.We can compress up to 256 column values(not columns) 3.We can't compress variable length fields (vartext,varchar..)

Explanation
for 2 condition:create table tab1(::Order_type char(25) compress ('air','sea','road'):)in the above example order type have 3 fields, one should be selected by the user, so one of the field will repeat for every order, like these column values we can use compress statement because these are repeating for
entire table, like these column values TD supports 256 col generally NCR people will provides ready made scripts for these type of compressions However, we can store only one value per column and not 3(air, sea, road). The compressed value is
stored in column header and is to be used as default of that column unless a value is present.
e.g.
Dept of Taxes in Washington has a database that tracks all people working in Washington. Around 99.9% of the tax payers would have Washington as their state code on their address. Instead of storing “Washington” in millions of records the compress will store the value “Washington” in the table header. Now, a value can be assumed in a row as a default unless another value exists inside the column

What is the process to restart the multiload if it fails

What is the process to restart the multiload if it fails?
Answers:

MULTILOAD will creates 2 error tables, 1 work table When MULTILOAD fails We have to unlock the Main Table, here error tables and work tables are not locked like FLOAD.

To Unlock the Main Table in  case of acquisation Phase :
Mload RELEASE MLOAD ;
To release lock in application phase failure :
RELEASE MLOAD .IN APPLY;

Can we load a Multi set table using MLOAD?

Answers:

We can Load SET, MULTISET tables using Mload, But here when loading into MULTISET table using MLOAD duplicate rows will not be rejected, we have to take care of them before loading.But in case of Fload when we are loading into MULTISET duplicate rows are automatically rejected, FLOAD will not load duplicate rows weather table is SET or MULTISET

Can I use “drop” statement in the utility “fload”?

Ans:

YES,But you have to declare it out of the FLOAD Block it means it should not come between .begin loading,.end loading FLOAD also supports DELETE,CREATE,DROP statements which we have to declare out of FLOAD blocking the FLOAD Block we can give only INSERT

Is it possible that there r two primary key will be in a same table?

Answers:

Primary key
==========
1. A table should have only one primary key
2. More than one column can consist of a primary key – upto 64 columns
3. Can not be NULL values (missing values)
4. Should be unique values (no duplicate value)

Foreign key
==========
1. A table may have zero or more than that up-to 32 keys
2. More than one column can consist a primary key – up to 64 columns
3. Can have NULL values
4. Can have duplicate values
5. There should be a corresponding primary key in the parent table to enforce referential integrity for a foreign key

What are the types of tables in Teradata ?

Answers:

1. set table
2. multiset table
3. derived table
4. global temporary table(temporary table)
5. volatile table

Join strategies?

Join Strategies There are 2 tables, table A with 10 million records, table B has 100 million records, now we are joining both tables, when we seen Explain Plan the plan showing TD will took the table A and it will redistributes itNow the Question is: By that plan is the optimizer is correct job or not ? Justify Ans2. From the same above example now the optimizer is taking Table B (100 million records) and it is distributing it, Now is the optimizer is doing best? and How you avoid this situation

Answers:

Teradata is smart enough to decide when to redistribute and when to copy.
It compares the tables. Are they comparable? or one is big as compared to the other?Based on simple logic it decides whether to distribute the smaller table on all the AMPs or to copy.
what I mean is the small table is copied into all the AMPs in the SPOOL space.Remember all always the Join's has to take place on the AMPs SPOOL Space.By redistributing it is making sure that the 100 million rows table gets the feeling that
it is making AMP local JOIN. Remember the basic thing what ever Teradata does.
It does keeping in consideration for Space and Performance and not to forget the Efficiency.

My simple formula:
If the table is small redistribute them to all the AMPs to have the AMP local Join.
Always JOINs are made AMP local if it cannot then you have the high chance of running out of SPOOL space.

What is the difference between start schema and Fsldm?

Answers:
FSLDM --> Financial Services Logical Data Model (Teradata FS-LDM 7.0) its developed by Teradata for the financial sectors (Specifically for Bank). it can be customised based on the user requirement.

StarSchema --> Its a relational database schema for representing multidimensional data. The data is stored in a central fact table, with one or more tables holding information on each dimension.