TDVAN5 Practice Questions

Teradata Vantage allows for the integration of user authorization with an external directory service, such as LDAP or Active Directory. This is accomplished by creating a database role and mapping it to a specific group within the directory using the AS EXTERNAL NAME clause. When a user who is a member of that external group authenticates to Vantage, they are automatically granted the permissions of the corresponding database role for the duration of their session. This feature centralizes user authorization management within the directory, simplifying administration.

A. The directory username must match a database username.

This is incorrect. A Teradata database user is mapped to an external user's Distinguished Name (DN), and the database username does not need to match the common name (cn) or user ID in the directory.

B. Single Sign-On is available with LDAP authentication.

This is misleading. Teradata's primary mechanism for Single Sign-On (SSO) is Kerberos. LDAP authentication in Vantage is a challenge-response mechanism, not an SSO implementation.

D. External authentication is not permitted for mainframe clients.

This is incorrect. Mainframe-attached clients can use supported external authentication mechanisms, such as Kerberos, to connect to Teradata Vantage. There is no general prohibition.

1. For Correct Answer (C) and Incorrect (A):

Teradata Vantage™ - Security Administration, Release 17.20, B035-1100-172K, December 2022.

Chapter 11: Directory-Based Authorization, "Overview of Directory Authorization" (Page 233): "You can associate a Teradata Database role with an external group defined in a directory... When a user who is a member of the external group logs on to the Teradata Database, the user is automatically granted membership in the associated database role for the duration of the logon session."

Chapter 10: Directory-Based Authentication, "Creating a User with an External Authentication" (Page 222): The CREATE USER syntax example shows CREATE USER useracct AS EXTERNAL NAME 'cn=accounting,ou=finance,o=teradata,c=us' ...;. This demonstrates that the database username (useracct) can be different from the directory common name (accounting).

2. For Incorrect Answer (B):

Teradata Vantage™ - Security Administration, Release 17.20, B035-1100-172K, December 2022.

Chapter 8: Single Sign-On and Kerberos Security, "Overview of Kerberos Authentication" (Page 153): This chapter details the use of Kerberos for SSO.

Chapter 10: Directory-Based Authentication, "Overview of Directory Authentication" (Page 221): "LDAP authentication is a challenge-response authentication mechanism that allows the Teradata Database to authenticate users against a central directory server." This description contrasts with the ticket-based SSO mechanism of Kerberos.

3. For Incorrect Answer (D):

Teradata® Call-Level Interface Version 2 Reference for Mainframe-Attached Systems, Release 17.10, B035-2417-171K, November 2020.

Chapter 2: DBCAREA and Function Arguments, "Logon String" (Page 61): The documentation lists MECHANISM 'KRB5' as a valid option for the logon string, confirming that mainframe clients can use Kerberos, a form of external authentication.

In Teradata Vantage, the AUTHORIZATION object is the designated mechanism for storing credentials used to access external systems, including Native Object Stores (NOS). To facilitate shared access, an administrator creates a single AUTHORIZATION object containing the required credentials. Subsequently, the administrator grants the USAGE privilege on this specific AUTHORIZATION object to the relevant end-users or roles. When these users query a foreign table that references this AUTHORIZATION object, Teradata Vantage uses the stored, shared credentials to access the data in the object store. This centralizes credential management and provides a secure method for shared access.

A. Define a FOREIGN TABLE with DEFINER TRUSTED security.

A FOREIGN TABLE references an AUTHORIZATION object for credentials. The table definition itself does not store or share the credentials; the AUTHORIZATION object does.

B. Define a FUNCTION MAPPING with INVOKER TRUSTED security.

FUNCTION MAPPING is not the standard object for this purpose. Furthermore, INVOKER security uses the end-user's credentials, which contradicts the requirement for shared credentials.

C. Use an S3 IAM role or Azure SAS token rather than User and Password.

This describes types of credentials that can be stored in an AUTHORIZATION object but does not explain the Teradata mechanism for sharing them among users.

1. Teradata Vantage™ - Native Object Store Getting Started Guide, B035-1214-062K, Chapter 2: Getting Started with Native Object Store, Section: Authorization Objects. The documentation states, "The AUTHORIZATION object stores the credentials that Vantage uses to access the object store... To use an authorization object that you do not own, you must have the USAGE privilege on the object."

2. Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144-162K, Chapter 4: Data Definition Statements, Section: CREATE AUTHORIZATION. This section details the syntax for creating the object that holds credentials for external servers.

3. Teradata Vantage™ SQL Data Control Language, B035-1149-162K, Chapter 2: Grant and Revoke, Section: GRANT (Object Privilege Form). This document specifies that the USAGE ON AUTHORIZATION privilege allows a user to use the specified authorization object to access external data. This is the mechanism for sharing.

This action is appropriate for a scenario where table corruption is localized to a single Access Module Processor (AMP), often due to a hardware issue like a disk failure. In such cases, the system may take the faulty AMP offline to isolate the problem. The administrator's subsequent task is to recover the data on that AMP. For tables without Fallback protection, this involves restoring the affected table from the last known good backup. This targeted approach is preferred as it minimizes system downtime and impact compared to a full system restore, adhering to the principle of least impact for recovery.

A. Perform a SYSINIT, and restore the corrupted table.

SYSINIT reinitializes the entire system, deleting all user data and resetting the DBC data dictionary. Restoring a single table into a fresh system is not a valid recovery path.

B. Set the corrupted AMP offline, and restore the entire system.

Restoring the entire system is an excessive and unnecessary action if the corruption is isolated to a single AMP. This contradicts the principle of targeted and efficient recovery.

D. Perform a SYSINIT, and restore the entire system.

This is a last-resort disaster recovery procedure for catastrophic, system-wide DBC corruption that cannot be repaired by other means. It is not the standard first action for a single corrupted table.

1. Teradata Vantage™ - Database Administration, Release 17.20 (B035-1093-172K)

Chapter 10: Recovery and Restart, Section: "AMP Failure": This section details the process when an AMP fails. The system brings the vprocs for the failed AMP offline. The recovery procedure involves either using the Table Rebuild utility (for Fallback tables) or restoring the affected non-Fallback tables from a backup. This supports the sequence of an AMP going offline followed by a table-level recovery action, as described in option C.

Chapter 12: Database Utilities, Section: "SYSINIT": The documentation states, "Reinitialization deletes all user data and returns the Data Dictionary to its initial state." This confirms that SYSINIT is a drastic measure used for complete system reinitialization, making option D an extreme last resort, not a standard procedure for single-table corruption.

2. Teradata Vantage™ - Archive/Recovery Utility Reference, Release 17.20 (B035-1149-172K)

Chapter 2: ARCHIVE/RECOVERY (ARC) Mainframe, Section: "RESTORE": This section describes the syntax for restoring specific database objects, including individual tables. It confirms that restoring a single table is a valid operation, which is a key part of the correct answer. The context of an AMP failure dictates why this specific restore would be necessary.

When using Teradata QueryGrid with pushdown processing to a foreign server like Hadoop/Hive, temporary space is consumed on both the Vantage system and the foreign system. On the Vantage side, intermediate or final result sets returned from the foreign server consume the user's spool space. On the Hadoop/Hive side, complex operations that are pushed down may require the creation of temporary tables. The location for these temporary tables is specified by the temporary database name property within the QueryGrid link definition. An administrator concerned about performance and space consumption must investigate both of these aspects to manage resources effectively.

A. Perm space assigned to the user executing the QueryGrid query

Perm space is used for storing permanent database objects like tables, not for temporary data generated during query execution.

B. The default temporary NOS location defined on the connector

NOS (Native Object Store) locations are for reading data from object stores like S3 or ADLS. This is not the primary mechanism for managing temporary tables on a Hadoop cluster for Hive pushdown processing.

D. Temp space assigned to the Teradata user defined in the authorization object

Temp space in Teradata is reserved for Global Temporary Tables (GTTs). Standard query processing and intermediate results use spool space, making spool the relevant resource to investigate, not temp space.

---

1. Teradata® QueryGrid™ Installation and User Guide (Release 17.10, B035-5800-0122K)

Section: "Link Properties" (Chapter 3: Using QueryGrid). This section details the configuration properties for connectors. For connectors that support pushdown, it describes the TEMPORARYDATABASE or equivalent property. It states, "For connectors that support pushdown of extended SQL, you can specify a remote database or schema for QueryGrid to create temporary tables." This confirms the need to investigate the temporary database name on the link.

Section: "QueryGrid Processing" (Chapter 1: Introduction to Teradata QueryGrid). The documentation explains that when results are returned to Vantage or when parts of the query cannot be pushed down, the data is processed on the AMPs and consumes the user's spool space. This validates the "spool space" part of the answer.

2. Teradata Vantage™ Database Administration (Release 17.10, B035-1093-171K)

Chapter 12: Managing Space. This chapter defines the different types of space. Specifically, it describes Spool Space as "a temporary workspace for the results of SQL queries" and distinguishes it from Perm Space (for permanent data) and Temp Space (for global temporary tables), confirming that spool is the correct space to consider for query execution.

Batch-level functions, such as data loading with Teradata Parallel Transporter (TPT) or legacy utilities, frequently require the creation of temporary work, error, or staging tables within the target database. The CREATE TABLE privilege is essential for these operations. In Teradata, the best practice for managing permissions is to bundle privileges into roles and then grant those roles to users. By setting the user's default role to one that possesses the CREATE TABLE privilege on the TablesDatabase, the administrator correctly and efficiently enables the user to perform the required batch tasks.

A. The CHECKPOINT privilege is specific to the FastLoad utility for restart capabilities and is insufficient by itself for most batch operations, which also require DDL and DML privileges.

C. Profiles in Teradata are used to manage system resources like spool space, temporary space, and password policies, not for granting object access privileges like CREATE TABLE.

D. Object access privileges cannot be granted to a profile. Privileges are granted directly to users or, more commonly, to roles, which are then assigned to users.

1. Teradata Vantage™ - Security Administration, Release 17.20, B035-1100-2109K, Chapter 4: Managing Access Rights, Section: About Roles: "A role is a database object that is a collection of privileges. You can grant a role to a user or to another role... Using roles simplifies security administration." This supports using roles to manage collections of privileges.

2. Teradata Vantage™ - Security Administration, Release 17.20, B035-1100-2109K, Chapter 3: Managing User Accounts, Section: About Profiles: "A profile is a named object that contains a set of session- and resource-related attributes that you can assign to one or more users... For example, you can use a profile to specify... Spool space limit... Password controls." This confirms that profiles manage resources, not object privileges, making options C and D incorrect.

3. Teradata® Tools and Utilities Access Module Reference, Release 17.10, B035-2425-021K, Chapter 2: Access Module Privileges, Section: Teradata PT Load Operator: This section details the privileges required for loading data, which include INSERT on the target table and CREATE TABLE and DROP TABLE on the database where error tables are created. This confirms the necessity of the CREATE TABLE privilege for batch functions.

A Teradata profile is a database object that defines a set of attributes that can be assigned to multiple users. This simplifies administration by allowing common settings to be managed in one place. One of the key attributes that can be defined within a profile is the SPOOL space limit. This allows an administrator to set a consistent spool space allocation for a group of users who have similar query processing needs, rather than defining it individually for each user.

A. Reducing the growth of the DBC.AccessRights table: This benefit is achieved by using roles to grant privileges to groups of users, not by using profiles.

B. Managing user privileges on database objects: This is the primary function of roles. Profiles manage user environment attributes, not object access rights.

C. Assigning permanent space for groups of users with similar needs: Permanent space (PERM) is an attribute of a user or database and is assigned directly, not through a profile.

1. Teradata Vantage™ SQL Data Definition Language, Release 17.20, B035-1184-172K, December 2022.

Section: "Chapter 2: Data Dictionary Objects > Profiles > Purpose of a Profile". This section states, "A profile is a set of attributes that can be assigned to one or more users... For example, you can use a profile to specify a common SPOOL space limit for a group of users."

Section: "Chapter 6: SQL Data Definition Language Statements > CREATE PROFILE". The syntax for the CREATE PROFILE statement explicitly includes the SPOOL = integer | decimal clause, confirming that spool space is a definable attribute. It does not include a PERM space clause.

2. Teradata Vantage™ - Database Administration, Release 17.20, B035-1093-172K, December 2022.

Section: "Chapter 10: Setting Up Users > About User Definitions > Profiles". This chapter explains, "A profile is a named object that defines limits for spool space... and other user attributes. You can assign a profile to one or more users to simplify the management of these attributes." It clearly distinguishes this from roles, which manage privileges.

The ResUsageSawt (Scheduler Activity by Workload Type) table is specifically designed to monitor the activity of the Teradata workload management scheduler. It provides aggregated data at the workload level for specific time intervals. To confirm if queries are being throttled, an administrator would analyze the DelayTime and DelayCount columns in this table. These columns directly measure the total time and the number of requests that were held in a delay queue due to system or workload throttles, providing clear evidence of throttling's impact on the workload.

A. ResUsageSps: This table tracks resource usage (CPU, I/O) by workload, but it does not contain specific metrics like delay counts or times to confirm throttling activity.

C. DBQLogTbl: While this table has a DelayTime column for each individual query, ResUsageSawt is superior for analyzing the overall workload trend, as it provides aggregated delay statistics over time.

D. DBQLStepTbl: This table logs performance data for each step of a query after it has begun execution. Throttling occurs before the first step starts, so this table would not contain that information.

1. Teradata Vantage™ - Resource Usage Macros and Tables, Release 17.20, B035-1149-1121K, Chapter 3: Resource Usage Tables, Section: "ResUsageSawt".

Page 111: The document states, "The ResUsageSawt table logs scheduler activity by workload type... Use ResUsageSawt data to... Determine how often and how long requests are delayed by workload management throttles." This directly supports using ResUsageSawt to investigate throttling. The table definition on page 112 explicitly lists DelayTime and DelayCount as columns.

2. Teradata Vantage™ - Database Administration, Release 17.20, B035-1093-1121K, Chapter 13: Using DBQL to Monitor Query Performance, Section: "DBQLogTbl".

Page 428: This section describes the DelayTime column in DBQLogTbl as "Time in seconds the query was delayed by Teradata WM." This confirms that DBQLogTbl contains delay information, but it is on a per-query basis, making ResUsageSawt the more appropriate tool for workload-level analysis as implied by the question.

3. Teradata Vantage™ - Resource Usage Macros and Tables, Release 17.20, B035-1149-1121K, Chapter 3: Resource Usage Tables, Section: "ResUsageSps".

Page 115: The description for ResUsageSps states it "logs resource usage by priority scheduler partitions." The column definitions include metrics like CPUUServ and IOUnclass, confirming it tracks resource consumption, not scheduler delays.

The scenario describes tactical queries joining a few small tables on their primary index (PI). The most effective optimization for this workload is to eliminate all data movement and communication between AMPs during query execution.

Assigning the tables to TD1AmpSparseMap1Node forces all rows of these tables onto a single AMP. Using the same colocation name for all tables is crucial; it informs the Teradata Optimizer that rows with the same PI value across these tables are physically stored together on that single AMP. This allows for a direct, highly efficient local join on one AMP, completely avoiding data redistribution steps and network overhead, which is ideal for the sub-second response times required by tactical queries.

A. Using different colocation names would prevent the optimizer from recognizing that the tables are co-located, negating the benefit of the single-AMP map for PI joins.

C. TDMap1 is a contiguous map that distributes data across a subset of AMPs. This is less optimal than a single-AMP map, as it may still require inter-AMP communication for joins.

D. This option combines a suboptimal map (TDMap1) with incorrect colocation (different names), resulting in an inefficient data layout for the described queries.

1. Teradata Vantage™ - Database Design, 17.20 (B035-1094-172K), Chapter 10: Data Distribution and Maps, Section: "Sparse Maps". This section states, "A sparse map can be used to place a very small table on one or a few AMPs... This is useful for placing small, related tables that are frequently joined on a single AMP to optimize tactical queries."

2. Teradata Vantage™ - Database Design, 17.20 (B035-1094-172K), Chapter 10: Data Distribution and Maps, Section: "Colocating Tables". This section explains, "To colocate tables, you must assign them to the same map and specify the same colocation name... Colocation assures the Optimizer that rows from each table with the same primary index value are on the same AMP."

3. Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, 17.20 (B035-1144-172K), Chapter 2: CREATE TABLE, Section: "Table-level Attributes". The syntax MAP = mapname, COLOCATE USING colocationname is detailed, showing how to implement this strategy. The documentation notes that colocation is a performance feature for PI-based joins.

Workload Designer: This portlet is the interface for Teradata Workload Management (TASM/TIWM). Administrators use it to define Rulesets and Exceptions. An "exception" is a specific workload event (e.g., a query running too long) that can be configured to trigger an alert.

Server Management: This portlet provides a view of the physical and virtual infrastructure (CMICs, nodes). It specifically handles Server Management summary alerts related to hardware or OS-level health.

Performance Data Collection: This portlet configures which data metrics are harvested from the system. Specific data collectors include "Job" (e.g., for specific job metrics) and "Database Space" (for perm/spool space usage). Enabling these collectors is the prerequisite "configuration" required to trigger alerts based on these metrics.

Monitored Systems: By elimination and functional hierarchy, this matches the general definition of adding alerts to a system. The Monitored Systems portlet is where systems are added to Viewpoint, and it serves as the foundational setup where alert connectivity and system-level monitoring rules are initialized (often linking to the Alert Setup service).

Teradata Viewpoint User Guide, "Administration Portlets" Section:

Reference for Workload Designer: Describes configuring exceptions within a workload management ruleset to trigger actions such as alerts.

Reference for Performance Data Collection: Lists distinct data collectors including "Database Space" and "Job", which must be enabled/configured to monitor and alert on those specific areas.

Reference for Server Management: details the "Server Management" portlet and its "Summary" views for hardware alerts.

Teradata Official Documentation (Docs.teradata.com): "Workload Designer Portlet" – "Exceptions allow you to define rules... and actions such as Send an Alert."

When a profile assigned to a user is dropped, the Profile attribute for that user in the DBC.UsersV view is set to NULL. Upon the user's next logon, the system applies the system-level defaults for any attributes that would have been controlled by the profile. This includes password attributes such as expiration, complexity, and lockout rules, which are defined in the DBC.SysSecDefaults table and are visible through the DBC.SecurityDefaultsV view. The logon process will succeed, but with these system-wide security settings enforced.

A. While the user's profile setting in the data dictionary does become NULL, this is the cause, not the full effect. The question asks what happens when the user logs on, and this option only describes the state change, not the resulting behavior.

C. The system does not issue a warning message in this scenario. It transparently applies the system-level default settings for the session without notifying the user of the change.

D. Once a profile is dropped, it ceases to exist, and none of its specifications, including SPOOL or DEFAULT DATABASE, can be applied. The user will revert to their own defined attributes from the CREATE/MODIFY USER statement or system defaults.

1. Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, Release 17.20 (B035-1144-172K).

Section: "DROP PROFILE"

Content: "When you drop a profile, any user or profile that was assigned the dropped profile is reassigned to have no profile (the value of the Profile column in DBC.Users is NULL)." This supports the initial state change that leads to the behavior described in the correct answer.

2. Teradata Vantage™ - Security Administration, Release 17.20 (B035-1100-172K).

Chapter 6: "Managing User Passwords", Section: "Password Controls Precedence"

Content: This section explains that if a user is not assigned a profile (i.e., the profile is NULL), the system-level password controls defined in DBC.SysSecDefaults apply. This directly validates that the attributes from DBC.SecurityDefaultsV (the view on DBC.SysSecDefaults) will be used.