TOP TERADATA QUESTIONS-3

 

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

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

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

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

79.What are all Utilities of Teradata

    • BTEQ-Basic Teradata Query Language (For Export/Import) which supports SELECT, INSERT, UPDATE and DELETE
    • Teradata FastLoad(For To Load empty tables at high speed)
    • Teradata MultiLoad(For To insert, update and delete records)
    • Teradata FastExport(For export data from the Teradata Database)
  • Teradata TPump(For real time data loads)
80.Why Multiload and Fastload does not supports SI,JI,RI and TRIGGERS?

SI

  • USI – generally stored in sub table in a different amp other than the amp having data row. Both the utilities do not support.
  • NUSI – The sub table is stored in the same amp itself. Mload supports it and Fastload not.

JI

  • TD generally stores the rows of join index across the amps Both the utilities do not support.

RI and triggers

  • These are multi amp operations and any operation involving communication between two amps is not supported by these two utilities

81.Why MLOAD needs Work Tables?

  • Work Tables are used to receive and sort data and SQL on each AMP prior to storing them permanent on disk.
  • The purpose of worktables is to hold two things:
    • 1. The Data Manipulation Language (DML) tasks
    • 2. The input data that is ready to APPLY to the AMPs Multi Load will automatically create one worktable for each target table. This means that in IMPORT mode you could have one or more worktables.
  • In the DELETE mode, you will only have one worktable since that mode only works on one target table.
  • When you loading data it’s not load directly to amp,first it load to work load at the 5 stage it loads from work table to target table,Then after final stage it’s delete the work tables and error tables every thing

82. Does Multiload use Transient Journaling ?

Multiload does not use Transient Journaling. It uses a LOG table to maintain the process status.

TPUMP

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

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

Rule #1: No concatenation of input data files is allowed. TPump is not designed to support this.
Rule #2: TPump will not process aggregates, arithmetic functions or exponentiation. If you need data conversions or math, you might consider using an INMOD to prepare the data prior to loading it.
Rule #3: The use of the SELECT function is not allowed. You may not use SELECT in your SQL statements.
Rule #4: No more than four IMPORT commands may be used in a single load task. This means that at most, four files can be directly read in a single run.
Rule #5: Dates before 1900 or after 1999 must be represented by the yyyy format for the year portion of the date, not the default format of yy. This must be specified when you create the table. Any dates using the default yy format for the year are taken to mean 20th century years.
Rule #6: On some network attached systems, the maximum file size when using TPump is 2GB. This is true for a computer running under a 32-bit operating system.
Rule #7: TPump performance will be diminished if Access Logging is used. TPump uses normal SQL to accomplish its tasks.

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.