Teradata Typical Questions Set-3

Hi All,

1.Which statement about spool space is true?

A.) Spool space must be specified during creation of the user
B.) Spool space is used to hold the intermediate result set.
C.) Spool space is permanent space.
D.) Spool space is limited to the size of the table.

Answer--- B

2. Which Utility used to copy a table and restore it to another database?

A.) BTEQ
B.) Archive Recovery
C.) Fastload
D.) Table rebuild

Answer ---B

3.The Given below, which tool should you use to collect and analyze database performance?

A.) BTEQ
B.) TDQM
C.) Teradata SQL Assistant
D.) Teradata Manager

Answer --- D

4. In Which way is data distributed with a partitioned primary index?

A.) based on the secondary unique index
B.) based on the Primary key of the table
C.) Based on the Primary Index of the table
D.) based on the partitioning column .

Answer --- c

5. Batch process block level loading are performed against populated tables by which utility?

A.) Multiload
B.) bulkload
C.) Tpump
D.) Fastload

Answer-- A

6. . Teradata Index wizard used for to

A.) compare performance of multiple workloads
B.) recommend secondary indexes for a table
C.) recommend primay indexes for a table
D.) compare primay and secondary indexes for a table

Answer --B

7. Which statement is true if using NUPIs to accomplish data distribution?

A.) The increase of hash buckets has resulted in more hash collisions.
B.) Duplicate NUPI values will produce the same row hash.
C.) Uniform row distribution is accomplished using UPIs.
D.) The low-order 16 bits of the row hash are input to the hash map.

Ans -- B

8. Which is the component limits queries based on set thresholds

A.) TDQM
B.) Teradata Manager
C.) BTEQ
D.) Query Man

Answer --- A

9.How many BYNETs in a teradata system?

Well, BYNET is both a hard ware and software BYNET.
Hard ware BYNET connects every PE and AMP in the sytem . It can be scalable at any time.
Every teradata system has two BYNETS . one for backup in case of one failed

However both BYNETS can be used at same time by AMP or PES .

10. What is TENACITY? What is its default value?

TENACITY specifies the amount of time in hours, to retry to obtain a loader slot or to establish all requested sessions to logon.

The default for Fast Load is “no tenacity”, meaning that it will not retry at all.

If several FastLoad jobs are executed at the same time, we recommend setting the TENACITY to 4, meaning that the system will continue trying to logon for the number of sessions requested for up to four hours.

11. 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 indexes 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.

12.who will read and write data in teradata?

AMP works like a hard worker. AMP read and write data to their own assigned
disks.

13. Failure 7547 Target row updated by multiple source rows.

-updating the same target row by multiple source rows

-Remove the duplicate data.

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

15. Failure 2646 No more spool space

Check select query well tuned.
Collecting stats on the table may fix this problem.

16. How do you declare a variable inside a Teradata macro and Teradata stored procedure?

No facility to declare variables inside a Teradata macro, however you can pass values into the macro by means of input parameters. These values will then be embedded in the SQL statements that the macro contains.

Teradata stored procedure supports IN, OUT and INOUT parameters, as well as the ability to declare variables within the procedure itself. Such variables could be useful as counters when you have looping logic.

17. What does SLEEP function does in Fast load?

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.

18. Given below, What is the maximum level of data protection?

A.) RAID 1 and Fallback
B.) RAID 5 and Fallback
C.) RAID 5 and No Fallback
D.) RAID 1 and No Fallback

ANswer - A

19. When utilizing a unique secondary index, how many AMP(s) are most probably accessed?

A.) 1 AMP
B.) Multi_AMP
C.) 2 AMP
D.) All-AMP

Answer --- c

20. Difference between count(*) and count(1) ?

Count is one of very important functions used in any database .

But what many dont know that is the result count we get from COUNT function might be different based on how it is used.

Let us consider the example of count(*) and Count(1) and understand how there is some difference the usage.

Say for a given table with data

X y
------------
21 31
Null 32
Null 33
41 34
Null Null

And this query:

select count(*), count(x), count(1) from table;

Results: 5, 2, 4

* Count(*) counts all occurrences including nulls. This is a row count for a given table.
* Count(x) counts all non null occurrences of values in column x. If x has null values, count(x) will be less than count(*).
* Count(1) is similar to count(x) .It counts all null values in a "pseudo column" created by the constant. Since the constant 1 will never be null, this would, be like count(*) i.e row count .

21.What is use of compress in teradata?Explain?

Compression is used to Minimize the table size, for example when the table size is increasing anonymously We can use Compression to reduce the size of the table.

Conditions:
1.Compression can be declared at the time of table creation
2.We can compress up to 256 column values(not columns)
3.We can't compress variable length fields (vartext,varchar..)

22. What is the difference between start schema and Fsldm?

FSLDM --> Financial Services Logical Data Model (Teradata FS-LDM 7.0) its developed by Teradata for the financial sectors (Specifically for Bank). it can be customised based on the user requirement.

StarSchema --> Its a relational database schema for representing multidimensional data. The data is stored in a central fact table, with one or more tables holding information on each dimension.

23. Teradata performance tuning and optimization steps?


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.

24. Explain command displays the execution plan of the query chosen by the Teradata Optimizer

a) False b) True

Answer: b

25. Success/Failure/Error responses from Teradata are received by

a) Request Parcel b) Data parcel c) Response Parcel

Answer: c

26. Teradata Database facilities for software fault tolerance on

a) Vproc Migration & Fallback tables b) AMP Clusters & Journaling

c) Backup/Archive/Restore d) Table Rebuild Utility e) All of the above

f) None of the above

Answer: e

27.Total Number of nodes suppport by BYNET are

a)510 SMP Nodes a) 512 SMP Nodes b) 513 SMP Nodes c) 514 SMP Nodes

Answer: b

28.SMP Stands for

a) Symmetric Multi-processor b) Symmetric Massive-Processor

c) System Multi-processor d) Symmetric Multi-Processor

Answer: a

29.Terabytes represent

a) 10 power 15 b) 10 power 12 c) 10 power 18 d) 10 power 24

Answer: b

30.Global Temporary Tables are stored in the TD are

a) Perm Space b) Spool Space c) Temp Space

Answer : c

31.What are the following object required for Teradata Database-Prem space?.

a) Tables & Stored Procedure b) Tables & Views

c) Macros & Triggers d) Views & Stored Procedures

Answer: a.

32. HASHROW funtions is

a. Returns the hash bucket number that corresponds to a specified row hash value.

b. Returns the hexadecimal row hash value for an expression

c. Returns the AMP number of the primary AMP corresponding to the specified hash bucket number.

d. Returns the identification number of the fallback AMP corresponding to the specified hash bucket.

Answer: b

33.Teradata—A Brief History ?

• 1979 — Teradata Corp founded in Los Angeles, California.

Development begins on a massively parallel database computer.

• 1982 — BYNET technology is patented.
• 1984 — Teradata sells first DBC/1012 to Wells Fargo Bank of California. Total revenue for year – $3 million.
• 1986 — Product of the Year!
• 1989 — Teradata and NCR partner on next generation of DBC.
• 1990 — First Terabyte system installed and in production.
• 1991 — NCR is acquired by AT&T. Teradata revenue $280M.
• 1992 — Teradata is merged into NCR.
• 1995 — Teradata Version 2 for UNIX operating systems released.
• 1996 — NCR only vendor to run multi-user TPC-D Benchmarks.
• 1996 — AT&T spins off NCR Corporation with Teradata product.
• 1997 — Teradata database becomes industry leader in scalable data warehousing.
• 1997 — 100 Gigabyte TPC-D Benchmark leader.
• 1998 — 24 Terabyte system in production.
• 1999 — 1 TB TPC-D winner
• 2000 — 50+ TB system in production! Teradata Division created.

VN:F [1.9.8_1114]

34.What are the functions of a Tera data DBA?

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.

35. what is Full table scan (FTS) ?

What are the scenarios in which Full Table Scans occurs?

A Full Table Scan (FTS) is a query that reads every row of a table.

Scenarios Full table scan occurs

1. 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)

36. How to identify PPI columns?

Select databasename , tablename , columnposition ,columnname from dbc.indexes

where indextype ='Q'

order by 1 ,2,3 ;

37.Can you create a table with no Primary Index in teradata?

NO. Its not possible.... if you dont mention external primary index, teradata creates one for you.

Teradata uses this Primary index to distribute data among AMPs.

38.In case of NUSI, does AMP has local subtable?

Yes. In case of NUSI, AMP is going to have a subtable

which has only information for the rows in the same AMP.

But in case of USI, Each row in AMP is hashed and decide AMP number

to store its information in Secondary index Sub table.

39. How long ROW ID is?

64 Bits...

AMP receives 32 bit hash value and row data from PE. AMP then append a 32 bit unique value to row HASH to make 64 bit ROW ID.

ROW ID is used to sort rows in AMP

First AMP checks is there any row with same HASH value? If yes, then it append32 bit 1 to (0000 0000 0000 0000 0000 0000 0000 0001) to row hash and make it 64 bit ROW ID... If one more row comes with same hash value then it appends 2( 0000 0000 0000 0000 0000 0000 0000 0010) to Row HASH.

40.Can a user creates any object who assigned no perm space?

A user with No permspace can't create any tables in his user space.

But He can create Macro, Views and triggers.

Because these objects will be stored in Global Space .

41. When a user query fails because of spool space?

Query may fails because of spool space in two cases

When there is no empty perm space. i.e complete system is full.

User query requires more spool space than user spool space limit .

42.Does AMP will do row by row search once it has Hash value to retrieve a row?

AMP receives a hash value from PE for which data is required.

AMP applies a Binary Search to find that row.... Its never do linear search.

43.A Table has UPI on EMP no. I wrote a query in which i used deptno in emp table?

How many amps gets this data?

Even though table has Primary Index ,

my SQL query written on

dept number on which there is not index.... So its a Full table scan.

44.While Multiload is running client system restarted?

We need to manually resubmit the script so that it runs from Last configuration check point.

45.While Executing Multiload script teradata server restarted?

Along with server multiload script restarted and Runs from last checkpoint.

46.Can we concatenate Multiple files with same structure and load in multiple tables?

NO ,only in fastload its possible.

47.How do you load data from a file to multiple tables MLOAD?

In this apply class we use where conditions while loading data into multiple tables.

Apply Ins_table where Recind = ‘T1’

Apply Ins_table where Recind = ‘T2’

48.If Multiload is fail in Acquistion or Application phase how do you restart?

a.Incase of old file to Run

Dont drop ET,UV,WT and log tables

Dont Release locks on target tables

Rectify errors in the File or script and Run again.

b.Incase of new file to Run

Drop ET,UV,WT and log table.

Release locks on target tables by using Release MLOAD command.

RELEASE MLOAD Table1, ...Table 5

RELEASE MLOAD Table1, ...Table 5 IN APPLY

49.Terdata Data Dictonary Directory explain?

The DD/D ( Data Dictionary Directory)

• is an integrated set of system tables
• contains definitions of and information about all objects in the system
• is entirely maintained by the RDBMS
• is “data about the data” or “metadata”
• is distributed across all AMPs like all tables
• may be queried by administrators or support staff
• is accessed via Teradata supplied views

Examples of DD/D views:

DBC.Tables —> information about all tables
DBC.Users —> information about all users
DBC.AllRights —> information about access rights
DBC.AllSpace —> information about space utilization.

50.Can I use drop statement in Fload utility?

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

In the FLOAD Block we can give only INSERT