Explore topic-wise InterviewSolutions in .

The following rules govern how Primary INDEXES in a Teradata Database must be defined as well as how they function:
ONE Primary Index per table.
A Primary Index value can be UNIQUE or non-unique.
The Primary Index value can be NULL.
The Primary Index value can be modified.
The Primary Index of a POPULATED table cannot be modified.
A Primary Index has a limit of 64 columns.

The following rules govern how Primary Indexes in a Teradata Database must be defined as well as how they function:
One Primary Index per table.
A Primary Index value can be unique or non-unique.
The Primary Index value can be NULL.
The Primary Index value can be modified.
The Primary Index of a populated table cannot be modified.
A Primary Index has a limit of 64 columns.

ODS=>OPERATIONAL DATA SOURCE.

ODS=>Operational Data Source.

V2R6 included the feature of replica in it. in which copy of DATA base are available on ANOTHER system which MEANS V2R6 provide the additional data PROTECTION as comparison to V2R5 while if data from one system has been vanishes.

V2R6 included the feature of replica in it. in which copy of data base are available on another system which means V2R6 provide the additional data protection as comparison to V2R5 while if data from one system has been vanishes.

Performance tuning and OPTIMIZATION of a QUERY involves collecting statistics on join columns, avoiding cross product join, selection of appropriate primary INDEX (to AVOID skewness in storage) and USING secondary index.

Performance tuning and optimization of a query involves collecting statistics on join columns, avoiding cross product join, selection of appropriate primary index (to avoid skewness in storage) and using secondary index.

1. collecting statistics.
2. Explain Statements.
3. Avoid Product JOINS when possible.
4. select appropriate PRIMARY index to avoid skewness in storage.
5. Avoid Redistribution when possible.
6. Use sub-selects instead of big "IN" lists.
7. Use derived tables.
8. Use GROUP BY instead of DISTINCT ( GROUP BY sorts the data locally on the VPROC. DISTINCT sorts the data after it is redistributed).
9. Use Compression on LARGE tables.

1. collecting statistics.
2. Explain Statements.
3. Avoid Product Joins when possible.
4. select appropriate primary index to avoid skewness in storage.
5. Avoid Redistribution when possible.
6. Use sub-selects instead of big "IN" lists.
7. Use derived tables.
8. Use GROUP BY instead of DISTINCT ( GROUP BY sorts the data locally on the VPROC. DISTINCT sorts the data after it is redistributed).
9. Use Compression on large tables.

We can Load SET, MULTISET tables using Mload, But here when loading into MULTISET table using MLOAD DUPLICATE rows will not be REJECTED, we have to take CARE of them before loading.But in CASE of Fload when we are loading into MULTISET duplicate rows are automatically rejected, FLOAD will not load duplicate rows weather table is SET or MULTISET.

We can Load SET, MULTISET tables using Mload, But here when loading into MULTISET table using MLOAD duplicate rows will not be rejected, we have to take care of them before loading.But in case of Fload when we are loading into MULTISET duplicate rows are automatically rejected, FLOAD will not load duplicate rows weather table is SET or MULTISET.

YES,But you have to DECLARE it out of the FLOAD Block it MEANS it should not come between .begin loading,.END loading FLOAD ALSO supports DELETE,CREATE,DROP statements which we have to declare out of FLOAD blocking the FLOAD Block we can give only INSERT.

YES,But you have to declare it out of the FLOAD Block it means it should not come between .begin loading,.end loading FLOAD also supports DELETE,CREATE,DROP statements which we have to declare out of FLOAD blocking the FLOAD Block we can give only INSERT.

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.

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.

1. set table.
2. MULTISET table.
3. DERIVED table.
4. global temporary table(temporary table).
5. volatile table.

1. set table.
2. multiset table.
3. derived table.
4. global temporary table(temporary table).
5. volatile table.

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.

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.

To FIND RELEASE and VERSION INFORMATION you can query this Data Dictionary table DBC.DBCINFO

To find Release and Version information you can query this Data Dictionary table DBC.DBCINFO

Transferring of LARGE AMOUNT of data can be done USING various Application Teradata UTILITIES which resides on the host computer ( Mainframe or Workstation) i.e. BTEQ, FastLaod, MultiLoad, Tpump and FastExport.

• BTEQ (Basic Teradata QUERY) supports all 4 DMLs: SELECT, INSERT, UPDATE and DELETE. BTEQ also support IMPORT/EXPORT protocols.
•  Fastload, MultiLoad and Tpump transfer the data from Host to Teradata.
•  FastExport is used to export data from Teradata to the Host.

   

Transferring of large amount of data can be done using various Application Teradata Utilities which resides on the host computer ( Mainframe or Workstation) i.e. BTEQ, FastLaod, MultiLoad, Tpump and FastExport.

• 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.

   

1. Ensure statistics are collected on join columns and this is especially IMPORTANT if the columns you are joining on are not unique.
2. Make sure you are REFERENCING the correct ALIAS.
3. Also, if you have an alias, you must always reference it instead of a fully qualified tablename.
4. 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.

1. Ensure statistics are collected on join columns and this is especially important if the columns you are joining on are not unique.
2. Make sure you are referencing the correct alias.
3. Also, if you have an alias, you must always reference it instead of a fully qualified tablename.
4. 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.

To select N records in Teradata you can use RANK function. QUERY syntax would be as FOLLOWS

To select N records in Teradata you can use RANK function. Query syntax would be as follows

Following QUERY describes each column in the Teradata RDBMS

Following query describes COLUMNS CONTAINED in INDEXES in the Teradata RDBMS

Following query describes each column in the Teradata RDBMS

Following query describes columns contained in indexes in the Teradata RDBMS

• Preliminary Phase – BASIC Setup
•  DML Phase – GET DML steps down on AMPS
•  Acquisition Phase – SEND the input data to the AMPs and sort it
•  Application Phase – Apply the input data to the APPROPRIATE Target Tables
•  End Phase – Basic Cleanup

   

Following are the DIFFERENT functions which a DBA can perform:
1. User Management – Creation and managing Users, Databases, Roles, Profiles and ACCOUNTS.
2. Space Allocation – Assigning Permanent Space, Spool Space and Temporary Space.
3. Access of Database Objects – Granting and Revoking Access Rights on different database objects.
4. Security Control – Handling logon and logoff rules for Users.
5. System Maintenance – Specification of system defaults, restart etc.
6. System Performance – Use of Performance Monitor(PMON), PRIORITY Scheduler and Job Scheduling.
7. Resource Monitoring – Database QUERY Log(DBQL) and Access Logging.
8. Data Archives, Restores and Recovery – ARC Utility and Permanent Journals.

Following are the different functions which a DBA can perform:
1. User Management – Creation and managing Users, Databases, Roles, Profiles and Accounts.
2. Space Allocation – Assigning Permanent Space, Spool Space and Temporary Space.
3. Access of Database Objects – Granting and Revoking Access Rights on different database objects.
4. Security Control – Handling logon and logoff rules for Users.
5. System Maintenance – Specification of system defaults, restart etc.
6. System Performance – Use of Performance Monitor(PMON), Priority Scheduler and Job Scheduling.
7. Resource Monitoring – Database Query Log(DBQL) and Access Logging.
8. Data Archives, Restores and Recovery – ARC Utility and Permanent Journals.

MultiLoad is a very powerful utility; it has FOLLOWING LIMITATIONS:

• MultiLoad Utility doesn’t support SELECT statement.
•  Concatenation of MULTIPLE input data files is not allowed.
•  MultiLoad doesn’t support Arithmatic Functions i.e. ABS, LOG etc. in MLOAD Script.
•  MultiLoad doesn’t support Exponentiation and Aggregator OPERATORS i.e. AVG, SUM etc. in Mload Script.

   

MultiLoad is a very powerful utility; it has following limitations:

Following are the limitations of Teradata TPUMP UTILITY:

• Use of SELECT statement is not allowed.
•  Concatenation of Data FILES is not SUPPORTED.
•  Exponential & Aggregate Operators are not allowed.
•  Arithmatic FUNCTIONS are not supported.

   

Following are the limitations of Teradata TPUMP Utility:

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

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

Explain gives the execution strategy means what are the different steps that the query will go through.
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.

Explain gives the execution strategy means what are the different steps that the query will go through.
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.

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.

.

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.

.

YES, we can Load a table using multiple datafiles in FASTLOAD. Before GIVING "end LOADING" statement user can define file path and use insert sql for multiple source files and give "end loading" statement at the end.

Yes, we can Load a table using multiple datafiles in Fastload. Before giving "end loading" statement user can define file path and use insert sql for multiple source files and give "end loading" statement at the end.

Answer : SELECT DATABASENAME , TABLENAME , columnposition ,columnname from dbc.indices where indextype ='Q' ORDER by 1 ,2,3 ;

1. The where clause in SELECT STATEMENT does not use either primary index or secondary index.
2. SQL Statement which uses a partial value (like or not like), in the WHERE statement.
3. SQL Statement which does not contain where clause.
4. SQL statement USING RANGE in where clause. Ex. (COL1 > 40 or col1 < =10000).

1. The where clause in SELECT statement does not use either primary index or secondary index.
2. SQL Statement which uses a partial value (like or not like), in the WHERE statement.
3. SQL Statement which does not contain where clause.
4. SQL statement using range in where clause. Ex. (col1 > 40 or col1 < =10000).

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.

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.

- USER is a database with password but database cannot have password.
- Both can contain Tables, views and macros.
- Both USERS and DATABASES may or may not hold privileges.
- Only users can login, ESTABLISH a session with Teradata database and they can submit REQUESTS.

- User is a database with password but database cannot have password.
- Both can contain Tables, views and macros.
- Both users and databases may or may not hold privileges.
- Only users can login, establish a session with Teradata database and they can submit requests.

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 PERM space
• A macro that allows only input values

Stored Procedure:

Macros:

SPARSE Join Indexes are a type of Join Index which contains a WHERE clause that reduces the number of rows which WOULD otherwise be INCLUDED in the index. All types of join indexes, including SINGLE table, multitable, simple or aggregate can be sparse.

Sparse Join Indexes are a type of Join Index which contains a WHERE clause that reduces the number of rows which would otherwise be included in the index. All types of join indexes, including single table, multitable, simple or aggregate can be sparse.

1. Partition by CASE
CREATE TABLE ORDER_Table
(
ORD_number integer NOT NULL,
customer_number integer NOT NULL,
Generated by Foxit PDF Creator © Foxit SOFTWARE
http://www.foxitsoftware.com For evaluation only.
order_date date ,
order_total integer
)
PRIMARY INDEX (customer_number)
PARTITION BY case1 (
order_total < 10000 ,
order_total < 20000 ,
order_total < 30000,
NO CASE OR UNKNOWN ) ;
2. Partition by RANGE - example using date range
CREATE TABLE ORDER_Table
(
ORD_number integer NOT NULL,
customer_number integer NOT NULL,
order_date date ,
order_total integer
)
PRIMARY INDEX (customer_number)
PARTITION BY range1 (
Order_date BETWEEN date '2010-01-01' AND date '2010-12-01'
EACH interval '1' month ,
NO RANGE OR UNKNOWN);

1. Partition by CASE
CREATE TABLE ORDER_Table
(
ORD_number integer NOT NULL,
customer_number integer NOT NULL,
Generated by Foxit PDF Creator © Foxit Software
http://www.foxitsoftware.com For evaluation only.
order_date date ,
order_total integer
)
PRIMARY INDEX (customer_number)
PARTITION BY case1 (
order_total < 10000 ,
order_total < 20000 ,
order_total < 30000,
NO CASE OR UNKNOWN ) ;
2. Partition by Range - example using date range
CREATE TABLE ORDER_Table
(
ORD_number integer NOT NULL,
customer_number integer NOT NULL,
order_date date ,
order_total integer
)
PRIMARY INDEX (customer_number)
PARTITION BY range1 (
Order_date BETWEEN date '2010-01-01' AND date '2010-12-01'
EACH interval '1' month ,
NO RANGE OR UNKNOWN);

The SLEEP command specifies the amount minutes to wait before retrying to logon and establish all SESSIONS. Sleep command can be USED with all load utilities not only fastload. This situation can occur if all of the LOADER slots are used or if the NUMBER of requested sessions is not available. The default value is 6 minutes. If tenacity was SET to 2 hours and sleep 10 minutes, Sleep command will try to logon for every 10 minutes up to 2 hours duration.

The SLEEP command specifies the amount minutes to wait before retrying to logon and establish all sessions. Sleep command can be used with all load utilities not only fastload. This situation can occur if all of the loader slots are used or if the number of requested sessions is not available. The default value is 6 minutes. If tenacity was set to 2 hours and sleep 10 minutes, Sleep command will try to logon for every 10 minutes up to 2 hours duration.

There are 5 different indices present in Teradata
1. Primary Index.
a.Unique primary index.
b. NON Unique primary index.
2. Secondary Index.
a. Unique Secondary index.
b. non Unique Secondary index.
3. Partitioned Primary Index.
a. Case partition (EX. age, salary...).
b. range partition ( ex. date).
4. JOIN index.
a. Single table join index.
b. multiple table join index.
c. Sparse Join index ( CONSTRAINT applied on join index in where clause).
5. Hash index.

There are 5 different indices present in Teradata
1. Primary Index.
a.Unique primary index.
b. non Unique primary index.
2. Secondary Index.
a. Unique Secondary index.
b. non Unique Secondary index.
3. Partitioned Primary Index.
a. Case partition (ex. age, salary...).
b. range partition ( ex. date).
4. Join index.
a. Single table join index.
b. multiple table join index.
c. Sparse Join index ( constraint applied on join index in where clause).
5. Hash index.

• SET session TRANSACTION ANSI /* this is to set ANSI MODE */
• set session transaction BTET /* this is to set TERADATA transaction mode */

The BTEQ .SHOW CONTROL command DISPLAYS BTEQ SETTINGS.

The BTEQ .SHOW CONTROL command displays BTEQ settings.

while using the MLOAD of fastload if you dont want to load a PARTICULAR filed in the DATAFILE to the target then use this FILLER command to achieve this.

while using the mload of fastload if you dont want to load a particular filed in the datafile to the target then use this filler command to achieve this.

Global Temporary TABLES (GTT) -
1. When they are created, its DEFINITION goes into Data Dictionary.
2. When materialized data goes in temp space.
3. thats why, data is active upto the SESSION ends, and definition will remain there upto its not dropped using Drop table statement.
If dropped from some other session then its should be Drop table all;
4. you can collect stats on GTT.

Volatile Temporary tables (VTT) -
1. Table Definition is STORED in SYSTEM cache
2. Data is stored in spool space.
3. thats why, data and table definition both are active only upto session ends.
4. No collect stats for VTT.

Global Temporary tables (GTT) -
1. When they are created, its definition goes into Data Dictionary.
2. When materialized data goes in temp space.
3. thats why, data is active upto the session ends, and definition will remain there upto its not dropped using Drop table statement.
If dropped from some other session then its should be Drop table all;
4. you can collect stats on GTT.

Volatile Temporary tables (VTT) -
1. Table Definition is stored in System cache
2. Data is stored in spool space.
3. thats why, data and table definition both are active only upto session ends.
4. No collect stats for VTT.

Teradata will redirect the NEW INSERTED row as per its PI to the target AMP (on the basis of its row hash value), and if it find same row hash value in that AMP (hash synonyms) then it START comparing the whole row, and find out if duplicate. If its a duplicate it silently SKIPS it without throwing any ERROR.

Teradata will redirect the new inserted row as per its PI to the target AMP (on the basis of its row hash value), and if it find same row hash value in that AMP (hash synonyms) then it start comparing the whole row, and find out if duplicate. If its a duplicate it silently skips it without throwing any error.

There are 12 CODD's RULES APPLIED to the TERADATA database.

There are 12 codd's rules applied to the teradata database.

MAXIMUM 5 DML can be CODED in a MultiLoad SCRIPT.

Maximum 5 DML can be coded in a MultiLoad script.

A Logical data model is the version of a data model that represents the business requirements (entire or part) of an organization and is developed before the physical data model. A sound logical design should STREAMLINE the physical design process by clearly defining data structures and the relationships between them. A good data model is created by clearly thinking about the current and FUTURE business requirements. Logical data model includes all REQUIRED entities, attributes, KEY GROUPS, and relationships that represent business information and define business rules.

A Logical data model is the version of a data model that represents the business requirements (entire or part) of an organization and is developed before the physical data model. A sound logical design should streamline the physical design process by clearly defining data structures and the relationships between them. A good data model is created by clearly thinking about the current and future business requirements. Logical data model includes all required entities, attributes, key groups, and relationships that represent business information and define business rules.

WinCLI INTERFACE.

WinCLI interface.

YES we can CONNECT.

Yes we can connect.

When there is an error during the UPDATE PROCESS, an ENTRY is posted in the error log table. Query the log table and fix the error and RESTART the JOB.

When there is an error during the update process, an entry is posted in the error log table. Query the log table and fix the error and restart the job.

• Preliminary PHASE.
• DML phase.
• ACQUISITION phase.
• APPLICATION phase.
• END phase.

• Preliminary phase.
• DML phase.
• Acquisition phase.
• Application phase.
• End phase.

Tpump provides an alternative to MULTILOAD for low volume batch maintenance of large databases under control of a Teradata system. Tpump updates information in real time, acquiring every bit of a data from the client system with low processor utilization. It does this through a continuous feed of data into the data warehouse, rather than the TRADITIONAL batch updates. Continuous updates RESULTS in more accurate, TIMELY data. Tpump uses row hash locks than table LEVEL locks. This allows you to run queries while Tpump is running.

Tpump provides an alternative to MultiLoad for low volume batch maintenance of large databases under control of a Teradata system. Tpump updates information in real time, acquiring every bit of a data from the client system with low processor utilization. It does this through a continuous feed of data into the data warehouse, rather than the traditional batch updates. Continuous updates results in more accurate, timely data. Tpump uses row hash locks than table level locks. This allows you to run queries while Tpump is running.

SUBJECT AREA means fundamental ENTITIES that make up the major components of the business, e.g. CUSTOMER, product, employee.

Subject area means fundamental entities that make up the major components of the business, e.g. customer, product, employee.

The data STAGING area is a system that stands between the legacy systems and the analytics system, usually a data warehouse and sometimes an ODS. The data staging area is considered the "back room" portion of the data warehouse ENVIRONMENT. The data staging area is where the extract, transform and LOAD (ETL) takes place and is out of bounds for end users. Some of the functions of the data staging area include:

• Extracting data from multiple legacy systems.
• Cleansing the data, usually with a specialized tool.
• Integrating data from multiple legacy systems into a single data warehouse.
• Transforming legacy system keys into data warehouse keys, usually surrogate keys.
• Transforming disparate codes for gender, marital status, ETC., into the data warehouse standard.
• Transforming the heterogeneous legacy data structures to the data warehouse data structures.
•  Loading the VARIOUS data warehouse tables via automated jobs in a particular sequence through the bulk loader provided with the data warehouse database or a third-party bulk loader.

The data staging area is a system that stands between the legacy systems and the analytics system, usually a data warehouse and sometimes an ODS. The data staging area is considered the "back room" portion of the data warehouse environment. The data staging area is where the extract, transform and load (ETL) takes place and is out of bounds for end users. Some of the functions of the data staging area include:

Primary Key is a combination of unique and not null. It can be a collection of key values called as composite primary key. Partition Key is a just a PART of Primary Key. There are several METHODS of partition like HASH, DB2, and Random ETC. While using Hash partition we SPECIFY the Partition Key.

Primary Key is a combination of unique and not null. It can be a collection of key values called as composite primary key. Partition Key is a just a part of Primary Key. There are several methods of partition like Hash, DB2, and Random etc. While using Hash partition we specify the Partition Key.

DIMENSION table is one that describes the BUSINESS ENTITIES of an enterprise, represented as hierarchical, categorical information such as time, departments, locations, and products. Dimension tables are sometimes called lookup or reference tables. In a relational DATA modeling, for normalization purposes, country lookup, state lookup, COUNTY lookup, and city lookups are not merged as a single table. In a dimensional data modeling (star schema), these tables would be merged as a single table called LOCATION DIMENSION for performance and slicing data requirements. This location dimension helps to compare the sales in one region with another region. We may see good sales profit in one region and loss in another region. If it is a loss, the reasons for that may be a new competitor in that area, or failure of our marketing strategy etc.

Dimension table is one that describes the business entities of an enterprise, represented as hierarchical, categorical information such as time, departments, locations, and products. Dimension tables are sometimes called lookup or reference tables. In a relational data modeling, for normalization purposes, country lookup, state lookup, county lookup, and city lookups are not merged as a single table. In a dimensional data modeling (star schema), these tables would be merged as a single table called LOCATION DIMENSION for performance and slicing data requirements. This location dimension helps to compare the sales in one region with another region. We may see good sales profit in one region and loss in another region. If it is a loss, the reasons for that may be a new competitor in that area, or failure of our marketing strategy etc.