Download Teradata Vantage Administration V2 Practice Exam and more Exams Technology in PDF only on Docsity!
Question 1. Which Viewpoint portlet is most appropriate for identifying CPU‑bound queries? A) Disk I/O Utilization B) Query Performance C) System Resource Usage D) Session Activity Answer: C Explanation: The System Resource Usage portlet displays real‑time CPU consumption per AMP and helps pinpoint CPU‑bound queries. Question 2. In Viewpoint, which log type records events such as node failures and license changes? A) Console Log B) Event Log C) Diagnostic Log D) Query Log Answer: B Explanation: The Event Log captures system‑wide events like node failures, license updates, and configuration changes. Question 3. Which tool is used to monitor Teradata Parallel Transporter (TPT) jobs in real time? A) DBQL B) Viewpoint – Data Movement Portlet C) Teradata Manager D) BTEQ Answer: B
Explanation: The Data Movement portlet in Viewpoint provides status and performance metrics for TPT jobs. Question 4. When a query accesses data stored in Native Object Store (NOS), which Viewpoint portlet can be used to monitor its performance? A) Query Performance B) External Data Access C) Disk Utilization D) System Resource Usage Answer: B Explanation: The External Data Access portlet shows latency and throughput for NOS and other external sources. Question 5. Which of the following is NOT a type of Teradata system log? A) Event Log B) Console Log C) Audit Log D) Query Log Answer: D Explanation: “Query Log” is not a distinct system log; query activity is captured by DBQL or the Audit Log. Question 6. What is the primary benefit of collecting column statistics on a table with highly skewed data distribution? A) Reduces disk space usage B) Improves join elimination C) Enables the optimizer to choose better join strategies
B) SpoolUsageLog C) ResUsageLog D) QueryLog Answer: C Explanation: ResUsageLog records resource usage including spool, CPU, and I/O for each query. Question 10. In DBQL analysis, a high “CPU Skew” value indicates which condition? A) Uneven disk I/O across AMPs B) One or more AMPs consuming disproportionate CPU time C) Excessive network latency D) Insufficient spool space allocation Answer: B Explanation: CPU Skew measures the variance of CPU consumption across AMPs; a high value signals imbalance. Question 11. Which lock type is acquired when a user creates a new table in a database? A) Row Hash Lock B) Table Lock C) Database Lock D) System Lock Answer: C Explanation: Creating a table requires a Database lock to protect the metadata of the database. Question 12. What is the most common cause of a deadlock in Teradata? A) Simultaneous INSERTs on the same table B) Two sessions holding locks the other needs to proceed
C) Excessive spool usage D) Missing statistics on a column Answer: B Explanation: A deadlock occurs when two sessions each hold a lock the other requires, causing a circular wait. Question 13. Which table attribute should be used to store semi‑structured JSON data efficiently? A) VARCHAR B) CLOB C) JSON D) BLOB Answer: C Explanation: The native JSON data type provides optimized storage and querying for JSON documents. Question 14. In a multi‑temperature storage environment, which table type is best for “hot” data accessed frequently? A) NoPI Table B) Multi‑Set Table C) Base Table on Primary Disk D) Base Table on Flash Disk Answer: D Explanation: Flash Disk offers low latency, making it ideal for hot data workloads. Question 15. Which command assigns a table to a specific hash map in a multi‑hash environment?
Question 18. Which classification criterion would you use to separate reporting queries from batch loads? A) User ID B) Query Band “JobType” C) Database Name D) Query Text Pattern Answer: B Explanation: Query Band attributes like JobType allow precise classification independent of user or database. Question 19. How can a utility job (e.g., FastLoad) be given higher priority than regular user queries? A) By assigning it to a separate workload with a higher priority level B) By increasing its spool space allocation C) By disabling DBQL during its execution D) By running it on a dedicated AMP Answer: A Explanation: Defining a dedicated workload for utilities with a higher priority ensures they pre‑empt lower‑priority queries. Question 20. Which option in Workload Management controls the maximum number of concurrent sessions for a given workload? A) Session Limit B) Concurrency Control C) Max Sessions D) Throttle Limit Answer: C
Explanation: The Max Sessions parameter caps the number of simultaneous sessions for the workload. Question 21. When configuring QueryGrid to access an Oracle database, which object must be defined on the Teradata side? A) Foreign Server B) External Table C) View D) User‑Defined Function Answer: A Explanation: A Foreign Server object describes the remote system and connection details for QueryGrid. Question 22. Which of the following is a performance implication of using a Join Index? A) Increased spool usage for all queries B) Faster join processing for queries that match the index definition C) Mandatory full table scans on the base table D) Reduced need for primary index statistics Answer: B Explanation: Join Indexes pre‑compute join results, allowing matching queries to bypass costly runtime joins. Question 23. In a Teradata system, which space type is automatically reclaimed after a user session ends? A) Permanent (Perm) Space B) Temporary (Temp) Space C) Spool Space
D) System Log Answer: B Explanation: The Audit Log captures each statement, providing a complete trail of user activity. Question 27. To enable Row‑Level Security (RLS) on a table, which object must be created? A) RLS Policy B) Row Access Control (RAC) View C) Column Masking Rule D) Security Role Answer: A Explanation: An RLS Policy defines the row filters applied based on the executing user’s attributes. Question 28. Which credential storage method is recommended for secure access to NOS from Teradata? A) Plain‑text passwords in QueryBand B) Encrypted credentials in the Credential Store C) Hard‑coded keys in BTEQ scripts D) Using the default system account Answer: B Explanation: The Credential Store encrypts and centrally manages credentials for external services like NOS. Question 29. Which DBC view can be queried to monitor current spool space usage per user? A) DBC.DiskSpaceV B) DBC.ResUsageV
C) DBC.SpoolSpaceV D) DBC.UserSpaceV Answer: B Explanation: DBC.ResUsageV provides real‑time resource usage, including spool consumption per user. Question 30. After an unexpected system restart, which action is required before users can reconnect? A) Re‑run all stored procedures B) Flush the DBQL tables C) Perform a system “Reset” to clear stale locks D) Re‑initialize the Viewpoint server Answer: C Explanation: A system reset clears any orphaned locks or sessions left from the abrupt shutdown. Question 31. Which backup strategy minimizes the impact on production workloads while ensuring point‑in‑time recovery? A) Full backup daily B) Incremental backup every hour with a weekly full C) Log‑only backup every 5 minutes D) No backup; rely on Data Mover replication Answer: B Explanation: Hourly incremental backups combined with a weekly full provide near‑real‑time recovery with low overhead. Question 32. When planning future capacity, which metric is most indicative of I/O bottlenecks?
B) ALTER USER user2 SET PROXY = user C) GRANT ROLE proxy_role TO user D) SET SESSION PROXY = user Answer: A Explanation: GRANT PROXY ON USER permits one user to execute statements on behalf of another. Question 36. Which of the following is a benefit of using Profiles in Teradata? A) Automatic data compression B) Centralized resource limits (CPU, spool, etc.) for a group of users C) Encryption of all user passwords D) Real‑time query rewriting Answer: B Explanation: Profiles define resource caps and defaults, simplifying management for multiple users. Question 37. Which DBC table stores information about defined Roles? A) DBC.RolesV B) DBC.RoleInfoV C) DBC.RoleGrantsV D) DBC.AllRolesV Answer: A Explanation: DBC.RolesV lists all roles defined in the system. Question 38. What is the default behavior of the Access Logging facility when a user executes a SELECT statement? A) Logs only the statement text, not the result set
B) Logs the entire result set to the audit table C) Does not log SELECTs unless explicitly enabled D) Logs only the execution time Answer: A Explanation: Access Logging records the SQL text and execution metadata, but not the data returned. Question 39. Which security feature can restrict a user’s ability to view specific columns in a table? A) Row‑Level Security B) Column‑Level Masking C) Table‑Level Encryption D) Database‑Level Access Control Answer: B Explanation: Column‑Level Masking hides or transforms column values based on user privileges. Question 40. When configuring QueryGrid to access a Hadoop cluster, which object must be defined on the Teradata side? A) Foreign Server B) Hadoop Connector C) External Schema D) Data Lake View Answer: A Explanation: A Foreign Server describes the Hadoop endpoint and credentials for QueryGrid. Question 41. Which of the following statements about Data Mover is TRUE? A) It can only move data between Teradata systems in the same data center
A) Full Statistics on All Columns (Daily) B) Incremental Statistics on the volatile column only C) No Statistics – rely on default estimates D) Histogram Statistics on every column Answer: B Explanation: Incremental stats on the volatile column keep optimizer estimates accurate without the overhead of full collection. Question 45. Which index type can be used to improve the performance of a query that aggregates large volumes of data but does not require exact row retrieval? A) Primary Index B) Join Index C) Aggregate Index (Materialized) D) Unique Secondary Index Answer: C Explanation: Aggregate (materialized) indexes store pre‑computed aggregates, speeding up large‑scale summarization queries. Question 46. In a multi‑hash environment, what is the effect of assigning a table to a hash map that already has high load? A) Improves query parallelism B) Increases data skew and potential performance degradation C) Automatically redistributes data across all hash maps D) Triggers automatic compression of the table Answer: B Explanation: Overloading a single hash map creates skew, leading to uneven AMP workload and slower performance.
Question 47. Which command is used to collect statistics on a specific column of a table? A) COLLECT STATISTICS ON table(column) B) ANALYZE TABLE table COLUMN column C) COLLECT STATISTICS COLUMN (column) ON table D) HELP STATISTICS table(column) Answer: C Explanation: COLLECT STATISTICS COLUMN (column) ON table gathers stats for the designated column. Question 48. Which of the following is a direct consequence of setting a user’s spool space limit too low? A) The user can’t create tables B) Queries may fail with “Insufficient spool space” errors C) The user’s CPU usage is throttled D) The user loses all privileges Answer: B Explanation: Insufficient spool allocation leads to query failures when intermediate results exceed the limit. Question 49. Which classification rule would you use to route all queries submitted by the ETL user group to a low‑priority workload? A) UserID = ‘ETL%’ B) QueryBand ‘JobClass’ = ‘ETL’ C) Database = ‘ETL_DB’ D) QueryText LIKE ‘%INSERT%’ Answer: B
D) DBC.LockSessionsV Answer: B Explanation: DBC.LockLogV records lock acquisition and release events for active sessions. Question 53. Which parameter controls the amount of memory allocated to the Optimizer for a single query? A) OptMemoryLimit B) MaxOptMem C) QueryBand ‘OptMem’ D) SessionMemoryLimit Answer: B Explanation: MaxOptMem sets the upper bound of optimizer memory per query. Question 54. Which of the following is a recommended practice when creating a Primary Index on a date column for a fact table? A) Use the date column as the Primary Index to enable partition pruning B) Avoid using the date column; choose a surrogate key instead C) Create a secondary index on the date column instead of a Primary Index D) Use a hash of the date column as the Primary Index Answer: B Explanation: Date columns often have low cardinality and cause skew; a surrogate key provides uniform distribution. Question 55. Which tool can be used to automatically rebalance data after a change in the number of AMPs? A) Rebalance Utility (RBAL) B) Teradata Administrator
C) Viewpoint – System Management D) DBQL Rebalance Answer: A Explanation: The RBAL utility redistributes data to maintain even load after AMP changes. Question 56. Which of the following best describes a “Spool Space Overflow” condition? A) Permanent storage is full B) Temporary space on one or more AMPs is exhausted, causing query failure C) Disk I/O latency exceeds threshold D) CPU usage reaches 100 % Answer: B Explanation: Spool overflow occurs when an AMP cannot allocate the required temporary space for a query. Question 57. Which DBC view provides historical information about executed queries, including elapsed time and CPU usage? A) DBC.QueryLogV B) DBC.DBQLLogV C) DBC.ResUsageV D) DBC.HistQueryLogV Answer: B Explanation: DBC.DBQLLogV stores detailed historical query performance metrics. Question 58. Which of the following is a direct effect of enabling “Automatic Statistics Collection” on a table? A) Immediate rebuild of all indexes B) Periodic collection of statistics without manual intervention
