Teradata interview questions

How to extract numbers from strings in Teradata Sql?

how can we analyze the locks ?

If the script is aborted. why it is aborted and how can you identify ?

how do you handle ET and UV errors ?

If the query is NOT WRITTEN PROPERLY then what are the recommendations you can give to the developer ?

If RDBMS is halted what will you do ?

we have the num like this +91666666666......i want to remove 91 and i want lo load remaining data to the target table..

whinch function we can use in teradata?

what is meant by Hot amp?

What is meant by MATCHTAG in multiload utility?

Cursors in Teradata - simple example

/* Program to Revise the employees salaries and return count  based on range for a particular department */

Create procedure emp_sal(IN deptno INTEGER,OUT vrow INTEGER)

Begin

 Declare v_cur CURSOR FOR select empno,salary from emp where deptno=:deptno;
 DEclare v_empno INTEGER;
 Declare v_sal INTEGER;

 Declare V_count INTEGER default 0;
 Declare V_rowcount INTEGER;
 Declare V_pcount INTEGER;


 OPEN v_cur;  

  set v_rowcount=ACTIVITY_COUNT;
  set v_pcount=v_rowcount;

 
  Repeat
   FETCH v_CUR into v_empno,v_sal;

   set v_count =v_count+1;

   if(v_sal<30000) then
 
    set Rev_sal=v_sal+v_sal*10/100;
 
   elseif    
 
      if(v_sal<50000) then
 
    set Rev_sal=v_sal+v_sal*8/100;
 
    else
       set Rev_sal=v_sal+v_sal*5/100;    

   end if;

  // to insert into other table

    select current_date;  
 
    INSERT rev_emp_sal(v_empno,v_sal,rev_sal,:current_date);

  // update the  employee table

    Update emp set sal=rev_sal where empno=v_empno;

    v_pcount=v_pcount-1;
 
    unit v_pcount=0;

    set vrow=v_count;  

    close cur1;
end;

Teradata - Performace Tuning

Use group by instead of distinct because distinct occupies more spool space

Use EXISTS instead of IN because EXISTS stops the process once it finds the value

Join Columns of same datatype

Use UNION ALL instead of UNION

Remove secondary indexes when the data is being loaded,updated or deleted

Avoid correlated queries,cross join, product join

use Delete ALL - normal delete have rollback, for delete all there is no rollback(no transient journal overhead)

Does SDLC changes when you use Teradata instead of Oracle ?

If Teradata is just a database then no change
IF we are using utilities then it changes
If the schema is in 3rd normal form then there won't be huge changes

Teradata Utilities

Teradata Utilities continues...

Teradata vs Other RDBMS

Teradata: Shared nothing(Ex: Each VPROC is associated with one VDisk)
Other: Shared Everything

Process Million of Instructions per second
Process Thousands of Instructions per second

Stores billions of rows
stores millions of rows

unconditional parallelism(Each AMP works with its own drive)
Conditional Parallelism

Better storage and fast retrieval(using indexes)
fast retrieval only

bulk load utilities(BTEQ,FLOAD,MLOAD,TPUMP)
limited utilities

Teradata Questions - SQL Queries

Number of nodes in a system

select count(distinct(nodeid)) from dbc.resusagescpu

To find database size

select sum(currentPerm) from DBC.diskspace where databasename='mydb'

To find table size

select sum(currentperm) from DBC.tablesize where databasename='mydb' and tablename='mytable'

To find version of terdata

select * from dbc.dbcinfo

To find CPU time consumed

select username,accountname,sum(CPUTIME) from dbc.ampusage where username='xyz' group by 1,2

To find the space used by each table in MB's in a database

select tablename,sum(currentperm)/1024/1024 MB
from DBC.AllSpace
where databasename='mydb'
group by 1

To identify skewness

SELECT
   DatabaseName,
   TableName,
   SUM(CurrentPerm) AS CurrentPerm ,
   SUM(PeakPerm) AS PeakPerm,
   (100 - (AVG(CurrentPerm)/MAX(CurrentPerm)*100)) AS SkewFactor
  FROM DBC.TableSize
 WHERE databasename =  database
   AND TableName IN ('SALES' , 'CUSTOMERS')
 GROUP BY 1,2
 ORDER BY SkewFactor DESC;