TDVAN5 Sample Questions & Answers

False. A user profile is a powerful object for managing groups of users. It can be used to set numerous attributes, including password controls (complexity, history, expiration) and session settings like SPOOL, TEMPORARY space, and DEFAULT DATABASE. Assigning a default database via a profile is a common administrative practice.

The USING THRESHOLD options are specifically designed for collecting detailed statistics on columns with skewed data distributions. This method creates a more detailed histogram, storing information about the most frequent values separately from the rest of the data. This provides the optimizer with the precise information it needs to make better cardinality estimates and choose an optimal join plan, without the overhead of full statistics collection.

The Query Monitor portlet is designed for real-time monitoring of active sessions and queries. It provides detailed metrics for each query, including the current and peak spool space being used. An administrator can sort by the 'Spool Usage' column to immediately identify the top consumers and decide whether to abort a query or contact the user. The Space Usage portlet shows overall database space but not per-query consumption in real-time.

The TASM State Matrix is the feature explicitly designed for this purpose. The administrator can define different System States based on time criteria (e.g., 'BusinessHours', 'BatchWindow'). Then, the State Matrix is used to map which Workload definitions are active and which Service Level Goal (SLG) they are assigned to for each specific state. This allows for a fully automated, dynamic adjustment of system priorities and resource allocation as the system transitions between states.

While DBC.DiskSpaceV provides a system-level view, it doesn't explain why a specific query is using so much temp space. The most effective diagnostic step is to analyze the query execution plans. Enabling DBQL with XMLPLAN captures the detailed optimizer plan, which shows how volatile tables are created, populated, and used. This allows the administrator or developer to identify issues like unnecessary data materialization, poor distribution of volatile tables, or inefficient query logic that leads to temp space exhaustion.

Setting the Primary Index (PI) to patient_id ensures that all records for a given patient are co-located on the same AMP. This makes joins with the PATIENTS table and lookups by patient_id extremely efficient. Partitioning the table by range on encounter_date directly addresses the secondary access path. When queries filter by a date range, the system can use partition elimination to scan only the relevant data partitions, dramatically reducing I/O and improving performance for time-based analysis.

The Teradata optimizer makes decisions about where to process a federated query based on cost, which heavily relies on table statistics. If statistics are missing for the remote Oracle table, the optimizer may use default or inaccurate heuristics, often assuming the remote table is very large. This can lead it to make the poor decision of sending the large local table to the remote system instead of bringing the small remote table to Vantage. Collecting statistics on the remote table would give the optimizer the accurate information needed to generate an efficient plan.

Teradata Row-Level Security (RLS) is the feature designed specifically for this use case. RLS allows an administrator to define security constraints on a table that act as an automatic, server-enforced WHERE clause. By creating a constraint that references a lookup table (ANALYST_REGIONS), the system can dynamically filter rows based on the logged-in user's attributes, ensuring data segregation transparently. While secure views can work, they become unmanageable with many regions.

The correct syntax to establish a proxy user relationship is GRANT CONNECT THROUGH TO ;. This command grants the permanent users (Alice, Bob) the ability to have their sessions proxied by the application user (AppUser). When the application connects as AppUser, it can then issue a SET QUERY_BAND PROXYUSER='Alice'; statement, and subsequent queries will be executed with Alice's identity and privileges.

Effective management of TPT jobs in TASM involves three key steps: 1. Classification (identifying the jobs via Utility Name), 2. Concurrency Control (limiting simultaneous jobs with a Utility Throttle), and 3. Execution Control (using a Filter to enforce time windows). These three controls provide comprehensive management over utility workloads.