Interview Questions : SET 1

Question 1:

Explain TPUMP (Teradata Parallel Data Pump) Utility in Teradata?

Answer:

• TPUMP allows near real time updates from Transactional Systems into the Data Warehouse.It can perform Insert, Update and Delete operations or a combination from the same source.
• It can be used as an alternative to MLOAD for low volume batch maintenance of large databases.
• TPUMP allows target tables to have Secondary Indexes, Join Indexes, Hash Indexes, Referential Integrity, Populated or Empty Table, Multiset or Set Table or Triggers defined on the Tables.
• TPUMP can have many sessions as it doesn’t have session limit.
• TPUMP uses row hash locks thus allowing concurrent updates on the same table

||||=====================================================================================================================================================||||

Question 2:

How do you transfer large amount of data in Teradata?

Answer:

Transferring of large amount of data can be done by using the various Teradata Utilities i.e. BTEQ, FASTLOAD, MULTILOAD, TPUMP and FASTEXPORT.

• BTEQ (Basic Teradata Query) supports all 4 DMLs: 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.

||||=====================================================================================================================================================||||

Question 3:

What is multi insert?

Answer:

Inserting data records in the table using multiple insert statement. The semicolon is used at the beginning of the next INSERT statement instead of placing it at the last of insert statement

e.g.

insert into Employee values (1,’John’,’IT’)

;insert into Employee values(2,’Mike’,’HR’)

Placing semicolon in front of insert will cause a parallel insertion of both these rows

||||=====================================================================================================================================================||||

Question 4:

Is multi insert is an ANSI standard ?

Answer:

NO, its Teradata specific.

||||=====================================================================================================================================================||||

Question 5:

How to create a table with an existing structure of another table with or without data and also with stats defined ?

Answer:

CREATE TABLE new_TABLE AS old_TABLE WITH DATA

CREATE TABLE new_TABLE AS old_TABLE WITH NO DATA

CREATE TABLE new_TABLE AS old_TABLE WITH DATA AND STATS

||||=====================================================================================================================================================||||

Question 6:

You have to make a BTEQ script, which drops a table and creates a table. Now you have to make this script so that it will not return any error if while dropping the table does not exist?

Answer:

We can do it by setting error level to zero before our drop statement and then setting it back to 8 after dropping the table

e.g.

ERRORLEVEL (3807) SEVERITY 0;

DROP TABLE EMPLOYEE;

ERRORLEVEL (3807) SEVERITY 8;

||||=====================================================================================================================================================||||