TERADATA UTILITIES Useful Information Q/A Basic & Expert levels

Database

TERADATA UTILITIES Useful Information Q/A Basic & Expert levels

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.

11 REPLIES

Re: TERADATA UTILITIES Useful Information Q/A Basic & Expert levels

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.

Re: TERADATA UTILITIES Useful Information Q/A Basic & Expert levels

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

Re: TERADATA UTILITIES Useful Information Q/A Basic & Expert levels


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.

Re: TERADATA UTILITIES Useful Information Q/A Basic & Expert levels

How to make sure BTEQ utility is not erroring out while dropping a table when the table doesnt exist in Teradata?

Setting the error level to 0 will achieve this.

ERRORLEVEL (3807) SEVERITY 0;

DROP TABLE TABLENAME;

ERRORLEVEL (3807) SEVERITY 8;

Re: TERADATA UTILITIES Useful Information Q/A Basic & Expert levels

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.

Re: TERADATA UTILITIES Useful Information Q/A Basic & Expert levels

Different phases of MultiLoad in Teradata?

• Preliminary phase

• DML phase

• Acquisition phase

• Application phase

• End phase

Re: TERADATA UTILITIES Useful Information Q/A Basic & Expert levels

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.

Re: TERADATA UTILITIES Useful Information Q/A Basic & Expert levels

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 n"start record" and for n "stop record" are not mentioned, mload considers records from start till the end of the file

Re: TERADATA UTILITIES Useful Information Q/A Basic & Expert levels

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