Posts

5 Top Teradata DATE functions to read now

Image
Top list of DATE functions. These functions frequently used in Teradata. The following statement requests the current system date:

Teradata Quick refresher for most popular Utilities

Image
Teradata utilities are very important while working with SQL and in data warehousing projects. The below short and crispy definitions help you can use while attending for interviews or in your day to day work to refresh your Teradata knowledge. 



Top Teradata Utilities

1# MultiLoad 
Inserts, updates, deletes, or upserts rows in a Teradata table by using the MLOAD command-line utility. It can handle multiple SQL statements in a single operation.

You can provide user-defined SQL for complex operations. This stage is best used for bulk updates, deletes, upserts, and complex interface operations.


2# TPump 
Inserts, updates, deletes, or upserts rows in a Teradata table using the TPUMP command-line utility. It can do concurrent updates on the same table. It can handle multiple SQL statements in a single operation. You can provide user-defined SQL for complex operations.


3# FastExport 
Exports data from a Teradata table by using the FEXP command-line utility. You can customize it to use user…

All You Need NULL Value Functions

Image
The below are the Null value functions in Tearadata. One is "NVL" and another one is "NVL2". These two functions are part of Teradata SQL.

NVL -The NVL function replace null value with a string or numeric value. This is the functionality is same as Teradata function NULLIF. It requires two arguments. If the value is not null, it will not do anything.


The best SQL Null functions 
NVL function: 
SELECT NVL (NULL,'USA'), NVL('SPAIN','USA');
-----------
USA SPAIN

Also Read: Top 70 Teradata Interview Questions

The NVL2 function :
Returns one of two specified values (arguments#2 and Arguments#3) based on weather the first arguments in NULL or not. It requires three arguments.

The rule is as follows:
If expression 1 is not null, expression 2 is returnedIf expression 1 is null, expression 3 is returned
The best NVL2 example
SELECT NVL2(NULL,'FRANCE','SPAIN'), NVL2('ENGLAND','FRANCE','SPAIN')
-----------------…

How to Know Teradata is in Pseudo table Lock

The pseudo table mechanism is a way to serially queue all table level locks through the use of an internal pseudo table. In this table the database locks the row hash of the table's table ID.

How Teradata determines primary index

Image
Every table must have at least one column as the Primary Index. The Primary Index is defined when the table is created.There are two reasons you might pick a different Primary Index then your Primary Key. They are (1) for Performance reasons and (2) known access paths.


Primary Index Rules 
Rule 1: One Primary Index per table.
Rule 2: A Primary Index value can be unique or non-unique.
Rule 3: The Primary Index value can be NULL.
Rule 4: The Primary Index value can be modified.
Rule 5: The Primary Index of a populated table cannot be modified.
Rule 6: A Primary Index has a limit of 64 columns.


Two Types of Primary Indexes (UPI or NUPI) 

Unique Primary Index(UPI) - A Unique Primary Index (UPI) is unique and cannot have any duplicates. If you try and insert a row with a Primary Index value that is already in the table, the row will be rejected. An UPI enforces UNIQUENESS for a column

Non-Unique Primary Index (NUPI) - A Non-Unique Primary Index (NUPI) means that the values for the se…

34 Complex Teradata Architecture Questions

Image
QUESTION No:1
An administrator is trying to manage resources on the system because there is a business critical reporting requirement. The reporting workload starts at 9AM and is required to finish by 10AM. At the same time, ad hoc users are allowed to access the system. The administrator is building a Query Resource Throttle in TDWM to better manage the resources. Which two are available as options for building the throttle? (Choose two.)

A. Index Scan Types Allowed
B. Throttle All-AMP Queries Only
C. No Aggregate Functions Allowed
D. Only limit queries with Step Time Threshold

Answer: C,D

QUESTION NO: 2
An administrator is asked to keep a daily backup of a very large transaction table. The table is defined with a PPI with a daily partitioning expression on insert date and the table is insert only. There is not enough of a window to backup the entire table each night. In this situation, which backup strategy is the best approach?

A. Implement a cluster backup of the table.
B. Implement…

12 Frequently Used File Control Commands in BTEQ utility

Image
The below list shows ultra frequently used commands in BTEQ utility.

DEFAULTS: Reset all BTEQ format command options to their defaults. This will utilize the default configurations

ECHOREQ - Enable the Echo required function in BTEQ returing a copy of each Teradata SQL request and BTEQ command to the standard output stream

EXPORT - Open a file with a specific format to transfer information directly from the Teradata database.

IMPORT - Open a file with a specific format to import information into Teradata

INDICDATA - One of multiple data mode options for data selected from Teradata. The modes are INDICDATAFIELD or RECORD MODE

OS - Execute an MS-DOS, PC-DOS or Unix command inside BTEQ

QUIET -Limit BTEQ output displays to all error messages and request processing statistics

REPEAT - Submit the next request a certain amount of times

RUN - Execute Teradata SQL requests and BTEQ commnads directly from a specified run file

TSO - Execute an MVS TSO command from inside the BTEQ environment