241.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.

Phase 1 – Acquisition Phase

• 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.
• 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

244. 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.

A NoPI table is useful for a Staging Table.

Loading data into a NoPI staging table will be faster when compared to the same table with a Primary Index.

• Data can be loaded into a staging table quicker using FastLoad if it is a NoPI table thus freeing up client resources earlier.

• The data-redistribution processing in the acquisition phase is done more efficiently by using bigger blocks to distribute the rows between AMPs (4KB versus 64KB).

• The End Loading (sort phase) is completely eliminated with a NoPI table. 

• While a NoPI target table is being loaded with FastLoad, users can view the table content with an ACCESS lock.
• This is allowed because rows are always appended at the end of a NoPI table. This is not allowed on a PI target table until the data has been sorted which does not happen until the end of Phase 2.

249.

Loading Multiple Input Files with FastLoad

• No END LOADING statement causes a “FastLoad Paused” state.
• Then, we run the job again with a different flat file.
• When the second FastLoad uses the END LOADING statement, this indicates no more data and Phase 2 starts.
• This is the only way to trick FastLoad into using multiple input files.

 250.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.