253..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!

Referential Integrity and Load Utility Solutions

FastLoad and MultiLoad don’t support Batch Referential Integrity, so the above examples are two approaches to solving the problem.

MultiLoad has IMPORT and DELETE Tasks

DO INSERT FOR MISSING UPDATE ROWS ;

The keywords you can’t forget for an UPSERT MultiLoad are listed above in red and are: DO INSERT FOR MISSING UPDATE ROWS. That tells Teradata to first try and do an Update, and if it is successful move on. But, if the Update fails, then do an Insert.

 254.Name the five phases that come under MultiLoad Utility.

MultiLoad has Five Phases

	Preliminary Phase • Basic Setup.
	DML Transaction Phase • Get DML steps down onto the AMPs3
	Acquisition Phase • Send the input data to the AMPs and sort it.
	Application Phase • Apply the input data to the appropriate Target Tables.
	Cleanup Phase • Basic Cleanup

255.A 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.

256.sample script

257. What is the process to restart the multiload if it fails?

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.

258.Five Formats of MultiLoad Files

Binary• Each record is a 2-byte integer, n, that is followed by n bytes of data. A byte is the smallest means of storage for Teradata.

FastLoad• This format is the same as Binary, plus a marker (X ‘0A’ or X ‘0D’) that specifies the end of the record.

Text• Each record has a random number of bytes and is followed by an end of the record marker.

Unformat• The format for these input records is defined in the LAYOUT statement of the MultiLoad script using the components FIELD, FILLER and TABLE.

Vartext• This is variable length text RECORD format separated by delimiters such as a comma. For this format you may only use VARCHAR, LONG VARCHAR (IBM) or VARBYTE data formats in your MultiLoad LAYOUT. Note that two delimiter characters in a row will result in a null value between them.

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

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

261. Give a justifiable reason why Multiload supports NUSI instead of USI.