FASTLOAD
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.
- Loads data in 64K blocks and
- only 30 FastLoad and MultiLoad combinations can run simultaneously.
- No Secondary Indexes (FastLoad)
- No Unique Secondary Indexes (FastLoad and MultiLoad)
- No Triggers (FastLoad and MultiLoad)
- No Join Indexes (FastLoad and MultiLoad)
- No Referential Integrity (FastLoad and MultiLoad)
- Only 30 FastLoad and MultiLoad combinations can run at one time.
- Only 60 FastExport jobs can run simultaneously!
- Duplicate rows will NOT load even if the table is a Multiset table.
- Block Level Utility Limits
242. Fastload has some limitations.
- . Target table must be empty
- . Fload performs inserts only (no updates)
- . Only one target table at a time
- . Must drop secondary indexes before loading and recreate them afterward Multiload allows nonunique secondary indexes – automatically rebuilds them after loading
- It does not load duplicate data.
- It does not run in modes. a) Import mode. b) Delete mode.
- It does not support NUSI table
- a It applies Access lock on target Table during loading.
Phase 1 – Acquisition Phase
• Transfer the 64K blocks from the host to the AMPs.
• Have each AMP hash the rows by the table’s Primary Index and send them to a work table on the correct AMP.
Phase 2 – Application Phase
• Sort the data in the work tables by their Row-ID.
• Write the data to the table.
243.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.
- 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.
245.How to skip the header row in the fastload script
RECORD 2; /* this skips first record in the source file */ DEFINE …
246. 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:
247.
FastLoad to a NoPI Table
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.
- Execute the FASTLOAD script by removing the DML block , i.e only BEGIN LOADING and END LOADING in the FASTLOAD script should be kept.
- Another method can be to drop the table and create it again..
The block utilities have limits that prevent too many of them running at the same time. This feature is actually controlled by a new DBS Control parameter named MaxLoadAWT which controls AMP Worker Tasks (AWT). When MaxLoadAWT is set to zero, then it is like going back in time to pre-V2R6.0 where only 15 FastLoad, MultiLoad and FastExport jobs can run max.
FastLoad permits one conversion per column from one data type to another.Notice that our Accounts_Table has different data types than the flat file, but FastLoad will convert them for us automatically within the FastLoad.
A FastLoad that Uses the NULLIF Statement
NULLIF allows you to specify that if an input field contains a specific value, it should be treated as a NULL.This example occurs when dates are entered as zeroes. This would normally cause a failure, but not now.
251.
FastLoad and Referential Integrity Solutions
Approach Number One
Approach Number Two
FastLoad does Not support Batch Referential Integrity, so the above examples are two approaches to solving the problem.