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;
||||=====================================================================================================================================================||||
4 comments
No ping yet
Charankumar says:
April 29, 2013 at 1:18 pm (UTC 0)
Hi Admin,
Really greatwork and theoretically it is very usefull. can you please post some real-time scenario and answers.
Thanks.
kumar says:
April 17, 2013 at 4:26 am (UTC 0)
Hi,
This material is very good for preparation. Could you please share the differences of various teradata utilities and some more new tricky questions.
Thanks a lot ..:)
gaurav sharma says:
November 10, 2011 at 7:10 am (UTC 0)
HI SIR. pLEASE ADD SOME NEW TOPICS ..
ravindra says:
November 17, 2012 at 7:46 am (UTC 0)
Hi plese send optimised query based questions and realtime question and answers
Thanks