77.How to improve performance of the query
78.Explain Primary Index and how do we select that
The Primary Index determines which AMP stores an individual row of a table. The PI data is converted into the Row Hash using a mathematical hashing formula. The result is used as an offset into the Hash Map to determine the AMP number. Since the PI value determines how the data rows are distributed among the AMPs, requesting a row using the PI value is always the most efficient retrieval mechanism for Teradata.
POINTS:
.It determines how data will be distributed and is also the most efficient access path.
79.What is difference between Role, Privilege and profile
A role can be assigned a collection of access rights in the same way a user can.
You then grant the role to a set of users, rather than grant each user the same rights.
This cuts down on maintenance, adds standardization (hence reducing erroneous access to sensitive data) and reduces the size of the dbc.allrights table, which is very important in reducing DBC blocking in a large environment.
Profiles assign different characteristics on a User, such as spool space, perm space and account strings.
Again this helps with standardization. Note that spool assigned to a profile will overrule spool assigned on a create user statement. Check the on line manuals for the full lists of properties
Data Control Language is used to restrict or permit a user's access. It can selectively limit a user's ability to retrieve, add, or modify data. It is used to grant and revoke access privileges on tables and views.
80.What are different spaces in Teradata and difference ? Perm Space
Temp Space spool space
Perm Space :All databases have a defined upper limit of permanent space.
Permanent space is used for storing the data rows of tables. Perm space is not pre-allocated. It represents a maximum limit.
Spool Space :
All databases also have an upper limit of spool space. If there is no limit defined for a particular database or user, limits are inherited from parents. Theoretically, a user could use all unallocated space in the system for their query. Spool space is temporary space used to hold intermediate query results or formatted answer sets to queries. Once the query is complete, the spool space is released.
Example: You have a database with total disk space of 100GB. You have 10GB of user data and an additional 10GB of overhead. What is the maximum amount of spool space available for queries?
Answer: 80GB. All of the remaining space in the system is available for spool Temp Space :
The third type of space is temporary space. Temp space is used for Global and Volatile temporary tables, and these results remain available to the user until the session is terminated. Tables created in temp space will survive a restart.
81.If your Skew factor is going up. What are remedies ?
Skew factor occurs when the primary index column selected is not a good candidate.
Mean, If for a table when the PI selected having highly non unique values then SKEW factor will be getting by default it will be zero, if skew factor selected is greater than 25 then it is not a good sign.
82.When, How and why we use Secondary Indexes.?
A secondary index is an alternate path to the data. Secondary indexes are used to improve performance by allowing the user to avoid scanning the entire table during a query. A secondary index is like a primary index in that it allows the user to locate rows. Unlike a primary index, it has no influence on the way rows are distributed among AMPs. Secondary Indexes are optional and can be created and dropped dynamically.
Secondary Indexes require separate subtables which require extra I/O to maintain the indexes.
83.What is difference between Primary Key and Primary Index
84.What is difference between database and user in Teradata. what are the things you can do or can not do in both.
Both may own objects such as tables, views, macros, procedures, and functions. Both users and databases may hold privileges. However, only users may log on, establish a session with the Teradata Database, and submit requests.
A user performs actions where as a database is passive. Users have passwords and startup strings;
databases do not. Users can log on to the Teradata Database, establish sessions, and submit SQL statements; databases cannot.
Creator privileges are associated only with a user because only a user can log on and submit a CREATE statement. Implicit privileges are associated with either a database or a user because each can hold an object and an object is owned by the named space in which it resides
85.What is Checkpoint ?
86.When do you use BTEQ. What other softwares have you used or can we use rather than BTEQ.
When the query is performing operations on lesser amount of data in a table then we go for BTEQ.
Any kind of SQL operations like SELECT, UPDATE, INSERT and delete.
Can be used for import, export and reporting purposes.
Macros and Stored procs can also be run using BTEQ.
The other utilities which we can use instead of BTEQ for loading purposes are FASTLOAD and MLOAD.
And exporting is FASTEXPORT. But these are used while accessing large amount of data.
87.How many type of files have you loaded and their differences. (Fixed and Variable) ?
88.How do you execute your jobs in Teradata Environment.
In a channel environment I.e mainframes, the load utilities can be execute through a JCL.
In a network I.e from a command prompt the load scripts can be run through the following command.
<utility name> <scriptname>
89.What was the environment of your latest project (Number of Amps, Nodes, Teradata Server Number etc)
Number of Amps production and integration – 24 development – 12 Number of nodes - production and integration – 4 development – 2 90.What is the process to restart the multiload if it fails
If Mload failed in the Acquisition phase just rerun the job. If Mload failed in Application Phase:
a) Try to drop error tables, work tables, log tables, release Mload if required n submit the job from .Begin Import onwards.
b) if ur table is fallback protected u need to make sure un fallback and use RELEASE MLOAD IN APPLY sql.
Then resubmit the job.
1. Relase the Mload on the target table.
2. Drop all error tables and work tables 3. Resubmit the Mload script.
91.How does indexing improve query performance.?
Indexing is a way to physically reorganize the records to enable some frequently used queries to run faster.
The index can be used as a pointer to the large table. It helps to locate the required row quickly and then return to back to the user.
or
The frequently used queries need not hit a large table for data. they can get what they want from the index itself. - cover queries.
Index comes with the overhead of maintenance. Teradata maintains its index by itself. Each time an insert/update/delete is done on the table the indexes will also need to be updated and maintained.
Indexes cannot be accessed directly by users. Only the optimizer has access to the index.
92.What is difference between Multiload, FastLoad and TPUMP
93.what are the different functions you do in BTEQ (Errorcode, ErrorLevel, etc) ? Error Level :Assigns severity to errors
you can assign an error level (severity) for each error code returned.
you can make decisions can be based on error level.
94.what is difference between ZEROIFNULL and NULLIFZERO ? The ZEROIFNULL function: will pass zero when data coming as null The NULLIFZERO function: will pass null when data coming as zero.
95.What is Range_N
Range_N is defined on a partition primary index to specify the range of values of a column that should be assigned to a partition.
The number of partitions = the number of ranges specified + no case + unknown no case – if the value does not belong to any range
unknown- for the values like nulls, spaces etc 96.Explain PPI?
PPI :-
Partitioned Primary Indexes are Created so as to divide the table onto partitions based on Range or Values as Required .the data is first Hashed into Amps , then Stored in amps based on the Partitions !!! which when Retrived for a single partition / multiple Partitions , will be a all amps
Scan, but not a Full Table Scan !!!! . this is effective for Larger Tables partitioned on the Date Specially !!!
there is no extra Overhead on the System (no Spl Tables Created ect ) 97.What is Casting in Teradata ?
It will convert the data type The casting is similar to DDL:
CAST('02/03/2009-01:25:11' AS TIMESTAMP FORMAT 'MM/DD/YYYY-HH:MI:SS') 98.What is difference between UNION and MINUS?
Both are set operators on two tables generally. UNION gives all rows from both tables eliminating duplicate rows.
MINUS gives records from first table excluding common records from both tables. Its just like EXCEPT in Teradata
Operator Returns
UNION All rows selected by either query.
UNION ALL All rows selected by either query, including all duplicates.
INTERSECT All distinct rows selected by both queries.
MINUS All distinct rows selected by the first query but not the second.
UNION Example
The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_DATE and TO_NUMBER
functions) when columns do not exist in one or the other table:
SELECT part, partnum, to_date(null) date_in FROM orders_list1 UNION
SELECT part, to_number(null), date_in FROM orders_list2;
PART PARTNUM DATE_IN --- --- ---
SPARKPLUG 3323165 SPARKPLUG 10/24/98 FUEL PUMP 3323162 FUEL PUMP 12/24/99 TAILPIPE 1332999 TAILPIPE 01/01/01 CRANKSHAFT 9394991 CRANKSHAFT 09/12/02 SELECT part
FROM orders_list1 UNION
SELECT part FROM orders_list2;
PART --- SPARKPLUG FUEL PUMP TAILPIPE CRANKSHAFT MINUS Example
The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:
SELECT part
FROM orders_list1 MINUS
SELECT part
Ans : Explain is a fn using which you can find the execution procedure of any query in sql assistant. To use this fn type Explain before any query and run it or press F6 after writing a query. It also gives the estimated time, join confidence and memory needed to execute that query. It's advisable to use explain before executing any complex query.
100.what will you do if you get low-confidence in explain plan.
In EXPLAIN plan when we get low confidence on a column, we define COLLECT STATISTICS for that particular column. Then onwards PE prepares plan with High confidence.
101.what will you do if you get high-confidence in explain plan.
Then we will run the query without hesitation.
102.I have one sql query, when I ran explain plan its showing Product join.
What are the factors you will look in to the query to make merge join ? Product joins are the only join type that can join two tables without a bind term.
The only way to avoid a product join to make a merge join is to supply a connecting term between the tables where the operator of the term is =. (These terms are called BIND TERMS.)
I.e we can add another join condition like 1 = 1
110.How does indexing improve query performance?
Indexing is a way to physically reorganize the records to enable some frequently used queries to run faster.
The index can be used as a pointer to the large table. It helps to locate the required row quickly and then return to back to the user.
or
The frequently used queries need not hit a large table for data. they can get what they want from the index itself. - cover queries.
Index comes with the overhead of maintenance. Teradata maintains its index by itself. Each time an insert/update/delete is done on the table the indexes will also need to be updated and maintained.
Indexes cannot be accessed directly by users. Only the optimizer has access to the index.
111.Can we do collect stats on a table when the table is being updated?
no
112.What is Join Index in TD and How it works?
ANS : JOIN INDEX:
---Join Index is nothing but pre-joining 2 or more tables or views which are commonly joined in order to reduce the joining overhead.
So teradata uses the join index instead of resolving the joins in the participating base tables.
They increase the efficiency and performance of join queries.
They can have different primary indexes than the base tables and also are automatically updated as and when the base rows are updated. they can have repeating values.
There are 3 types of join indexes:
1)Single table join index - here the rows are distributed based on the foreign key hash value of the base table.
2) Multi table join index - joining two tables.
3) Aggregate join index - performing the aggregates but only sum and count.
113.I have two tables and one of the table index is defined as UPI or USI. The second table is having any of the indexes like UPI,NUPI,USI OR NUSI. In this scenario what type of join strategy optimizer will use ?
Merge Join Strategy
114.I have two tables. Most of the time I am joining on the same columns. Which type of join index will improve the performance in this scenario ?
Multi table join index
115.When will you create PPI and when will you create secondary indexes?
Partitioned Primary Indexes are Created so as to divide the table onto partitions based on Range or Values as Required. This is effective for Larger Tables partitioned on the Date and integer columns. There is no extra Overhead on the System (no Spl Tables Created ect )
Secondary Indexes are created on the table for an alternate way to access data. This is the second fastest method to retrieve data from a table next to the primary index. Sub tables are created.
PPI and secondary indexes do not perform full table scans but they access only a defined st of data in the AMP's.
116.what is an
optimization
and performance tuning and how does it really work in practical projects. can i get any example to better understand.117.Explain about Skew Factor?
Skew factor occurs when the primary index column selected is not a good candidate.
Mean, If for a table when the PI selected having highly non unique values then SKEW factor will be getting by default it will be zero, if skew factor selected is greater than 25 then it is not a good sign.
118.When you chose primary index and when will you choose secondary index?
Primary index will be chosen at the time of table creation. This will help us in data distribution, data retrieval and join operations.
Secondary indexes can be created and dropped at any time. They are used as an alternate path to access data other than the primary index.
119.When will go for Join index ?
When we have two tables which are joined based on the same join condition very frequently then we go for Join Index
120.When will you go for hash index?
a.A hash index organizes the search keys with their associated pointers into a hash file structure.
b.We apply a hash function on a search key to identify a bucket,
and store the key and its associated pointers in the bucket (or in overflow buckets).
c.Strictly speaking, hash indices are only secondary index structures,
since if a file itself is organized using hashing, there is no need for a separate hash index structure on it.
scenario based questions
121. In case of replacement loading which utility you prefer? Mload or Fload?
Fload.
122.I have a scenario where I update one column in a table using flat file as source. At the same time, the same column is getting updated because of another flat file. Which utility will be more applicable in this case?
Tpump is better as it locks at row level
The table got loaded with wrong data using Fastload and it failed. The error message shown was:
“RDBMS error 2652: Operation not allowed: _db_._table_ is being Loaded.” How to realese lock on this table?
When the data got loaded completely and still its locked, submit another fastload script with BEGIN LOADING AND END LOADING atetments alone.
I need to create a delimited file using fastexport. As fast export do not support delimited format, so I have written the following select to get the delimited output:
select
trim(col1) || '|' ||
trim(col2) || '|' ||
trim(col3) || '|' || ...
...
trim(col50) from table
but the above script prefix each line with 2 junk characters.
How to get the data without the junk characters.
when the fastload check point value is <= 60 and > 60, how is that going to matter?
When the checkpoint interval is <= 60, that indicates the minutes (time) interval. If the value is more than 60, it will be considered as the no. of records but not the time.
123. I am loading a delimited flat file with a time format as the following:
HH:MM PM/AM Examples would be : 9:45 AM
10:25 PM
And there is no zero if the hours is a single integer value.
Is there any way that I would get the mload acquisition phase count in the mload script? MLOAD support environment provides different variables (total ins, upd, del etc.) at the application phase, but not at the acquisition phase.
Is there any way other than scan the log file?
There are various commands available for the same.
SYSAPLYCNT SYSNOAPLYCNT SYSRCDCNT SYSRJCTCNT
124. I have this requirement when error table gets generated during the MLOAD, I want to send an email. How can I achieve this?\
After Mload use a BTEQ to query for the error table if present quit on some value say '99' and use your OS to mail when the return code is 99.
I am using the following syntax to logon to Teradata Demo thru BTEQ/BTEQWin:
.logon demotdat/dbc,dbc;
and having the following error:
*** Error: Invalid logon!
*** Total elapsed time was 1 second.
Teradata BTEQ 08.02.00.00 for WIN32. Enter your logon or BTEQ command:
The hosts file shows the following:
127.0.0.1 localhost DemoTDAT DemoTDATcop1
Hovewer when I use .logon demotdat/dbc
without specifying its password, it prompts for a password... when I type in its password, I am able to logon.
125.What is the reason?
When we use BTEQ in interactive mode, we cant directly gine the Id and pwd.
We have to first give and the logon id and then press enter. Only after that we have to enter the password.
126.Can we make a MLOAD script fail when the error tables are created ?
Currently the mload scripts exits with a return code = 0 which means loading is successful even though it is not.It has created some error tables which indicate some data has been rejected....
There are various commands to do this operation.
.logoff &SYSUVCNT + &SYSRJCTCNT + &SYSETCNT + &SYSRC;
TROUBLE SHOOTING
1) open batch session got failed because of the following error.
WRITER_1_*_1> WRT_8229 Database errors occurred:
FnName: Execute -- [NCR][ODBC Teradata][Teradata Database] Duplicate unique prime key
FnName: Execute -- [NCR][ODBC Teradata][Teradata Database] Duplicate unique prime key