how do see the difference in TD version 14?

Teradata is continuously evolving company by either through R&D or acquisition of BI companies. Recent one I came across was Aprimo. Also, this has been used in major semiconductor manufacture companies which runs TD DB.

Now coming to differences. In TD V 14, and now V15 released i could give you some insights in terms of improvements.

TD V14 can now go for 128 table joins, with max 2048 columns, Table header has 1 MB , max columns under an index is 64. Spool for a tb query is 2GB.

PPI's early had 65,535 and now in 14 it touches sky with its range; 9.2 * 10^18 uuuhhhff and partition levels upto 62. I added more info i have come across any such.

I promise to give you details on Aprimo not just a weblink :) in next post

Some tips on Basics!

Different types of Tables in Teradata:

Permenant Tables:
1. SET table - Strictly NO duplicate values [By-Default]
2. MULTISET table - Allows Duplicate Values.

Temporary Tables:
1. Global Temporary Table - supports [Compression in table],
when session logged out, Table persists and its record gets
vanished.
2. Volatile Table - When session logged out, TABLE and its
records are not available further.

Derived table: stores the derived result from a subquery.

It uses only spool space, so no table definition, permission needed.

simple query

select * from (select max(sal) from emp) DT (Max_sal)

answer:
derived table(DT) has Max salary and result would be

Max_sal- here subquery result is maintained in derived table
-------
25000

3. derived table auto drops after the query ends

Macros in Teradata

Basics related to Macros in Teradata.

A Macro: It is a collection of SQL queries preferably DML statments and it is stored as an object in DD(Data Dictionary). Unlike VIEWS (we will deal about Views in another post), a macro can have multiple sequels. It is known that a macro can handle many DML and it is restricted to only one DDL.

Reason: DDL statements like Create,Alter,Drop,Rename generally locks the DD for their processing, since macros transaction is an internal process it is highly dependent on DD. Therefore DDL can be last statement in a macro. - This is what provided as theoretical explanation .

Example:

CM AS // CM doesnt work with Queryman better try with BTEQ
or
CREATE MACRO MyMacroName AS
(
insert into (eno,name,ereqno) emp values (101,'Rajesh',43243259);
select * from emp;
//DDL stmt here (doesnt work with queryman)
)
Issue: There is an issue with macro that, DDL couldnt be even as last statement in a macro(with Queryman). I used Teradata 12 and I am getting Warning as Data Dictionary must be solitary. I will update after working with BTEQ.

Parameterized Macro:
//paramNum is passed as Parameter to Select statement.
//passed parameter shd be accessed using =:paramname

CREATE MACRO macro_name (paramNum integer) AS
{
DMLs here....
select ename,esal from emp where eno = :paramName ;
};

REPLACE Macro:

If you require more DMLs to be added to an existing macro, you can use
REPLACE MACRO AS
{[insert];
[update];
[delete];
[select];);

To Execute Macro:

EXEC []
eg: exec myfirsmacro_name;
exec myfirsmacro_name (invalue=1,invalue='abc');

To Drop:
DROP MACRO macro-name;

NOTE: TERADATA sql is case-blind and so no need to worry about capitalization,etc
Comments highly appreciated.

Basics of Teradata Database

What exactly is a Teradata Database?

• Teradata Database is a RDMS (Relational Database Management System) which helps in driving the company’s Data warehouse.

• It provides foundation for the company to grow, compete, evolve business by getting answers to a new generation of questions (typically “what if” queries)

• It supports business growth from gigabytes to whooping hundreds of Terabytes

• Teradata Database is an Open system and compliant with major support from ANSI standards

• It works on UNIX MP-RAS, WINDOWS and LINUX operating systems, connects with other client through Network Attached channel (TCP/IP) and Channel connection for Mainframe.

Why Teradata Database?

•  Ability to keep up with rapid changes and ever growing demand from business (through Scalabilty)

•  It supports and maintains

     o Enterprise Data warehousing

     o Active Data warehousing

     o CRM

     o DataMarts

What makes Teradata Database unique?

1. Single Data store

* Doesn’t replicate data for different requests from varied clients

* Concurrent approach, avoids data replication for each requests

2. Scalability

*  Provides Linear Scalability, slope of 1.

*  If you add a Node (memory block), increases performance without delayed throughput

* Supports terabytes of data. (10power12)

3. Unconditional Parallelism

* Multiple processors work together to accomplish a task

4. Parallel aware Optimizer

* Aware of all components present in the system

* It determines the Least Expensive path (time-wise)

5. Ability to model the business

* Provides Single View of Business

* Focus is on the whole-Business Model and on individual model

-- We will focus on classical architecture of TeradataDB in next post.

Getting started with Teradata!

Major split-up for studying Teradata is as follows:

Basics	Utilities	SQL
Basics of Teradata Database Architecture	Fast Load	Basics Sequel
Data Protection Techniques	Multi Load	OLAP
Index Service	Bteq	and much more....
Space Management	Fast Export
Locks

Based on the split-up, I would share my knowledge on Teradata.

In the next post, I will introduce the Basics and Architecture of Teradata.

Cheers,
Rajesh
cherish the data, Teradata.

Know about Teradata

Hi all. I am writing this blog to share the basics of Teradata and I wish that it would be useful resource for all.

Wish you all best New Year ahead!.