NR0-016 NCR Teradata Application Development V2R5 Exam Set 1

Updatable cursors are permitted in which two types of application implementation? (Choose two.)


Options are :

  • triggers
  • applications that use a Teradata pre-processor (Correct)
  • stored procedures (Correct)
  • macros

Answer : applications that use a Teradata pre-processor stored procedures

What are three considerations when projecting space growth for a specific database? (Choose three.)


Options are :

  • incorporating multi-value compression on existing tables (Correct)
  • new tables added for new applications (Correct)
  • spool space required for new applications
  • rows deleted from the existing tables (Correct)
  • expansion of hardware for the system
  • number of new users to be added

Answer : incorporating multi-value compression on existing tables new tables added for new applications rows deleted from the existing tables

Which three features can you use to improve overall system performance? (Choose three.)


Options are :

  • PMON
  • triggers
  • multi-statement requests (Correct)
  • multi-value compression (Correct)
  • Partitioned Primary Index (PPI) (Correct)

Answer : multi-statement requests multi-value compression Partitioned Primary Index (PPI)

Which two statements are true about the following ALTER TABLE statement. (Choose two.) ALTER TABLE Table_1 ADD CHECK (Col_a > 0 OR Col_b > 0);


Options are :

  • The constraint may be changed with the MODIFY CONSTRAINT syntax
  • The constraint is unnamed. (Correct)
  • The table must be empty.
  • A table level check constraint is place on Table_1 . (Correct)
  • Only one of the two columns may be greater than zero.

Answer : The constraint is unnamed. A table level check constraint is place on Table_1 .

What does the TSET utility do? (Choose two.)


Options are :

  • allows for the importing of workloads from other Teradata utilities
  • allows you to import data demographics from one system to another (Correct)
  • allows you to schedule statistics collection
  • allows you to import system characteristics from one system to another (Correct)
  • creates a view of your data demographics

Answer : allows you to import data demographics from one system to another allows you to import system characteristics from one system to another

What is the first stage where you make decisions to take full advantage of Teradata parallelism?


Options are :

  • design of the PDM (Correct)
  • design of the application code
  • design of the LDM
  • design of the ELDM
  • design of the OLAP

Answer : design of the PDM

What can handle an input data type of timestamp without conversion?


Options are :

  • FastLoad
  • Insert/Select (Correct)
  • MultiLoad
  • Teradata Warehouse Builder

Answer : Insert/Select

You have a 4-node Teradata system, with 40 AMPs and 6 PEs. By default, what is the maximum number of sessions that can be connected at the same time?


Options are :

  • 400
  • 720 (Correct)
  • 240
  • 960
  • 2400

Answer : 720

How are Volatile tables similar to Derived tables? (Choose three.)


Options are :

  • are designed for optimal performance (Correct)
  • have no Data Dictionary access or transaction locks (Correct)
  • are local to the query
  • table definition kept in memory (Correct)

Answer : are designed for optimal performance have no Data Dictionary access or transaction locks table definition kept in memory

Given this request where col is the UPI of table T1 : SELECT * FROM T1 WHERE col = 1 ;SELECT * FROM T1 WHERE col = 2; Which three types of parallelism does it exhibit? (Choose three.)


Options are :

  • multi-request
  • multi-AMP (Correct)
  • multi-statement (Correct)
  • multi-step (Correct)
  • multi-session

Answer : multi-AMP multi-statement multi-step

What are three circumstances when the Optimizer would choose a Product Join? (Choose three.)


Options are :

  • when the join condition is not based on equality (Correct)
  • when there are no statistics available to quantify the demographics of a column or index
  • when the workload of the system is skewed
  • when it is less costly than other join forms (Correct)
  • when the join conditions are ORed (Correct)

Answer : when the join condition is not based on equality when it is less costly than other join forms when the join conditions are ORed

What are three characteristics of derived tables? (Choose three.)


Options are :

  • Spool rows are discarded when the query finishes. (Correct)
  • They are materialized in temporary space
  • They are local to the query.
  • They may be defined with DDL. (Correct)
  • There is no Data Dictionary involvement. (Correct)

Answer : Spool rows are discarded when the query finishes. They may be defined with DDL. There is no Data Dictionary involvement.

Which three demographics are useful for index selection? (Choose three.)


Options are :

  • value access frequency (Correct)
  • join access frequency (Correct)
  • number of concurrent users
  • number of columns in a composite index
  • distinct values (Correct)

Answer : value access frequency join access frequency distinct values

What two logging options are available for database query logging in DBQL? (Choose two.)


Options are :

  • Summary logging (Correct)
  • Exception logging
  • Performance logging
  • Threshold logging (Correct)

Answer : Summary logging Threshold logging

Updatable cursors are permitted in which two types of application implementation? (Choose two.)


Options are :

  • stored procedures (Correct)
  • triggers
  • applications that use a Teradata pre-processor (Correct)
  • macros

Answer : stored procedures applications that use a Teradata pre-processor

Which lock is placed for the following SELECT statement? LOCKING prod.table_a FOR WRITE SELECT * FROM prod.table_a WHERE UPI_column = 1;


Options are :

  • We lock prod.table_a for write. (Correct)
  • We lock prod.table_a for read.
  • We lock prod.table_a for write on a Row-hash
  • We lock prod.table_a for read on a Row-hash.

Answer : We lock prod.table_a for write.

What are three characteristics of PP2? (Choose three.)


Options are :

  • supports COBOL, PL/I, C, and Pascal
  • uses indicator variables for NULL handling (Correct)
  • supports multiple sessions (Correct)
  • supports macros (Correct)

Answer : uses indicator variables for NULL handling supports multiple sessions supports macros

Which two types of journals can be defined for a table? (Choose two.)


Options are :

  • Permanent LOCAL AFTER JOURNAL (Correct)
  • Permanent DUAL BEFORE JOURNAL (Correct)
  • Permanent UPDATE ONLY JOURNAL
  • Transient LOCAL AFTER JOURNAL
  • Transient UPDATE ONLY JOURNAL

Answer : Permanent LOCAL AFTER JOURNAL Permanent DUAL BEFORE JOURNAL

What is a Bit Map Set Manipulation Step (BMSMS)?


Options are :

  • taking a single row of a table and using a secondary index value that hashes to the AMP on which the table row is stored
  • handling weakly selective secondary indexes that have been ANDed (Correct)
  • combining rows under control of a UNION, EXCEPT, MINUS, or INTERSECT operator
  • relocating data in preparation for an upcoming join step

Answer : handling weakly selective secondary indexes that have been ANDed

Which two statements are true about COLLECT STATISTICS ? (Choose two.)


Options are :

  • Statistics are automatically updated by the system.
  • Statistics allow the Optimizer to choose NUSI bit mapping. (Correct)
  • The Optimizer is more conservative when statistics are missing. (Correct)
  • Statistics must be refreshed after a reconfiguration of the system.

Answer : Statistics allow the Optimizer to choose NUSI bit mapping. The Optimizer is more conservative when statistics are missing.

Data Control Language (DCL) statements allow which three actions? (Choose three.)


Options are :

  • restricting access to database objects (Correct)
  • logging on to the client
  • logging on to Teradata (Correct)
  • holding membership in a role (Correct)

Answer : restricting access to database objects logging on to Teradata holding membership in a role

AMP memory is needed to hold the small table in which type of join strategy?


Options are :

  • Exclusion Join
  • Merge Join
  • Row-hash Join (Correct)
  • Product Join

Answer : Row-hash Join

Which two statements regarding rows per NUPI value are true? (Choose two.)


Options are :

  • All rows with the same NUPI value always end up in the same datablock.
  • A SET table with many rows for each NUPI value has much worse insert performance than a MULTISET table with many rows for each NUPI value. (Correct)
  • Adding a second column to the NUPI generally reduces the number of rows per primary index value. (Correct)
  • A large table with fewer unique NUPI values than there are AMPs in the system can still have an even distribution.

Answer : A SET table with many rows for each NUPI value has much worse insert performance than a MULTISET table with many rows for each NUPI value. Adding a second column to the NUPI generally reduces the number of rows per primary index value.

Which three statements are true about the Extended Logical Data Model (ELDM)? (Choose three.)


Options are :

  • It is the main source of information for creating the PDM. (Correct)
  • It maps applications and transactions to the related objects (Correct)
  • It provides demographics of data distribution, sizing, and access. (Correct)
  • It contains the business rules of the organization.

Answer : It is the main source of information for creating the PDM. It maps applications and transactions to the related objects It provides demographics of data distribution, sizing, and access.

In the following request, one employee has a null Manager_Number . SELECT Manager_Number FROM Department WHERE Manager Number NOT IN (SELECT Manager_Number FROM Employee); Which two statements about the result are true? (Choose two.)


Options are :

  • No rows will be returned. (Correct)
  • The condition WHERE Manager_Number IS NOT NULL should be added to the subquery. (Correct)
  • No rows returned means all Manager Number values from the Department table found a match.
  • All unmatched Manager_Number values from the Department table will be returned, if any

Answer : No rows will be returned. The condition WHERE Manager_Number IS NOT NULL should be added to the subquery.

Which two statements about the following request are true? (Choose two.) SELECT C10, C20, C30 FROM T1 RIGHT JOIN T2 ON T1.C1 = T2.C2 LEFT JOIN T3 ON T2.C2 = T3.C2;


Options are :

  • T3 is an inner table and T2 is an outer table. (Correct)
  • T1 is an inner table. (Correct)
  • T3 and T2 are outer tables.
  • T3 is an outer table.

Answer : T3 is an inner table and T2 is an outer table. T1 is an inner table.

What are three characteristics of PP2? (Choose three.)


Options are :

  • supports multiple sessions (Correct)
  • supports COBOL, PL/I, C, and Pascal
  • supports macros (Correct)
  • uses indicator variables for NULL handling (Correct)

Answer : supports multiple sessions supports macros uses indicator variables for NULL handling

Which three periodic summaries assist with capacity planning? (Choose three.)


Options are :

  • number of materialized tables
  • user logons and logoffs (Correct)
  • spool usage (Correct)
  • table growth (Correct)

Answer : user logons and logoffs spool usage table growth

Which two statements about the following request are true? (Choose two.) SELECT C10, C20, C30 FROM T1 RIGHT JOIN T2 ON T1.C1 = T2.C2 LEFT JOIN T3 ON T2.C2 = T3.C2;


Options are :

  • T3 is an inner table and T2 is an outer table. (Correct)
  • T1 is an inner table. (Correct)
  • T3 is an outer table
  • T3 and T2 are outer tables.

Answer : T3 is an inner table and T2 is an outer table. T1 is an inner table.

To ensure you are working with current, consistent data, which lock should you recommend for SELECT queries in a multi-user environment with update activity?


Options are :

  • read (Correct)
  • exclusive
  • access
  • write

Answer : read

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions