Monday, 9 April 2018

Most useful Teradata interview questions -- PART - II

76.How do you set default date setting in BTEQ?
 They have to be set after logging on to the session
set session dateform = ANSIDATE ;  /*format is yyyy-mm-dd */
set session dateform = integerdate ; /* format is yy/mm/dd   -teradata date format */
77.What does DROP table table_name command do?
  • It deletes all data in table_name
  • Removes the definition from the data dictionary
  • Removes all explicit access rights on the table
78.Is Like comparison case-sensitive in Teradata?
 LIKE operator is not case sensitive in Teradata session mode. Consider the following example Select F_name from employee where F_name like ‘%JO%’; The following query will pick values matching with ‘JO’ and ‘jo’ as well, since Teradata is not case-sensitive To overcome this problem, a new function called “CASESPECIFIC” is used in TERADATA as follows Select F_name from employee where F_name (CASESPECIFIC) like ‘%JO%’;
79.How do you submit bteq script (batch mode)?
Start the BTEQ , by typing BTEQ
Enter the following command
.run file = BTEQScript.btq OR
Bteq < BTEQScript.btq
BTEQ Script.btq contains following .logon 127.0.0.1/dbc, dbc; sel top 10 * from dbc.tables; .quit These are some tricky questions asked about explain to see if an candidate has really worked on query tuning and how much he would know about optimizer & explain           .
80.What is residual condition?
Residual condition means a filter is applied on particular table to limit the number of rows fetched into Spool. Say for example Sel * from EMP.Employee where emp_sal > 10000; Here , residual condition will act upon to fetch only employees with salary greater than 10000

Related Links
81.What is Spool “ Last use”
The particular spool file used in the step will be used for last time and the spool space will be released since it is no longer used in further steps of the query execution.
82.How do you create materialized view in Teradata?
 There is no such thing as a “materialized view” in Teradata. The equivalent in Teradata would be a join index (or aggregate index) with a corresponding view put on top of it. The command to create one is “CREATE JOIN INDEX…(lots of options)”. Join indices are maintained when the data is maintained. They cannot be accessed directly, but are invoked when the optimizer determines it is beneficial. Even though you cannot invoke a join index directly, you create a view that looks like the join index and in that way, it would be similar to having direct access. However, this approach does not guarantee that the join index will be used.
83.What are Differences between Teradata and ANSI Session modes in Teradata?
 TERADATA MODE ————-
1. Comparison is not Case sensitive
2. Create table are default to SET tables
3. Each transaction is committed implicitly
4. Supports all Teradata commands
5. It follows BTET (Begin and End Transaction) Mode
ANSI MODE ————-
1. Comparison is CASE sensitive
2. Create table are default to MULTISET tables
3. Each transaction has to be committed explicitly
4. Does not support all Teradata commands
5. It does not follow BTET Mode
84.Which two statements are true about a foreign key?
 Each Foreign Key must exist as a Primary Key.
Foreign Keys can change values over time.
First: True Second: False
 85.What are the benefits of fallback?
  • Protects your data from hardware (disk) failure.
  • Protects your data from software (node) failure.
  • Automatically recovers with minimum recovery time, after repairs or fixes are complete
 86.What is a Dirty-Read or Stale-Read Lock?
 This occurs when a access lock is applied on the table which is doing a update. May produce erroneous results if performed during table maintenance resulting in Dirty Read or stale read , which might result in inconsistent result set. 
87. What is Cross Join?
 It is a Teradata specified Join, which is used as equivalent to product join. There is no “On” clause in case of CROSS join SELECT EMP.ename , DPT.Dname FROM employee EMP CROSS JOIN Department DPT WHERE EMp.deptno = DPT.depto
 88. Difference between Stored Procedure and Macro?
Stored Procedure:
  • It does not return rows to the user.
  • It has to use cursors to fetch multiple rows
  • It used Inout/Out to send values to user
  • It Contains comprehensive SPL
  • It is stored in DATABASE or USER PERM
  • A stored procedure also provides output/Input capabilities
Macros:
  • It returns set of rows to the user.
  • It is stored in DBC SPOOL space
  • A macro that allows only input values
 89. What are the scenarios in which Full Table Scans occurs?
  • The where clause in SELECT statement does not use either primary index or secondary index
  • SQL Statement which uses a partial value (like or not like), in the WHERE statement.
  • SQL Statement which does not contain where clause.
  • SQL statement using range in where clause. Ex. (col1 > 40 or col1 < =10000)
 90.How to eliminate Product Joins in a Teradata SQL query?
  • Ensure statistics are collected on join columns and this is especially important if the columns you are joining on are not unique.
  • Make sure you are referencing the correct alias.
  • Also, if you have an alias, you must always reference it instead of a fully qualified tablename.
  • Sometimes product joins happen for a good reason. Joining a small table (100 rows) to a large table (1 million rows) a product join does make sense.
 91..What do High confidence, Low confidence and No confidence mean in EXPLAIN plan?
 HIGH CONFIDENCE:Statistics are collected.
LOW CONFIDENCE: Statistics are not collected. But the where condition is having the condition on indexed column. Then estimations can be based on sampling.
NO CONFIDENCE: Statistics are not collected and the condition is on non indexed column.
 92. What is Teradata Virtual storage?
  • This concept is introduced in TD12. It does the following tasks
  • Maintains Information On Frequency Of Data Access
  • Tracks Data Storage Task On Physical Media
  • Migrating Frequently Used Data To Fast Disks And Less Frequently Used Data To Slower Disks.
  • Allocating Cyclinders From Storage To Individual Amps
 93.How to start / stop a database in windows?
  • logon to CMD
  • check for state pdestate -d
  • run the following command to start “net start recond”
  • check for status pdestate -d
  • to STOP the database Trusted Parallel Application or TPA
  • tpareset -x comment
  • The -x option stops Teradata without stopping the OS.
94 What is a role?
 A role is a set of access rights which can be assigned to the users. They indirectly help in performance by reducing the number of rows entered in DBC.accessrights
 95.What is a profile?
 A profile contains set of user parameters like accounts, default database, spool space, and temporary space for a group of users To assign the profile to a user, use the AS PROFILE modified in a CREATE USER or MODIFY USER statement: MODIFY USER username AS PROFILE=profilename ; To remove a profile from a member but retain the profile itself: MODIFY USER username AS PROFILE=NULL ;
96. How to check if given object is a database or user ?
 To check whether the given object is user or database , we can use following query sel * from dbc.databases where dbkind =’U’ or dbkind=’D’;
97. In a table can we use primary key in one column and in another column both unique and not null constrains.if yes how?
 Yes, you can have a column for Primary key and have another column which will have no duplicates or null.e.g.A Salary Table will have employee ID as primary key. The table also contains TAX-ID which can not be null or duplicate
98. Is it possible that there are two primary key will be in a same table?
Primary key ==========
  • 1. A table should have only one primary key
  • 2. More than one column can consist of a primary key – upto 64 columns
  • 3. Can not be NULL values (missing values)
  • 4. Should be unique values (no duplicate value)
Foreign key ==========
  • 1. A table may have zero or more than that up-to 32 keys
  • 2. More than one column can consist a primary key – up to 64 columns
  • 3. Can have NULL values
  • 4. Can have duplicate values
  • 5. There should be a corresponding primary key in the parent table to enforce referential integrity for a foreign key
 99.Can you recover the password of a user in Teradata?
 No, you can’t recover the password of a user in Teradata. Passwords are stored in this data dictionary table (DBC.DBASE) using a one-way encryption method. You can view the encrypted passwords using the following query SELECT * FROM DBC.DBASE;
100.What are the functions of a Teradata DBA?
  • User Management – Creation and managing Users, Databases, Roles, Profiles and Accounts.
  • Space Allocation – Assigning Permanent Space, Spool Space and Temporary Space.
  • Access of Database Objects – Granting and Revoking Access Rights on different database objects.
  • Security Control – Handling logon and logoff rules for Users.
  • System Maintenance – Specification of system defaults, restart etc.
  • System Performance – Use of Performance Monitor(PMON), Priority Scheduler and Job Scheduling.
  • Resource Monitoring – Database Query Log(DBQL) and Access Logging.
  • Data Archives, Restores and Recovery – ARC Utility and Permanent Journals.
101. How does Hashing happens in Teradata?
  • Hashing is the mechanism through which data is distributed and retrieved to/from AMPs.
  • Primary Index (PI) value of a row is the input to the Hashing Algorithm.
  • Row Hash (32-bit number) value is the output from this Algorithm.
  • Table Id + Row Hash is used to locate Cylinder and Data block.
  • Same Primary Index value and data type will always produce same hash value.
  • Rows with the same hash value will go to the same AMP.
  • So data distribution depends directly on the Row Hash uniqueness; be careful while Choosing Indexes in Teradata.
102. How to insert a new line between two strings concatenated in answer sets of teradata.
 It can be achieved by using the hexadecimal string ’0D’XC for this. eg: SELECT ‘ABC’ || ’0D’XC || ‘DEF’ ; When you execute this in queryman you will see the title in two lines. But the result will be in a line, just copy/paste the result in Dos or windows or notepad or textpad it will appear in two lines
 103..Verify if two database are in sync and having same objects ?
104.How to recover left over spool space in Teradata ?
 To Identify the left over spool, use the following SQL.
105. Explain LOB?
 It is possible to create functions which operate on large object data types. The following library functions exists to provide the ability use LOB’s as function input or output:
106.What is the difference between EXCEPT and MINUS operator in Teradata SQL?
EXCEPT is  ANSI Compliant and MINUS is Teradata specific. Function wise both are same.
107: How many codd’s rules are satisfied by teradata database?
There are 12 codd’s rules applied to the teradata database
108. What is meant by a Least Cost Plan?
A Least Cost Plan basically executes in less time across the shortest path.
109. What is meant by a Highest Cost Plan?
As per Highest Cost Plan, the time taken to execute the process is more, and it takes the longest path available.
110.Why Fload doesn’t support multiset table?
 For Fast Performance it will not support the duplicate values,Refential Intigrity
111. What is FILLER command in Teradata?
while using the mload of fastload if you don;t want to load a particular filed in the datafile to the target then use this filler command to achieve this :
112.Why does varchar occupy 2 extra bytes?
The two bytes are for the number of bytes for the binary length of the field. It stores the exact no of characters stored in varchar
113.  How to you verify a complicated SQL?
  Explain statement
114. In Teradata, how do we Generate Sequence?
In Teradata, we Generate Sequence by making use of Identity Column
115.   How do you see a DDL for an existing table?
116.What is the Maximum number of volatile tables that can be created?
TD14.0, 1000 volatile tables can be created in a session
117.How can you apply access lock on table rather than read lock?
 We can override default read lock on any table by explicitly mentioning the ACCESS LOCK before using the table. Syntax is: LOCK TABLE EMPLOYEES FOR ACCESS SELECT * FROM EMPLOYEES;
118 How to check all the volatile tables created for the current session ?
HELP VOLATILE TABLE;
119. Where is password stored in Teradata?
 User can view the password in DBC.DBASE view however it will be in encrypted format.
120. How to specify security mechanism option in BTEQ?
  • We can use logmech command to specify security mechanism in BTEQ in Teradata
  • eg: .LOGMECH LDAP;
121. What is datatype of keyword NULL ?
  •  NULL keyword is of INTEGER datatype.
122. ORDER BY clause is not allowed along with SELECT clause in which situation ?
In Subqueries
123. Can you specify ORDER BY COLUMN_NAME in SET OPERATORS like UNION/MINUS etc ?
 No. If you need to use ORDER BY , then specify COLUMN ORDER rather than COLUMN NAME.
124. While exporting data using BTEQ Export, you are getting junk characters in the exported file. How will you remove these junk characters ?
 Add a BTEQ setting before your SELECT clause in BTEQ Export: .SET RECORDMODE OFF;

125. How can I change NULL and DATE values representation in TSA?
 Go to TOOLS –> OPTIONS –> DATA FORMAT
126. When we get the error in TSA, we can see it in bottom status bar. However how to see last encountered error when we have executed some other successful query?
 You can press F11 and it will display last error encountered.
127. When I am fetching column from table of BIGINT datatype I am seeing junk characters. How to see proper values? I am using old version of SAT.
 Try using CAST to change the value to VARCHAR and you can see the value now. Alternatively, you can use BTEQ utility to preview correct format values.
128. What are the connectivity options available for TSA?
 ODBC and Teradata.net
129. Name three result code variables in Stored Procedures?
  • SQLSTATE
  • SQLCODE
  • ACTIVITY_COUNT
130. What is the default value for any three result code variables?
 SQLSTATE : ‘00000’,
SQLCODE: 0,
ACTIVITY_COUNT: 0
131. What are the SQL SECURITY Options available in Stored Procedures in Teradata?
 SQL SECURITY OWNER/CREATOR/DEFINER/INVOKER
131. What is the default SQL SECURITY OPTION in Stored Procedures in Teradata?
 SQL SECURITY DEFINER
132. I want to process all the column values in some table using Stored Procedure. How to implement it in Teradata ?
 We can use REF CURSOR to implement this in Stored Procedure in Teradata.
133.Creating Duplicate users for Teradata ?
 Teradata Administrator (WinDDI) gives us an option to clone the users.
134. While retrieving COUNT(*) in your SQL Query, it is giving numeric overflow error. How will you get the count now ?
  •  Try to cast count(*) to some other bigger datatype in order to avoid numeric overflow error:
  • SEL CAST(COUNT(*) AS DECIMAL(18,0)) FROM TABLE_NAME;
135. Will the count be same if we use COUNT(*), COUNT(20), COUNT(COLUMN_NAME) from some table ?
COUNT(*) and COUNT(20) will give same number of records however COUNT(COLUMN_NAME) will return number of records excluding NULL values. So if all the records are NOT NULL , then all three will result in same number of records.
136. While exporting data using BTEQ Export, you are getting junk characters in the exported file. How will you remove these junk characters ?
 Add a BTEQ setting before your SELECT clause in BTEQ Export: .SET RECORDMODE OFF;
137.How to find the number of AMPs in any system?
SELECT HASHAMP()+1 ; [ We have to do +1 because the HASHAMP number starts with 0]
138 .How will you connect a database server to other server?
ssh or FTP or SU ssh – ssh user_id@server_nam
139.  Syntax for CASE WHEN statement?
    CASE value_expression_1
WHEN value_expression_n
THEN scalar_expression_n
END;
140.What are the types of HASH functions used in teradata?
  • These are the types of HASH, HASHROW, HASHAMP and HASHBAKAMP.
  • Their SQL functions are- HASHROW (column(s)) HASHBUCKET (hashrow) HASHAMP (hashbucket) HASHBAKAMP (hashbucket)
  • To find the data distribution of a table based on PI, below query will be helpful. This query will give the number of records in each AMP for that particular table.
SELECT HASHAMP(HASHBUCKET(HASHROW(PI_COLUMN))),COUNT(*) FROM TABLENBAME GROUP BY 1.
at

No comments:

Post a Comment