1) Why is BTET transaction processing overhead in Teradata.
Answer:
BTET makes all the queries running between BT and ET as single transaction . If any of query fails then the entire set of queries will not be committed
BTET makes all the queries running between BT and ET as single transaction . If any of query fails then the entire set of queries will not be committed.
BTET also has an overhead with locking , since it holds locks on tables till the ET is occured or all the queries have executed successfully
DDL statements cannot be used everywhere in BTET processing , but these statements can be given towards the end of BTET transaction.
Using large number of BTET caused transient Journal to grow and has tendancy for System Restart.

2) What are the options not available for global temporary table ?
Answer :
Global temporary tables are the temporary tables and occupies the temporary space defined.
Temporary tables definition is stored in Data Dictionary, It act as template and provide separate instance to each user who refers to them.
The following options are not available for global temporary tables:
Any kind of constraints like check/referential cannot be applied on table
Identity columns since data in GTT are materialized only for session
Permanent Journaling cannot be done as data in tables are instances only to user in that session
PPI cannot be applied as data does not get stored in PERM , only TEMP space is utilized here.

3) What are the options not available for Volatile tables ?
Answer: Volatile tables occupies the spool space in Teradata, They exist for a particular session and are dropped off.
The following options are not available for volatile tables because table definition is not stored in data dictionary
Default values for columns
Title clause for columns
Named Indexes for table
Compression on columns/table level since table data are spooled
Stats cannot be collected since data is materialized only for session
Identity columns as these again would need entry in IDcol tables
PPI cannot be done on tables
Any kind of constraints like check/referential cannot be applied on table

4) What are permanent journals in Teradata ?
Answer:
Journals are used to capture information about table in Teradata. In case of Permanent journals they capture details of Journal enabled tables in teradata with all the pre transaction and post transaction details .
Journal tables are assigned PERM space and they reside in same database as of parent or they can reside on different database.
They are mainly used for protection of data and sometimes also for disaster recovery ( fallback is better in this case ) Permanent journal tables can be enabled or disabled by running alter database <databasename> ‘no journal’ /’ journal = <databasename.jrnltbl>’
Arcmain utility provides the feature of backing up Journal tables
We can find details about all journal tables present in teradata database using DBC.JOURNALS table.

5) What is RAID, What are the types of RAID?
Answers : Redundant Array of Inexpensive Disks (RAID) is a type of protection available in Teradata. RAID provides Data protection at the disk Drive level. It ensures data is available even when the disk drive had failed.
There are around 6 levels of RAID ( RAID0 to RAID5) . Teradata supports Two levels of RAID protection
RAID 1 Mirrored copy of data in other disk
RAID 5 Parity bit (XOR) based Data protection on each disk array.
One of the major overhead’s of RAID is Space consumption

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

7) What is a clique?
Answers : 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.

8) What are benefits of Permanent Journal?
Answer :
The benefits of Permanent Journal are
Permits capture of before images for database rollback.
Permits capture of after images for database roll forward.
Permits archiving change images during table maintenance.
Reduces need for full-table backups.
Provides a means of recovering NO FALLBACK tables.
Requires additional disk space for change images.
Requires user intervention for archive and recovery activity

9) What are the benefits of fallback?
Answer :
The benefits of fallback are
Protects your data from hardware (disk) failure.
Protects your data from software (node) failure.
Automatically recovers with minimum recovery time, after repairs or fixes are complete

10) What is an error table? What are the uses of it?
Answer
An error table is a chart which contains the list of information concerning-
Contains rows which failed to be adjusted due to Translation error.
Captures rows that contain duplicate values for UPAs.
Data conversion errors and other error conditions.
Captures rows that contain duplicate values.

11) How can you apply access lock on table rather than read lock?
Answer. We can override default read lock on any table by explicitly mentioning the ACCESS LOCK before using the table. Syntax is:
LOCK TABLE EMPLOYEES FOR ACCESS
SELECT * FROM EMPLOYEES;

12) Fast Load Script is failed and error tables are available then how do you restart?
There are 2 ways
1.Incase of Old file to Run
Dont drop error tables simply rectify the error in the script or file and Run again.so that it runs from last configuration.

2.Incase of new file to Run
Drop Error tables
Try to run the script with only begin and end loading statements. ,so that it releases the lock on target table If possible remove the record from fastlog table.
Run the script with new file freshly.

13).Aborted in Phase 2 data acquisition completed in fastload?
Ans.Simply take Begin and End loading in scripts and Run again.so that it runs from Amp to table.

14).How do you load Multiple files to a table by using fast load scripts?
Ans.Remove End Loading statement in the script and Replace the file one by one in the script till last file and submit every time so that data appended in Amp Level.For the last file specify End Loading statement in the script and Run.so that it runs from Amp to table.

15).What is Virtual disk?
Ans. A collection of Cylinders(physical disks) arranged in an Array fashion is called Vdisk or Virtual disk.Traditionally this is called as disk Array or Array of disk.

16).what is Amp?
Ans.Access Module ProcessorIt is a Virtual processor responsible for managing one portion of the database(collection of virtualdisks).This portion is not sharable by any other AMP.so well call this Architecture as shared nothing Architecture.
Amp contains Database Manager subsystem and it performs the below operations
a.Performing DDL
b.Performing DML
c.Implementing Joins,Aggregations.
d.Applying and Releasing Locks etc.

17).What is Parsing Engine?
Ans.Refer Parsing Engine from the blog

18).How many MAX session PE handles at a time?
Ans.PE handles MAX 120 sessions.
19).What is BYNET?
Ans.It acts like a "Message Communication" happens between Components.It is responsible for
1. Sending messages
2. Merging data
3.Sorting data

20).what is Clique?
It prevents from Node Failure.
a.A Clique is a collection of Nodes will Share Common Disk drives.
b.whenever any node went down automatically Vprocs all migrate from fail node to other node to retrieve data from common disk drives.

21).List different types of LOCKS in teradata?
Teradata can apply four types of LOCKS
a.Access Lock
b.Read lock
c.Write Lock

22).How do you set the session mode parameters in BTEQ?
Ans.     .set session transaction ANSI-----> this is to set ANSI mode
.set session transaction BTET-----> this is to set Teradata transaction mode.
These commands have to be entered before logging to the session.

23).How Teradata makes sure that there are no duplicate rows being inserted when its a SET table?
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.

24).List types of HASH functions used in teradata?
Ans.There are HASHROW, HASHBUCKET, HASHAMP and HASHBAKAMP.
The SQL hash functions are:
HASHROW (column(s))
HASHBUCKET (hashrow)
HASHAMP (hashbucket)
HASHBAKAMP (hashbucket)

25).what is derived table?
a.It stores intermediate results and calculations.
b.You can specify derived table in an SQL statement(Preferrably Select).
c.This table created and dropped as a part of the query.
d.Its stored under spool memory.
e.Once the query finishes execution table is not available.
f.This types of tables are called as Inline Query tables.

26).How many types of Image supports Journaling?
Four types of Images are supported by Journaling they are
a. Single Image---->one copy of data will be taken.
b.Dual Image----->Two copies of data will be taken.
c.Before Image----->Before changes occur on the row data will be taken.
d.After Image---->After changes happen on the row taking copy of data.

27).What is Transient Journal?
Transient Journal - an area of space in the DBC database which is used primarily for storing of roll-back information during inserts/deletes/updates of tables.
Detail Explanation:
The Transient Journal maintains a copy of all before images of all rows affected by the transaction.
If the event of transaction failure, the before images are reapplied to the affected tables, the images are deleted from the journal and a rollback operation is completed.
In the event of transaction success, at the point of transaction commit,
the before images for the transaction are discarded from the journal.
In Summary, if a Transaction fails (for whatever reason), the
before images in the transient journal are used to return the data (in the
tables involved in the transaction) to its original state.

28).What is Permanent Journal?
Ans.The purpose of the Permanent Journal is to provide selective or full database recovery to a specified point in time.It permits recovery from unexpected hardware or software disasters.
The Permanent Journal also has the effect of reducing the need for full table backups which can be costly both in time and resource.
· Dual After Journal

29).Permanent Journals are automatically purged in teradata. True / False?
False.
The Permanent Journal must be manually purged from time to time.

30).Where does TD store transient journal?
Ans.In perm space -> dbc.transient journal
But that special table can grow over dbc's perm limit, until the whole system runs out of perm space.

31).Is it possible to alter NO RANGE and Unknown partition on a table?
Ans.Yes if table is empty,we can alter NO RANGE and Unknown Partition of a table.

32.can you apply a read lock on table where write lock is already applied?
Ans.No

33).How teradata makes sure that there are no duplicate rows being inserted when its a SET table?
Ans.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 its a duplicate it silently skips it without throwing any error.

34).Why Fastload Utility does not support multiset table and duplication?
Ans.Multi set table supports duplicate rows.
Fastload utility does not support duplicate rows.
Restart logic is one of the reason. Fastload is one of the utility which loads data in blocks(64K). During restart Fastload sends some of the rows for the second time which occur after a checkpoint. Due to this Fastload rejects duplicates.
Example
consider 20 rows to be loaded in a table. FL utility is used to load the table and ckpt is 5 rows. If restart occurs after 7th row FL may send 6 and 7th to AMPs during the restart. This records would be considered as duplicates and rejected.


35).Can you Recover the password of a User in Teradata?
Ans.No , you cant recover the password of a User in Teradata. Passwords are stored in Data Dictionary table (DBC.DBASE).Using a one way encryption method.
You can view the encrypted passwords using the following query.
SEL * FROM DBC.DBASE;

36).what is the differnce between Sub Query and Corelated Sub Query?
Ans.Sub Query
If Queries written in a nested manner then its termed as a sub query.
Inner query executed First and executed Only one time.
Corelated Sub Query
Co-Related Sub query get executed once for each row of the parent query.
Inner Query executed many based on Outer query.

37).How do you transfer large Amount of Data in Teradata?
Ans.Transfering of large Amount of data can be done using various Applications like Teradata Utilities.
BTEQ,FastLoad,MultiLoad,Tpump and FastExport.
BTEQ (Basic Teradata Query) supports all 4 DML s : SELECT, INSERT,UPDATE and DELETE.
BTEQ also support IMPORT/EXPORT protocols.
Fastload ,Multiload and Tpump transfer the data from Host to Teradata.
FastExport is used to export data from Teradata to the Host.

38).what is FALLBACK in teradata?
Ans.FALLBACK is a table protection feature used in case an AMP fails. You can use FALLBACK on all tables.
FALLBACK tables use twice as much disk space as NON-FALLBACK rows .

39).What are Differences between Teradata and ANSI Session modes in Teradata?
Ans.TERADATA MODE
-------------
1. Comparison is not Case sensitive
2. Create table are default to SET tables
3. Each transaction is committed implicitly
4. Supports all Teradata commands
5. It follows BTET (Begin and End Transaction) Mode
ANSI MODE
-------------
1. Comparison is CASE sensitive
2. Create table are default to MULTISET tables
3. Each transaction has to be committed explicitly
4. Does not support all Teradata commands .
5. It does not follow BTET Mode.

40).How to skip 1st record while using Bteq IMPORT?
By using SKIP=1 ; , we can skip first record.
.import infile=, skip=1;

41).How many BYNETs in a teradata system?
Ans.Well, BYNET is both a hard ware and software BYNET.
Hard ware BYNET connects every PE and AMP in the sytem . It can be scalable at any time.
Every teradata system has two BYNETS . one for backup in case of one failed
However both BYNETS can be used at same time by AMP or PES .

42. What does SLEEP function does in Fast load?
Ans.The SLEEP command specifies the amount minutes to wait before retrying to logon and establish all sessions.
Sleep command can be used with all load utilities not only fastload.
This situation can occur if all of the loader slots are used or if the number of requested sessions is not available. The default value is 6 minutes. If tenacity was set to 2 hours and sleep 10 minutes, Sleep command will try to logon for every 10 minutes up to 2 hours duration.

43). Given below, What is the maximum level of data protection?
A.) RAID 1 and Fallback
B.) RAID 5 and Fallback
C.) RAID 5 and No Fallback
D.) RAID 1 and No Fallback
ANswer - A

44).What is use of compress in teradata?Explain?
Ans.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 creation
2.We can compress up to 256 column values(not columns)
3.We can't compress variable length fields (vartext,varchar..)

45). What is the difference between start schema and Fsldm?
Ans.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.

46). HASHROW funtions is
Ans..a. Returns the hash bucket number that corresponds to a specified row hash value.
b. Returns the hexadecimal row hash value for an expression
c. Returns the AMP number of the primary AMP corresponding to the specified hash bucket number.
d. Returns the identification number of the fallback AMP corresponding to the specified hash bucket.
Answer: b