BTEQ

• BTEQ is a report writer, unlike SQL Assistant which is more of a spreadsheet
• Exports Teradata data off of Teradata a row at a time.
• Imports data onto Teradata a row at a time.
• Queries can be run interactively or in a Batch Script!
•  It is a cool report writer and can do things SQL Assistant cannot do
• Batch TEradata Query (BTEQ) tool was the original way that SQL was submitted to Teradata as a means of getting an answer set in a desired format

• Remember that BTEQ commands begin with a period (.) and do not require a semi-colon (;) to end the statement. SQL commands do not ever start with a period, and they must always be terminated with a semi-colon.

How to Logon to BTEQ in Interactive Mode

Running Queries in BTEQ in Interactive Mode

BTEQ Commands vs BTEQ SQL Statements

How to Logon to BTEQ in a SCRIPT

Running Queries in BTEQ through a Batch Script

Running a BTEQ Batch Script through the Command Prompt

Running a BTEQ Batch Script through the Run Command

Using BTEQ Scripts to IMPORT Data

Creating a BTEQ IMPORT for a Comma Separated Value File

each column is defined using VARCHAR. This is necessary when using CSV files.

Ways to Run a Teradata BTEQ Script

1.At your Command Prompt

 C: BTEQ < C:\Temp\Script1.txt

3.Logon to a BTEQ Session:

Enter your logon or

BTEQ Command:

.logon localtd/dbc, apple123.RUN FILE=C:\Temp\Script1.txt

• • EXPORT DATA–Exports data in record mode, but completely unreadable to viewers.
  • EXPORT Report—Exports in report mode, which includes the headers and is readable.
  • EXPORT INDICDATA–Exports in INDICDATA mode warning mainframes of NULLs.

 

• • EXPORT DIF –Exports and converts data into Data Interchange Format for PC’s.
  • BTEQ allows for multiple techniques to export data. We usually think of an export as moving data off of Teradata to a normal flat file. That is example number one and that is called RECORD Mode.
  • BTEQ can actually take your SQL output report and include the Headers and export all together. It looks like an electronic report. That is EXPORT REPORT mode. This is also called Field Mode
  • When are NULL’s in your data and you export them to a mainframe the actual mainframe application could run into problems so INDICDATA warns of NULL values.

 

• Use DIF to be able to be used by PC applications in Data Interchange Format.

Creating a BTEQ Export Script in Record Mode

Creating a BTEQ Export Script in Report Mode

Using Report Mode to Create a Comma Separated Report

Creating a BTEQ IMPORT for a Comma Separated Value File

Using Multiple Sessions in BTEQ

BTEQ Fast Path Inserts

BTEQ Can Use Conditional Logic

FASTEXPORT

• Moves data off Teradata into flat files in 64 K blocks to mainframe or computer.
• Use FastExport instead of BTEQ Export if there are more than 500,000 rows.
• A Block Level Utility so no more than 60 FastExport jobs can run simultaneously.
• Can export from multiple tables.
• Can use multiple sessions for speed

• V13 enhanced so it won’t use spool before exporting rows.
• Before Teradata V2R6.0 the DBS control parameter MaxLoadTasks had a max of 15 block level utilities that include FastLoad, MultiLoad and FastExport.
• This meant that no more than 15 Block Utilities combined could run simultaneously.
• After V2R6.0 a new DBS control parameter named MaxLoadAWT was introduced.
• MaxLoadAWT allows for up to 30 FastLoad and MultiLoad jobs to run concurrently.
• MaxLoadAWT also allows for up to 60 FastExport jobs to run concurrently (minus FastLoad or MultiLoad jobs).
• If MaxLoadAWT is set to 0 then things run like the old days and no more than 15 block utilities can run simultaneously.
• If MaxLoadAWT is set > 0 then new rules take effect, but remember this can impact queries.

4 modes

A Sample FastExport Script

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.

Phase 1 – Acquisition Phase

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

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.

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.

   

TPUMP

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

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

• It is called TPump because you can adjust the rate for which you want the data loaded, from trickle to maximum speed.
• TPump is used when you want to load during the day while simultaneously querying a table.
• TPump does it all and is used for maintenance on populated tables one row at a time.
• TPump is NOT a Block Level Utility and has No session  Limits
• so it is perfect when you don’t need to load massive amounts of data or you want to simultaneously load and query a table.
• It is often used if the tables you’re loading to have secondary indexes, join indexes, triggers, or referential integrity.
• It is also great when you need hundreds of load jobs to run simultaneously.
• It is not as fast as FastLoad or MultiLoad, but it is pretty darn fast.
• It is called TPump because you can adjust the rate for which you want the data loaded, from a trickle to maximum speed.
• TPump is NOT a Block Level Utility and has No Limits
• FastLoad performs Inserts into empty Teradata tables in 64K blocks.
• MultiLoad loads to populated Teradata tables in 64K blocks.
• FastExport – Exports data off of Teradata at the 64K blo
• 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!
• TPump has none of the limits above, so it is perfect when you don’t need to load massive amounts of data or you want to simultaneously load and query a table.
• It is often used if the tables you’re loading to have secondary indexes, join indexes, triggers, or referential integrity.
• It is also great when you need hundreds of load jobs to run simultaneously.
• It is not as fast as FastLoad or MultiLoad, but it is pretty darn fast

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

281.

AS in MLOAD

SESSIONS Max [Min]
ERRORTABLE Tablename (defaults to jobname_ET)
ERRLIMIT Errcount [errpercent]
CHECKPOINT Frequency (default is 15 minutes)
TENACITY Hours to continue to try and logon to run (default 4)
SLEEP Minutes to sleep between logon attempts

These .BEGIN Parameters are only used in TPump

• SERIALIZE If ON, this options guarantees that operations on a row occur serially.
• PACK Statements to pack into a multiple-statement request. (default is 20, max is 600)
• PACKMAXIMUM Use the max pack factor.
• RATE Max rate statements are sent to the Teradata RDBMS per minute. (Default unlimited)
• LATENCY Number (range is from 10-600 seconds) Allows TPump to commit to Teradata any data sitting in the buffer longer than the LATENCY value.
• NOMONITOR Prevents TPump from checking for statement rate changes from or update status information for the TPump Monitor application. By default the monitoring is ON
• ROBUST ON|OFF (default is ON). The OFF parameter signals TPump to use “simple” restart logic. In this case, restarts cause TPump to begin where the last checkpoint occurred.
• MACRODB dbname (default is logtable database). Database if a macro is used in the TPump job.

Five Formats of TPump 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.

Sample script

Executing a TPump Script