Thursday, February 10, 2011

can you list some metrics used in Physical model/ELDM?

Join Access Frequency

Value Access Frequency

Change Rating

Distinct Rows

Max rows NULL, MAX Rows per Value

How do you choose Primary Index for a table in teradata?

A great Primary Index will have:

A Value Access frequency that is high
A Join Access frequency that is high
Reasonable distribution (i.e unique)
A change rating below 2

what is ELDM (Extended Logical Data Modeling) ?

ELDM is very important in terdata data modeling. It comes after Logical modeling
and before physical modeling of database.

1. ELDM is very important and needs extra care. otherwise wrong inputs will go Physical modeling
2.The ELDM will become our final input into the Physical Data Model.
3. In developing Any teradata Physical database, we go through
Logical Design
ELDM
Physical Design
4.The Logical Data Model will be the input to the Extended Logical Data Model.
The Extended Logical Data Model will be input to the Physical Data Model.

5.demographics of data is very important. which we get by questioning logical data modelers

6. Logical data Modeling:
The LDM is a logical representation of tables that reside in the data warehouse database
Once the tables are defined, related, and normalized, the Logical Data Model is complete. Teradata recommends third normal form tables. we can also limit to 2NF

The completed Logical Data Model is used as a basis for the development of the Extended Logical Data Model (EDLM).

7.The ELDM includes information regarding physical column access and data demographics for each table. This also serves as input to the implementation of the Physical Data Model. The ELDM contains demographic information about data distribution, sizing, and access.

The two major components of an ELDM are the following:

COLUMN ACCESS in the WHERE Clause or JOIN CLAUSE.

DATA DEMOGRAPHICS.

8.Physical Database Design portion where we choose our Primary Indexes and Secondary Indexes.

Wednesday, February 9, 2011

when you may get 10065 WSA E host unreach?






This is mainly because of Firewall on your computer

and we may see this message
C:\Documents and Settings\suresh>pdestate -a
PDE state is RUN/STARTED.
DBS state is 7: System is operational without PEs - Sessions are not allowed

After turning off firewall, if you are still having issue then we may to
bring PE to ONLINE using MULTITOOL.
Once logon to Multitool you may see the following things:

Then we need to set PE state using Tools --> Vproc Manager

Then Vproc Manager opens. then select tab Set Vproc State.


Then set offline PE to online... Now we see





C:\Documents and Settings\suresh>pdestate -a
PDE state is RUN/STARTED.
DBS state is 4: Logons are enabled - Users are logged on

How do you compress tables in teradata?

While creating table we use compress keyword in CREATE TABLE
statement,

we can't compress the following fields

1. Primary Index column
2. Not NULL column

what is a Queue Table?

A queue table is a persistent database table with the properties of an asynchronous
first-in-first-out (FIFO) queue

The queue table is different from a standard base table in that a queue table always contains a user-defined Queue Insertion Timestamp (QITS) as the first column of the table.

Can you expland BYNET?

BYNET Banyan Network (high-speed interconnect)

what is EBCDIC and FIPS ?

EBCDIC Extended Binary Coded Decimal Interchange Code
FIPS Federal Information Processing Standards

what is TDGSS in teradata?

TDGSS : Teradata Generic Security Services

what is RSS in teradata?

Resource Sampling Subsystem (RSS) helps to collects resource usage
data in teradata

what is Normalization?

Normalization is the process of reducing a complex data structure into a simple, stable one. Generally this process involves removing redundant attributes, keys, and relationships from the conceptual data model.

How teradata is actively available?

Teradata “single system” availability through its support for
large cliques,
hot standby nodes,
and fallback

waht is TASM in Teradata?

TASM: Teradata Active System Management
Teradata ASM) is a portfolio of products that enables the real-time system management required for delivering Active Enterprise Intelligence

Teradata ASM assists the database administrator in analyzing and establishing workloads and resource allocation to meet business needs. Teradata ASM facilitates monitoring workload requests to ensure that resources are used efficiently and that dynamic workloads are prioritized automatically.

Teradata ASM also provides state-of-the-art techniques to visualize the current operational environment and to analyze long-term trends. Teradata ASM enables database administrators to set SLGs, to monitor adherence to them, and to take any necessary steps to reallocate resources to meet business objectives.

what teradata supports 6A's means?

Active Load
Active Access
Active Events
Active Workload Management
Active Enterprise Integration
Active Availability

what tools can be used for Active Load in teradata?

ETL Tools can use queue tables and triggers, and use FastLoad, MultiLoad and TPump utilities

iS It true that Teradata Warehouse provides both Strategic Intelligence and Operational Intelligence?

Yes.

Teradata Warehouse provides both Strategic Intelligence and Operational Intelligence.

1.supports strategic decision-making.
2.support front-line or operational decision-making

Difference between Strategic Queries and Tactical Queries?

Strategic Queries
Strategic queries represent business questions that are intended to draw strategic advantage from large stores of data.
Strategic queries are often complex queries, involving aggregations and joins across multiple tables in the database. They are sometimes long-running and tend not to have a strict service level expectation.
Strategic queries are sometimes ad hoc. They may require significant database resources to execute and they are often submitted from third-party tools.

Tactical Queries
Tactical queries are short, highly tuned queries that facilitate action-taking or decision making in a time-sensitive environment. They usually come with a clear service level expectation and consume a very small percentage of the overall system resources.
Tactical queries are usually repetitively executed and take advantage of techniques such as request (query plan) caching and session-pooling.

what are the main Characteristics of Active Data Warehouse?

An active data warehouse:
• Provides a single up-to-date view of the enterprise on one platform.
• Represents a logically consistent store of detailed data available for strategic, tactical, and event driven business decision making.
• Relies on timely updates to the critical data - as close to real time as needed.
• Supports short, tactical queries that return in seconds, alongside of traditional decision support.

what are the main Characteristics of data warehouse ?

The data in the data warehouse was:
• Subject-oriented
• Integrated
• Usually identified by a timestamp
• Nonvolatile, that is, nothing was added or removed

what is row?

A row is one instance of all the columns in a table

what is derived table?

A special type of temporary table is the derived table. You can specify a derived table in an SQL SELECT statement. A derived table is obtained from one or more other tables as the result of a subquery. The scope of a derived table is only visible to the level of the SELECT statement calling the subquery.

Using derived tables avoids having to use the CREATE and DROP TABLE statements for
storing retrieved information and assists in coding more sophisticated, complex queries.

what is Volatile table?

If you need a temporary table for a single use only, you can define a volatile table. The definition of a volatile table resides in memory but does not survive across a system restart.

Using volatile tables improves performance even more than using global temporary tables
because the system does not store the definitions of volatile tables in the Data Dictionary.
Privilege checking is not necessary because only the creator can access the volatile table.

what are global temporary tables in teradata?

Global temporary tables are tables that exist only for the duration of the SQL session in which they are used. The contents of these tables are private to the session, and the system automatically drops the table at the end of that session.

However, the system saves the global temporary table definition permanently in the Data Dictionary. The saved definition may be shared by multiple users and sessions with each session getting its own instance of the table.

IS Relational model dervied from set theory?

Yes
The relational model for database management is based on concepts derived from the
mathematical theory of sets.

what is cardinality and degree of relation?

The number of rows is the cardinality of the relation, and the number of columns is the degree

Does Teradata support ADW?

Yes . Teradata supports Active data warehousing

what is RASUI?

RASUI:

Reliability
Availability
Serviceability,
Usability,
Installability


Teradata address RASUI

Friday, February 4, 2011

what is QCD and QCF?

QCD: Query Capture Database

QCF: Query Capture Facility

Teradata VE reads the execution plan, which has been stored in the Query Capture Database (QCD), and converts the plan into a series of icons. To view an execution plan using Teradata VE, the execution plan information must first be captured into the QCD using the Query Capture Facility (QCF)

difference between INTEGERDATE and ANSIDATE?

INTEGERDATE: uses the YY/MM/DD format and
ANSIDATE uses the YYYY-MM-DD format for a date

when Mixing AND and OR operatos , OR executes first?

False........

When mixing AND with OR in the same WHERE clause, it is important to know that the AND is evaluated first.

write NOT EQUQL operator in teradata?

In where clause we can use non equal operators

<>

Teradata SQL is Case Sensitive. True / False?

False.....

How do you know version of your teradata?

we can use DBCINFO table information. its a system table stored
in DBC database

SELECT * FROM DBC.DBCINFO;

How Many Columns can be used in secondary Index creation ?

SI can consist of up to 16 columns until V2R6 and then up to 64 columns.

total time to complete SQL request is based in high performance AMP?

Nooooooooo

The time to process a single user request is always based on the slowest AMP.

More columns in PI(UPI/NUPI) means more performance?

there is a downside to using several columns for a PI. The PE needs every data value for each column as input to the hashing calculation to directly access a particular row. If a single column value is missing, a full table scan will result because the row hash cannot be recreated.

How Many Columns can be used in Primary Index creation (2)?

The Primary Index can consist of up to 16 different columns prior to V2R6 and 64 columns V2R6

how to know which database currently i am in?

Use command:

HELP SESSION or SELECT DATABASE

what is database command?

When user log on to the teradata system he is placed in a database ,
which is used while creating the user.

To access objects in this database , he can access by using name

If wants to access objects in other databases then he has
to prefix object name with database name.

Instead of doing that , he can use database command to change into
a particular database and access all objects by their names.

Syntax : database ;

Ex: database salesdb;

what is COP in teradata?

Communication Processor (COP) - The Communication Processor is also an older term used on the DBC 1012.

it used to communicate with network attached HOSTs (DOS-PC/UNIX).

What is TDP in teradata?

The Teradata Director Program(TDP) is used by the mainframe HOST to communicate with the Teradata system.

It manages all traffic between the Call Level Interface (CLI) and the Teradata System. Its functions include session initiation and termination, logging, verification, recovery, and restart, as well as physical input to and output from IFPs and COPs.

write difference between user and database in teradata?

In Teradata, a user is the same as a database with one exception. A user is able to logon to the system and a database cannot

what is the use of TEMP space?

TEMPORARY (TEMP) space :
A database may or may not have TEMP space, however, it is required if Global Temporary Tables are used.

what is common practice in creating views and tables in teradata?

It is a common practice in Teradata to have some databases with PERM space that contain only tables.

Then, other databases contain only views. These view databases require no PERM space and are the only databases that users have privileges to access.

The views in these databases control all access to the real tables in other databases. They insulate the actual tables from user access

PERM space is a pre allocated space? True/ False?

False. PERM space is not pre allocated space. its a maximum allowed space
to user or a database.

As user or database creating objects(Tables,procedures,journals,secondary indexes..etc) he uses PERM space

Can we create Macros and view in a database or user with no PERM Space?

Yes. We can. Although a database without PERM space cannot store tables, it can store views and macros because they are physically stored in the Data Dictionary (DD) PERM space and require no user storage space.

The Data Dictionary is in a "database" called DBC.

Thursday, February 3, 2011

how to install Teradata Express editions?

Teradata express edition available in two modes

1. One to install directly on windows (Xp,Vista )
2. Second Virtual machine which runs on VM player (SuSE LinuX)
here host machine can be windows or Linux. Guest OS is SUSE Linux
But your computer processor must support Intel VT (Virtual technology)
My laptop has Intel Core 2 duo T6400 64 Bit processor. I am not
able to run SUSE Linux Teradata because my CPU is not supporting
Intel VT technology

And Teradata windows Express Edition works on only 32 Bit Operating System.
It does not matter whether your CPU is 32 bit or 64 Bit your OS must be
32 bit OS.

I have 64 Bit Laptop and I installed 32bit windows vista home premium
i was able to install teradata express edition 12 and run. But when
I install Windows vista 64 Bit OS, I am not able to install it.

So I created a Windows XP Virtual Machine

Wednesday, February 2, 2011

what is OLTP System ?

Online Transaction Processing System (OLTP) is a system whose main
purpose is to capture and store the business transactions

Ex: Oracle 9i,10g,11g,IBM DB2, Microsoft SQL server RDBMS are used as
OLTP systems.

All company Business transactions goes into this database

what is Data warehouse?

A data warehouse is a system that retrieves and consolidates data periodically
from the soure systems into a dimensional or normalized data store

It usually keeps hears of history and is queried for business intelligence or
other analytical activities

what is DDS in data warehouse projecy?

DDS Means Dimensional Data Store. This is the data base where ETL loads
data from source systems into it.

Ex: Teradata is used for DDS

Here data is in Dimensional model

what are teradata purpose built platform server?

Teradata manufacture varies server for different purpose
customer can buy based on their requirement

Teradata Data Mart Applicance: 540 S, 540P

Teradata Extreme Data Appliance : 1550,1600,1650

Teradata Data warehouse Appliance : 2500,2550,2650...etc

Teradata Active Enterprise Data warehouse Appliance : 5500(C/H/E),5550(C/H),5555

and

Teradata Extreme Performace appliance : 4555 ( with solid state drives (SSD)

what are the supporting Operating System for Teradata?

Windows NT Server / Windows Server 2003

SUSE Linux

MP-RAS Unix

Can you list the backup and restore software used with teradata?

we can use

Symantec : NetBackup

Backbone / Quest software : Netvalut

Tuesday, February 1, 2011

How teradata does load balancing?

we don't. Teradata automatically balances sessions across all nodes to evenly distribute work across the entire parallel configuration. Users connect to the system as a whole rather than a specific node, and the system uses a balancing algorithm to assign their sessions to a node. Balancing requires no effort from users or system administrators.

How clients reach Teradata system?

Each Teradata node has an IP address

Client machines use hosts file windows/system32 directory. where we
list teradata nodes using specific syntax

nodeipaddress tpdidcop{1,2,3...etc}

Ex:

172.64.127.51 teradatanode1cop1
172.64.127.52 teradatanode2cop2
172.64.127.53 teradatanode3cop3

Then Client tries to connect one by one. Client may reach any one node
and the software in the node assigns a PE from any node after looking
load balancing

Expand TDPID?

Teradata Director Program Id

what is FALLBACK in teradata?

FALLBACK is a table protection feature used in case an AMP fails. You can use FALLBACK on all tables, some tables or no tables

FALLBACK tables use twice as much disk space as NON-FALLBACK rows

How many AMPs can be in a cluster?

the minimum number of AMPs per cluster is two, but the maximum number of AMPs per cluster is 16

In most systems, AMPs are clustered in a group of four.Because The chances of losing two AMPs out of four are quite low. However, if one AMP is lost, the other three will share in the extra work.


The next most popular clustering scheme is a group of three.

what is cluster in teradata?

Cluster is group of AMPs in teradata.


It is useful whenever an AMP fails.

Using cluster it is okay to loose ONE AMP.

what is RAID?

RAID :"Redundant Array of Independent Disks (RAID)".

It protects against a disk failure.

There are many levels of RAID in the data storage industry.

The most common level, and one that is used by Teradata, is RAID-1, also called MIRRORING.

With RAID-1, each primary disk has a mirror image, or an exact copy of all its data on another disk. The contents of both disks are identical.

what is a Clique in teradata?

A Clique is group of nodes in teradata. Clicke is formed by connecting
one node disks to other node via some cables.

so when one node goes down, its AMP and PE will migrate
to other node but still have an access to its own disks.....

Clicks : A group of nodes

can you expand DBC?

DBC (DBC 1012) Data Base Computer was initially introduced by Teradata

DBC: Data Base Computer

What is the best physical data model for Teradata?

Most EDW's use 3NF in teradata i.e on corporate wide database

It gives a chance to run all types of queries...

If you want to use star schema or snow flake schema we can create
data marts from teradata CDW

What is the best physical data model for Teradata?

Most EDW's use 3NF in teradata i.e on corporate wide database

It gives a chance to run all types of queries...

If you want to use star schema or snow flake schema we can create
data marts from teradata CDW

Explain star schema , FACTS and Dimension tables?

A "Star-Schema" model is comprised of a fact table and a number of dimension tables.

The fact table is a table with a multi-part key. Each element of the key is, itself a foreign key, to a single dimension table. The remaining fields in the fact table are known as facts, and are numeric, continuously valued, and additive. Facts can be thought of as measurements taken at the intersection of all of the dimensions.

Dimension attributes are mostly textual, and are almost always the source of constraints and report breaks.

This model enhances performance on known queries, or in other words, queries users run repeatedly day after day.