Question #1
There is a call center application that repetitively sends tactical queries to Vantage These queries use a few small tables that are joined on the primary index column.
The following maps are defined in the system:
* TD_GlobalMap
* TD_Map1
* TD_DataDictionaryMap
* TD_1AmpSparseMap_1Node
What can be done to optimize these queries?
- A . Assign these tables to TD_1AmpSparseMap_1Node using different colocation names for each table.
- B . Assign these tables to TD_1AmpSparseMap_1Node using the same colocation name for every table
- C . Assign these tables to TD_Map1 using the same colocation name for every table.
- D . Assign these tables to TD_Map1 using different colocation names for each table.
Correct Answer: B
B
Explanation:
Example:
TD_1AmpSparseMap_1Node is a sparse map that assigns the data to a single AMP (or a few AMPs), and using the same colocation name ensures that the tables are collocated on the same AMP. This helps in efficient joining because no data redistribution is required between AMPs when tables are joined on the primary index.
Using different colocation names for each table (options A and D) would place the tables on different AMPs, leading to less efficient joins since data would need to be shuffled between AMPs.
TD_Map1 is a predefined map in the system but does not specifically optimize small, frequently accessed tables in the same way that TD_1AmpSparseMap_1Node does, which is more suitable for these scenarios.
Thus, using the same colocation name within the TD_1AmpSparseMap_1Node ensures that the joins are AMP-local, optimizing the repetitive tactical queries.
B
Explanation:
Example:
TD_1AmpSparseMap_1Node is a sparse map that assigns the data to a single AMP (or a few AMPs), and using the same colocation name ensures that the tables are collocated on the same AMP. This helps in efficient joining because no data redistribution is required between AMPs when tables are joined on the primary index.
Using different colocation names for each table (options A and D) would place the tables on different AMPs, leading to less efficient joins since data would need to be shuffled between AMPs.
TD_Map1 is a predefined map in the system but does not specifically optimize small, frequently accessed tables in the same way that TD_1AmpSparseMap_1Node does, which is more suitable for these scenarios.
Thus, using the same colocation name within the TD_1AmpSparseMap_1Node ensures that the joins are AMP-local, optimizing the repetitive tactical queries.
Question #2
Which workload management technique should the Administrator use to reject all QueryGrid
queries during a pre-defined critical batch state?
- A . An exception that includes the following Foreign Servers:
TD_SYSFNLIB.QGInitiatorExport
TD_SYSFNLIB.QGInitiatorlmport
TD_SYSFNLIB.QGRemoteExport
TD_SYSFNLIB.QGRemotelmport - B . A filter that includes the following functions:
TD_SYSFNLIB.QGInitiatorExport
TD_SYSFNLIB.QGInitiatorlmport
TD_SYSFNLIB.QGRemoteExport
TD_SYSFNLIB.QGRemotelmport - C . An exception that excludes the following Foreign Servers:
TD_SYSFNLIB.QGInitiatorExport
TD_SYSFNLIB.QGInitiatorlmport
TD_SYSFNLIB.QGRemoteExport
TD_SYSFNLIB.QGRemotelmport - D . A filter that excludes the following functions:
TD_SYSFNLIB.QGInitiatorExport
TD_SYSFNLIB.QGInitiatorlmport
TD_SYSFNLIB.QGRemoteExport
TD_SYSFNLIB.QGRemotelmport
Correct Answer: A
A
Explanation:
In this case, using an exception with the Foreign Servers related to QueryGrid functions will prevent queries involving those foreign servers from running. This is a typical method to block or reject specific workloads during critical times, such as a batch processing window.
Exceptions in workload management are used to define specific conditions under which queries or workloads should be rejected or managed differently. By including these QueryGrid-related functions in an exception, the administrator ensures that any QueryGrid queries involving the listed foreign servers will be rejected during the critical batch state.
Options B and D, which mention filters, are not appropriate for rejecting queries. Filters are used for monitoring or routing purposes, not for outright rejection of workloads.
Option C, which suggests excluding the foreign servers in an exception, would not achieve the goal, as this would mean those queries are not affected by the exception. The goal here is to include these functions in an exception to actively reject them.
A
Explanation:
In this case, using an exception with the Foreign Servers related to QueryGrid functions will prevent queries involving those foreign servers from running. This is a typical method to block or reject specific workloads during critical times, such as a batch processing window.
Exceptions in workload management are used to define specific conditions under which queries or workloads should be rejected or managed differently. By including these QueryGrid-related functions in an exception, the administrator ensures that any QueryGrid queries involving the listed foreign servers will be rejected during the critical batch state.
Options B and D, which mention filters, are not appropriate for rejecting queries. Filters are used for monitoring or routing purposes, not for outright rejection of workloads.
Option C, which suggests excluding the foreign servers in an exception, would not achieve the goal, as this would mean those queries are not affected by the exception. The goal here is to include these functions in an exception to actively reject them.
Question #3
A customer is complaining that the creation of a large table in the lab environment is getting stuck in the merge step. The customer is using the following command to create the table: CREATE TABLE … AS (SELECT * …) WITH DATA
Which recommendation will help the merge step?
- A . A column that provides good distribution should be specified as PRIMARY INDEX.
- B . The MERGEBLOCKRATIO should be specified for the table to improve the data block merge operation.
- C . The table should be created with "CREATE TABLE … AS (SELECT *…) WITH NO DATA", and afterwards, "INSERT … SELECT *" should be used to fill the table.
- D . Users of the lab may have lower priority than other workloads, so the creation of the table should be moved to off-peak hours.
Correct Answer: C
C
Explanation:
Merge step issues often occur when a large amount of data is being processed during the table creation, especially if the system is trying to simultaneously create the table and insert data.
By using "WITH NO DATA", the table structure is created first, without the actual data being inserted during the table creation process. The *"INSERT … SELECT " command can then be used afterwards to populate the table in a more controlled way, reducing the load on the system during the creation phase and potentially improving the efficiency of the merge step.
Specifying a good distribution for the primary index can help overall performance, but it doesn’t directly address the issue with the merge step in this scenario.
Specifying the MERGEBLOCKRATIO isn’t typically a solution for this specific problem; the merge block ratio is more about the optimization of data block merges rather than the creation of tables.
Moving the creation to off-peak hours may help if the environment is busy, but it doesn’t directly address the core issue of the merge step getting stuck.
C
Explanation:
Merge step issues often occur when a large amount of data is being processed during the table creation, especially if the system is trying to simultaneously create the table and insert data.
By using "WITH NO DATA", the table structure is created first, without the actual data being inserted during the table creation process. The *"INSERT … SELECT " command can then be used afterwards to populate the table in a more controlled way, reducing the load on the system during the creation phase and potentially improving the efficiency of the merge step.
Specifying a good distribution for the primary index can help overall performance, but it doesn’t directly address the issue with the merge step in this scenario.
Specifying the MERGEBLOCKRATIO isn’t typically a solution for this specific problem; the merge block ratio is more about the optimization of data block merges rather than the creation of tables.
Moving the creation to off-peak hours may help if the environment is busy, but it doesn’t directly address the core issue of the merge step getting stuck.
Question #4
Which benefit is achieved by creating profiles?
- A . Reducing the growth of the DBC.AccessRights table
- B . Managing user privileges on database objects
- C . Assigning permanent space for groups of users with similar needs
- D . Defining spool space for groups of users with similar needs
Correct Answer: D
D
Explanation:
Profiles in Teradata are used to manage and enforce resource limits for groups of users. This includes defining spool space, temporary space, and permanent space for users who share similar needs. Spool space is important for query processing, and managing it by profile helps prevent individual users from consuming excessive resources and ensures better overall system performance. Reducing the growth of the DBC.AccessRights table is not directly related to profiles. Profiles do not reduce access rights or the growth of this table, which tracks individual user privileges. Managing user privileges on database objects is handled through roles and grants, not profiles.
Profiles focus on resource management, not privileges.
Assigning permanent space for groups of users is partially true since profiles can manage some space attributes, but the main focus of profiles is on managing spool space rather than permanent space.
D
Explanation:
Profiles in Teradata are used to manage and enforce resource limits for groups of users. This includes defining spool space, temporary space, and permanent space for users who share similar needs. Spool space is important for query processing, and managing it by profile helps prevent individual users from consuming excessive resources and ensures better overall system performance. Reducing the growth of the DBC.AccessRights table is not directly related to profiles. Profiles do not reduce access rights or the growth of this table, which tracks individual user privileges. Managing user privileges on database objects is handled through roles and grants, not profiles.
Profiles focus on resource management, not privileges.
Assigning permanent space for groups of users is partially true since profiles can manage some space attributes, but the main focus of profiles is on managing spool space rather than permanent space.
Question #5
What is a use case for Data Mover?
- A . Archiving data to a Hadoop system
- B . Copying data between Vantage systems for active-active replication
- C . Replicating data to a disaster recovery system
- D . Copying data between Hadoop systems
Correct Answer: C
C
Explanation:
Teradata Data Mover is primarily designed to copy and replicate data between Teradata or Vantage systems. One of its common use cases is to move data to a disaster recovery system, ensuring that data is available in case of system failure or disaster, and making it a valuable tool for maintaining high availability and business continuity.
Archiving data to Hadoop and Copying data between Hadoop systems are more relevant to other tools such as Teradata QueryGrid, which integrates Vantage with Hadoop and other external systems. Copying data between Vantage systems for active-active replication might involve Data Mover, but active-active replication typically involves more sophisticated real-time synchronization technologies like Teradata’s Unity or QueryGrid.
C
Explanation:
Teradata Data Mover is primarily designed to copy and replicate data between Teradata or Vantage systems. One of its common use cases is to move data to a disaster recovery system, ensuring that data is available in case of system failure or disaster, and making it a valuable tool for maintaining high availability and business continuity.
Archiving data to Hadoop and Copying data between Hadoop systems are more relevant to other tools such as Teradata QueryGrid, which integrates Vantage with Hadoop and other external systems. Copying data between Vantage systems for active-active replication might involve Data Mover, but active-active replication typically involves more sophisticated real-time synchronization technologies like Teradata’s Unity or QueryGrid.
Question #6
An Administrator manages a Vantage system that is continually updated. The system is critical to the business and must be available as much as possible. The Administrator decides to use a backup strategy that will allow changes to tables while a backup is in progress.
Which backup strategy should be used?
- A . Offline
- B . Skip statistics
- C . Online
- D . Dictionary Only
Correct Answer: C
C
Explanation:
An Online backup strategy allows the database to remain accessible and operational while the backup is taking place. This means that users can continue to modify data, and the system can remain available without requiring downtime. It is a common approach for mission-critical systems that need to maximize uptime.
Offline would require the system or certain tables to be unavailable during the backup process, which is not suitable for a system that needs to remain available.
Skip statistics refers to skipping the backup of statistics on database objects and does not relate to whether the system can be updated during the backup.
Dictionary Only involves backing up only the system catalog or dictionary data, not the actual table data, and would not fulfill the requirement of a comprehensive backup while allowing updates.
C
Explanation:
An Online backup strategy allows the database to remain accessible and operational while the backup is taking place. This means that users can continue to modify data, and the system can remain available without requiring downtime. It is a common approach for mission-critical systems that need to maximize uptime.
Offline would require the system or certain tables to be unavailable during the backup process, which is not suitable for a system that needs to remain available.
Skip statistics refers to skipping the backup of statistics on database objects and does not relate to whether the system can be updated during the backup.
Dictionary Only involves backing up only the system catalog or dictionary data, not the actual table data, and would not fulfill the requirement of a comprehensive backup while allowing updates.
Question #7
A client is having many problems with a poorly written SQL. They have product joins running against large tables, and this is causing severe performance issues. The Administrator needs to help the client avoid the harm to the system that is being caused by running these queries.
Which action should be taken by the Administrator to assist this client?
- A . Use the Query Spotlight Viewpoint portlet to deprioritize the queries.
- B . Use the Flex Throttle option.
- C . Use a TASM system filter.
- D . Use the Query Log Viewpoint portlet to identity and abort the harmful queries.
Correct Answer: C
C
Explanation:
Teradata Active System Management (TASM) allows the system administrator to control and manage query workload in real-time. A TASM system filter can be used to prevent or deprioritize certain types of queries, such as product joins or poorly written queries, that are consuming excessive resources. This approach ensures that problematic queries do not harm the overall system performance.
Query Spotlight Viewpoint portlet focuses on identifying and managing individual queries but does not provide the same level of proactive filtering and control as TASM system filters.
Flex Throttle is useful for controlling the concurrency of queries, but it doesn’t specifically address preventing or managing product joins in a way that would directly resolve the issue with poorly written SQL.
Query Log Viewpoint portlet would help in identifying and aborting harmful queries, but it’s a reactive approach rather than a proactive solution like using TASM system filters.
C
Explanation:
Teradata Active System Management (TASM) allows the system administrator to control and manage query workload in real-time. A TASM system filter can be used to prevent or deprioritize certain types of queries, such as product joins or poorly written queries, that are consuming excessive resources. This approach ensures that problematic queries do not harm the overall system performance.
Query Spotlight Viewpoint portlet focuses on identifying and managing individual queries but does not provide the same level of proactive filtering and control as TASM system filters.
Flex Throttle is useful for controlling the concurrency of queries, but it doesn’t specifically address preventing or managing product joins in a way that would directly resolve the issue with poorly written SQL.
Query Log Viewpoint portlet would help in identifying and aborting harmful queries, but it’s a reactive approach rather than a proactive solution like using TASM system filters.
Question #8
The Administrator has dropped a profile which was associated with a group of users.
What will happen when one of the users from the group logs on to the database?
- A . The default profile setting for the user will be reset to NULL.
- B . Password attributes defined at the system level in DBCSecurityDefaultsV will apply
- C . The system will show a warning message.
- D . SPOOL and DEFAULT DATABASE specifications for the dropped profile will still apply to the user.
Correct Answer: B
B
Explanation:
When a profile is dropped, the users who were associated with that profile will no longer have the specific resource and configuration settings defined by that profile. Instead, the system-level default settings, such as those specified in DBC.SecurityDefaultsV, will apply to the users. This includes password attributes and other system-wide settings.
Option A (The default profile setting for the user will be reset to NULL) is not accurate, as the system will revert to the default settings rather than leaving values unset.
Option C (The system will show a warning message) does not typically occur when a profile is dropped. The system will manage the transition without user-facing warnings at login.
Option D (SPOOL and DEFAULT DATABASE specifications for the dropped profile will still apply to the user) is incorrect because once the profile is dropped, its settings no longer apply to the users who were associated with it.
Thus, the correct outcome is that system-level attributes defined in DBC.SecurityDefaultsV will govern the users’ settings moving forward.
B
Explanation:
When a profile is dropped, the users who were associated with that profile will no longer have the specific resource and configuration settings defined by that profile. Instead, the system-level default settings, such as those specified in DBC.SecurityDefaultsV, will apply to the users. This includes password attributes and other system-wide settings.
Option A (The default profile setting for the user will be reset to NULL) is not accurate, as the system will revert to the default settings rather than leaving values unset.
Option C (The system will show a warning message) does not typically occur when a profile is dropped. The system will manage the transition without user-facing warnings at login.
Option D (SPOOL and DEFAULT DATABASE specifications for the dropped profile will still apply to the user) is incorrect because once the profile is dropped, its settings no longer apply to the users who were associated with it.
Thus, the correct outcome is that system-level attributes defined in DBC.SecurityDefaultsV will govern the users’ settings moving forward.
Question #9
An Administrator has been asked to improve the response time of the workloads in the tactical tier. Workloads are in all tiers of TASM. They are at CPU and I/O capacity, and they have AWT reserved for tactical. The Administrator begins by analyzing the data and the workload prioritization.
Which action should the Administrator take?
- A . Increase the relative weight of the tactical tier.
- B . Disable the expedite option of the workloads in the SLG Tier level 1.
- C . Increase the number of AWTs.
- D . Adjust the limits of awt concurrency in the tactical tier using DBS control.
Correct Answer: A
A
Explanation:
In a TASM (Teradata Active System Management) environment, the relative weight of different tiers (such as tactical, SLG, or background) determines the amount of CPU and I/O resources allocated to each tier. Increasing the relative weight of the tactical tier would prioritize these workloads, giving them more resources relative to other tiers and improving their response time.
Option B (Disable the expedite option of the workloads in the SLG Tier level 1) might alleviate some resource pressure from SLG Tier 1 workloads, but it doesn’t directly improve the prioritization or resource allocation for tactical workloads.
Option C (Increase the number of AWTs) is not applicable because the system already has reserved AWTs for tactical workloads, and AWT (AMP Worker Task) availability is likely not the bottleneck here. Simply increasing the number of AWTs without addressing the core CPU and I/O resource allocation will not improve tactical workload response.
Option D (Adjust the limits of AWT concurrency in the tactical tier using DBS control) might provide marginal improvements but won’t have a significant impact on overall resource prioritization. It focuses more on concurrency management than directly improving workload performance through resource allocation.
A
Explanation:
In a TASM (Teradata Active System Management) environment, the relative weight of different tiers (such as tactical, SLG, or background) determines the amount of CPU and I/O resources allocated to each tier. Increasing the relative weight of the tactical tier would prioritize these workloads, giving them more resources relative to other tiers and improving their response time.
Option B (Disable the expedite option of the workloads in the SLG Tier level 1) might alleviate some resource pressure from SLG Tier 1 workloads, but it doesn’t directly improve the prioritization or resource allocation for tactical workloads.
Option C (Increase the number of AWTs) is not applicable because the system already has reserved AWTs for tactical workloads, and AWT (AMP Worker Task) availability is likely not the bottleneck here. Simply increasing the number of AWTs without addressing the core CPU and I/O resource allocation will not improve tactical workload response.
Option D (Adjust the limits of AWT concurrency in the tactical tier using DBS control) might provide marginal improvements but won’t have a significant impact on overall resource prioritization. It focuses more on concurrency management than directly improving workload performance through resource allocation.
Question #10
At a large car manufacturer, huge volumes of diagnostic data for cars are collected in the following table:
The master data for each car is stored in the following table:
Many reports require data from both tables by joining via column VehicleId.
A very frequently performed query on the system returns the number of events by FaultCode and ModelType. This query consumes many CPU and I/O resources each day.
Which action should the Administrator take to improve the runtime and resource consumption for this query?
- A . Use an aggregate join index with columns FaultCode, ModelType, as well as an appropriate aggregate function.
- B . Use a sparse join index with columns FaultCode and ModelType, as well as an appropriate filter function.
- C . Use a NUSI on VehicleEvent.FaultCode and a NUSI on Vehicle.ModelType
- D . Use a single table join index on VehicleEvent hashed by FaultCode and another single table join index on Vehicle hashed by ModelType.
Correct Answer: A
A
Explanation:
To improve the runtime and resource consumption for a query that returns the number of events by FaultCode and ModelType from the two tables VehicleEvent and Vehicle, the most appropriate action would be:
A
Explanation:
To improve the runtime and resource consumption for a query that returns the number of events by FaultCode and ModelType from the two tables VehicleEvent and Vehicle, the most appropriate action would be:
Question #11
An Administrator has been given a task to generate a list of users who have not changed their password in the last 90 days.
Which DBC view should be used to generate this list?
- A . DBC.LOGONOFFV
- B . DBC.USERSV
- C . DBC.SECURITYDEFAULTSV
- D . DBC.ACCESSLOGV
Correct Answer: B
B
Explanation:
DBC.USERSV contains information about users, including the passwordlastmodified column, which records the date and time the user last changed their password. By querying this view, the Administrator can identify users who have not updated their password within the specified time frame (in this case, 90 days).
Option A (DBC.LOGONOFFV) logs user logon and logoff events, but it does not track password changes.
Option C (DBC.SECURITYDEFAULTSV) contains system-wide security defaults, but it does not track individual user password activity.
Option D (DBC.ACCESSLOGV) logs access control events, like who accessed which database objects, but it doesn’t track password changes either.
Therefore, DBC.USERSV is the appropriate view to use for this task.
B
Explanation:
DBC.USERSV contains information about users, including the passwordlastmodified column, which records the date and time the user last changed their password. By querying this view, the Administrator can identify users who have not updated their password within the specified time frame (in this case, 90 days).
Option A (DBC.LOGONOFFV) logs user logon and logoff events, but it does not track password changes.
Option C (DBC.SECURITYDEFAULTSV) contains system-wide security defaults, but it does not track individual user password activity.
Option D (DBC.ACCESSLOGV) logs access control events, like who accessed which database objects, but it doesn’t track password changes either.
Therefore, DBC.USERSV is the appropriate view to use for this task.
Question #12
A client has a healthy system but often sees delays in some queries because of workload concurrency limits. These limits have been carefully chosen, so the client needs a solution that will not modify them.
What should the Administrator use to assist this client?
- A . Use a system throttle.
- B . Use Query Group Viewpoint gortjet to change the throttle limit temporally.
- C . Use Flex Throttle option.
Use Query Monitor Viewpoint portlet to change query workloads.
Correct Answer: C
C
Explanation:
The Flex Throttle option allows the system to temporarily adjust workload concurrency limits based on system conditions. This provides more flexibility when handling spikes in query concurrency, without permanently modifying the established workload limits. Flex Throttle is ideal for handling temporary peaks in activity, helping to smooth out delays while keeping the core concurrency limits intact.
Option A (Use a system throttle) would enforce strict concurrency limits but doesn’t provide the flexibility needed in this scenario, where the client is trying to avoid modifying existing limits. Option B (Use Query Group Viewpoint portlet to change the throttle limit temporarily) suggests manually adjusting the throttle limit, which is not desirable in this case as the limits have been carefully chosen.
Option D (Use Query Monitor Viewpoint portlet to change query workloads) would involve changing the way queries are handled or prioritized but does not address the need to keep concurrency limits unchanged while still dealing with temporary delays.
Thus, Flex Throttle (Option C) provides the best solution to assist the client without altering the concurrency limits permanently.
C
Explanation:
The Flex Throttle option allows the system to temporarily adjust workload concurrency limits based on system conditions. This provides more flexibility when handling spikes in query concurrency, without permanently modifying the established workload limits. Flex Throttle is ideal for handling temporary peaks in activity, helping to smooth out delays while keeping the core concurrency limits intact.
Option A (Use a system throttle) would enforce strict concurrency limits but doesn’t provide the flexibility needed in this scenario, where the client is trying to avoid modifying existing limits. Option B (Use Query Group Viewpoint portlet to change the throttle limit temporarily) suggests manually adjusting the throttle limit, which is not desirable in this case as the limits have been carefully chosen.
Option D (Use Query Monitor Viewpoint portlet to change query workloads) would involve changing the way queries are handled or prioritized but does not address the need to keep concurrency limits unchanged while still dealing with temporary delays.
Thus, Flex Throttle (Option C) provides the best solution to assist the client without altering the concurrency limits permanently.
Question #13
Given a user creation request on a 10-AMP system:
CREATE USER hr_user AS PERM = 100e9 SKEW = 10 PERCENT, SPOOL = 100e9 SKEW = 20 PERCENT;
How does the SKEW factor affect the user’s Perm space, assuming the total space consumed is under the specified perm space limit?
- A . The per AMP limit of any AMP can reach 20 GB.
- B . The per AMP limit of 100 GB can be breached up to 10 percent.
- C . The per AMP limit of 10 GB can be breached to any percent.
- D . The per AMP limit of any AMP can reach 11 GET
Correct Answer: D
D
Explanation:
In the given CREATE USER statement, the SKEW = 10 PERCENT parameter applies to Perm space and allows some AMPs (Access Module Processors) to use up to 10% more space than the average allocation across the AMPs.
The user is allocated 100 GB of Perm space across a 10-AMP system, meaning the average space per AMP is 10 GB.
With a 10% skew allowed, this means that an AMP can use up to 10% more than the average allocation, which is 10 GB + 1 GB = 11 GB.
D
Explanation:
In the given CREATE USER statement, the SKEW = 10 PERCENT parameter applies to Perm space and allows some AMPs (Access Module Processors) to use up to 10% more space than the average allocation across the AMPs.
The user is allocated 100 GB of Perm space across a 10-AMP system, meaning the average space per AMP is 10 GB.
With a 10% skew allowed, this means that an AMP can use up to 10% more than the average allocation, which is 10 GB + 1 GB = 11 GB.
Question #14
An Administrator has been tasked with analyzing previous growth and usage patterns by utilities such as Multiload and FastLoad. The aim is to project the likely resource requirements of the existing loads in the next three to six months.
In the last month, the Administrator team have started using AccountString expansion on all user accounts and have started maintenance jobs to housekeep system data older than seven years. Two years ago, the following command was issued on the system: BEGIN QUERY LOGGING WITH ALL ON ALL;
No other logging commands have been issued.
Which view contains the utility usage data for the prior months?
- A . AMPUsageV
- B . LogOnOffV
- C . QryLogUtilityV
- D . QryLogV
Correct Answer: C
C
Explanation:
QryLogUtilityV is a specific DBQL (Database Query Logging) view that provides information about utility usage (such as Multiload and FastLoad) on the system. Since the BEGIN QUERY LOGGING WITH ALL ON ALL command was issued two years ago, the DBQL (Database Query Logging) has been tracking various events, including utility usage, which is stored in QryLogUtilityV.
Option A (AMPUsageV) contains AMP (Access Module Processor) level statistics and usage data but not detailed information about utility jobs.
Option B (LogOnOffV) tracks user logon and logoff activities but does not provide information about utility usage.
Option D (QryLogV) logs general query execution data but is not specifically focused on utility usage.
C
Explanation:
QryLogUtilityV is a specific DBQL (Database Query Logging) view that provides information about utility usage (such as Multiload and FastLoad) on the system. Since the BEGIN QUERY LOGGING WITH ALL ON ALL command was issued two years ago, the DBQL (Database Query Logging) has been tracking various events, including utility usage, which is stored in QryLogUtilityV.
Option A (AMPUsageV) contains AMP (Access Module Processor) level statistics and usage data but not detailed information about utility jobs.
Option B (LogOnOffV) tracks user logon and logoff activities but does not provide information about utility usage.
Option D (QryLogV) logs general query execution data but is not specifically focused on utility usage.
Question #15
A middle-tier application server logs on to the database as TrustedUser and submits requests on behalf of application end users. The server is shared by Finance and Human Resources groups and uses ProxyUser query band to identify end users to the database. Each group needs access to its own sensitive data, so the Administrator has created two separate roles with the appropriate permissions.
What is the best way to control access to each group’s sensitive data?
- A . Define the roles as external and use the ProxyRole query band to specify one role.
- B . Grant both roles to TrustedUser, and add the ProxyRole query band to specify one role.
- C . Specify the appropriate role for each end user in a grant connect through statement.
- D . Include both roles in the grant connect through statement, and use ProxyRole in the query band to select the appropriate role.
Correct Answer: D
D
Explanation:
The GRANT CONNECT THROUGH statement allows the TrustedUser to act on behalf of multiple end users while securely connecting to the database. By granting both roles (Finance and Human Resources) in this statement, you allow the ProxyUser to switch between roles depending on the query band’s ProxyRole value.
Using the ProxyRole query band, the application can specify which role (Finance or Human Resources) should be used for each specific request. This approach provides flexibility, as the application can dynamically assign the appropriate role to the user based on the query context. Option A (Defining roles as external and using ProxyRole) wouldn’t fully address the need to manage multiple roles dynamically for a shared server.
Option B (Granting both roles to TrustedUser) doesn’t allow for flexible role switching on a per-request basis without the use of GRANT CONNECT THROUGH and could lead to over-granting of permissions.
Option C (Specifying a role for each end user in GRANT CONNECT THROUGH) isn’t as flexible as allowing both roles to be used and dynamically selected through the query band.
Thus, Option D is the most appropriate solution, as it provides both security and flexibility, enabling the application to use the correct role based on the ProxyRole query band for each query submitted.
D
Explanation:
The GRANT CONNECT THROUGH statement allows the TrustedUser to act on behalf of multiple end users while securely connecting to the database. By granting both roles (Finance and Human Resources) in this statement, you allow the ProxyUser to switch between roles depending on the query band’s ProxyRole value.
Using the ProxyRole query band, the application can specify which role (Finance or Human Resources) should be used for each specific request. This approach provides flexibility, as the application can dynamically assign the appropriate role to the user based on the query context. Option A (Defining roles as external and using ProxyRole) wouldn’t fully address the need to manage multiple roles dynamically for a shared server.
Option B (Granting both roles to TrustedUser) doesn’t allow for flexible role switching on a per-request basis without the use of GRANT CONNECT THROUGH and could lead to over-granting of permissions.
Option C (Specifying a role for each end user in GRANT CONNECT THROUGH) isn’t as flexible as allowing both roles to be used and dynamically selected through the query band.
Thus, Option D is the most appropriate solution, as it provides both security and flexibility, enabling the application to use the correct role based on the ProxyRole query band for each query submitted.
Question #16
An Administrator needs to provide end users access to data in a Native Object Store using shared credentials.
How can this be accomplished?
- A . Define a FOREIGN TABLE with DEFINER TRUSTED security.
- B . Define a FUNCTION MAPPING with INVOKER TRUSTED security.
- C . Use an S3 1AM role or Azure SAS token rather than User and Password.
- D . Grant users access to a shared AUTHORIZATION object.
Correct Answer: D
D
Explanation:
In Teradata’s Native Object Store, the AUTHORIZATION object stores shared credentials (such as an S3 IAM role, Azure SAS token, or other cloud storage credentials). By granting users access to this shared authorization object, the administrator can allow multiple users to access the object store using the same set of credentials. This simplifies credential management and ensures secure access to the external data source.
Option A (Define a FOREIGN TABLE with DEFINER TRUSTED security) refers to defining a foreign table with a specific security model but does not handle shared credentials directly.
Option B (Define a FUNCTION MAPPING with INVOKER TRUSTED security) relates to function mappings and security contexts for user-defined functions, not directly to shared credentials for accessing an object store.
Option C (Use an S3 IAM role or Azure SAS token rather than User and Password) is a part of how credentials might be managed, but it does not address the mechanism for sharing these credentials among users. The AUTHORIZATION object is the correct method for managing and sharing these credentials securely.
D
Explanation:
In Teradata’s Native Object Store, the AUTHORIZATION object stores shared credentials (such as an S3 IAM role, Azure SAS token, or other cloud storage credentials). By granting users access to this shared authorization object, the administrator can allow multiple users to access the object store using the same set of credentials. This simplifies credential management and ensures secure access to the external data source.
Option A (Define a FOREIGN TABLE with DEFINER TRUSTED security) refers to defining a foreign table with a specific security model but does not handle shared credentials directly.
Option B (Define a FUNCTION MAPPING with INVOKER TRUSTED security) relates to function mappings and security contexts for user-defined functions, not directly to shared credentials for accessing an object store.
Option C (Use an S3 IAM role or Azure SAS token rather than User and Password) is a part of how credentials might be managed, but it does not address the mechanism for sharing these credentials among users. The AUTHORIZATION object is the correct method for managing and sharing these credentials securely.
Question #17
An Administrator has been presented with these performance metrics from the DBQLOGTBL table for four queries:
The Administrator needs to identify which query is suspected to be causing a full table scan or large redistribution.
Which query is producing this result?
- A . QRY_D because of CPU SKEW
- B . QRY_C because of UII
- C . QRY_B because of IO SKEW
- D . QRY_A because of PJI
Correct Answer: C
C
Explanation:
IO SKEW refers to an uneven distribution of I/O operations across AMPs, which can occur during large redistributions or full table scans, as data needs to be read from or written to the entire table. High IO skew indicates that some AMPs are handling significantly more I/O than others, which is often a sign of inefficient data distribution or full table scanning.
QRY_B has an IO SKEW value of 2.76, which is much higher than the other queries, suggesting that it is likely performing a full table scan or large redistribution, as this would result in uneven I/O across AMPs.
CPU SKEW (as seen in QRY_D) could indicate an issue with CPU load balancing, but it doesn’t necessarily suggest a full table scan or redistribution.
UII (Utility Impact Index) and PJI (Product Join Index) are also metrics that might indicate query inefficiencies, but the high IO SKEW in QRY_B is the most direct indicator of a full table scan or large redistribution.
QRY_B is the query suspected of causing the full table scan or large redistribution due to its high IO SKEW value.
C
Explanation:
IO SKEW refers to an uneven distribution of I/O operations across AMPs, which can occur during large redistributions or full table scans, as data needs to be read from or written to the entire table. High IO skew indicates that some AMPs are handling significantly more I/O than others, which is often a sign of inefficient data distribution or full table scanning.
QRY_B has an IO SKEW value of 2.76, which is much higher than the other queries, suggesting that it is likely performing a full table scan or large redistribution, as this would result in uneven I/O across AMPs.
CPU SKEW (as seen in QRY_D) could indicate an issue with CPU load balancing, but it doesn’t necessarily suggest a full table scan or redistribution.
UII (Utility Impact Index) and PJI (Product Join Index) are also metrics that might indicate query inefficiencies, but the high IO SKEW in QRY_B is the most direct indicator of a full table scan or large redistribution.
QRY_B is the query suspected of causing the full table scan or large redistribution due to its high IO SKEW value.
Question #18
BATCH_USER submits stored procedures, utility jobs, and other requests. All BATCH_USER work
classifies into workload WD-Batch based on username criterion.
System performance has slowed due to AWT exhaustion during the batch window. The Administrator determined the AWT exhaustion is due to high concurrency of stored procedure calls from BATCH_USER.
How can the Administrator effectively throttle these stored procedures?
- A . Create a utility limit, classify on username = BATCH_USER and utility = Stored Procedure, and set the limit.
- B . Define an AWT resource limit, classify on username = BATCH_USER and utility = Stored Procedure, and set the limit.
- C . Create a new workload, classify on username = BATCH_USER and statement type = CALL, and set a throttle.
- D . Define a system throttle, classify on username = BATCH_USER and stored procedure = *.*, and set the limit.
Correct Answer: C
C
Explanation:
In this scenario, the issue arises due to high concurrency of stored procedure calls, which are exhausting AWTs (AMP Worker Tasks). To address this, the administrator needs to throttle the specific stored procedure calls made by BATCH_USER. The most effective way to do this is to create a new workload that specifically targets those calls (statement type = CALL) from BATCH_USER and then applies a throttle to limit concurrency. This helps manage the system’s AWTs by controlling how many stored procedures can be run concurrently.
Other options are less suitable as they either do not target stored procedures specifically or may not provide the necessary granularity in controlling AWT usage for stored procedure calls.
C
Explanation:
In this scenario, the issue arises due to high concurrency of stored procedure calls, which are exhausting AWTs (AMP Worker Tasks). To address this, the administrator needs to throttle the specific stored procedure calls made by BATCH_USER. The most effective way to do this is to create a new workload that specifically targets those calls (statement type = CALL) from BATCH_USER and then applies a throttle to limit concurrency. This helps manage the system’s AWTs by controlling how many stored procedures can be run concurrently.
Other options are less suitable as they either do not target stored procedures specifically or may not provide the necessary granularity in controlling AWT usage for stored procedure calls.
Question #19
An application is consuming very high I/O on Teradata Vantage. The Administrator has been asked to identify the queries that are consuming more than 100 million IOs and are suspected of completing Full Table Scans or Large Redistribution.
Which metrics should be examined for high values to identify the queries?
- A . MAXAMPIO and PJI
- B . TOTALIOCOUNT and PJI
- C . I/O SKEW % and UII
- D . REQPHYSIO and UII
Correct Answer: B
B
Explanation:
TOTALIOCOUNT represents the total number of I/O operations a query performs, which is crucial when trying to identify queries consuming excessive I/O (in this case, more than 100 million IOs). This directly indicates the amount of I/O a query is generating.
PJI (Product Join Index) measures whether a product join is being performed in the query, which is often associated with inefficient joins that result in large redistributions of data. A high PJI value
indicates that the query might be performing a product join, leading to a significant performance hit due to large redistributions.
Option A (MAXAMPIO and PJI): MAXAMPIO refers to the maximum I/O performed by a single AMP, but TOTALIOCOUNT is a more direct indicator of the total I/O performed by a query across all AMPs. Option C (I/O SKEW % and UII): I/O SKEW % indicates uneven distribution of I/O, which can cause performance issues, but it does not directly measure the total I/O consumption like TOTALIOCOUNT does. UII (Utility Impact Index) is relevant but not as important for identifying large I/O queries. Option D (REQPHYSIO and UII): REQPHYSIO refers to the number of physical I/Os, but it is not as comprehensive as TOTALIOCOUNT, which includes both logical and physical I/O.
Thus, TOTALIOCOUNT (for total I/O) and PJI (to detect potential large redistributions due to product joins) are the key metrics to identify queries with high I/O and large redistributions.
B
Explanation:
TOTALIOCOUNT represents the total number of I/O operations a query performs, which is crucial when trying to identify queries consuming excessive I/O (in this case, more than 100 million IOs). This directly indicates the amount of I/O a query is generating.
PJI (Product Join Index) measures whether a product join is being performed in the query, which is often associated with inefficient joins that result in large redistributions of data. A high PJI value
indicates that the query might be performing a product join, leading to a significant performance hit due to large redistributions.
Option A (MAXAMPIO and PJI): MAXAMPIO refers to the maximum I/O performed by a single AMP, but TOTALIOCOUNT is a more direct indicator of the total I/O performed by a query across all AMPs. Option C (I/O SKEW % and UII): I/O SKEW % indicates uneven distribution of I/O, which can cause performance issues, but it does not directly measure the total I/O consumption like TOTALIOCOUNT does. UII (Utility Impact Index) is relevant but not as important for identifying large I/O queries. Option D (REQPHYSIO and UII): REQPHYSIO refers to the number of physical I/Os, but it is not as comprehensive as TOTALIOCOUNT, which includes both logical and physical I/O.
Thus, TOTALIOCOUNT (for total I/O) and PJI (to detect potential large redistributions due to product joins) are the key metrics to identify queries with high I/O and large redistributions.
Question #20
DRAG DROP
An Administrator is configuring an alert service on a Vantage system and must define threshold levels and triggers for the alerts.
Match the Administrator’s portlet to the correct use definition. (Each answer is used once.)
Correct Answer:
Question #21
An Administrator has set up security constraints on columns holding sensitive data through the use of profiles. A user, with an appropriate profile, then issues a select against this sensitive data.
Which type of privilege is being used?
- A . Inherited
- B . Ownership
- C . Automatic
- D . Assigned
Correct Answer: A
A
Explanation:
Inherited privileges are those that a user acquires based on their membership in a profile or role. In this case, the user has been assigned a profile that grants access to sensitive data, meaning the privilege to access that data is inherited through the profile.
Option B (Ownership) refers to the privileges that the creator (owner) of a database object has over that object, but in this case, the user is accessing data through a profile, not ownership.
Option C (Automatic) would suggest that the privilege is granted automatically without the need for an explicit assignment or inheritance, which doesn’t apply here because the privileges are coming from a profile.
Option D (Assigned) refers to direct privileges granted to a user individually, but in this scenario, the user is accessing data through a profile, which means the privileges are inherited rather than directly assigned.
A
Explanation:
Inherited privileges are those that a user acquires based on their membership in a profile or role. In this case, the user has been assigned a profile that grants access to sensitive data, meaning the privilege to access that data is inherited through the profile.
Option B (Ownership) refers to the privileges that the creator (owner) of a database object has over that object, but in this case, the user is accessing data through a profile, not ownership.
Option C (Automatic) would suggest that the privilege is granted automatically without the need for an explicit assignment or inheritance, which doesn’t apply here because the privileges are coming from a profile.
Option D (Assigned) refers to direct privileges granted to a user individually, but in this scenario, the user is accessing data through a profile, which means the privileges are inherited rather than directly assigned.
Question #22
How can an Administrator monitor the data volume that is transferred for a Data Mover job via Viewpoint?
- A . By accessing the Completed Queries portlet and selecting the Data Mover job
- B . By using the Data Mover portlet accessing the log tab
- C . By viewing the job statistics tab of the Performance Data Collection portlet
- D . By submitting the datamove status -job_name command in the remote console
Correct Answer: B
B
Explanation:
The Data Mover portlet in Viewpoint provides specific functionality for managing and monitoring Data Mover jobs. By accessing the log tab within the Data Mover portlet, the Administrator can review detailed logs, including the data volume that was transferred during the job execution.
Option A (Completed Queries portlet) focuses on monitoring and analyzing completed queries but does not provide detailed insights into Data Mover jobs.
Option C (Performance Data Collection portlet) is used for monitoring system performance but is not directly related to Data Mover job details or data transfer volumes.
Option D (datamove status command) is a CLI (Command-Line Interface) method to check job status, but it is not related to monitoring the data volume within the Viewpoint environment.
B
Explanation:
The Data Mover portlet in Viewpoint provides specific functionality for managing and monitoring Data Mover jobs. By accessing the log tab within the Data Mover portlet, the Administrator can review detailed logs, including the data volume that was transferred during the job execution.
Option A (Completed Queries portlet) focuses on monitoring and analyzing completed queries but does not provide detailed insights into Data Mover jobs.
Option C (Performance Data Collection portlet) is used for monitoring system performance but is not directly related to Data Mover job details or data transfer volumes.
Option D (datamove status command) is a CLI (Command-Line Interface) method to check job status, but it is not related to monitoring the data volume within the Viewpoint environment.
Question #23
An Administrator needs to enforce a security policy which prohibits the use of an application’s username from being used by certain query clients.
Which workload management feature should the Administrator use?
- A . Flex Throttle
- B . Utility Session
- C . Exception
- D . Filter
Correct Answer: D
D
Explanation:
Filters in Teradata workload management allow the Administrator to define rules that restrict or block certain types of queries or connections based on specific criteria, such as the username, query client, or other attributes. In this case, the Administrator can use a filter to prevent queries from specific clients using the application’s username, enforcing the security policy.
Option A (Flex Throttle) is used to manage query concurrency and system resources dynamically but is not designed to enforce security policies like user restrictions.
Option B (Utility Session) controls the number of utility sessions but does not help with filtering query clients based on usernames.
Option C (Exception) handles query workload exceptions based on certain thresholds or behaviors
but doesn’t directly enforce user or client restrictions.
D
Explanation:
Filters in Teradata workload management allow the Administrator to define rules that restrict or block certain types of queries or connections based on specific criteria, such as the username, query client, or other attributes. In this case, the Administrator can use a filter to prevent queries from specific clients using the application’s username, enforcing the security policy.
Option A (Flex Throttle) is used to manage query concurrency and system resources dynamically but is not designed to enforce security policies like user restrictions.
Option B (Utility Session) controls the number of utility sessions but does not help with filtering query clients based on usernames.
Option C (Exception) handles query workload exceptions based on certain thresholds or behaviors
but doesn’t directly enforce user or client restrictions.
Question #24
What is the maximum payload size for CSV and JSON data formats that the NOS_READ function can read, when it is based on the LATIN character set?
- A . 16 MB
- B . 32 MB
- C . 2GB
- D . 8GB
Correct Answer: B
B
Explanation:
When using the NOS_READ function in Teradata to read data in CSV and JSON formats based on the LATIN character set, the maximum payload size that can be read is 32 MB. This limit ensures efficient processing and handling of large datasets, while also maintaining performance.
B
Explanation:
When using the NOS_READ function in Teradata to read data in CSV and JSON formats based on the LATIN character set, the maximum payload size that can be read is 32 MB. This limit ensures efficient processing and handling of large datasets, while also maintaining performance.
Question #25
A table that contains about two billion records is showing a bad response time for range-based queries on the order date column and frequently projects 10 out of 100 columns. The Administrator decides to convert the table to a column and row partitioned table.
Which resource is constrained?
- A . I/O
- B . CPU
- C . AWT
- D . Space
Correct Answer: A
A
Explanation:
The table contains about two billion records and is exhibiting poor response time for range-based queries on the order date column, along with frequently projecting only 10 out of 100 columns. These factors indicate that the I/O (Input/Output) subsystem is under strain, as large amounts of data are being scanned and retrieved unnecessarily for each query.
Row and column partitioning can significantly reduce the amount of data that needs to be read from disk by limiting I/O to only the necessary partitions (rows) and columns, thus improving performance.
CPU might also be involved, but in this case, the primary concern appears to be I/O due to the large volume of data being retrieved and processed inefficiently.
AWT (AMP Worker Tasks) and Space are less likely to be the primary issues based on the problem description, as there is no mention of concurrency issues (AWT) or running out of space.
A
Explanation:
The table contains about two billion records and is exhibiting poor response time for range-based queries on the order date column, along with frequently projecting only 10 out of 100 columns. These factors indicate that the I/O (Input/Output) subsystem is under strain, as large amounts of data are being scanned and retrieved unnecessarily for each query.
Row and column partitioning can significantly reduce the amount of data that needs to be read from disk by limiting I/O to only the necessary partitions (rows) and columns, thus improving performance.
CPU might also be involved, but in this case, the primary concern appears to be I/O due to the large volume of data being retrieved and processed inefficiently.
AWT (AMP Worker Tasks) and Space are less likely to be the primary issues based on the problem description, as there is no mention of concurrency issues (AWT) or running out of space.