TeraData TDVAN5 Exam Practice Test Instant Access

Question 1

A web application executes millions of tactical queries on different tables of a large Vantage system, as shown below:

The most frequent query of the application is using the following SQL with this variable parameter:

The application owner requested to check for optimizations to improve the runtime of the query.

What should the Administrator suggest in this situation?

ACreate a sparse join index on Receiptline, with CustomerNumber as an input parameter.

BCreate a NUSI on Receiptline on each of the columns: ProductTypeId, OrderTypeId, VendorId.

CALTER the tables ProductType and OrderType to use a sparse map that includes only a subset of the AMPs.

DInstead of directly accessing table Receiptline. a global temporary table should be used, that needs to contain prefiltered rows with CustomerNumber = .

Answer : A

A sparse join index can be used to store a subset of rows from a table based on the condition of the most frequently queried parameter, in this case, CustomerNumber. By creating a sparse join index on the Receiptline table with CustomerNumber as an input parameter, the query can access a smaller subset of the data more efficiently, which can significantly improve performance, especially when millions of tactical queries are involved.

The other options are less optimal for this situation:

Create a NUSI on Receiptline: While NUSIs can improve query performance, creating NUSIs on multiple columns (e.g., ProductTypeId, OrderTypeId, VendorId) may not be as effective for improving this specific query focused on CustomerNumber.

ALTER tables ProductType and OrderType: Using a sparse map for these small tables (55 and 175 rows) wouldn't provide much benefit in terms of performance improvement, as the issue is not with these tables but with the larger Receiptline table.

Use a global temporary table: While prefiltering data in a temporary table could help in certain situations, this adds complexity and maintenance overhead. Additionally, it wouldn't necessarily offer a significant performance boost compared to a sparse join index.

Question 2

The Administrator has received a request to add SELECT rights on the BusinessViews database to end users, developers, and batch accounts in the accounting unit. The following roles are set up for each group:

The Administrator created the AcctShared role and will use it in a role nesting strategy to provide the required access.

Which actions can the Administrator take to fulfill this request?

AGrant SELECT on AcctShared to BusinessViews, then grant AcctUsers, AcctDev, and AcctBatch to AcctShared.

BGrant SELECT on BusinessViews to AcctShared, then grant AcctUsers, AcctDev, and AcctBatch to AcctShared.

CGrant SELECT on BusinessViews to AcctShared, then grant AcctShared to AcctUsers. AcctDev, and AcctBatch.

DGrant SELECT on AcctShared to BusinessViews, then grant AcctShared to AcctUsers. AcctDev, and AcctBatch.

Answer : C

The AcctShared role should be granted SELECT access on the BusinessViews database. This ensures that the role itself has the necessary privileges.

Then, you can nest this role by granting AcctShared to the individual roles of AcctUsers, AcctDev, and AcctBatch. This role nesting strategy allows the users in these groups to inherit the permissions from AcctShared without having to directly grant the privileges to each individual role.

This approach maintains a clean and efficient permission structure using role nesting.

Question 3

A capacity planner wants to keep a record of the number of rows that are added and deleted from certain tables over time and would like to obtain this information without having to change the application itself.

Which DBQL option should be enabled?

AUSECOUNT

BEXPLAIN

COBJECTS

DVERBOSE XMLPLAN

Answer : C

The OBJECTS option in DBQL (Database Query Logging) records the tables and other objects that are accessed by queries, including information on how many rows are added, updated, or deleted. This allows the capacity planner to track changes to specific tables without modifying the application itself.

The other options are less relevant to tracking row changes:

USECOUNT records how often specific queries are executed, but not the number of rows affected.

EXPLAIN captures the query execution plan, which doesn't provide details on rows added or deleted.

VERBOSE XMLPLAN gives detailed execution plans in XML format, but it is more focused on query execution and optimization, not tracking row modifications.

Question 4

A system in Viewpoint is regularly reported as being in a critical state due to a lack of available AWT. No flow control is observed on the system. The Administrator identified that this is due to a recently completed cloud migration for the system that increased the number of available AWT from 80 to 120.

Which process task is required to set up the system in Viewpoint to address this problem?

AConfigure the AWT Info data collector with the updated setting of 120 maximum AWT.

BIncrease by 40 the degraded and critical thresholds for the AWT in the system health setup portlet.

CUpdate the performance data collection portlet job that collects resource usage data with the 120 maximum AWT value.

DAdjust the system alert that has been configured for AWT to the recommended critical threshold of 92.

Answer : B

After the cloud migration increased the number of available AWTs from 80 to 120, the thresholds for critical and degraded states in Viewpoint are likely still based on the old maximum of 80 AWTs. Since the system is now falsely reporting critical states due to this change, the thresholds need to be updated to reflect the new maximum of 120 AWTs. Increasing the degraded and critical thresholds by 40 (to account for the additional AWTs) will prevent unnecessary critical alerts.

Question 5

An Administrator is required to load sensor data from cloud storage. This store contains many numeric columns.

Which strategy should the Administrator use to optimize the data load?

AUse a more generic LOCATION string in the USING clause.

BKeep the original VARCHAR data types in JSON or CSV for columns.

CCast column data types to appropriate values in a view of the foreign table.

DCollect statistics on all payload attributes.

Answer : C

When loading sensor data, especially if it involves many numeric columns, casting the data to appropriate types (like INT, FLOAT, etc.) in a view can significantly improve query performance and reduce storage overhead. This approach ensures that the numeric data is handled efficiently, avoiding unnecessary conversions or using inefficient data types such as VARCHAR.

Question 6

The Administrator has just created a new database object on a Vantage system.

Which privilege is NOT automatically granted to that database on itself?

ACREATE AUTHORIZATION

BCREATE MACRO

CCREATE PROCEDURE

DCREATE TRIGGER

Answer : A

When a new database object is created in Teradata Vantage, certain object creation privileges (such as CREATE MACRO, CREATE PROCEDURE, and CREATE TRIGGER) are typically granted automatically to the database or user who created the object.

However, the CREATE AUTHORIZATION privilege, which allows a user to create an authorization object (used for external authentication to external systems, such as a cloud storage service), is not automatically granted. This privilege requires an explicit grant.

Question 7

Users are complaining that queries in their workload are taking too long. The Administrator suspects that workload management may be throttling the queries.

Which table should be analyzed to confirm this suspicion?

AResUsageSps

BResUsageSawt

CDBQLogTbl

DDBQLStepTbl

Answer : B

The ResUsageSawt table captures information related to AMP Worker Task (AWT) usage and workload throttling. If queries are being throttled due to workload management, this table will provide relevant data, including details about tasks that may be waiting due to AWT limits or other resource constraints.

Option A (ResUsageSps) focuses on stored procedure statistics and may not provide direct insight into query throttling.

Option C (DBQLogTbl) logs query-level statistics but does not directly capture information about throttling due to workload management.

Option D (DBQLStepTbl) contains detailed information about query execution steps but, like DBQLogTbl, it does not provide information about throttling.

TeraData TDVAN5 Vantage Administration Exam Practice Test