Teradata Wallet

So what's so curious about TD Wallet. How many of you have come across Prod scripts where credentials are quite revealing?. Minimal I hope so, none other than Admin and batch team guys sneak peek script files - its rarity.

So what's big deal with that, any how prod support guys share their credentials among team. Why securing password in scripts required you might ask.. In case, your project/client has 3rd party or vendor enabled, might in position to secure your password as per client agreement. Hmm, considering more than one vendor in project may lead relaxation in MSA [Master Service Agreement] or in SLA not necessarily though..

I love to give out verbiage, so hitting the root.

TD Wallet:

what & how:

• Its an utility to secure your passwords, usually used in scripts
• It is stored in user login space
• TD Wallet basically uses Name & Values as like [Key, Value] pair as in Big Data - Hadoop
• eg: Name: prod_server_pass, Value: "thisIsMyPass"
• so instead of passing actual password "thisIsMyPass" - you key in $tdwallet(prod_server_pass)
• Download this utility from TTU 14.XX 
• Your wallet entries are encode as just Unicode characters.
• How long entries can be made, please check tdwallet help limits 

Things to know:

• Start this utility by tdwallet in your cmd prompt or in shell
• to use this 

• eg: .logon dbname/username,$tdwallet(passwd_reference_name)

• only CLIv2 connectivity is supported. Big NO to ODBC,.NET connectivity login
• UPDATE:  ODBC is supported now -- see article comments.
• You can download CLIv2 from TD support site. [Call Level Interface version 2]
• Invidual user can have own wallet. May not access other user wallet, how?
• 1 login id = 1 td wallet. What if more than one required?, well you can have more entries for credentials doesnt require add-on wallet to single login id.
• UPDATE: By "1 login id = 1 td wallet" you mean "1 system user id = 1 wallet". Not to be confused with database login id.
• when you login using your id, you cannot access other users wallet.
• if in case of batch team, hmm questionable on access, but hey still you cant access other than login id's td wallet pretty much it.

What not:

• After spending few hours glinting over few articles, it is understood that TD Wallet doesn't support following:
• TTU which refers/uses td wallet - all TTU needs to be version 14.XX 
• Doesn't support TD 13 level & no download of TD Wallet for 13.XX
• UPDATE: Wallet is client-side only, so you should be able to login to TD 13 using TTU 14. See the client-server support/compatibility matrix.
• Doesnt support UTF-16 sessions
• Update: There's a patch for UTF-16 support.

Update notes are as commented/posted by ANON . I wish to thank Anon by detailing out anomalies.

Reference: http://developer.teradata.com/tools/articles/introducing-teradata-wallet

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!.