Teradata NULL functions

The following are the NULL functions in Teradata sql:

1) NULLIFZERO

This will change a zero value to a NULL value.

2) NULLIFSPACES

This will change a space value to a NULL value.

3) ZEROIFNULL

This will change a NULL value to a zero.

4) NULLIF = 0

This will change a zero value to a NULL.

ex: define c1(decimal (5,2), NULLIF = 0);

5) NULLIF = ' '

This will change a space value to a NULL.

ex: define c1(char (2), NULLIF = ' ');

6) NULLIF = '?'

This will change a '?' value to a NULL.

ex: define c1(char (2), NULLIF = '?');

7) NULLIF = '40616263'XC

The '40616263'XC is a hexadecimal representation of '@abc'. Therefore, this will change a '@abc' value to a NULL.

ex: define c1(char (4), NULLIF = '40616263'XC );

8) CASE WHEN :c1 = '?' THEN NULL ELSE :c1 END

This will change a '?' to a NULL.

9) NULLIF(:c1,'?')  

This will change a '?' to a NULL. This is an abbreviation of the case command.

10) CASE WHEN c1 IS NOT NULL THEN c1 ELSE 0 END

This changes a null value to a zero.

Teradata data types

Teradata data types can be categorized as follows.

1) Character data:

This is character data that can be fixed or variable length.

The data types can be CHAR, VARCHAR, CHAR VARYING, LONG VARCHAR.

2) Arithmetic Numeric data:

This is numeric data that can have arithmetic operations performed on it.

This numeric data is usually money or quantity values.

The data types can be DATE, DECIMAL, NUMERIC, BYTEINT, SMALLINT, INTEGER, REAL, DOUBLE PRECISION, or FLOAT.

The arithmetic operations can be:

SUM(t1.c1), MAX(t1.c1), t1.c1 + t2.c1, etc.

3) Non-Arithmetic Numeric data:

This is numeric data that will not have arithmetic operations performed on it.

This numeric data is usually code or id values, and usually, is not money or quantity values.

The data types can be DATE, DECIMAL, NUMERIC, BYTEINT, SMALLINT, INTEGER, REAL, DOUBLE PRECISION, or FLOAT.

4) Byte  data:

This is bit oriented data, with hexadecimal orientation of values.

This data type is not currently used in the EDW application tables.

The data types can be BYTE, VARBYTE.

This data will not be covered as part of this NULL topic.

5) Graphic data:

This is Japanese Kanji oriented data, with a mixture of single and multiple bytes representing the Japanese language.

This data type is not currently used in the EDW application tables.

The data types can be GRAPHIC, VARGRAPHIC, LONG VARGRAPHIC.

This data will not be covered as part of this NULL topic.

Teradata Architecture

Teradata Architecture diagram

Here the basic and most important component is PE (Parsing Engine) and PE can support up to 120 sessions.

1) Session Control : It checks authorization of users before processing any SQL query.

2) Parser : It checks syntax of the SQL query and access rights to the user to access various database objects referred in the SQL query.

3) Optimizer : Determines the most effective way by creating a plan or execution steps to follow  in order to perform actions on database objects as per SQL query submitted by user and scans the SQL query to determine where to place locks.

4) Dispatcher : It controls the sequence in which steps are executed and  It passes the execution steps to AMP by using BYNET. Dispatcher also plays an important role by combining all the responses received and send it to user.

The Next component is BYNET. It is used for communication between PE's and AMP's (Access Module Processor). There are two BYNET's available in any TERADATA environment: BYNET-0 & BYNET-1. Two BYNET's allows for continuous sending and receiving messages between PE's and AMP's.

AMP's can be considered as the worker in TERADATA Architecture. All the calculations and actual execution takes place in AMP only.Each AMP has it's own dedicated VDISK (Virtual DISK) to which it queries and process the steps planned by Optimizer. AMP's work only on their own VDISK and do not have access to other AMP's VDISK. Once the AMP perform the steps, it send back response to PE via BYNET where all the responses from various AMP's is collected and sent back to user.

Drop Table and Create Table Commands in Teradata SQL

Drop Table and Create Table commands  will address the following problems:

1. Reduce update activity on the DD/D (Data Dictionary/Directory), and improve overall system throughput.

2. Resolve the occasional deadlock problems which occur from simultaneous DD/D updates. (This could cause your application to be idle for multiple hours.)

The drop table command should be replaced with the “DELETE FROM table ALL” command. The delete command will perform better than the drop command, and without the DD/D updates.

The create command should only be executed when needed. The create table command should only be executed for table alterations or recovery situations.

There are exceptions to this requirement:

1. The fastload error tables must always be dropped, so that fastload can re-create them.

2. The DSS(Decision support system) Query tool creates tables for intermediate data storage.

Disadvantages of Compression

·          Does not compress varchar columns.

·   There should not be any spoolspace usage differences, because where compressed values are converted back into spoolspace, there should have been a spool file anyway. The spoolspace usage differences may not be fully understood and tested yet.