60. 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.
61.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
62. 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.
63.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.
64..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.
- Import task require use of PI (Primary Index).
- MultiLoad loads to populated Teradata tables in 64K blocks via Inserts, Updates, Deletes and Upserts.
- No Unique Secondary Indexes (USI), but Non-Unique Secondary Indexes (NUSI) are allowed..Supports up to five populated tables.
- No concatenating input files.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!
65. MultiLoad Delete is often better than an SQL Delete
- The MultiLoad DELETE mode is used to perform a global (all AMP) delete on just one table.
- .BEGIN DELETE MLOAD is that it bypasses the Transient Journal (TJ) and can be RESTARTED if an error causes it to terminate prior to finishing.
- MultiLoad DELETE Rules
- DELETE tasks operate differently than IMPORT tasks.
- Deleting based on equality on a Unique Primary Index is not allowed.
- Deleting based on inequality such as < or > is allowed.
- Deleting a Non-Unique Primary Index on equality is allowed.
- A MultiLoad Delete is often better than an SQL Delete because:
- MultiLoad Delete is faster
- No Transient (WAL) Journal
- MultiLoad Delete is restartable
MultiLoad DELETE Rules
- DELETE tasks operate differently than IMPORT tasks.
- Deleting a Non-Unique Primary Index on equality is allowed.
- MultiLoad Delete if faster
- MultiLoad Delete is restartable
- The MultiLoad DELETE mode is used to perform a global (all AMP) delete on just one table.
- .BEGIN DELETE MLOAD is that it bypasses the Transient Journal (TJ) and can be RESTARTED if an error causes it to terminate prior to finishing.
- When performing in DELETE mode, the DELETE SQL statement cannot reference a Unique Primary Index in the WHERE clause with equality.
- This due to the fact that a primary index access is to a specific AMP; this is a global operation.
66. 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;
Troubleshooting MultiLoad
- If the MultiLoad job fails, you can restart the job and it will continue where it left off.
- If Teradata goes down, the MultiLoad will continue where it left off, without user intervention, when Teradata data comes back.
- If all else fails, you can use the RELEASE MLOAD statement below and start over.
- RELEASE MLOAD SQL01.Employee_Table ;
Sometimes the MultiLoad can be restarted and sometimes it is in point of no return. - Here are some troubleshooting suggestions:
- Make sure the MultiLoad is not running and if it is still running you can ABORT the MultiLoad (still restartable if ABORTED).
- Enter RELEASE MLOAD TableName
- If Successful, drop all the work and error tables.
- If not successful, restart MLOAD and let it complete, or dropt target, work, and Error Tables.
The RELEASE MLOAD statement can be quite valuable during desperate times.
67.A NoPI Table Does Not Work with MultiLoad
- A NoPI Table has NO PRIMARY INDEX so it does Not work with MultiLoad!
- A NoPI Table has no primary index.
- A NoPI table guarantees even distribution. A MultiLoad IMPORT works by utilizing the table’s Primary Index.
- A NOPI table stands for NO PRIMARY INDEX and therefore MultiLoad won’t work with this type of table.
Error Tables are dropped if there are not errors, but you can query the error tables.
68.Host Utility Locks (HUT Locks)
There are two types of Host Utility Locks (also referred to as HUT Locks)
:Acquisition locks – Prevent all DDL statements (except DROP) from running against the table(s) being MultiLoaded, but allow for the all DML statements such as SELECT
.Application Locks – Prevent all DDL statements (except DROP) from running against the table(s) being MultiLoaded, but allow for users to run SELECT statements only if they use an ACCESS Lock.
Although MultiLoad seems pretty intense, it never places an Exclusive lock inside the job. If you decide you want to trouble-shoot a MultiLoad job so you want to look at the logtable, the work tables, or any of the error tables, you must use an ACCESS Lock in your SQL to avoid abnormally terminating the MultiLoad job due to locking problems.
Locking Row for ACCESS
SELECT DISTINCT ErrorCode ,ErrorFieldName FROM SQL01.Emp_ET;
Although MultiLoad seems pretty intense, it never places an Exclusive lock inside the job.
If you decide you want to trouble-shoot a MultiLoad job so you want to look at the logtable, the work tables, or any of the error tables, you must use an ACCESS Lock in your SQL to avoid abnormally terminating the MultiLoad job due to locking problems
69. 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.
Whenever we define a Secondary index, an Secondary index subtable will be created.In case of UPI, when they go for has distribution subtable is in one AMP and actual data row pointed by USI subtable is in another AMP. So the AMPs need to communicate, which is not supported by Multiload.
- But in case of NUSI, the subtable and the references of the actual data will store in the same AMP hence AMPs no need to communicate here. So in case of NUSI, AMPs work in parallel and hence Mload supports that.
70.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.
71.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.
72.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.
73.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.
74.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.
75.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
TPT
85.
- The Teradata Parallel Transport (TPT) utility combines BTEQ, FastLoad, MultiLoad, TPump, and FastExport utilities into one comprehensive language utility.
- This allows TPT to insert data to tables, export data from tables, and update tables.
TPT works around the concept of Operators and Data Streams.
- There will be an Operator to read Source data, pass the contents of that Source to a data stream where another operator will be responsible for taking the Data Stream, and loading it to disk.
- A Producer Operator, designed to read input, will move the data to a Data Stream.
- The Consume Operator, designed to write data to a Teradata table, will then Load the data.
- TPT Producers Create Streams and Consumers Write Them
86.
- The Four Major Operators of TPT
Producer – Reads data and writes it to a stream.
Consumer – Takes data from streams and writes it to disk.
Filter – Reads streams, filters data, and then sends it down stream.
Standalone – Used to Create DDL (table structures).
- TPT is designed to read data from disk and then place that data in a memory data stream, thus eliminating unnecessary I/O.
- The Operators above work together to Create Table structures, read data from flat files and send it down the stream, filter data from the stream and then send it further down the stream, and to finally read the stream and write it to its final disk destination.
One of the clever concepts behind TPT is that you can take multiple sources (multiple flat files for example), and utilize multiple Producer Operators to reach each source and then move multiple data streams to multiple Consumer Operators. These operators will then write the multiple sources to one Teradata table. This couldn’t be done before with FastLoad or MultiLoad.
TPT can have more Operators than Consumers
Each source can be handled by a different instance of the producer operator which merges these files into a single data stream.
87.
Using the command prompt run the TBuild command.
tbuild –f C:\temp\script1.txt
88.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.