240.A certain load is being imposed on the table and that too, every hour. The traffic in the morning is relatively low, and that of the night is very high. As per this situation, which is the most advisable utility and how is that utility supposed to be loaded?

The most suggestible utility here has to be Tpump. By making use of packet size decreasing or increasing, the traffic can be easily handled.

 241.If Fast Load Script fails and only the error tables are made available to you, then how will you restart?

There are basically two ways of restarting in this case.

• Making the old file to run – Make sure that you do not completely drop the error tables. Instead, try to rectify the errors that are present in the script or the file and then execute again.
• Running a new file – In this process, the script is executed simply using end loading and beginning statements. This will help in removing the lock that has been put up on the target table and might also remove the given record from the log table. Once this is done, you are free to run the whole script once again.

242. Give a justifiable reason why Multiload supports NUSI instead of USI.

The index sub-table row happens to be on the same Amp in the same way as the data row in NUSI. Thus, each Amp is operated separately and in a parallel manner.

 243.How is MLOAD Client System restarted after execution?

The script has to be submitted manually so that it can easily load the data from the checkpoint that comes last.

 244.How is MLOAD Teradata Server restarted after execution?

The process is basically carried out from the last known checkpoint, and once the data has been carried out after execution of MLOAD script, the server is restarted.

 245.Let us say there is a file that consists of 100 records out of which we need to skip the first and the last 20 records. What will the code snippet?

We need to use BTEQ Utility in order to do this task. Skip 20, as well as Repeat 60 will be used in the script.

 246.In Teradata, what is the significance of UPSERT command?

UPSERT basically stands for Update Else Insert. This option is available only in Teradata.

 247.Name the five phases that come under MultiLoad Utility.

• Preliminary Phase,
• DML Phase,
• Data Acquisition Phase,
• Application Phase and
• End Phase.

248.Explain Fast Load in Teradata?

•        Loads large amount of data from external file into an empty table at high             speed.Only load one empty table with 1 fast load job.
•        Tables defined with Referential integrity, secondary indexes, join indexes, hash         indexes or triggers cannot be loaded with FastLoad.
• Duplicate rows cannot be loaded into a multiset table with FastLoad
• FastLoad has two phases – Data Acquisition and Application.
• It requires separate error table for each phase.
• Use the BEGIN LOADING statement to specify (and create) the two error tables for the operation.
• We can specify the error table in the same database as the data table or different database.
• We must remove the error tables before we re-run the same load job or it will terminate in an error condition.

249.Explain Multi Load in Teradata?

• Used for loading, updating or deleting data to and from populated tables, typically with batch inputs from a host file.
• Cannot process tables defined with USI’s, Referential Integrity, Join Indexes, Hash Indexes, or Triggers.
• No data retrieval capability.
• Import tasks require use of Primary Index
• The Multiload supports five target tables per script.
• Tables may contain pre-existing data.
• Ability to do INSERTs UPDATEs, DELETEs and UPSERTs.
• Use RELEASE MLOAD tablename; statement if MLOAD doesn’t successfully completed and there is no desire to restart the MLOAD. This will return the target table to general availability.
• Error tables should be dropped manually, as RELASE MLOAD will not drop them automatically.

250..What are the MultiLoad Utility limitations?

• MultiLoad is a very powerful utility; it has following limitations:
• MultiLoad Utility doesn’t support SELECT statement.
• Concatenation of multiple input data files is not allowed.
• MultiLoad doesn’t support Arithmetic Functions i.e. ABS, LOG etc. in Mload Script.
• MultiLoad doesn’t support Exponentiation and Aggregator Operators i.e. AVG, SUM etc. in Mload Script.
• MultiLoad doesn’t support USIs (Unique Secondary Indexes), Referential Integrity, Join Indexes, Hash Indexes and Triggers.
• Import task require use of PI (Primary Index).

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

• 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

252. What are TPUMP Utility Limitations?

• Use of SELECT statement is not allowed.
• Concatenation of Data Files is not supported.
• Exponential & Aggregate Operators are not allowed.
• Arithmetic functions are not supported.

253.How do you transfer large amount of data in Teradata?

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.

254.How to make sure BTEQ utility is not erroring out while dropping a table when the table doesn’t exist in Teradata?

Setting the error level to 0 will achieve this.

ERRORLEVEL (3807) SEVERITY 0;

DROP TABLE TABLENAME; ERRORLEVEL (3807) SEVERITY 8;

255.Difference between MultiLoad and TPump in Teradata?

• Tpump provides an alternative to MultiLoad for low volume batch maintenance of large databases under control of a Teradata system.
• Tpump updates information in real time, acquiring every bit of a data from the client system with low processor utilization.
• It does this through a continuous feed of data into the data warehouse, rather than the traditional batch updates.
• Continuous updates results in more accurate, timely data.
• Tpump uses row hash locks than table level locks.
• This allows you to run queries while Tpump is running.

256.Which is faster – MultiLoad delete or Delete command in Teradata?

• MultiLoad delete is faster than normal Delete command, since the deletion happens in data blocks of 64Kbytes, whereas delete command deletes data row by row.
• Transient journal maintains entries only for Delete command since Teradata utilities doesn’t support Transient journal loading
• For smaller table deletes, simple DELETE command is enough.
• Multiload delete is useful when the delete has to be performed on a large table in teradata.

257.How to Skip or Get first and Last Record from Flat File through MultiLoad in Teradata?

• In .IMPORT command in Mload we have a option to give record no. from which processing should begin. i.e. ‘FROM m’ ‘m’ is a logical record number, as an integer, of the record in the identified data source where processing is to begin. You can mention ’m’ as 2 and processing will start from second record.
• THRU k and FOR n are two options in the same Mload command, functions same towards the end of the processing. Adding to the above, if from m”start record” and for n “stop record” are not mentioned, mload considers records from start till the end of the file

258.Why Fload doesn’t support multiset table in Teradata?

Fload does not support Multiset table because of restart capability. Say, the fastload job fails. Till the fastload failed, some number of rows was sent to the AMP’s. Now if you restart FLOAD,  it would start loading record from the last checkpoint and some of the consecutive rows are sent for the second time. These will be caught as duplicate rows are found after sorting of data. This restart logic is the reason that Fastload will not load duplicate rows into a MULTISET table. It assumes they are duplicates because of this logic. Fastload support Multiset table but does not support the duplicate rows. Multiset tables are tables that allow duplicate rows. When Fastload finds the duplicate rows it discards it. Fast Load can load data into multiset table but will not load the duplicate rows

259.How to skip the header row in the fastload script

RECORD 2; /* this skips first record in the source file */ DEFINE …

260.  My table got locked during MLOAD due to a failed job. What do I do to perform other operations on it?

• Using RELEASE MLOAD. It removes access locks from the target tables in Teradata.
• It must be entered from BTEQ and not from MultiLoad. 
• To proceed, you can do RELEASE MLOAD.

261.  What is the difference between FastLoad and MultiLoad?

•   FastLoad uses multiple sessions to quickly load large amount of data on empty table.   MultiLoad is used for high-volume maintenance on tables and views.
• It works with non-empty tables also.
• Maximum 5 tables can be used in MultiLoad.

262. What is the process to restart the multiload if it fails?

• 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 Mload RELEASE MLOAD <TABLE NAME>;
• The above ans is only when the MLOAD failed in acquisition phase To release lock in application phase failure in MLOAD RELEASE MLOAD <Table Name> .IN APPLY;

263. Can we load a Multi set table using MLOAD?

• YES, 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

264. can I use “drop” statement in the utility “fload”?

• 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 block in the FLOAD Block we can give only INSERT Question:

265.IS IT POSSIBLE THAT THERE R TWO PRIMARY KEY WILL BE IN A SAME TABLE.

•  Absolutely No. One table can not have more than one PK. But u can have 2 cols as 1 PK called Composite Primary Key.
•  More than one column can consist of a primary key – upto 64 columns
•  Can not be NULL values (missing values)
•  Should be unique values (no duplicate value) Foreign key . A table may have zero or more than that upto 32 keys
•  There should be a corresponding primary key in the parent table to enforce referential integrity for a foreign key

266. IN A TABLE CAN WE USE PRIMARY KEY IN ONE COLUMN AND IN ANOTHER COLUMN BOTH UNIQUE AND NOT NULL CONSTRAINTS.IF YES HOW?PLZ GIVE ME A EXAMPLE.

• Yes, you can have a column for Primary key and have another column which will have no duplicates or null. e.g. A Salary Table will have employee ID as primary key.
• The table also contains TAX-ID which can not be null or duplicate.

267.What is the difference between Multiload & Fastload in terms of Performance?

•  Fastload is used to load empty tables and is very fast, can load one table at a time. Multiload can load at max 5 tbls at a time and can also update and delete the data.
• Fastload can be used only for inserting data, not updating and deleting.
• Multiload can at max 5 tables with non unique secondary indexes on them. where as in fastload u cannot have secondary indexes on the table.
• In multiload you can insert,update or delete data in already populated tables whereas in fastload the target table should be empty.
• Multiload works in 5 phases whereas fastload works in two phases.
• If we want to load data into an empty table then fastload is best option as compared to multiload.
• multiset table will allow duplication even though if you load it into fastload it wont allow duplication
• If you want to load, empty table then you use the fastload, so it will very usefull than the multiload..because fastload performs the loading of the data in 2 phase..and it 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 all the 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 Application phase it locks the table header

Phase4:In the DML operation will done in the tables

Phase 5: In this table locks will be released and work tables will be dropped

268. How many error tables are there in fload and what are their significance/use? Can we see the data of error tables?

 Fload uses 2 error tables Error table

1: where format of data is not correct. Error table

2: violations of UPI

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

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.

269. Fastload has some limitations.

 1. Target table must be empty

2. Fload performs inserts only (no updates)

3. Only one target table at a time

4. Must drop secondary indexes before loading and recreate them afterward Multiload allows nonunique secondary indexes – automatically rebuilds them after loading

5) It does not load duplicate data.

6) Runs 2 phases.

7) It does not run in modes. a) Import mode. b) Delete mode.

8) 2 error tables.

9) It does not support NUSI table

10) It applies Access lock on target Table during loading.