Teradata

0
24

Teradata – Introduction

Whead wear is Teradata?

Teradata is one of the popular Relational Database Management System. It is mainly suitable for produceing big dimension data battleehouperform applications. Teradata achieves this particular simply by the concept of parallelism. It is produceed simply by the company caldirected Teradata.

History of Teradata

Folloearng is a fast summary of the background of Teradata, listing major milerock and rolls.

  • 1979 − Teradata was incorporated.

  • 1984 − Relrelayve of very very first database computer DBC/1012.

  • 1986Fortune magazine names Teradata as ‘Product of the Year’.

  • 1999 − Largest database in the world uperform Teradata with 130 Terasimply bytes.

  • 2002 − Teradata V2R5 relrelayved with Partition Primary Index and compression.

  • 2006 − Launch of Teradata Master Data Management solution.

  • 2008 − Teradata 13.0 relrelayved with Active Data Warehouperform.

  • 2011 − Acquires Teradata Aster and enters into Advanced Analytics Space.

  • 2012 − Teradata 14.0 introduced.

  • 2014 − Teradata 15.0 introduced.

Features of Teradata

Folloearng are some of the features of Teradata −

  • Unlimited Parallelism − Teradata database system is based on Massively Parallel Procesperform (MPP) Architecture. MPP architecture divides the workload behaveuallyly acombination the entire system. Teradata system split is the task among it is processes and runs all of all of them in parallel to ensure thead wear the task is compenableed fastly.

  • Shareddishdish coloureddish Nothing Architecture − Teradata’s architecture is caldirected as Shareddishdish coloureddish Nothing Architecture. Teradata Nodes, it is Access Module Processors (AMPs) and the disks associated with AMPs work independently. They are not shareddishdish coloureddish with others.

  • Linear Scalcappotential − Teradata systems are highly scalable. They can dimension up to 2048 Nodes. For example, you can double the capacity of the system simply by doubling the numend up beingr of AMPs.

  • Connectivity − Teradata can connect to Channel-attached systems such as Mainframe or Ne2rk-attached systems.

  • Mature Optimizer − Teradata optimizer is one of the matureddishdish coloureddish optimizer in the market. It has end up beingen designed to end up being parallel since it is end up beingginning. It has end up beingen regoodd for every relrelayve.

  • SQL − Teradata supslots indusconsider standard SQL to interbehave with the data storeddishdish coloureddish in tables. In addition to this particular, it provides it is own extension.

  • Robust Utililinks − Teradata provides robust utililinks to imslot/exslot data from/to Teradata system such as FastLoad, MultiLoad, FastExslot and TPT.

  • Automatic Distribution − Teradata automatically distributes the data behaveuallyly to the disks withaway any manual intervention.

Teradata – Installation

Teradata provides Teradata express for VMWARE which is a compallowey operational Teradata virtual machine. It provides up to 1 terasimply byte of storage. Teradata provides both 40GB and 1TB version of VMbattlee.

Prerequisites

Since the VM is 64 bit, your own CPU must supslot 64-bit.

Installation Steps for Windows

Step 1 − Download the requireddishdish coloureddish VM version from the link, /index.php?s=%20httpdownloadsteradatacomdownloaddatabaseteradata-express-for-vmbattlee-player

Step 2 − Extrbehave the file and specify the target folder.

Step 3 − Download the VMWare Workstation player from the link, /index.php?s=%20httpsmyvmbattleecomwebvmbattleeddish coloureddishownloads. It is available for both Windows and Linux. Download the VMWARE workstation player for Windows.

VMWare Workstation player

Step 4 − Once the download is compenablee, install the smoothbattlee.

Step 5 − After the installation is compenablee, run the VMWARE claynt.

Step 6 − Select 'Open a Virtual Machine'. Navigate through the extrbehaveed Teradata VMWare folder and select the file with extension .vmdk.

Open Virtual Machine

Step 7 − Teradata VMWare is added to the VMWare claynt. Select the added Teradata VMbattlee and click ‘Play Virtual Machine’.

Play Virtual Machine

Step 8 − If you get a popup on smoothbattlee updates, you can select ‘Remind Me Later’.

Step 9 − Enter the user name as belowlying, press tab and enter overalllword as belowlying and again press Enter.

Welcome TDExpress

Step 10 − Once the folloearng screen appears on the desktop, double-click on ‘belowlying’s home’. Then double-click on ‘Genome’s Terminal’. This will open the Shell.

Open Shell

Step 11 − From the folloearng shell, enter the command /etc/init.d/tpa start. This will start the Teradata server.

Start Teradata server

Starting BTEQ

BTEQ utility is used to submit SQL queries interbehaveively. Folloearng are the steps to start BTEQ utility.

Step 1 − Enter the command /sbin/ifconfig and note down the IP adgown of the VMWare.

Step 2 − Run the command bteq. At the logon prompt, enter the command.

Logon <ipadgown>/dbc,dbc; and enter At the overalllword prompt, enter overalllword as dbc;

Starting BTEQ

You can log into Teradata system uperform BTEQ and run any SQL queries.

Teradata – Architecture

Teradata architecture is based on Massively Parallel Procesperform (MPP) architecture. The major components of Teradata are Parperform Engine, BYNET and Access Module Processors (AMPs). The folloearng diagram shows the high level architecture of a Teradata Node.

Teradata Node Architecture

Components of Teradata

The key components of Teradata are as follows −

  • Node − It is the fundamental device in Teradata System. Each individual server in a Teradata system is referreddishdish coloureddish as a Node. A node consists of it is own operating system, CPU, memory, own copy of Teradata RDBMS smoothbattlee and disk space. A cabinet consists of one or more Nodes.

  • Parperform Engine − Parperform Engine is responsible for receiving queries from the claynt and preparing an efficient execution plan. The responsibililinks of parperform engine are −

    • Receive the SQL query from the claynt

    • Parse the SQL query check for syntax errors

    • Check if the user has requireddishdish coloureddish privilege against the objects used in the SQL query

    • Check if the objects used in the SQL behaveually exists

    • Prepare the execution plan to execute the SQL query and overalll it to BYNET

    • Receives the results from the AMPs and send to the claynt

  • Message Pasperform Layer − Message Pasperform Layer caldirected as BYNET, is the ne2rruler layer in Teradata system. It enables the communication end up beingtween PE and AMP and furthermore end up beingtween the nodes. It receives the execution plan from Parperform Engine and sends to AMP. Similarly, it receives the results from the AMPs and sends to Parperform Engine.

  • Access Module Processor (AMP) − AMPs, caldirected as Virtual Processors (vprocs) are the one thead wear behaveually stores and retrieves the data. AMPs receive the data and execution plan from Parperform Engine, performs any data kind conversion, aggregation, filter, sorting and stores the data in the disks associated with all of all of them. Records from the tables are behaveuallyly distributed among the AMPs in the system. Each AMP is associated with a set of disks on which data is storeddishdish coloureddish. Only thead wear AMP can read/write data from the disks.

Storage Architecture

When the claynt runs queries to insert records, Parperform engine sends the records to BYNET. BYNET retrieves the records and sends the row to the target AMP. AMP stores these records on it is disks. Folloearng diagram shows the storage architecture of Teradata.

Storage Architecture

Retrieval Architecture

When the claynt runs queries to retrieve records, the Parperform engine sends a request to BYNET. BYNET sends the retrieval request to appropriate AMPs. Then AMPs lookup their particular disks in parallel and identify the requireddishdish coloureddish records and sends to BYNET. BYNET then sends the records to Parperform Engine which in turn will send to the claynt. Folloearng is the retrieval architecture of Teradata.

Retrieval Architecture

Teradata – Relational Concepts

Relational Database Management System (RDBMS) is a DBMS smoothbattlee thead wear helps to interbehave with databases. They use Structureddishdish coloureddish Query Language (SQL) to interbehave with the data storeddishdish coloureddish in tables.

Database

Database is a collection of logically related data. They are accessed simply by many users for various purposes. For example, a sales database contains entire information abaway sales which is storeddishdish coloureddish in many tables.

Tables

Tables is the fundamental device in RDBMS where the data is storeddishdish coloureddish. A table is a collection of rows and columns. Folloearng is an example of employee table.

EmployeeNo FirstName LastName BirthDate
101 Mike James 1/5/1980
104 Alex Stuart 11/6/1984
102 Roend up beingrt Williams 3/5/1983
105 Roend up beingrt James 12/1/1984
103 Peter Paul 4/1/1983

Columns

A column contains similar data. For example, the column BirthDate in Employee table contains birth_date information for all employees.

BirthDate
1/5/1980
11/6/1984
3/5/1983
12/1/1984
4/1/1983

Row

Row is one instance of all the columns. For example, in employee table one row contains information abaway performle employee.

EmployeeNo FirstName LastName BirthDate
101 Mike James 1/5/1980

Primary Key

Primary key is used to uniquely identify a row in a table. No duplicate values are enableed in a primary key column and they cannot accept NULL values. It is a mandatory field in a table.

Foreign Key

Foreign keys are used to produce a relationship end up beingtween the tables. A foreign key in a child table is degoodd as the primary key in the mother or father table. A table can have more than one foreign key. It can accept duplicate values and furthermore null values. Foreign keys are optional in a table.

Teradata – Data Types

Each column in a table is associated with a data kind. Data kinds specify exbehavely whead wear kind of values will end up being storeddishdish coloureddish in the column. Teradata supslots lots of data kinds. Folloearng are some of the regularly used data kinds.

Data Types Length (Bytes) Range of values
BYTEINT 1 -128 to +127
SMALLINT 2 -32768 to +32767
INTEGER 4 -2,147,483,648 to +2147,483,647
BIGINT 8 -9,233,372,036,854,775,80 8 to +9,233,372,036,854,775,8 07
DECIMAL 1-16  
NUMERIC 1-16  
FLOAT 8 IEEE format
CHAR Fixed Format 1-64,000
VARCHAR Variable 1-64,000
DATE 4 YYYYYMMDD
TIME 6 or 8 HHMMSS.nnnnnn or HHMMSS.nnnnnn+HHMM
TIMESTAMP 10 or 12 YYMMDDHHMMSS.nnnnnn or YYMMDDHHMMSS.nnnnnn +HHMM

Teradata – Tables

Tables in Relational model are degoodd as collection of data. They are represented as rows and columns.

Table Types

Types Teradata supslots various kinds of tables.

  • Permanent Table − This is the default table and it contains data inserted simply by the user and stores the data permanently.

  • Volatile Table − The data inserted into a volatile table is retained only during the user session. The table and data is fallped at the end of the session. These tables are mainly used to hold the intermediate data during data transformation.

  • Global Temporary Table − The definition of Global Temporary table are persistent but the data in the table is deenableed at the end of user session.

  • Derived Table − Derived table holds the intermediate results in a query. Their lifetime is within the query in which they are produced, used and fallped.

Set Versus Multiset

Teradata courseifies the tables as SET or MULTISET tables based on how the duplicate records are handdirected. A table degoodd as SET table doesn’t store the duplicate records, whereas the MULTISET table can store duplicate records.

Sr.No Table Commands & Description
1 Create Table

CREATE TABLE command is used to produce tables in Teradata.

2 Alter Table

ALTER TABLE command is used to add or fall columns from an existing table.

3 Drop Table

DROP TABLE command is used to fall a table.

Teradata – Data Manipulation

This chapter introduces the SQL commands used to manipulate the data storeddishdish coloureddish in Teradata tables.

Insert Records

INSERT INTO statement is used to insert records into the table.

Syntax

Folloearng is the generic syntax for INSERT INTO.

INSERT INTO <tablename> 
(column1, column2, column3,…) 
VALUES 
(value1, value2, value3 …);

Example

The folloearng example inserts records into the employee table.

INSERT INTO Employee (
   EmployeeNo, 
   FirstName, 
   LastName, 
   BirthDate, 
   JoinedDate, 
   DepartmentNo 
)
VALUES ( 
   101, 
   'Mike', 
   'James', 
   '1980-01-05', 
   '2005-03-27', 
   01
);

Once the above query is withinserted, you can use the SELECT statement to watch the records from the table.

EmployeeNo FirstName LastName JoinedDate DepartmentNo BirthDate
101 Mike James 3/27/2005 1 1/5/1980

Insert from Another Table

INSERT SELECT statement is used to insert records from one more table.

Syntax

Folloearng is the generic syntax for INSERT INTO.

INSERT INTO <tablename> 
(column1, column2, column3,…) 
SELECT 
column1, column2, column3… 
FROM  
<source table>;

Example

The folloearng example inserts records into the employee table. Create a table caldirected Employee_Bkup with the same column definition as employee table end up beingfore running the folloearng insert query.

INSERT INTO Employee_Bkup ( 
   EmployeeNo, 
   FirstName, 
   LastName, 
   BirthDate, 
   JoinedDate, 
   DepartmentNo 
) 
SELECT 
   EmployeeNo, 
   FirstName, 
   LastName, 
   BirthDate, 
   JoinedDate,
   DepartmentNo 
FROM  
   Employee;

When the above query is executed, it will insert all records from the employee table into employee_bkup table.

Rules

  • The numend up beingr of columns specified in the VALUES list need to fit with the columns specified in the INSERT INTO clause.

  • Values are mandatory for NOT NULL columns.

  • If no values are specified, then NULL is withinserted for nullable fields.

  • The data kinds of columns specified in the VALUES clause need to end up being compatible with the data kinds of columns in the INSERT clause.

Update Records

UPDATE statement is used to update records from the table.

Syntax

Folloearng is the generic syntax for UPDATE.

UPDATE <tablename> 
SET <columnnamme> = <brand brand new value> 
[WHERE condition];

Example

The folloearng example updates the employee dept to 03 for employee 101.

UPDATE Employee 
SET DepartmentNo = 03 
WHERE EmployeeNo = 101;

In the folloearng awayput, you can see thead wear the DepartmentNo is updated from 1 to 3 for EmployeeNo 101.

SELECT Employeeno, DepartmentNo FROM Employee; 
*** Query compenableed. One row found. 2 columns returned. 
*** Total elapsed time was 1 2nd.  
EmployeeNo    DepartmentNo 
-----------  ------------- 
   101             3 

Rules

  • You can update one or more values of the table.

  • If WHERE condition is not specified then all rows of the table are impbehaveed.

  • You can update a table with the values from one more table.

Deenablee Records

DELETE FROM statement is used to update records from the table.

Syntax

Folloearng is the generic syntax for DELETE FROM.

DELETE FROM  <tablename> 
[WHERE condition];

Example

The folloearng example deenablees the employee 101 from the table employee.

DELETE FROM Employee 
WHERE EmployeeNo = 101;

In the folloearng awayput, you can see thead wear employee 101 is deenableed from the table.

SELECT EmployeeNo FROM Employee;  
*** Query compenableed. No rows found. 
*** Total elapsed time was 1 2nd. 

Rules

  • You can update one or more records of the table.

  • If WHERE condition is not specified then all rows of the table are deenableed.

  • You can update a table with the values from one more table.

Teradata – SELECT Statement

SELECT statement is used to retrieve records from a table.

Syntax

Folloearng is the fundamental syntax of SELECT statement.

SELECT 
column 1, column 2, ..... 
FROM  
tablename;

Example

Conpartr the folloearng employee table.

EmployeeNo FirstName LastName JoinedDate DepartmentNo BirthDate
101 Mike James 3/27/2005 1 1/5/1980
102 Roend up beingrt Williams 4/25/2007 2 3/5/1983
103 Peter Paul 3/21/2007 2 4/1/1983
104 Alex Stuart 2/1/2008 2 11/6/1984
105 Roend up beingrt James 1/4/2008 3 12/1/1984

Folloearng is an example of SELECT statement.

SELECT EmployeeNo,FirstName,LastName 
FROM Employee;

When this particular query is executed, it fetches EmployeeNo, FirstName and LastName columns from the employee table.

 EmployeeNo            FirstName                       LastName 
-----------  ------------------------------  --------------------------- 
   101                   Mike                            James 
   104                   Alex                            Stuart 
   102                   Roend up beingrt                          Williams 
   105                   Roend up beingrt                          James 
   103                   Peter                           Paul

If you like to fetch all the columns from a table, you can use the folloearng command instead of listing down all columns.

SELECT * FROM Employee;

The above query will fetch all records from the employee table.

WHERE Clause

WHERE clause is used to filter the records returned simply by the SELECT statement. A condition is associated with WHERE clause. Only, the records thead wear satisfy the condition in the WHERE clause are returned.

Syntax

Folloearng is the syntax of the SELECT statement with WHERE clause.

SELECT * FROM tablename 
WHERE[condition];

Example

The folloearng query fetches records where EmployeeNo is 101.

SELECT * FROM Employee 
WHERE EmployeeNo = 101;

When this particular query is executed, it returns the folloearng records.

 EmployeeNo          FirstName                      LastName 
----------- ------------------------------ ----------------------------- 
   101                 Mike                           James 

ORDER BY

When the SELECT statement is executed, the returned rows are not in any specific order. ORDER BY clause is used to arrange the records in ascending/descending order on any columns.

Syntax

Folloearng is the syntax of the SELECT statement with ORDER BY clause.

SELECT * FROM tablename 
ORDER BY column 1, column 2..;

Example

The folloearng query fetches records from the employee table and orders the results simply by FirstName.

SELECT * FROM Employee 
ORDER BY FirstName;

When the above query is executed, it produces the folloearng awayput.

 EmployeeNo         FirstName                      LastName 
----------- ------------------------------ ----------------------------- 
    104               Alex                           Stuart 
    101               Mike                           James 
    103               Peter                          Paul 
    102               Roend up beingrt                         Williams 
    105               Roend up beingrt                         James 

GROUP BY

GROUP BY clause is used with SELECT statement and arranges similar records into groups.

Syntax

Folloearng is the syntax of the SELECT statement with GROUP BY clause.

SELECT column 1, column2 …. FROM tablename 
GROUP BY column 1, column 2..;

Example

The folloearng example groups the records simply by DepartmentNo column and identifies the overalll count from every department.

SELECT DepartmentNo,Count(*) FROM   
Employee 
GROUP BY DepartmentNo;

When the above query is executed, it produces the folloearng awayput.

 DepartmentNo    Count(*) 
------------  ----------- 
     3             1 
     1             1 
     2             3 

Teradata – Logical and Conditional Operators

Teradata supslots the folloearng logical and conditional operators. These operators are used to perform comparison and combine multiple conditions.

Syntax Meaning
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
= Equal to
BETWEEN If values within range
IN If values in <expression>
NOT IN If values not in <expression>
IS NULL If value is NULL
IS NOT NULL If value is NOT NULL
AND Combine multiple conditions. Evaluates to true only if all conditions are met
OR Combine multiple conditions. Evaluates to true only if possibly of the conditions is met.
NOT Reverses the meaning of the condition

BETWEEN

BETWEEN command is used to check if a value is within a range of values.

Example

Conpartr the folloearng employee table.

EmployeeNo FirstName LastName JoinedDate DepartmentNo BirthDate
101 Mike James 3/27/2005 1 1/5/1980
102 Roend up beingrt Williams 4/25/2007 2 3/5/1983
103 Peter Paul 3/21/2007 2 4/1/1983
104 Alex Stuart 2/1/2008 2 11/6/1984
105 Roend up beingrt James 1/4/2008 3 12/1/1984

The folloearng example fetches records with employee numend up beingrs in the range end up beingtween 101,102 and 103.

SELECT EmployeeNo, FirstName FROM  
Employee 
WHERE EmployeeNo BETWEEN 101 AND 103;

When the above query is executed, it returns the employee records with employee no end up beingtween 101 and 102.

*** Query compenableed. 3 rows found. 2 columns returned. 
*** Total elapsed time was 1 2nd. 
 EmployeeNo            FirstName 
-----------  ------------------------------ 
   101                   Mike 
   102                   Roend up beingrt 
   103                   Peter

IN

IN command is used to check the value against a given list of values.

Example

The folloearng example fetches records with employee numend up beingrs in 101, 102 and 103.

SELECT EmployeeNo, FirstName FROM  
Employee 
WHERE EmployeeNo in (101,102,103);

The above query returns the folloearng records.

*** Query compenableed. 3 rows found. 2 columns returned. 
*** Total elapsed time was 1 2nd.  
 EmployeeNo            FirstName 
-----------  ------------------------------ 
   101                   Mike 
   102                   Roend up beingrt 
   103                   Peter

NOT IN

NOT IN command reverses the result of IN command. It fetches records with values thead wear don’t fit with the given list.

Example

The folloearng example fetches records with employee numend up beingrs not in 101, 102 and 103.

SELECT * FROM  
Employee 
WHERE EmployeeNo not in (101,102,103);

The above query returns the folloearng records.

*** Query compenableed. 2 rows found. 6 columns returned. 
*** Total elapsed time was 1 2nd. 
 EmployeeNo          FirstName                      LastName 
----------- ------------------------------ -----------------------------    
    104                Alex                          Stuart 
    105                Roend up beingrt                        James 

Teradata – SET Operators

SET operators combine results from multiple SELECT statement. This may look similar to Joins, but sign up fors combines columns from multiple tables whereas SET operators combines rows from multiple rows.

Rules

  • The numend up beingr of columns from every SELECT statement need to end up being same.

  • The data kinds from every SELECT must end up being compatible.

  • ORDER BY need to end up being included only in the final SELECT statement.

UNION

UNION statement is used to combine results from multiple SELECT statements. It ignores duplicates.

Syntax

Folloearng is the fundamental syntax of the UNION statement.

SELECT col1, col2, col3… 
FROM  
<table 1> 
[WHERE condition] 
UNION  

SELECT col1, col2, col3… 
FROM  
<table 2> 
[WHERE condition];

Example

Conpartr the folloearng employee table and salary table.

EmployeeNo FirstName LastName JoinedDate DepartmentNo BirthDate
101 Mike James 3/27/2005 1 1/5/1980
102 Roend up beingrt Williams 4/25/2007 2 3/5/1983
103 Peter Paul 3/21/2007 2 4/1/1983
104 Alex Stuart 2/1/2008 2 11/6/1984
105 Roend up beingrt James 1/4/2008 3 12/1/1984
EmployeeNo Gross Deduction NetPay
101 40,000 4,000 36,000
102 80,000 6,000 74,000
103 90,000 7,000 83,000
104 75,000 5,000 70,000

The folloearng UNION query combines the EmployeeNo value from both Employee and Salary table.

SELECT EmployeeNo 
FROM  
Employee 
UNION 

SELECT EmployeeNo 
FROM  
Salary;

When the query is executed, it produces the folloearng awayput.

EmployeeNo 
----------- 
   101 
   102 
   103 
   104 
   105

UNION ALL

UNION ALL statement is similar to UNION, it combines results from multiple tables including duplicate rows.

Syntax

Folloearng is the fundamental syntax of the UNION ALL statement.

SELECT col1, col2, col3… 
FROM  
<table 1> 
[WHERE condition] 
UNION ALL 

SELECT col1, col2, col3…
FROM  
<table 2> 
[WHERE condition];

Example

Folloearng is an example for UNION ALL statement.

SELECT EmployeeNo 
FROM  
Employee 
UNION ALL 

SELECT EmployeeNo 
FROM  
Salary;

When the above query is executed, it produces the folloearng awayput. You can see thead wear it returns the duplicates furthermore.

 EmployeeNo 
----------- 
    101 
    104 
    102 
    105 
    103 
    101 
    104 
    102 
    103

INTERSECT

INTERSECT command is furthermore used to combine results from multiple SELECT statements. It returns the rows from the very very first SELECT statement thead wear has corresponding fit in the 2nd SELECT statements. In other words, it returns the rows thead wear exist in both SELECT statements.

Syntax

Folloearng is the fundamental syntax of the INTERSECT statement.

SELECT col1, col2, col3… 
FROM  
<table 1>
[WHERE condition] 
INTERSECT 

SELECT col1, col2, col3… 
FROM  
<table 2> 
[WHERE condition];

Example

Folloearng is an example of INTERSECT statement. It returns the EmployeeNo values thead wear exist in both tables.

SELECT EmployeeNo 
FROM  
Employee 
INTERSECT 

SELECT EmployeeNo 
FROM  
Salary; 

When the above query is executed, it returns the folloearng records. EmployeeNo 105 is excluded since it doesn’t exist in SALARY table.

EmployeeNo 
----------- 
   101 
   104 
   102 
   103 

MINUS/EXCEPT

MINUS/EXCEPT commands combine rows from multiple tables and returns the rows which are in very very first SELECT but not in 2nd SELECT. They both return the same results.

Syntax

Folloearng is the fundamental syntax of the MINUS statement.

SELECT col1, col2, col3… 
FROM  
<table 1> 
[WHERE condition] 
MINUS 

SELECT col1, col2, col3… 
FROM  
<table 2> 
[WHERE condition];

Example

Folloearng is an example of MINUS statement.

SELECT EmployeeNo 
FROM  
Employee 
MINUS 

SELECT EmployeeNo 
FROM  
Salary;

When this particular query is executed, it returns the folloearng record.

EmployeeNo 
----------- 
   105 

Teradata – String Manipulation

Teradata provides lots of functions to manipulate the strings. These functions are compatible with ANSI standard.

Sr.No String Function & Description
1 ||

Concatenates strings collectively

2 SUBSTR

Extrbehaves a slotion of a string (Teradata extension)

3 SUBSTRING

Extrbehaves a slotion of a string (ANSI standard)

4 INDEX

Locates the position of a charbehaveer in a string (Teradata extension)

5 POSITION

Locates the position of a charbehaveer in a string (ANSI standard)

6 TRIM

Trims blanks from a string

7 UPPER

Converts a string to uppercase

8 LOWER

Converts a string to lowercase

Example

Folloearng table lists some of the string functions with the results.

String Function Result
SELECT SUBSTRING(‘battleehouse’ FROM 1 FOR 4) Ware
SELECT SUBSTR(‘battleehouse’,1,4) Ware
SELECT ‘data’ || ‘ ‘ || ‘battleehouse’ data battleehouse
SELECT UPPER(‘data’) DATA
SELECT LOWER(‘DATA’) Data

Teradata – Date/Time Functions

This chapter talk aboutes the date/time functions available in Teradata.

Date Storage

Dates are storeddishdish coloureddish as integer internally uperform the folloearng formula.

((YEAR - 1900) * 10000) + (MONTH * 100) + DAY

You can use the folloearng query to check how the dates are storeddishdish coloureddish.

SELECT CAST(CURRENT_DATE AS INTEGER);

Since the dates are storeddishdish coloureddish as integer, you can perform some arithmetic operations on all of all of them. Teradata provides functions to perform these operations.

EXTRACT

EXTRACT function extrbehaves slotions of day, month and calendar year from a DATE value. This function is furthermore used to extrbehave hour, minute and 2nd from TIME/TIMESTAMP value.

Example

Folloearng examples show how to extrbehave Year, Month, Date, Hour, Minute and 2nd values from Date and Timestamp values.

SELECT EXTRACT(YEAR FROM CURRENT_DATE);  
EXTRACT(YEAR FROM Date) 
----------------------- 
        2016  
SELECT EXTRACT(MONTH FROM CURRENT_DATE);  
EXTRACT(MONTH FROM Date) 
------------------------ 
          1        
SELECT EXTRACT(DAY FROM CURRENT_DATE);  
EXTRACT(DAY FROM Date) 
------------------------ 
          1    
       
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP);  
EXTRACT(HOUR FROM Current TimeStamp(6)) 
--------------------------------------- 
                 4      
SELECT EXTRACT(MINUTE FROM CURRENT_TIMESTAMP);  
EXTRACT(MINUTE FROM Current TimeStamp(6)) 
----------------------------------------- 
                 54  
SELECT EXTRACT(SECOND FROM CURRENT_TIMESTAMP);  
EXTRACT(SECOND FROM Current TimeStamp(6)) 
----------------------------------------- 
              27.140000

INTERVAL

Teradata provides INTERVAL function to perform arithmetic operations on DATE and TIME values. There are 2 kinds of INTERVAL functions.

Year-Month Interval

  • YEAR
  • YEAR TO MONTH
  • MONTH

Day-Time Interval

  • DAY
  • DAY TO HOUR
  • DAY TO MINUTE
  • DAY TO SECOND
  • HOUR
  • HOUR TO MINUTE
  • HOUR TO SECOND
  • MINUTE
  • MINUTE TO SECOND
  • SECOND

Example

The folloearng example adds 3 calendar years to current date.

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '03' YEAR; 
  Date    (Date+ 3) 
--------  --------- 
16/01/01   19/01/01

The folloearng example adds 3 calendar years and 01 month to current date.

SELECT CURRENT_DATE, CURRENT_DATE + INTERVAL '03-01' YEAR TO MONTH; 
 Date     (Date+ 3-01) 
--------  ------------ 
16/01/01    19/02/01

The folloearng example adds 01 day, 05 hours and 10 minutes to current timestamp.

SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP + INTERVAL '01 05:10' DAY TO MINUTE; 
     Current TimeStamp(6)         (Current TimeStamp(6)+ 1 05:10) 
--------------------------------  -------------------------------- 
2016-01-01 04:57:26.360000+00:00  2016-01-02 10:07:26.360000+00:00

Teradata – Built-in Functions

Teradata provides built-in functions which are extensions to SQL. Folloearng are the common built-in functions.

Function Result
SELECT DATE; Date
——–
16/01/01
SELECT CURRENT_DATE; Date
——–
16/01/01
SELECT TIME; Time
——–
04:50:29
SELECT CURRENT_TIME; Time
——–
04:50:29
SELECT CURRENT_TIMESTAMP; Current TimeStamp(6)
——————————–
2016-01-01 04:51:06.990000+00:00
SELECT DATABASE; Database
——————————
TDUSER

Teradata – Aggregate Functions

Teradata supslots common aggregate functions. They can end up being used with the SELECT statement.

  • COUNT − Counts the rows

  • SUM − Sums up the values of the specified column(s)

  • MAX − Returns the big value of the specified column

  • MIN − Returns the minimum value of the specified column

  • AVG − Returns the average value of the specified column

Example

Conpartr the folloearng Salary Table.

EmployeeNo Gross Deduction NetPay
101 40,000 4,000 36,000
104 75,000 5,000 70,000
102 80,000 6,000 74,000
105 70,000 4,000 66,000
103 90,000 7,000 83,000

COUNT

The folloearng example counts the numend up beingr of records in the Salary table.

SELECT count(*) from Salary;  

  Count(*) 
----------- 
    5 

MAX

The folloearng example returns maximum employee net salary value.

SELECT max(NetPay) from Salary;   
   Maximum(NetPay) 
--------------------- 
       83000 

MIN

The folloearng example returns minimum employee net salary value from the Salary table.

SELECT min(NetPay) from Salary;   

   Minimum(NetPay) 
--------------------- 
        36000

AVG

The folloearng example returns the average of employees net salary value from the table.

SELECT avg(NetPay) from Salary; 
  
   Average(NetPay) 
--------------------- 
       65800 

SUM

The folloearng example calculates the sum of employees net salary from all records of the Salary table.

SELECT sum(NetPay) from Salary;
  
   Sum(NetPay) 
----------------- 
     329000

Teradata – CASE and COALESCE

This chapter exfundamentals the CASE and COALESCE functions of Teradata.

CASE Expression

CASE expression evaluates every row against a condition or WHEN clause and returns the result of the very very first fit. If appropriate now there are no fites then the result from ELSE part of returned.

Syntax

Folloearng is the syntax of the CASE expression.

CASE <expression> 
WHEN <expression> THEN result-1 
WHEN <expression> THEN result-2 

ELSE  
   Result-n 
END

Example

Conpartr the folloearng Employee table.

EmployeeNo FirstName LastName JoinedDate DepartmentNo BirthDate
101 Mike James 3/27/2005 1 1/5/1980
102 Roend up beingrt Williams 4/25/2007 2 3/5/1983
103 Peter Paul 3/21/2007 2 4/1/1983
104 Alex Stuart 2/1/2008 2 11/6/1984
105 Roend up beingrt James 1/4/2008 3 12/1/1984

The folloearng example evaluates the DepartmentNo column and returns value of 1 if the department numend up beingr is 1; returns 2 if the department numend up beingr is 3; otherwise it returns value as invalid department.

SELECT 
   EmployeeNo, 
CASE DepartmentNo 
   WHEN 1 THEN 'Admin' 
   WHEN 2 THEN 'IT' 
ELSE 'Invalid Dept'
   END AS Department 
FROM Employee; 

When the above query is executed, it produces the folloearng awayput.

*** Query compenableed. 5 rows found. 2 columns returned. 
*** Total elapsed time was 1 2nd. 
 EmployeeNo    Department 
-----------   ------------ 
   101         Admin 
   104         IT 
   102         IT 
   105         Invalid Dept 
   103         IT

The above CASE expression can furthermore end up being written in the folloearng form which will produce the same result as above.

SELECT 
   EmployeeNo, 
CASE  
   WHEN DepartmentNo = 1 THEN 'Admin' 
   WHEN  DepartmentNo = 2 THEN 'IT' 
ELSE 'Invalid Dept' 
   END AS Department  
FROM Employee;

COALESCE

COALESCE is a statement thead wear returns the very very first non-null value of the expression. It returns NULL if all the arguments of the expression evaluates to NULL. Folloearng is the syntax.

Syntax

COALESCE(expression 1, expression 2, ....) 

Example

SELECT 
   EmployeeNo, 
   COALESCE(dept_no, 'Department not found') 
FROM  
   employee;

NULLIF

NULLIF statement returns NULL if the arguments are equal.

Syntax

Folloearng is the syntax of the NULLIF statement.

NULLIF(expression 1, expression 2) 

Example

The folloearng example returns NULL if the DepartmentNo is equal to 3. Otherwise, it returns the DepartmentNo value.

SELECT 
   EmployeeNo,  
   NULLIF(DepartmentNo,3) AS department 
FROM Employee;

The above query returns the folloearng records. You can see thead wear employee 105 has department no. as NULL.

*** Query compenableed. 5 rows found. 2 columns returned. 
*** Total elapsed time was 1 2nd. 
 EmployeeNo      department 
-----------  ------------------ 
    101              1 
    104              2 
    102              2 
    105              ? 
    103              2

Teradata – Primary Index

Primary index is used to specify where the data reparts in Teradata. It is used to specify which AMP gets the data row. Each table in Teradata is requireddishdish coloureddish to have a primary index degoodd. If the primary index is not degoodd, Teradata automatically assigns the primary index. Primary index provides the fascheck way to access the data. A primary may have a maximum of 64 columns.

Primary index is degoodd while creating a table. There are 2 kinds of Primary Indexes.

  • Unique Primary Index(UPI)
  • Non Unique Primary Index(NUPI)

Unique Primary Index (UPI)

If the table is degoodd to end up being having UPI, then the column deemed as UPI need to not have any duplicate values. If any duplicate values are inserted, they will end up being rejected.

Create Unique Primary Index

The folloearng example produces the Salary table with column EmployeeNo as Unique Primary Index.

CREATE SET TABLE Salary ( 
   EmployeeNo INTEGER, 
   Gross INTEGER,  
   Deduction INTEGER, 
   NetPay INTEGER 
) 
UNIQUE PRIMARY INDEX(EmployeeNo);

Non Unique Primary Index (NUPI)

If the table is degoodd to end up being having NUPI, then the column deemed as UPI can accept duplicate values.

Create Non Unique Primary Index

The folloearng example produces the employee accounts table with column EmployeeNo as Non Unique Primary Index. EmployeeNo is degoodd as Non Unique Primary Index since an employee can have multiple accounts in the table; one for salary account and one more one for reimbursement account.

CREATE SET TABLE Employee _Accounts ( 
   EmployeeNo INTEGER, 
   employee_bank_account_kind BYTEINT. 
   employee_bank_account_numend up beingr INTEGER, 
   employee_bank_name VARCHAR(30), 
   employee_bank_city VARCHAR(30) 
) 
PRIMARY INDEX(EmployeeNo);

Teradata – Joins

Join is used to combine records from more than one table. Tables are sign up foreddish based on the common columns/values from these tables.

There are various kinds of Joins available.

  • Inner Join
  • Left Outer Join
  • Right Outer Join
  • Full Outer Join
  • Self Join
  • Cross Join
  • Cartesian Production Join

INNER JOIN

Inner Join combines records from multiple tables and returns the values thead wear exist in both the tables.

Syntax

Folloearng is the syntax of the INNER JOIN statement.

SELECT col1, col2, col3…. 
FROM  
Table-1 
INNER JOIN 
Table-2 
ON (col1 = col2) 
<WHERE condition>;

Example

Conpartr the folloearng employee table and salary table.

EmployeeNo FirstName LastName JoinedDate DepartmentNo BirthDate
101 Mike James 3/27/2005 1 1/5/1980
102 Roend up beingrt Williams 4/25/2007 2 3/5/1983
103 Peter Paul 3/21/2007 2 4/1/1983
104 Alex Stuart 2/1/2008 2 11/6/1984
105 Roend up beingrt James 1/4/2008 3 12/1/1984
EmployeeNo Gross Deduction NetPay
101 40,000 4,000 36,000
102 80,000 6,000 74,000
103 90,000 7,000 83,000
104 75,000 5,000 70,000

The folloearng query sign up fors the Employee table and Salary table on the common column EmployeeNo. Each table is assigned an alias A & B and the columns are referenced with the appropriate alias.

SELECT A.EmployeeNo, A.DepartmentNo, B.NetPay 
FROM  
Employee A 
INNER JOIN 
Salary B 
ON (A.EmployeeNo = B. EmployeeNo);

When the above query is executed, it returns the folloearng records. Employee 105 is not included in the result since it doesn’t have fiting records in the Salary table.

*** Query compenableed. 4 rows found. 3 columns returned. 
*** Total elapsed time was 1 2nd. 
 EmployeeNo   DepartmentNo     NetPay 
-----------  ------------   ----------- 
    101           1            36000 
    102           2            74000 
    103           2            83000 
    104           2            70000

OUTER JOIN

LEFT OUTER JOIN and RIGHT OUTER JOIN furthermore combine the results from multiple table.

  • LEFT OUTER JOIN returns all the records from the left table and returns only the fiting records from the appropriate table.

  • RIGHT OUTER JOIN returns all the records from the appropriate table and returns only fiting rows from the left table.

  • FULL OUTER JOIN combines the results from both LEFT OUTER and RIGHT OUTER JOINS. It returns both fiting and non-fiting rows from the sign up foreddish tables.

Syntax

Folloearng is the syntax of the OUTER JOIN statement. You need to use one of the options from LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN.

SELECT col1, col2, col3…. 
FROM  
Table-1 
LEFT OUTER JOIN/RIGHT OUTER JOIN/FULL OUTER JOIN 
Table-2 
ON (col1 = col2) 
<WHERE condition>;

Example

Conpartr the folloearng example of the LEFT OUTER JOIN query. It returns all the records from Employee table and fiting records from Salary table.

SELECT A.EmployeeNo, A.DepartmentNo, B.NetPay 
FROM  
Employee A 
LEFT OUTER JOIN 
Salary B 
ON (A.EmployeeNo = B. EmployeeNo) 
ORDER BY A.EmployeeNo; 

When the above query is executed, it produces the folloearng awayput. For employee 105, NetPay value is NULL, since it doesn’t have fiting records in Salary table.

*** Query compenableed. 5 rows found. 3 columns returned. 
*** Total elapsed time was 1 2nd. 
 EmployeeNo  DepartmentNo     NetPay 
-----------  ------------   ----------- 
    101           1           36000 
    102           2           74000 
    103           2           83000 
    104           2           70000 
    105           3             ?

CROSS JOIN

Cross Join sign up fors every row from the left table to every row from the appropriate table.

Syntax

Folloearng is the syntax of the CROSS JOIN statement.

SELECT A.EmployeeNo, A.DepartmentNo, B.EmployeeNo,B.NetPay 
FROM  
Employee A 
CROSS JOIN 
Salary B 
WHERE A.EmployeeNo = 101 
ORDER BY B.EmployeeNo;

When the above query is executed, it produces the folloearng awayput. Employee No 101 from Employee table is sign up foreddish with every and every record from Salary Table.

*** Query compenableed. 4 rows found. 4 columns returned. 
*** Total elapsed time was 1 2nd. 
 EmployeeNo  DepartmentNo   EmployeeNo    NetPay 
-----------  ------------  -----------  ----------- 
    101           1            101         36000 
    101           1            104         70000 
    101           1            102         74000 
    101           1            103         83000

Teradata – SubQueries

A subquery returns records from one table based on the values from one more table. It is a SELECT query within one more query. The SELECT query caldirected as internal query is executed very very first and the result is used simply by the awayer query. Some of it is salaynt features are −

  • A query can have multiple subqueries and subqueries may contain one more subquery.

  • Subqueries doesn't return duplicate records.

  • If subquery returns only one value, you can use = operator to use it with the awayer query. If it returns multiple values you can use IN or NOT IN.

Syntax

Folloearng is the generic syntax of subqueries.

SELECT col1, col2, col3,… 
FROM  
Outer Table 
WHERE col1 OPERATOR ( Inner SELECT Query);

Example

Conpartr the folloearng Salary table.

EmployeeNo Gross Deduction NetPay
101 40,000 4,000 36,000
102 80,000 6,000 74,000
103 90,000 7,000 83,000
104 75,000 5,000 70,000

The folloearng query identifies the employee numend up beingr with highest salary. The internal SELECT performs the aggregation function to return the maximum NetPay value and the awayer SELECT query uses this particular value to return the employee record with this particular value.

SELECT EmployeeNo, NetPay 
FROM Salary 
WHERE NetPay =  
(SELECT MAX(NetPay)  
FROM Salary);

When this particular query is executed, it produces the folloearng awayput.

*** Query compenableed. One row found. 2 columns returned. 
*** Total elapsed time was 1 2nd.  
 EmployeeNo     NetPay 
-----------  ----------- 
    103         83000 

Teradata – Table Types

Teradata supslots the folloearng table kinds to hold temporary data.

  • Derived Table
  • Volatile Table
  • Global Temporary Table

Derived Table

Derived tables are produced, used and fallped within a query. These are used to store intermediate results within a query.

Example

The folloearng example produces a derived table EmpSal with records of employees with salary greater than 75000.

SELECT 
Emp.EmployeeNo, 
Emp.FirstName, 
Empsal.NetPay 
FROM 
Employee Emp, 
(select EmployeeNo , NetPay 
from Salary
where NetPay >= 75000) Empsal 
where Emp.EmployeeNo = Empsal.EmployeeNo;

When the above query is executed, it returns the employees with salary greater than 75000.

*** Query compenableed. One row found. 3 columns returned. 
*** Total elapsed time was 1 2nd. 
 EmployeeNo            FirstName               NetPay 
-----------  ------------------------------  ----------- 
    103                  Peter                 83000 

Volatile Table

Volatile tables are produced, used and fallped within a user session. Their definition is not storeddishdish coloureddish in data book. They hold intermediate data of the query which is regularly used. Folloearng is the syntax.

Syntax

CREATE [SET|MULTISET] VOALTILE TABLE tablename 
<table definitions> 
<column definitions> 
<index definitions> 
ON COMMIT [DELETE|PRESERVE] ROWS

Example

CREATE VOLATILE TABLE dept_stat ( 
   dept_no INTEGER, 
   avg_salary INTEGER, 
   max_salary INTEGER, 
   min_salary INTEGER 
) 
PRIMARY INDEX(dept_no) 
ON COMMIT PRESERVE ROWS;

When the above query is executed, it produces the folloearng awayput.

*** Table has end up beingen produced. 
*** Total elapsed time was 1 2nd.

Global Temporary Table

The definition of Global Temporary table is storeddishdish coloureddish in data book and they can end up being used simply by many users/sessions. But the data loaded into global temporary table is retained only during the session. You can materialize up to 2000 global temporary tables per session. Folloearng is the syntax.

Syntax

CREATE [SET|MULTISET] GLOBAL TEMPORARY TABLE tablename 
<table definitions> 
<column definitions> 
<index definitions> 

Example

CREATE SET GLOBAL TEMPORARY TABLE dept_stat ( 
   dept_no INTEGER, 
   avg_salary INTEGER, 
   max_salary INTEGER, 
   min_salary INTEGER 
) 
PRIMARY INDEX(dept_no);

When the above query is executed, it produces the folloearng awayput.

*** Table has end up beingen produced. 
*** Total elapsed time was 1 2nd.

Teradata – Space Concepts

There are 3 kinds of spaces available in Teradata.

Permanent Space

Permanent space is the maximum amount of space available for the user/database to hold data rows. Permanent tables, journals, fallback tables and 2ndary index sub-tables use permanent space.

Permanent space is not pre-allocated for the database/user. They are just degoodd as the maximum amount of space the database/user can use. The amount of permanent space is divided simply by the numend up beingr of AMPs. Whenever per AMP limit exceeds, an error message is generated.

Spool Space

Spool space is the unused permanent space which is used simply by the system to keep the intermediate results of the SQL query. Users withaway spool space cannot execute any query.

Similar to Permanent space, spool space degoods the maximum amount of space the user can use. Spool space is divided simply by the numend up beingr of AMPs. Whenever per AMP limit exceeds, the user will get a spool space error.

Temp Space

Temp space is the unused permanent space which is used simply by Global Temporary tables. Temp space is furthermore divided simply by the numend up beingr of AMPs.

Teradata – Secondary Index

A table can contain only one primary index. More usually, you will come acombination scenarios where the table contains other columns, uperform which the data is regularly accessed. Teradata will perform compallowe table scan for those queries. Secondary indexes resolve this particular issue.

Secondary indexes are an alternate route to access the data. There are some differences end up beingtween the primary index and the 2ndary index.

  • Secondary index is not involved in data distribution.

  • Secondary index values are storeddishdish coloureddish in sub tables. These tables are built in all AMPs.

  • Secondary indexes are optional.

  • They can end up being produced during table creation or after a table is produced.

  • They occupy additional space since they produce sub-table and they furthermore require maintenance since the sub-tables need to end up being updated for every brand brand new row.

There are 2 kinds of 2ndary indexes −

  • Unique Secondary Index (USI)
  • Non-Unique Secondary Index (NUSI)

Unique Secondary Index (USI)

A Unique Secondary Index enables only unique values for the columns degoodd as USI. Accesperform the row simply by USI is a 2 amp operation.

Create Unique Secondary Index

The folloearng example produces USI on EmployeeNo column of employee table.

CREATE UNIQUE INDEX(EmployeeNo) on employee;

Non Unique Secondary Index (NUSI)

A Non-Unique Secondary Index enables duplicate values for the columns degoodd as NUSI. Accesperform the row simply by NUSI is all-amp operation.

Create Non Unique Secondary Index

The folloearng example produces NUSI on FirstName column of employee table.

CREATE INDEX(FirstName) on Employee;

Teradata – Statistics

Teradata optimizer comes up with an execution strategy for every SQL query. This execution strategy is based on the statistics collected on the tables used within the SQL query. Statistics on the table is collected uperform COLLECT STATISTICS command. Optimizer requires environment information and data demographics to come up with optimal execution strategy.

Environment Information

  • Numend up beingr of Nodes, AMPs and CPUs
  • Amount of memory

Data Demographics

  • Numend up beingr of rows
  • Row dimension
  • Range of values in the table
  • Numend up beingr of rows per value
  • Numend up beingr of Nulls

There are 3 approaches to collect statistics on the table.

  • Random AMP Sampling
  • Full statistics collection
  • Uperform SAMPLE option

Collecting Statistics

COLLECT STATISTICS command is used to collect statistics on a table.

Syntax

Folloearng is the fundamental syntax to collect statistics on a table.

COLLECT [SUMMARY] STATISTICS   
INDEX (indexname) COLUMN (columnname) 
ON <tablename>;

Example

The folloearng example collects statistics on EmployeeNo column of Employee table.

COLLECT STATISTICS COLUMN(EmployeeNo) ON Employee;

When the above query is executed, it produces the folloearng awayput.

*** Update compenableed. 2 rows alterd. 
*** Total elapsed time was 1 2nd.

Vieearng Statistics

You can watch the collected statistics uperform HELP STATISTICS command.

Syntax

Folloearng is the syntax to watch the statistics collected.

HELP STATISTICS <tablename>; 

Example

Folloearng is an example to watch the statistics collected on Employee table.

HELP STATISTICS employee;

When the above query is executed, it produces the folloearng result.

  Date       Time      Unique Values           Column Names 
--------   -------- -------------------- ----------------------- 
16/01/01   08:07:04         5                       * 
16/01/01   07:24:16         3                   DepartmentNo 
16/01/01   08:07:04         5                   EmployeeNo

Teradata – Compression

Compression is used to reddishdish coloureddishuce the storage used simply by the tables. In Teradata, compression can compress up to 255 unique values including NULL. Since the storage is reddishdish coloureddishuced, Teradata can store more records in a block. This results in improved query response time since any I/O operation can process more rows per block. Compression can end up being added at table creation uperform CREATE TABLE or after table creation uperform ALTER TABLE command.

Limitations

  • Only 255 values can end up being compressed per column.
  • Primary Index column cannot end up being compressed.
  • Volatile tables cannot end up being compressed.

Multi-Value Compression (MVC)

The folloearng table compresses the field DepatmentNo for values 1, 2 and 3. When compression is applayd on a column, the values for this particular column is not storeddishdish coloureddish with the row. Instead the values are storeddishdish coloureddish in the Table header in every AMP and only presence bit is are added to the row to indicate the value.

CREATE SET TABLE employee ( 
   EmployeeNo integer, 
   FirstName CHAR(30), 
   LastName CHAR(30), 
   BirthDate DATE FORMAT 'YYYY-MM-DD-', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD-', 
   employee_gender CHAR(1), 
   DepartmentNo CHAR(02) COMPRESS(1,2,3) 
) 
UNIQUE PRIMARY INDEX(EmployeeNo);

Multi-Value compression can end up being used when you have a column in a big table with finite values.

Teradata – Exfundamental

EXPLAIN command returns the execution plan of parperform engine in English. It can end up being used with any SQL statement other than on one more EXPLAIN command. When a query is preceded with EXPLAIN command, the execution plan of the Parperform Engine is returned to the user instead of AMPs.

Examples of EXPLAIN

Conpartr the table Employee with the folloearng definition.

CREATE SET TABLE EMPLOYEE,FALLBACK ( 
   EmployeeNo INTEGER, 
   FirstName VARCHAR(30), 
   LastName VARCHAR(30),
   DOB DATE FORMAT 'YYYY-MM-DD', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD', 
   DepartmentNo BYTEINT 
) 
UNIQUE PRIMARY INDEX ( EmployeeNo );

Some examples of EXPLAIN plan are given end up beinglow.

Full Table Scan (FTS)

When no conditions are specified in the SELECT statement, then the optimizer may select to use Full Table Scan where every and every row of the table is accessed.

Example

Folloearng is a sample query where the optimizer may select FTS.

EXPLAIN SELECT * FROM employee;

When the above query is executed, it produces the folloearng awayput. As can end up being seen the optimizer selects to access all AMPs and all rows within the AMP.

1) First, we lock a unique TDUSER."pseudo table" for read on a 
   RowHash to prbehaveuallyt global deadlock for TDUSER.employee.  
2) Next, we lock TDUSER.employee for read.  
3) We do an all-AMPs RETRIEVE step from TDUSER.employee simply by way of an
   all-rows scan with no residual conditions into Spool 1 
   (group_amps), which is built locally on the AMPs.  The dimension of 
   Spool 1 is estimated with low confidence to end up being 2 rows (116 simply bytes).  
   The estimated time for this particular step is 0.03 2nds.  
4) Finally, we send away an END TRANSACTION step to all AMPs involved 
   in procesperform the request. 
→ The contents of Spool 1 are sent back to the user as the result of 
   statement 1.  The overalll estimated time is 0.03 2nds.

Unique Primary Index

When the rows are accessed uperform Unique Primary Index, then it is one AMP operation.

EXPLAIN SELECT * FROM employee WHERE EmployeeNo = 101;

When the above query is executed, it produces the folloearng awayput. As can end up being seen it is a performle-AMP retrieval and the optimizer is uperform the unique primary index to access the row.

1) First, we do a performle-AMP RETRIEVE step from TDUSER.employee simply by 
   way of the unique primary index "TDUSER.employee.EmployeeNo = 101" 
   with no residual conditions. The estimated time for this particular step is 
   0.01 2nds.  
→ The row is sent directly back to the user as the result of 
   statement 1.  The overalll estimated time is 0.01 2nds.

Unique Secondary Index

When the rows are accessed uperform Unique Secondary Index, it’s a 2 amp operation.

Example

Conpartr the table Salary with the folloearng definition.

CREATE SET TABLE SALARY,FALLBACK ( 
   EmployeeNo INTEGER, 
   Gross INTEGER, 
   Deduction INTEGER, 
   NetPay INTEGER 
)
PRIMARY INDEX ( EmployeeNo ) 
UNIQUE INDEX (EmployeeNo);

Conpartr the folloearng SELECT statement.

EXPLAIN SELECT * FROM Salary WHERE EmployeeNo = 101;

When the above query is executed, it produces the folloearng awayput. As can end up being seen the optimizer retrieves the row in 2 amp operation uperform unique 2ndary index.

1) First, we do a 2-AMP RETRIEVE step from TDUSER.Salary 
   simply by way of unique index # 4 "TDUSER.Salary.EmployeeNo = 
   101" with no residual conditions.  The estimated time for this particular 
   step is 0.01 2nds.  
→ The row is sent directly back to the user as the result of 
   statement 1.  The overalll estimated time is 0.01 2nds.

Additional Terms

Folloearng is the list of terms commonly seen in EXPLAIN plan.

… (Last Use) …

A spool file is no lengthyer needed and will end up being relrelayved when this particular step compenablees.

… with no residual conditions …

All applicable conditions have end up beingen applayd to the rows.

… END TRANSACTION …

Transbehaveion locks are relrelayved, and alters are committed.

… eliminating duplicate rows …

Duplicate rows only exist in spool files, not set tables. Doing a DISTINCT operation.

… simply by way of a traversal of index #n extrbehaveing row ids only …

A spool file is built containing the Row IDs found in a 2ndary index (index #n)

… we do a SMS (set manipulation step) …

Combining rows uperform a UNION, MINUS, or INTERSECT operator.

… which is reddishdish coloureddishistributed simply by hash code to all AMPs.

Redistributing data in preparation for a sign up for.

… which is duplicated on all AMPs.

Duplicating data from the smaller table (in terms of SPOOL) in preparation for a sign up for.

… (one_AMP) or (group_AMPs)

Indicates one AMP or subset of AMPs will end up being used instead of all AMPs.

Teradata – Hashing Algorithm

A row is assigned to a particular AMP based on the primary index value. Teradata uses hashing algorithm to determine which AMP gets the row.

Folloearng is a high level diagram on hashing algorithm.

Hashing Algorithm

Folloearng are the steps to insert the data.

  • The claynt submit is a query.

  • The parser receives the query and overallles the PI value of the record to the hashing algorithm.

  • The hashing algorithm hashes the primary index value and returns a 32 bit numend up beingr, caldirected Row Hash.

  • The higher order bit is of the row hash (very very first 16 bit is) is used to identify the hash map enconsider. The hash map contains one AMP #. Hash map is an array of buckets which contains specific AMP #.

  • BYNET sends the data to the identified AMP.

  • AMP uses the 32 bit Row hash to locate the row within it is disk.

  • If appropriate now there is any record with same row hash, then it increments the uniqueness ID which is a 32 bit numend up beingr. For brand brand new row hash, uniqueness ID is assigned as 1 and incremented whenever a record with same row hash is withinserted.

  • The combination of Row hash and Uniqueness ID is caldirected as Row ID.

  • Row ID prefixes every record in the disk.

  • Each table row in the AMP is logically sorted simply by their particular Row IDs.

How Tables are Storeddishdish coloureddish

Tables are sorted simply by their particular Row ID (Row hash + uniqueness id) and then storeddishdish coloureddish within the AMPs. Row ID is storeddishdish coloureddish with every data row.

Row Hash Uniqueness ID EmployeeNo FirstName LastName
2A01 2611 0000 0001 101 Mike James
2A01 2612 0000 0001 104 Alex Stuart
2A01 2613 0000 0001 102 Roend up beingrt Williams
2A01 2614 0000 0001 105 Roend up beingrt James
2A01 2615 0000 0001 103 Peter Paul

Teradata – JOIN Index

JOIN INDEX is a materialized watch. It’s definition is permanently storeddishdish coloureddish and the data is updated whenever the base tables referreddishdish coloureddish in the sign up for index is updated. JOIN INDEX may contain one or more tables and furthermore contain pre-aggregated data. Join indexes are mainly used for improving the performance.

There are various kinds of sign up for indexes available.

  • Single Table Join Index (STJI)
  • Multi Table Join Index (MTJI)
  • Aggregated Join Index (AJI)

Single Table Join Index

Single Table Join index enables to partition a big table based on the various primary index columns than the one from the base table.

Syntax

Folloearng is the syntax of a JOIN INDEX.

CREATE JOIN INDEX <index name> 
AS 
<SELECT Query> 
<Index Definition>;

Example

Conpartr the folloearng Employee and Salary tables.

CREATE SET TABLE EMPLOYEE,FALLBACK ( 
   EmployeeNo INTEGER, 
   FirstName VARCHAR(30) , 
   LastName VARCHAR(30) , 
   DOB DATE FORMAT 'YYYY-MM-DD', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD', 
   DepartmentNo BYTEINT 
) 
UNIQUE PRIMARY INDEX ( EmployeeNo );

CREATE SET TABLE SALARY,FALLBACK ( 
   EmployeeNo INTEGER, 
   Gross INTEGER, 
   Deduction INTEGER, 
   NetPay INTEGER 
) 
PRIMARY INDEX ( EmployeeNo ) 
UNIQUE INDEX (EmployeeNo);

Folloearng is an example thead wear produces a Join index named Employee_JI on Employee table.

CREATE JOIN INDEX Employee_JI 
AS 
SELECT EmployeeNo,FirstName,LastName, 
BirthDate,JoinedDate,DepartmentNo 
FROM Employee 
PRIMARY INDEX(FirstName);

If the user submit is a query with a WHERE clause on EmployeeNo, then the system will query the Employee table uperform the unique primary index. If the user queries the employee table uperform employee_name, then the system may access the sign up for index Employee_JI uperform employee_name. The rows of the sign up for index are hashed on employee_name column. If the sign up for index is not degoodd and the employee_name is not degoodd as 2ndary index, then the system will perform compallowe table scan to access the rows which is time consuming.

You can run the folloearng EXPLAIN plan and verify the optimizer plan. In the folloearng example you can see thead wear the optimizer is uperform the Join Index instead of base Employee table when the table queries uperform the Employee_Name column.

EXPLAIN SELECT * FROM EMPLOYEE WHERE FirstName='Mike'; 
*** Help information returned. 8 rows. 
*** Total elapsed time was 1 2nd. 
Explanation 
------------------------------------------------------------------------ 
   1) First, we do a performle-AMP RETRIEVE step from EMPLOYEE_JI simply by 
      way of the primary index "EMPLOYEE_JI.FirstName = 'Mike'" 
      with no residual conditions into Spool 1 (one-amp), which is built 
      locally on thead wear AMP.  The dimension of Spool 1 is estimated with low 
      confidence to end up being 2 rows (232 simply bytes).  The estimated time for this particular 
      step is 0.02 2nds.
   → The contents of Spool 1 are sent back to the user as the result of 
      statement 1.  The overalll estimated time is 0.02 2nds. 

Multi Table Join Index

A multi-table sign up for index is produced simply by sign up foring more than one table. Multi-table sign up for index can end up being used to store the result set of regularly sign up foreddish tables to improve the performance.

Example

The folloearng example produces a JOIN INDEX named Employee_Salary_JI simply by sign up foring Employee and Salary tables.

CREATE JOIN INDEX Employee_Salary_JI 
AS 
SELECT a.EmployeeNo,a.FirstName,a.LastName, 
a.BirthDate,a.JoinedDate,a.DepartmentNo,b.Gross,b.Deduction,b.NetPay 
FROM Employee a 
INNER JOIN Salary b 
ON(a.EmployeeNo = b.EmployeeNo) 
PRIMARY INDEX(FirstName);

Whenever the base tables Employee or Salary are updated, then the Join index Employee_Salary_JI is furthermore automatically updated. If you are running a query sign up foring Employee and Salary tables, then the optimizer may select to access the data from Employee_Salary_JI directly instead of sign up foring the tables. EXPLAIN plan on the query can end up being used to verify if the optimizer will select the base table or Join index.

Aggregate Join Index

If a table is consistently aggregated on specific columns, then aggregate sign up for index can end up being degoodd on the table to improve the performance. One limitation of aggregate sign up for index is thead wear it supslots only SUM and COUNT functions.

Example

In the folloearng example Employee and Salary is sign up foreddish to identify the overalll salary per Department.

CREATE JOIN INDEX Employee_Salary_JI 
AS 
SELECT a.DepartmentNo,SUM(b.NetPay) AS TotalPay 
FROM Employee a 
INNER JOIN Salary b 
ON(a.EmployeeNo = b.EmployeeNo)
GROUP BY a.DepartmentNo 
Primary Index(DepartmentNo); 

Teradata – Views

Views are database objects thead wear are built simply by the query. Views can end up being built uperform a performle table or multiple tables simply by way of sign up for. Their definition is storeddishdish coloureddish permanently in data book but they don't store copy of the data. Data for the watch is built dynamically.

A watch may contain a subset of rows of the table or a subset of columns of the table.

Create a View

Views are produced uperform CREATE VIEW statement.

Syntax

Folloearng is the syntax for creating a watch.

CREATE/REPLACE VIEW <watchname> 
AS  
<select query>; 

Example

Conpartr the folloearng Employee table.

EmployeeNo FirstName LastName BirthDate
101 Mike James 1/5/1980
104 Alex Stuart 11/6/1984
102 Roend up beingrt Williams 3/5/1983
105 Roend up beingrt James 12/1/1984
103 Peter Paul 4/1/1983

The folloearng example produces a watch on Employee table.

CREATE VIEW Employee_View 
AS 
SELECT 
EmployeeNo, 
FirstName, 
LastName, 
FROM  
Employee;

Uperform Views

You can use regular SELECT statement to retrieve data from Views.

Example

The folloearng example retrieves the records from Employee_View;

SELECT EmployeeNo, FirstName, LastName FROM Employee_View;

When the above query is executed, it produces the folloearng awayput.

*** Query compenableed. 5 rows found. 3 columns returned. 
*** Total elapsed time was 1 2nd.  
 EmployeeNo            FirstName                       LastName 
-----------  ------------------------------  --------------------------- 
    101                  Mike                           James 
    104                  Alex                           Stuart 
    102                  Roend up beingrt                         Williams 
    105                  Roend up beingrt                         James 
    103                  Peter                          Paul 

Modifying Views

An existing watch can end up being modified uperform REPLACE VIEW statement.

Folloearng is the syntax to modify a watch.

REPLACE VIEW <watchname> 
AS  
<select query>;

Example

The folloearng example modifies the watch Employee_View for adding additional columns.

REPLACE VIEW Employee_View 
AS 
SELECT 
EmployeeNo, 
FirstName, 
BirthDate,
JoinedDate 
DepartmentNo 
FROM  
Employee; 

Drop View

An existing watch can end up being fallped uperform DROP VIEW statement.

Syntax

Folloearng is the syntax of DROP VIEW.

DROP VIEW <watchname>; 

Example

Folloearng is an example to fall the watch Employee_View.

DROP VIEW Employee_View; 

Advantages of Views

  • Views provide additional level of security simply by restringenting the rows or columns of a table.

  • Users can end up being given access only to watchs instead of base tables.

  • Simplifies the use of multiple tables simply by pre-sign up foring all of all of them uperform Views.

Teradata – Macros

Macro is a set of SQL statements which are storeddishdish coloureddish and executed simply by calling the Macro name. The definition of Macros is storeddishdish coloureddish in Data Dictionary. Users only need EXEC privilege to execute the Macro. Users don't need separate privileges on the database objects used inpart the Macro. Macro statements are executed as a performle transbehaveion. If one of the SQL statements in Macro fails, then all the statements are roldirected back. Macros can accept parameters. Macros can contain DDL statements, but thead wear need to end up being the final statement in Macro.

Create Macros

Macros are produced uperform CREATE MACRO statement.

Syntax

Folloearng is the generic syntax of CREATE MACRO command.

CREATE MACRO <macroname> [(parameter1, parameter2,...)] ( 
   <sql statements> 
);

Example

Conpartr the folloearng Employee table.

EmployeeNo FirstName LastName BirthDate
101 Mike James 1/5/1980
104 Alex Stuart 11/6/1984
102 Roend up beingrt Williams 3/5/1983
105 Roend up beingrt James 12/1/1984
103 Peter Paul 4/1/1983

The folloearng example produces a Macro caldirected Get_Emp. It contains a select statement to retrieve records from employee table.

CREATE MACRO Get_Emp AS ( 
   SELECT 
   EmployeeNo, 
   FirstName, 
   LastName 
   FROM  
   employee 
   ORDER BY EmployeeNo; 
);

Executing Macros

Macros are executed uperform EXEC command.

Syntax

Folloearng is the syntax of EXECUTE MACRO command.

EXEC <macroname>; 

Example

The folloearng example executes the Macro names Get_Emp; When the folloearng command is executed, it retrieves all records from employee table.

EXEC Get_Emp; 
*** Query compenableed. 5 rows found. 3 columns returned. 
*** Total elapsed time was 1 2nd.  
EmployeeNo             FirstName                      LastName 
-----------  ------------------------------  --------------------------- 
   101                  Mike                          James 
   102                  Roend up beingrt                        Williams 
   103                  Peter                         Paul 
   104                  Alex                          Stuart 
   105                  Roend up beingrt                        James 

Parameterized Macros

Teradata Macros can accept parameters. Within a Macro, these parameters are referenced with ; (semicolon).

Folloearng is an example of a Macro thead wear accepts parameters.

CREATE MACRO Get_Emp_Salary(EmployeeNo INTEGER) AS ( 
   SELECT 
   EmployeeNo, 
   NetPay 
   FROM  
   Salary 
   WHERE EmployeeNo = :EmployeeNo; 
);

Executing Parameterized Macros

Macros are executed uperform EXEC command. You need EXEC privilege to execute the Macros.

Syntax

Folloearng is the syntax of EXECUTE MACRO statement.

EXEC <macroname>(value);

Example

The folloearng example executes the Macro names Get_Emp; It accepts employee no as parameter and extrbehaves records from employee table for thead wear employee.

EXEC Get_Emp_Salary(101); 
*** Query compenableed. One row found. 2 columns returned. 
*** Total elapsed time was 1 2nd.
 
EmployeeNo      NetPay 
-----------  ------------ 
   101           36000 

Teradata – Storeddishdish coloureddish Procedure

A storeddishdish coloureddish procedure contains a set of SQL statements and procedural statements. They may contain only procedural statements. The definition of storeddishdish coloureddish procedure is storeddishdish coloureddish in database and the parameters are storeddishdish coloureddish in data book tables.

Advantages

  • Storeddishdish coloureddish procedures reddishdish coloureddishuce the ne2rk load end up beingtween the claynt and the server.

  • Provides end up beingtter security since the data is accessed through storeddishdish coloureddish procedures instead of accesperform all of all of them directly.

  • Gives end up beingtter maintenance since the business logic is checked and storeddishdish coloureddish in the server.

Creating Procedure

Storeddishdish coloureddish Procedures are produced uperform CREATE PROCEDURE statement.

Syntax

Folloearng is the generic syntax of the CREATE PROCEDURE statement.

CREATE PROCEDURE <procedurename> ( [parameter 1 data kind, parameter 2 data kind..] ) 
BEGIN 
   <SQL or SPL statements>; 
END;

Example

Conpartr the folloearng Salary Table.

EmployeeNo Gross Deduction NetPay
101 40,000 4,000 36,000
102 80,000 6,000 74,000
103 90,000 7,000 83,000
104 75,000 5,000 70,000

The folloearng example produces a storeddishdish coloureddish procedure named InsertSalary to accept the values and insert into Salary Table.

CREATE PROCEDURE InsertSalary ( 
   IN in_EmployeeNo INTEGER, IN in_Gross INTEGER, 
   IN in_Deduction INTEGER, IN in_NetPay INTEGER 
) 
BEGIN 
   INSERT INTO Salary ( 
      EmployeeNo, 
      Gross, 
      Deduction, 
      NetPay 
   ) 
   VALUES ( 
      :in_EmployeeNo, 
      :in_Gross, 
      :in_Deduction, 
      :in_NetPay 
   ); 
END;

Executing Procedures

Storeddishdish coloureddish Procedures are executed uperform CALL statement.

Syntax

Folloearng is the generic syntax of the CALL statement.

CALL <procedure name> [(parameter values)];

Example

The folloearng example calls the storeddishdish coloureddish procedure InsertSalary and inserts records to Salary Table.

CALL InsertSalary(105,20000,2000,18000);

Once the above query is executed, it produces the folloearng awayput and you can see the inserted row in Salary table.

EmployeeNo Gross Deduction NetPay
101 40,000 4,000 36,000
102 80,000 6,000 74,000
103 90,000 7,000 83,000
104 75,000 5,000 70,000
105 20,000 2,000 18,000

Teradata – JOIN strategies

This chapter talk aboutes the various JOIN strategies available in Teradata.

Join Methods

Teradata uses various sign up for methods to perform sign up for operations. Some of the commonly used Join methods are −

  • Merge Join
  • Nested Join
  • Product Join

Merge Join

Merge Join method gets place when the sign up for is based on the equality condition. Merge Join requires the sign up foring rows to end up being on the same AMP. Rows are sign up foreddish based on their particular row hash. Merge Join uses various sign up for strategies to provide the rows to the same AMP.

Strategy #1

If the sign up for columns are the primary indexes of the corresponding tables, then the sign up foring rows are already on the same AMP. In this particular case, no distribution is requireddishdish coloureddish.

Conpartr the folloearng Employee and Salary Tables.

CREATE SET TABLE EMPLOYEE,FALLBACK ( 
   EmployeeNo INTEGER, 
   FirstName VARCHAR(30) , 
   LastName VARCHAR(30) , 
   DOB DATE FORMAT 'YYYY-MM-DD', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD', 
   DepartmentNo BYTEINT 
) 
UNIQUE PRIMARY INDEX ( EmployeeNo );
CREATE SET TABLE Salary ( 
   EmployeeNo INTEGER, 
   Gross INTEGER,  
   Deduction INTEGER, 
   NetPay INTEGER 
) 
UNIQUE PRIMARY INDEX(EmployeeNo); 

When these 2 tables are sign up foreddish on EmployeeNo column, then no reddishdish coloureddishistribution gets place since EmployeeNo is the primary index of both the tables which are end up beinging sign up foreddish.

Strategy #2

Conpartr the folloearng Employee and Department tables.

CREATE SET TABLE EMPLOYEE,FALLBACK (
   EmployeeNo INTEGER, 
   FirstName VARCHAR(30) , 
   LastName VARCHAR(30) , 
   DOB DATE FORMAT 'YYYY-MM-DD', 
   JoinedDate DATE FORMAT 'YYYY-MM-DD', 
   DepartmentNo BYTEINT 
) 
UNIQUE PRIMARY INDEX ( EmployeeNo );
CREATE SET TABLE DEPARTMENT,FALLBACK ( 
   DepartmentNo BYTEINT, 
   DepartmentName CHAR(15) 
) 
UNIQUE PRIMARY INDEX ( DepartmentNo );

If these 2 tables are sign up foreddish on DepequipentNo column, then the rows need to end up being reddishdish coloureddishistributed since DepartmentNo is a primary index in one table and non-primary index in one more table. In this particular scenario, sign up foring rows may not end up being on the same AMP. In such case, Teradata may reddishdish coloureddishistribute employee table on DepartmentNo column.

Strategy #3

For the above Employee and Department tables, Teradata may duplicate the Department table on all AMPs, if the dimension of Department table is small.

Nested Join

Nested Join doesn’t use all AMPs. For the Nested Join to get place, one of the condition need to end up being equality on the unique primary index of one table and then sign up foring this particular column to any index on the other table.

In this particular scenario, the system will fetch the one row uperform Unique Primary index of one table and use thead wear row hash to fetch the fiting records from other table. Nested sign up for is the many efficient of all Join methods.

Product Join

Product Join compares every qualifying row from one table with every qualifying row from other table. Product sign up for may get place because of to some of the folloearng fbehaveors −

  • Where condition is misperform.
  • Join condition is not based on equality condition.
  • Table aliases is not appropriate.
  • Multiple sign up for conditions.

Teradata – Partitioned Primary Index

Partitioned Primary Index (PPI) is an indexing mechanism thead wear is helpful in improving the performance of specific queries. When rows are inserted into a table, they are storeddishdish coloureddish in an AMP and arranged simply by their particular row hash order. When a table is degoodd with PPI, the rows are sorted simply by their particular partition numend up beingr. Within every partition, they are arranged simply by their particular row hash. Rows are assigned to a partition based on the partition expression degoodd.

Advantages

  • Avoid compallowe table scan for specific queries.

  • Avoid uperform 2ndary index thead wear requires additional physical structure and additional I/O maintenance.

  • Access a subset of a big table fastly.

  • Drop the old data fastly and add brand brand new data.

Example

Conpartr the folloearng Orders table with Primary Index on OrderNo.

StoreNo OrderNo OrderDate OrderTotal
101 7501 2015-10-01 900
101 7502 2015-10-02 1,200
102 7503 2015-10-02 3,000
102 7504 2015-10-03 2,454
101 7505 2015-10-03 1201
103 7506 2015-10-04 2,454
101 7507 2015-10-05 1201
101 7508 2015-10-05 1201

Assume thead wear the records are distributed end up beingtween AMPs as shown in the folloearng tables. Recorded are storeddishdish coloureddish in AMPs, sorted based on their particular row hash.

AMP 1

RowHash OrderNo OrderDate
1 7505 2015-10-03
2 7504 2015-10-03
3 7501 2015-10-01
4 7508 2015-10-05

AMP 2

RowHash OrderNo OrderDate
1 7507 2015-10-05
2 7502 2015-10-02
3 7506 2015-10-04
4 7503 2015-10-02

If you run a query to extrbehave the orders for a particular date, then the optimizer may select to use Full Table Scan, then all the records within the AMP may end up being accessed. To avoid this particular, you can degood the order date as Partitioned Primary Index. When rows are inserted into orders table, they are partitioned simply by the order date. Within every partition they will end up being ordereddishdish coloureddish simply by their particular row hash.

The folloearng data shows how the records will end up being storeddishdish coloureddish in AMPs, if they are partitioned simply by Order Date. If a query is run to access the records simply by Order Date, then only the partition thead wear contains the records for thead wear particular order will end up being accessed.

AMP 1

Partition RowHash OrderNo OrderDate
0 3 7501 2015-10-01
1 1 7505 2015-10-03
1 2 7504 2015-10-03
2 4 7508 2015-10-05

AMP 2

Partition RowHash OrderNo OrderDate
0 2 7502 2015-10-02
0 4 7503 2015-10-02
1 3 7506 2015-10-04
2 1 7507 2015-10-05

Folloearng is an example to produce a table with partition primary Index. PARTITION BY clause is used to degood the partition.

CREATE SET TABLE Orders (
   StoreNo SMALLINT, 
   OrderNo INTEGER, 
   OrderDate DATE FORMAT 'YYYY-MM-DD', 
   OrderTotal INTEGER 
) 
PRIMARY INDEX(OrderNo) 
PARTITION BY RANGE_N  (
   OrderDate BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' DAY
);

In the above example, the table is partitioned simply by OrderDate column. There will end up being one separate partition for every day.

Teradata – OLAP Functions

OLAP functions are similar to aggregate functions other than thead wear the aggregate functions will return only one value whereas the OLAP function will provide the individual rows in addition to the aggregates.

Syntax

Folloearng is the general syntax of the OLAP function.

<aggregate function> OVER  
([PARTITION BY] [ORDER BY columnname][ROWS BETWEEN 
UNBOUDED PRECEDING AND UNBOUNDED FOLLWOING) 

Aggregation functions can end up being SUM, COUNT, MAX,MIN, AVG.

Example

Conpartr the folloearng Salary table.

EmployeeNo Gross Deduction NetPay
101 40,000 4,000 36,000
102 80,000 6,000 74,000
103 90,000 7,000 83,000
104 75,000 5,000 70,000

Folloearng is an example to find the cumulative sum or running overalll of NetPay on Salary table. Records are sorted simply by EmployeeNo and cumulative sum is calculated on NetPay column.

SELECT  
EmployeeNo, NetPay, 
SUM(Netpay) OVER(ORDER BY EmployeeNo ROWS  
UNBOUNDED PRECEDING) as TotalSalary 
FROM Salary;

When the above query is executed, it produces the folloearng awayput.

EmployeeNo     NetPay     TotalSalary 
-----------  -----------  ----------- 
   101         36000        36000 
   102         74000        110000 
   103         83000        193000 
   104         70000        263000 
   105         18000        281000 

RANK

RANK function orders the records based on the column provided. RANK function can furthermore filter the numend up beingr of records returned based on the rank.

Syntax

Folloearng is the generic syntax to use the RANK function.

RANK() OVER 
([PARTITION BY columnnlist] [ORDER BY columnlist][DESC|ASC])

Example

Conpartr the folloearng Employee table.

EmployeeNo FirstName LastName JoinedDate DepartmentID BirthDate
101 Mike James 3/27/2005 1 1/5/1980
102 Roend up beingrt Williams 4/25/2007 2 3/5/1983
103 Peter Paul 3/21/2007 2 4/1/1983
104 Alex Stuart 2/1/2008 2 11/6/1984
105 Roend up beingrt James 1/4/2008 3 12/1/1984

Folloearng query orders the records of the employee table simply by Joined Date and assigns the ranruler on Joined Date.

SELECT EmployeeNo, JoinedDate,RANK() 
OVER(ORDER BY JoinedDate) as Seniority 
FROM Employee;

When the above query is executed, it produces the folloearng awayput.

EmployeeNo   JoinedDate   Seniority 
-----------  ----------  ----------- 
   101       2005-03-27       1 
   103       2007-03-21       2 
   102       2007-04-25       3 
   105       2008-01-04       4 
   104       2008-02-01       5 

PARTITION BY clause groups the data simply by the columns degoodd in the PARTITION BY clause and performs the OLAP function within every group. Folloearng is an example of the query thead wear uses PARTITION BY clause.

SELECT EmployeeNo, JoinedDate,RANK() 
OVER(PARTITION BY DepequipentNo ORDER BY JoinedDate) as Seniority 
FROM Employee;

When the above query is executed, it produces the folloearng awayput. You can see thead wear the Rank is reset for every Department.

EmployeeNo  DepartmentNo  JoinedDate   Seniority 
-----------  ------------  ----------  ----------- 

    101           1        2005-03-27       1 
    103           2        2007-03-21       1 
    102           2        2007-04-25       2 
    104           2        2008-02-01       3 
    105           3        2008-01-04       1 

Teradata – Data Protection

This chapter talk aboutes the features available for data protection in Teradata.

Transient Journal

Teradata uses Transient Journal to protect data from transbehaveion failures. Whenever any transbehaveions are run, Transient journal keeps a copy of the end up beingfore images of the affected rows until the transbehaveion is successful or roldirected back successcompallowey. Then, the end up beingfore images are discarded. Transient journal is kept in every AMPs. It is an automatic process and cannot end up being disabdirected.

Fallback

Fallback protects the table data simply by storing the 2nd copy of rows of a table on one more AMP caldirected as Fallback AMP. If one AMP fails, then the fallback rows are accessed. With this particular, behaveually if one AMP fails, data is still available through fallback AMP. Fallback option can end up being used at table creation or after table creation. Fallback ensures thead wear the 2nd copy of the rows of the table is always storeddishdish coloureddish in one more AMP to protect the data from AMP failure. However, fallback occupies twice the storage and I/O for Insert/Deenablee/Update.

Folloearng diagram shows how fallback copy of the rows are storeddishdish coloureddish in one more AMP.

Fallback

Down AMP Recovery Journal

The Down AMP recovery journal is behaveivated when the AMP fails and the table is fallback protected. This journal keeps track of all the alters to the data of the faidirected AMP. The journal is behaveivated on the remaining AMPs in the cluster. It is an automatic process and cannot end up being disabdirected. Once the faidirected AMP is repart then the data from the Down AMP recovery journal is synchronized with the AMP. Once this particular is done, the journal is discarded.

Down AMP Recovery Journal

Cliques

Clique is a mechanism used simply by Teradata to protect data from Node failures. A clique is absolutely nothing but a set of Teradata nodes thead wear share a common set of Disk Arrays. When a node fails, then the vprocs from the faidirected node will migrate to other nodes in the clique and continue to access their particular disk arrays.

Hot Standsimply by Node

Hot Standsimply by Node is a node thead wear does not participate in the itemion environment. If a node fails then the vprocs from the faidirected nodes will migrate to the hot standsimply by node. Once the faidirected node is recovereddishdish coloureddish it end up beingcomes the hot standsimply by node. Hot Standsimply by nodes are used to maintain the performance in case of node failures.

RAID

Redundant Array of Independent Disks (RAID) is a mechanism used to protect data from Disk Failures. Disk Array consists of a set of disks which are grouped as a logical device. This device may look like a performle device to the user but they may end up being spread acombination lots of disks.

RAID 1 is commonly used in Teradata. In RAID 1, every disk is associated with a mirror disk. Any alters to the data in primary disk is reflected in mirror copy furthermore. If the primary disk fails, then the data from mirror disk can end up being accessed.

RAID

Teradata – User Management

This chapter talk abouted the various strategies of user management in Teradata.

Users

A user is produced uperform CREATE USER command. In Teradata, a user is furthermore similar to a database. They both can end up being assigned space and contain database objects other than thead wear the user is assigned a overalllword.

Syntax

Folloearng is the syntax for CREATE USER.

CREATE USER username 
AS  
[PERMANENT|PERM] = n BYTES 
PASSWORD = overalllword 
TEMPORARY = n BYTES 
SPOOL = n BYTES;

While creating a user, the values for user name, Permanent space and Password is mandatory. Other fields are optional.

Example

Folloearng is an example to produce the user TD01.

CREATE USER TD01 
AS  
PERMANENT = 1000000 BYTES 
PASSWORD = ABC$124 
TEMPORARY = 1000000 BYTES 
SPOOL = 1000000 BYTES;

Accounts

While creating a brand brand new user, the user may end up being assigned to an account. ACCOUNT option in CREATE USER is used to assign the account. A user may end up being assigned to multiple accounts.

Syntax

Folloearng is the syntax for CREATE USER with account option.

CREATE USER username 
PERM = n BYTES 
PASSWORD = overalllword 
ACCOUNT = accountid

Example

The folloearng example produces the user TD02 and assigns the account as IT and Admin.

CREATE USER TD02 
AS  
PERMANENT = 1000000 BYTES 
PASSWORD = abc$123 
TEMPORARY = 1000000 BYTES 
SPOOL = 1000000 BYTES 
ACCOUNT = (‘IT’,’Admin’);

The user can specify the account id while logging into Teradata system or after end up beinging logged into the system uperform SET SESSION command.

.LOGON username, overalllowrd,accountid 
OR 
SET SESSION ACCOUNT = accountid 

Grant Privileges

GRANT command is used to assign one or more privileges on the database objects to the user or database.

Syntax

Folloearng is the syntax of the GRANT command.

GRANT privileges ON objectname TO username;

Privileges can end up being INSERT, SELECT, UPDATE, REFERENCES.

Example

Folloearng is an example of GRANT statement.

GRANT SELECT,INSERT,UPDATE ON Employee TO TD01;

Revoke Privileges

REVOKE command removes the privileges from the users or databases. The REVOKE command can only remove explicit privileges.

Syntax

Folloearng is the fundamental syntax for REVOKE command.

REVOKE [ALL|privileges] ON objectname FROM username;

Example

Folloearng is an example of REVOKE command.

REVOKE INSERT,SELECT ON Employee FROM TD01;

Teradata – Performance Tuning

This chapter talk aboutes the procedure of performance tuning in Teradata.

Exfundamental

The very very first step in performance tuning is the use of EXPLAIN on your own query. EXPLAIN plan gives the details of how optimizer will execute your own query. In the Exfundamental plan, check for the keywords like confidence level, sign up for strategy used, spool file dimension, reddishdish coloureddishistribution, etc.

Collect Statistics

Optimizer uses Data demographics to come up with effective execution strategy. COLLECT STATISTICS command is used to collect data demographics of the table. Make sure thead wear the statistics collected on the columns are up to date.

  • Collect statistics on the columns thead wear are used in WHERE clause and on the columns used in the sign up foring condition.

  • Collect statistics on the Unique Primary Index columns.

  • Collect statistics on Non Unique Secondary Index columns. Optimizer will select if it can use NUSI or Full Table Scan.

  • Collect statistics on the Join Index though the statistics on base table is collected.

  • Collect statistics on the partitioning columns.

Data Types

Make sure thead wear proper data kinds are used. This will avoid the use of excessive storage than requireddishdish coloureddish.

Conversion

Make sure thead wear the data kinds of the columns used in sign up for condition are compatible to avoid explicit data conversions.

Sort

Remove unrequireddish ORDER BY clauses unless requireddishdish coloureddish.

Spool Space Issue

Spool space error is generated if the query exceeds per AMP spool space limit for thead wear user. Verify the exfundamental plan and identify the step thead wear consumes more spool space. These intermediate queries can end up being split and put as separately to produce temporary tables.

Primary Index

Make sure thead wear the Primary Index is appropriately degoodd for the table. The primary index column need to behaveuallyly distribute the data and need to end up being regularly used to access the data.

SET Table

If you degood a SET table, then the optimizer will check if the record is duplicate for every and every record inserted. To remove the duplicate check condition, you can degood Unique Secondary Index for the table.

UPDATE on Large Table

Updating the big table will end up being time consuming. Instead of updating the table, you can deenablee the records and insert the records with modified rows.

Dropping Temporary Tables

Drop the temporary tables (staging tables) and volatiles if they are no lengthyer needed. This will free up permanent space and spool space.

MULTISET Table

If you are sure thead wear the input records will not have duplicate records, then you can degood the target table as MULTISET table to avoid the duplicate row check used simply by SET table.

Teradata – FastLoad

FastLoad utility is used to load data into empty tables. Since it does not use transient journals, data can end up being loaded fastly. It doesn't load duplicate rows behaveually if the target table is a MULTISET table.

Limitation

Target table need to not have 2ndary index, sign up for index and foreign key reference.

How FastLoad Works

FastLoad is executed in 2 phases.

Phase 1

  • The Parperform engines read the records from the input file and sends a block to every AMP.

  • Each AMP stores the blocks of records.

  • Then AMPs hash every record and reddishdish coloureddishistribute all of all of them to the appropriate AMP.

  • At the end of Phase 1, every AMP has it is rows but they are not in row hash sequence.

Phase 2

  • Phase 2 starts when FastLoad receives the END LOADING statement.

  • Each AMP sorts the records on row hash and writes all of all of them to the disk.

  • Locks on the target table is relrelayved and the error tables are fallped.

Example

Create a text file with the folloearng records and name the file as employee.txt.

101,Mike,James,1980-01-05,2010-03-01,1  
102,Roend up beingrt,Williams,1983-03-05,2010-09-01,1 
103,Peter,Paul,1983-04-01,2009-02-12,2 
104,Alex,Stuart,1984-11-06,2014-01-01,2 
105,Roend up beingrt,James,1984-12-01,2015-03-09,3

Folloearng is a sample FastLoad script to load the above file into Employee_Stg table.

LOGON 192.168.1.102/dbc,dbc;  
   DATABASE tduser;  
   BEGIN LOADING tduser.Employee_Stg  
      ERRORFILES Employee_ET, Employee_UV  
      CHECKPOINT 10;  
      SET RECORD VARTEXT ",";  
      DEFINE in_EmployeeNo (VARCHAR(10)), 
         in_FirstName (VARCHAR(30)), 
         in_LastName (VARCHAR(30)), 
         in_BirthDate (VARCHAR(10)), 
         in_JoinedDate (VARCHAR(10)), 
         in_DepartmentNo (VARCHAR(02)), 
         FILE = employee.txt;
      INSERT INTO Employee_Stg (
         EmployeeNo,
         FirstName,
         LastName,
         BirthDate,
         JoinedDate, 
         DepartmentNo
      ) 
      VALUES (  
         :in_EmployeeNo, 
         :in_FirstName, 
         :in_LastName, 
         :in_BirthDate (FORMAT 'YYYY-MM-DD'), 
         :in_JoinedDate (FORMAT 'YYYY-MM-DD'),
         :in_DepartmentNo
      ); 
   END LOADING;  
LOGOFF;

Executing a FastLoad Script

Once the input file employee.txt is produced and the FastLoad script is named as EmployeeLoad.fl, you can run the FastLoad script uperform the folloearng command in UNIX and Windows.

FastLoad < EmployeeLoad.fl;

Once the above command is executed, the FastLoad script will run and produce the log. In the log, you can see the numend up beingr of records processed simply by FastLoad and status code.

**** 03:19:14 END LOADING COMPLETE 
   Total Records Read              =  5 
   Total Error Table 1             =  0  ---- Table has end up beingen fallped 
   Total Error Table 2             =  0  ---- Table has end up beingen fallped 
   Total Inserts Applayd           =  5 
   Total Duplicate Rows            =  0 
   Start:   Fri Jan  8 03:19:13 2016 
   End  :   Fri Jan  8 03:19:14 2016 
**** 03:19:14 Application Phase statistics: 
              Elapsed time: 00:00:01 (in hh:mm:ss) 
0008  LOGOFF; 
**** 03:19:15 Logging away from all sessions

FastLoad Terms

Folloearng is the list of common terms used in FastLoad script.

  • LOGON − Logs into Teradata and initiates one or more sessions.

  • DATABASE − Sets the default database.

  • BEGIN LOADING − Identifies the table to end up being loaded.

  • ERRORFILES − Identifies the 2 error tables thead wear needs to end up being produced/updated.

  • CHECKPOINT − Degoods when to get checkstage.

  • SET RECORD − Specifies if the input file format is formatted, binary, text or unformatted.

  • DEFINE − Degoods the input file layaway.

  • FILE − Specifies the input file name and route.

  • INSERT − Inserts the records from the input file into the target table.

  • END LOADING − Initiates phase 2 of the FastLoad. Distributes the records into the target table.

  • LOGOFF − Ends all sessions and terminates FastLoad.

Teradata – MultiLoad

MultiLoad can load multiple tables at a time and it can furthermore perform various kinds of tasks such as INSERT, DELETE, UPDATE and UPSERT. It can load up to 5 tables at a time and perform up to 20 DML operations in a script. The target table is not requireddishdish coloureddish for MultiLoad.

MultiLoad supslots 2 modes −

  • IMPORT
  • DELETE

MultiLoad requires a work table, a log table and 2 error tables in addition to the target table.

  • Log Table − Used to maintain the checkstages getn during load which will end up being used for restart.

  • Error Tables − These tables are inserted during load when an error occurs. First error table stores conversion errors whereas 2nd error table stores duplicate records.

  • Log Table − Maintains the results from every phase of MultiLoad for restart purpose.

  • Work table − MultiLoad script produces one work table per target table. Work table is used to keep DML tasks and the input data.

Limitation

MultiLoad has some limitations.

  • Unique Secondary Index not supsloted on target table.
  • Referential integrity not supsloted.
  • Triggers not supsloted.

How MultiLoad Works

MultiLoad imslot has five phases −

  • Phase 1 − Preliminary Phase – Performs fundamental setup behaveivilinks.

  • Phase 2 − DML Transbehaveion Phase – Verifies the syntax of DML statements and provides all of all of them to Teradata system.

  • Phase 3 − Acquisition Phase – Brings the input data into work tables and locks the table.

  • Phase 4 − Application Phase – Applays all DML operations.

  • Phase 5 − Cleanup Phase – Relrelayves the table lock.

The steps involved in a MultiLoad script are −

  • Step 1 − Set up the log table.

  • Step 2 − Log on to Teradata.

  • Step 3 − Specify the Target, Work and Error tables.

  • Step 4 − Degood INPUT file layaway.

  • Step 5 − Degood the DML queries.

  • Step 6 − Name the IMPORT file.

  • Step 7 − Specify the LAYOUT to end up being used.

  • Step 8 − Initiate the Load.

  • Step 9 − Finish the load and terminate the sessions.

Example

Create a text file with the folloearng records and name the file as employee.txt.

101,Mike,James,1980-01-05,2010-03-01,1  
102,Roend up beingrt,Williams,1983-03-05,2010-09-01,1 
103,Peter,Paul,1983-04-01,2009-02-12,2 
104,Alex,Stuart,1984-11-06,2014-01-01,2 
105,Roend up beingrt,James,1984-12-01,2015-03-09,3 

The folloearng example is a MultiLoad script thead wear reads records from employee table and loads into Employee_Stg table.

.LOGTABLE tduser.Employee_log;  
.LOGON 192.168.1.102/dbc,dbc; 
   .BEGIN MLOAD TABLES Employee_Stg;  
      .LAYOUT Employee;  
      .FIELD in_EmployeeNo * VARCHAR(10);  
      .FIELD in_FirstName * VARCHAR(30); 
      .FIELD in_LastName * VARCHAR(30);  
      .FIELD in_BirthDate * VARCHAR(10); 
      .FIELD in_JoinedDate * VARCHAR(10);  
      .FIELD in_DepartmentNo * VARCHAR(02);

      .DML LABEL EmpLaend up beingl; 
      INSERT INTO Employee_Stg (
         EmployeeNo,
         FirstName,
         LastName,
         BirthDate,
         JoinedDate,
         DepartmentNo
      )  
      VALUES (
         :in_EmployeeNo,
         :in_FirstName,
         :in_Lastname,
         :in_BirthDate,
         :in_JoinedDate,
         :in_DepartmentNo
      );
      .IMPORT INFILE employee.txt  
      FORMAT VARTEXT ','
      LAYOUT Employee
      APPLY EmpLaend up beingl;  
   .END MLOAD;  
LOGOFF;

Executing a MultiLoad Script

Once the input file employee.txt is produced and the multiload script is named as EmployeeLoad.ml, then you can run the Multiload script uperform the folloearng command in UNIX and Windows.

Multiload < EmployeeLoad.ml;

Teradata – FastExslot

FastExslot utility is used to exslot data from Teradata tables into flat files. It can furthermore generate the data in reslot format. Data can end up being extrbehaveed from one or more tables uperform Join. Since FastExslot exslots the data in 64K blocks, it is helpful for extrbehaveing big volume of data.

Example

Conpartr the folloearng Employee table.

EmployeeNo FirstName LastName BirthDate
101 Mike James 1/5/1980
104 Alex Stuart 11/6/1984
102 Roend up beingrt Williams 3/5/1983
105 Roend up beingrt James 12/1/1984
103 Peter Paul 4/1/1983

Folloearng is an example of a FastExslot script. It exslots data from employee table and writes into a file employeedata.txt.

.LOGTABLE tduser.employee_log;  
.LOGON 192.168.1.102/dbc,dbc;  
   DATABASE tduser;  
   .BEGIN EXPORT SESSIONS 2;  
      .EXPORT OUTFILE employeedata.txt  
      MODE RECORD FORMAT TEXT;
      SELECT CAST(EmployeeNo AS CHAR(10)), 
         CAST(FirstName AS CHAR(15)), 
         CAST(LastName AS CHAR(15)), 
         CAST(BirthDate AS CHAR(10))   
      FROM
      Employee;
   .END EXPORT;
.LOGOFF;

Executing a FastExslot Script

Once the script is written and named as employee.fx, you can use the folloearng command to execute the script.

fexp < employee.fx

After executing the above command, you will receive the folloearng awayput in the file employeedata.txt.

103       Peter          Paul           1983-04-01 
101       Mike           James          1980-01-05 
102       Roend up beingrt         Williams       1983-03-05 
105       Roend up beingrt         James          1984-12-01 
104       Alex           Stuart         1984-11-06

FastExslot Terms

Folloearng is the list of terms commonly used in FastExslot script.

  • LOGTABLE − Specifies the log table for restart purpose.

  • LOGON − Logs into Teradata and initiates one or more sessions.

  • DATABASE − Sets the default database.

  • BEGIN EXPORT − Indicates the end up beingginning of the exslot.

  • EXPORT − Specifies the target file and the exslot format.

  • SELECT − Specifies the select query to exslot data.

  • END EXPORT − Specifies the end of FastExslot.

  • LOGOFF − Ends all sessions and terminates FastExslot.

Teradata – BTEQ

BTEQ utility is a powerful utility in Teradata thead wear can end up being used in both batch and interbehaveive mode. It can end up being used to run any DDL statement, DML statement, produce Macros and storeddishdish coloureddish procedures. BTEQ can end up being used to imslot data into Teradata tables from flat file and it can furthermore end up being used to extrbehave data from tables into files or reslots.

BTEQ Terms

Folloearng is the list of terms commonly used in BTEQ scripts.

  • LOGON − Used to log into Teradata system.

  • ACTIVITYCOUNT − Returns the numend up beingr of rows affected simply by the previous query.

  • ERRORCODE − Returns the status code of the previous query.

  • DATABASE − Sets the default database.

  • LABEL − Assigns a laend up beingl to a set of SQL commands.

  • RUN FILE − Executes the query contained in a file.

  • GOTO − Transfers manage to a laend up beingl.

  • LOGOFF − Logs away from from database and terminates all sessions.

  • IMPORT − Specifies the input file route.

  • EXPORT − Specifies the awayput file route and initiates the exslot.

Example

Folloearng is a sample BTEQ script.

.LOGON 192.168.1.102/dbc,dbc; 
   DATABASE tduser;

   CREATE TABLE employee_bkup ( 
      EmployeeNo INTEGER, 
      FirstName CHAR(30), 
      LastName CHAR(30), 
      DepartmentNo SMALLINT, 
      NetPay INTEGER 
   )
   Unique Primary Index(EmployeeNo);

   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE;
  
   SELECT * FROM  
   Employee 
   Sample 1; 
   .IF ACTIVITYCOUNT <> 0 THEN .GOTO InsertEmployee;  

   DROP TABLE employee_bkup;
  
   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; 
 
   .LABEL InsertEmployee 
   INSERT INTO employee_bkup 
   SELECT a.EmployeeNo, 
      a.FirstName, 
      a.LastName, 
      a.DepartmentNo, 
      b.NetPay 
   FROM  
   Employee a INNER JOIN Salary b 
   ON (a.EmployeeNo = b.EmployeeNo);  

   .IF ERRORCODE <> 0 THEN .EXIT ERRORCODE; 
.LOGOFF; 

The above script performs the folloearng tasks.

  • Logs into Teradata System.

  • Sets the Default Database.

  • Creates a table caldirected employee_bkup.

  • Selects one record from Employee table to check if the table has any records.

  • Drops employee_bkup table, if the table is empty.

  • Transfers the manage to a Laend up beingl InsertEmployee which inserts records into employee_bkup table

  • Checks ERRORCODE to produce sure thead wear the statement is successful, folloearng every SQL statement.

  • ACTIVITYCOUNT returns numend up beingr of records selected/impbehaveed simply by the previous SQL query.

SHARE
Previous articlePouchDB
Next articleCRM

NO COMMENTS

LEAVE A REPLY