Teradata Database Utilities

BTEQ (Basic Teradata Query) uses a spool mechanism to store query results temporarily. If BTEQ terminates abnormally (due to error, system failure, or manual interruption), all rows currently in the spool are automatically discarded to prevent partial or inconsistent data from being committed. This ensures data integrity and prevents incomplete results from being processed or exported.

FastExport is a Teradata utility designed for high-speed data extraction. Unlike MultiLoad or TPump, FastExport does not support error files or error limit configurations. The utility assumes successful execution and focuses on performance rather than error handling. If errors occur, they are not captured in error files, and there is no built-in mechanism to specify acceptable error thresholds.

FastLoad is designed for high-speed data loading into empty Teradata tables. During a FastLoad operation, each column can only undergo ONE data type conversion. Attempting multiple conversions per column (e.g., first from CHAR to INTEGER, then from INTEGER to DECIMAL) is not supported and will cause an error. This restriction ensures data integrity and loading performance.

MultiLoad DELETE mode is specifically designed to perform efficient delete operations on Teradata tables. Unlike standard SQL DELETE commands that process rows one at a time, MultiLoad DELETE mode can perform a global delete operation that spans all AMPs (Access Module Processors) simultaneously. It targets a single table for deletion, making it ideal for bulk data removal tasks. This statement accurately describes the DELETE mode functionality.

TPump (Teradata Parallel Utility for Pump) supports both Unique and Non-Unique Secondary Indexes (NUSI). Unlike some other Teradata utilities that have index restrictions, TPump can handle tables with non-unique secondary indexes. This flexibility makes TPump suitable for continuous data maintenance scenarios where tables may have various index configurations. The statement that TPump does not allow NUSI is false.

BTEQ EXPORT function supports four export formats: Data mode (raw data), Report mode (formatted output), Indicator mode (with null indicators), and DIF mode (Data Interchange Format - a legacy format for spreadsheet interchange). DIF mode was commonly used in older systems to exchange data with spreadsheet applications like Lotus 1-2-3 and early Excel versions.

The .LOGOFF command in BTEQ is optional because BTEQ automatically terminates the session when it reaches the end of the script or input file. If you don't explicitly include .LOGOFF, the session will still close properly when BTEQ finishes processing. However, including .LOGOFF is considered good practice for clarity and to ensure immediate session closure in interactive mode.

The QUIT command in Teradata's BTEQ utility is used to terminate all active database sessions and exit the BTEQ environment back to the operating system. It does not keep BTEQ open (unlike option 546286) and is the standard, orderly way to close the utility.

BTEQ allows you to override standard error codes when terminating the script using the .QUIT or .EXIT commands with a custom return code. This feature enables you to communicate specific exit conditions to calling processes or batch scripts. For example, you can use '.QUIT 100' to exit with a custom code of 100 instead of the default error codes. This is useful for integrating BTEQ scripts into automated workflows where specific error codes need to be trapped and handled.

BTEQ (Basic Teradata Query) supports both interactive and batch modes for submitting SQL. The statement claims it works 'only' in batch mode, which is incorrect. BTEQ can run queries interactively from the command line or in batch mode from script files.

FastExport can export data from multiple tables in a single operation by using SQL JOIN, UNION, or subqueries in the SELECT statement. The export query can combine data from multiple tables before writing to the export file.

FastExport is a read-only utility designed specifically for high-volume data extraction from Teradata. Only SELECT queries are permitted within FastExport tasks. INSERT, UPDATE, and DELETE are data modification commands that are not supported.

In Teradata utilities (FastExport, MultiLoad, TPump), the IMPORT command specifies the external client-side file that contains data values. These values are referenced by the USING clause for parameter substitution in the SELECT statement.

In FastExport scripts, all utility commands must begin with a period (dot) character. This syntax convention (.BEGIN, .EXPORT, .IMPORT, .LAYOUT, etc.) distinguishes FastExport commands from SQL SELECT statements and defines the script structure.

The SLEEP command in FastExport defaults to 6 minutes. This command controls how long FastExport waits before retrying when certain error conditions occur, such as when tables are locked or during transient failures.

Aggregate (group) functions like COUNT, SUM, AVG, MAX, MIN cannot be used in the WHERE clause. WHERE clause filters individual rows BEFORE grouping. Aggregate functions operate on groups of rows, so they must be used in HAVING clause (which filters after grouping) or in the column list with GROUP BY. This is a fundamental SQL execution order rule.

The DELETE statement 'DELETE FROM Employee WHERE grade = 'Y'' will delete ALL rows from the Employee table where the grade column equals 'Y'. If 50 rows have grade='Y', all 50 are deleted. It does not delete all rows in the table, does not delete exactly 1 row, and it definitely deletes something (rows matching the condition). DELETE, like UPDATE, operates on the entire rowset that matches the WHERE condition.

WITH CHECK OPTION is a view constraint clause that prevents DML operations from creating rows that would be invisible to the view. When you update or insert through a view with WITH CHECK OPTION, the database ensures the modified/inserted row will still appear in the view (i.e., satisfies the view's WHERE clause). This prohibits 'escaping' rows from the view's definition. CHECK keyword alone is for table constraints, WITH CHECK is incomplete syntax.