TERADATA HELPFUL DBA SQL

HELPFUL DBA SQL

149.How to use analytical function as row number with qualify ?

We can use Teradata Qualify Row Number to have an alternate solution to Group by clause or using distinct.Qualify works on the final set of result rows.Qualify row number we can use in order to have a specific record or number of records.
SELECT EMP_NAME,EMP_LOCATION FROM EMP QUALIFY ROW_NUMBER() OVER ( PARTITION BY EMP_NAME ORDER BY DATE_EFFECTIVE DESC) = 1;
This query will result in getting all the EMPLOYEE NAMES with their latest location.ROW NUMBER will fetch the first record and we are ordering on the basis of date in descending order so latest record will be fetched for each employee.
Using Qualify Row Number is also very useful when we have very complex queries consisting of left , right joins involving several tables.In that case we can implement all our required rules and at the end we can refine the result set using Qualify Row Number.The Qualify ROW_NUMBER() can be very helpful in removing Unique Primary Key Violation error while loading data into your Target Table.

150. How to find Teradata Release and Version details?

SELECT * FROM DBC.DBCINFO;

151.How can we add new columns in a existing table after specific column ?

No, you can’t. As It’s a relational database, Suppose, if we need the new column to be in a specific position as a 1st column or in middle, the you need to do like this,

1) RENAME EXISTING TABLE TO BACKUP TABLE
2) CREATE THE NEW TABLE WITH THE SPECIFIED COLUMN POSITION THAT YOU WANT
3) INSERT INTO NEW TABLE SEL OLD COLUMNS FROM BACKUP TABLE (You should use SEL COLUMN LIST, Since New column will not be in the backup table)
4) DROP THE BACKUP TABLE.

152.How to create a table with an existing structure of another table with or without data and also with stats defined in Teradata?

CREATE TABLE new_TABLE AS old_TABLE WITH DATA
CREATE TABLE new_TABLE AS old_TABLE WITH NO DATA
CREATE TABLE new_TABLE AS old_TABLE WITH DATA AND STATS

153.How to find the duplicate rows in the table in Teradata?

SELECT name, COUNT (*) FROM TABLE EMPLOYEE GROUP BY name HAVING COUNT (*)>1; Also DISTINCT will be useful. If both DISTINCT and COUNT(*) returns same number then there are no duplicates.
Method : 1
- Create table tmp as ( select * from table qualify row_number() over (partition by pk_table order by pk_table) = 1 ) with data;
- Delete from table all;
- Insert into table select * from tmp;
- Drop table tmp; ————
Method : 2
- If we are having huge data in a file then simply load in temp table by using Fastload(only file to table) where it wont allow duplicates.
Method : 3
- Load in a SET table and rename it into main table

Delete DML wont allow order by function in select query and also if you sub query it could be chance of deleting entire rows, so we can just read the duplicate record with the below query for deleting we need to follow above three methods.

sel * from table a qualify row_Number() over (partition by col1 col2 col3….coln order by col1 col2 col3….coln) > 1

154. How to calculate the tablesize , database size and free space left in a database in teradata?

DBC.TABLESIZE and DBC.DISKSPACE are the systems tables used to find the space occupied. Below Query gives the table size of each tables in the database and it will be useful to find the big tables in case of any space recovery.

SELECT DATABASENAME, TABLENAME,

SUM(CURRENTPERM/(1024*1024*1024)) AS “TABLE SIZE”

FROM DBC.TABLESIZE

WHERE DATABASENAME = <‘DATABASE_NAME’>

AND TABLENAME = <‘TABLE_NAME’> GROUP BY 1,2;

Below query gives the total space and free space available in a database.

SELECT DATABASENAME DATABASE_NAME,

SUM(MAXPERM)/(1024*1024*1024) TOTAL_PERM_SPACE, SUM(CURRENTPERM)/(1024*1024*1024) CURRENT_PERM_SPACE,

TOTAL_PERM_SPACE-CURRENT_PERM_SPACE as FREE_SPACE

FROM DBC.DISKSPACE

WHERE DATABASENAME = <‘DATABASE_NAME’> group by 1

155. Query to gives the skewness for all tables.

SELECT

ts.DatabaseName ,ts.TableName,td.CreateTimeStamp AS Created ,td.LastAlterTimeStamp AS LastAltered,td.AccessCount ,td.LastAccessTimeStamp AS LastAccess ,

SUM(ts.CurrentPerm) AS CurrentPerm ,

SUM(ts.PeakPerm) AS PeakPerm,

(100 – (AVG(ts.CurrentPerm)/MAX(ts.CurrentPerm)*100)) AS SkewFactor

FROM DBC.TableSize ts JOIN DBC.Tables td

ON ts.DatabaseName = td.DatabaseName

AND ts.TableName = td.TableName

GROUP BY 1,2,3,4,5,6;

156.Query to find the Primary key, Foreign key, primary Index,PPI for the Database?

Select DatabaseName, TableName ,columnName,

Case When IndexType=’K’ Then ‘Primary Key’

When IndexType=’S’ Then ‘Secondary Index’

When IndexType=’P’ Then ‘Primary Index’

When IndexType=’Q’ Then ‘PPIndex’

When IndexType=’J’ Then ‘Join Index’

End as implimented _Index From DBC.IndicesWhere TableName in ( Select distinct TableName From DBC.Tablesize Where DatabaseName <>’DBC’ And CurrentPerm>0 ) Order by 1,2,3

157.How to find number of records present in Each AMP or a Node for a given Table through SQL?

Select HASHAMP(HASHBUCKET(HASHROW(PRIMARY_INDEX_COLUMNS))) AS AMP_NO,COUNT(*) 
from DATABASENAME.TABLE_NAME GROUP BY

158. How to select 5th row from a table?

SELECT * FROM TABLENAME

QUALIFY ROW_NUMBER() OVER (ORDER BY COLUMN) = 5;

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

select * from dbc.tables where databasename='<DATABASENAME>’;
help database <DATABASENAME’;

160.How to rename columns using views?

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

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

161.How to select first N Records in Teradata?

SELECT BOOK_NAME, BOOK_COUNT, RANK(BOOK_COUNT) A FROM LIBRARY QUALIFY A <= 10

162.How to view every column and the columns contained in indexes in Teradata?

SELECT * FROM DBC.TVFields;
SELECT * FROM DBC.Indexes;

163.How to find out list of indexes in Teradata?

IndexType	Description
P	Nonpartitioned Primary
Q	Partitioned Primary
S	Secondary
J	join index
N	hash index
K	primary key
U	unique constraint
V	value ordered secondary
H	hash ordered ALL covering secondary
O	valued ordered ALL covering secondary
I	ordering column of a composite secondary index
M	Multi column statistics
D	Derived column partition statistics
1	field1 column of a join or hash index
2	field2 column of a join or hash index

SELECT databasename, tablename, columnname, indextype, indexnumber, indexname FROM dbc.indices ORDER BY databasename, tablename, indexnumber;

164.How do you execute the given SQL statement repeatedly in BTEQ?

Select top 1* from database.table1; =n

Here “=n” is to run the previous sql statement, “n” number of times.

165.What is the command in BTEQ to check for session settings ?

The BTEQ .SHOW CONTROL command displays BTEQ settings.

166.How can you track Login Parameters of users in Teradata?

SELECT LOGDATE, LOGTIME, USERNAME, EVENT FROM DBC.LOGONOFF;

167..How can you determine I/O and CPU usage at a user level in Teradata?

SELECT ACCOUNTNAME, USERNAME, SUM(CPUTIME) AS CPU, SUM(DISKIO) AS DISKIO FROM DBC.AMPUSAGE GROUP BY 1,2 ORDER BY 3 DESC;

168.BTEQ script to fetch the DDL’s of table/view/macro ?

/* This BTEQ script fetches the table DDL’s for the current DB */ /* Make sure you set the database before running the script */ /* Copy contents and paste it in a text file (eg getDDL.scp) */ /* in the prompt # bteq < getDDL.scp (to execute) */ .SET SIDETITLES OFF .SET WIDTH 254 .SET QUIET ON .os IF EXIST showDDL.tmp del showDDL.tmp .EXPORT REPORT file=showDDL.tmp SELECT ‘.SET TITLEDASHES OFF’ (TITLE ”); /*

Generate Table DDL’s */ SELECT ‘SHOW TABLE ‘||TABLENAME||’;’ (TITLE ”) FROM DBC.TABLES WHERE DATABASENAME = DATABASE AND TABLEKIND = ‘T’ ORDER BY 1; /*

Generate Views DDL’s */ SELECT ‘SHOW VIEW ‘||TABLENAME||’;’ (TITLE ”) FROM DBC.TABLES WHERE DATABASENAME = DATABASE AND TABLEKIND = ‘V’ ORDER BY 1; /*
Generate Macro DDL’s */ SELECT ‘SHOW MACRO ‘||TABLENAME||’;’ (TITLE ”) FROM DBC.TABLES WHERE DATABASENAME = DATABASE AND TABLEKIND = ‘M’ ORDER BY 1; /*
Generate Stored Procedure DDL’s */ SELECT ‘SHOW PROCEDURE ‘||TABLENAME||’;’ (TITLE ”) FROM DBC.TABLES WHERE DATABASENAME = DATABASE AND TABLEKIND = ‘P’ ORDER BY 1;

SELECT ‘.DEFAULTS’ (TITLE ”); SELECT ‘.EXPORT RESET’ (TITLE ”); .EXPORT RESET .OS IF EXIST DDLS.txt del DDLS.txt .EXPORT REPORT file=DDLS.txt .run file = showDDL.tmp .OS IF EXIST showDDL.tmp del showDDL.tmp/q

169..Define POSITION Function?

The POSITION function Returns the actual position of the character which occurs first. POSITION function is ANSI standard. Teradata has an equivalent function called INDEX. Both the POSITION and INDEX functions returns position of character’s first occurrence in a string. Examples for the POSITION function

SELECT POSITION( ‘e’ IN ‘Read’); Displays Result as ‘2’