Sunday, June 26, 2011

How do you find the list of employees named "john" in an employee table without using Like operator??

This question seems tricky.. but yes there is another way by which we can find names/patters without using like operator.
By using "BETWEEN" , we can find the lsit of employees named john...
sel * from employee where name betwee 'J' and 'K';

But at times usage of between is tricky, if there are other employees starting with J, those employees will also be listed by this query.

how do you list all the objects available in given database?

1.)select * from dbc.tables where databasename='<DATABASENAME>';

2.) By running a normal help command on that database as follows. 
help database <DATABASENAME';

Sunday, June 12, 2011

What are different types of Spaces available in Teradata ?

There are 3 types of Spaces available in teradata ,they are

1. Perm space
-This is disk space used for storing user data rows in any tables located on the database.
-Both Users & databases can be given perm space.
-This Space is not pre-allocated , it is used up when the  data rows are stored on disk.

2.Spool Space
-It is a  temporary workspace which is used for processing Rows for given SQL statements.
-Spool space is assigned only to users . -
-Once the SQL processing is complete the spool is freed and given to some other query.
-Unused Perm space is automatically available for Spool . 

3. TEMP space
-It is allocated to any databases/users where Global temporary tables are created and data is stored in them.
-Unused perm space is available for TEMP space

Thursday, June 9, 2011

What is hash collision ?

This occurs when there is same hash value generated for two different Primary Index Values. It is a rare occurance and Has been taken care in future versions of TD.

What is RAID, What are the types of RAID?

Redundant Array of Inexpensive Disks (RAID)  is a type of protection available in Teradata. RAID  provides Data protection at the disk Drive level.  It ensures data is available even when the disk drive had failed.

Th\ere are around 6 levels of RAID ( RAID0 to RAID5) .  Teradata supports Two levels of RAID protection
RAID 1 - Mirrored copy of data in other disk
RAID 5 - Parity bit (XOR) based Data protection on each disk array.

One of the major overhead's of RAID is Space consumption

Sunday, June 5, 2011

How do you find the No of AMP 's in the teradata Database?

1.) You can do a SELECT HASHAMP()+1 to get the total number of Amps in the  given teradata system.
2.) Details about amps can also be checked in Configuration management on  teradata PMON tool.

How do you see a DDL for an existing table in Teradata?

By using show table command  as follows
show table tablename ;

This will display DDL structure of table along with following details
Before/after journal
set/multiset table type
details about column - datatype ,default,identity/primary -foreign key .

How to find duplicates in a table?

To find duplicates in the table , we can use group by function on those columns which are to be used and then listing them if their count is >1 .

Following sample query can be used to find duplicates in table having  3 columns
select col1, col2,col3, count(*) from table
group by col1, col2, col3 
having count  (*) > 1 ;

Saturday, June 4, 2011

what are different types of journals in teradata?

There are  3 different types of journals available in Teradata. They are

1. Transient Journal  - This maintains current transaction history. Once the query is successful it deletes entries from its table .  If the current query transaction fails, It rolls back data from its table.

2. Permanent Journal  - This is defined when a table is created.  It can store BEFORE or AFTER image of tables. DUAL copies can also be stored. Permanent Journal maintains Complete history of table.

3.Down AMP recovery Journal (DARJ)  - This journal activates when the AMP which was supposed to process goes down.  This journal will store all entries for AMP which went down.  Once that AMP is up, DARJ copies all entries to that AMP and makes that AMP is sync with current environment.

What are the reasons for product joins ?

1. Stale or no stats causing optimizer to use product join
2. Improper usage of aliases in the query.
3. missing where clause ( or Cartesian product join  1=1 )
4. non equality conditions like > ,< , between   example ( date)
5. few join conditions
6.  when or conditions are used.

last but not the least   product joins are not bad always!! sometimes PJ are better compared to other types of joins.

Thursday, June 2, 2011

How to rename columns using views?

Create a view which is a subset of the employee table as follows.
Renaming columns in views will help increase security of sensitive tables  and hiding columns under alias names

Replace  view EmployeeV( number,fullname,addr,phno,depnum,sal, expr) as
locking row for access 
EmpNo ,                       
Name  ,                       
Phone   ,                     
DeptNo   ,                    
Salary    ,                   
from employee;

When we run sel * form employeeV, Only alias columns are displayed .

What are Restrictions on Views in Teradata?

An index cannot be Created on a view.
It cannot contain an ORDER BY clause.
All the derived columns and aggregate columns used in the view  must have an AS clause (alias defined).
A view cannot be used to UPDATE if it contains the following :
        – Data from more than one table (JOIN VIEW)
        – The same column twice
        – Derived columns
        – A DISTINCT clause
        – A GROUP BY clause