Chapter 22 – Practice Test Questions to Build Your Confidence
Questions without answers:
Which feature allows the Teradata Database to process enormous volumes of data quickly?
Is the Teradata Database is primarily a client or a server?
Which choice represents a Trillion bytes or a TB (PB) of data?
Which choice represents a quadrillion bytes or a Petabyte (PB) of data?
In a relational table, what is the column or set of columns that uniquely identify a row?
What language is used to access a table in a relational database?
Can you name eight Teradata objects?
Name four major components of the Teradata architecture?
What are views?
What are macros?
Can you name all software elements that might reside in clients?
What three things does the PE do besides check the syntax?
What two things does an AMP do?
Questions with answers:
Which feature allows the Teradata Database to process enormous volumes of data quickly? Parallelism
Is the Teradata Database is primarily a client or a server? Server
Which choice represents a Trillion bytes or a TB (TB) of data? 10 to the 12th power
Which choice represents a quadrillion bytes or a Petabyte (PB) of data? 10 to the 16th power
In a relational table, what is the column or set of columns that uniquely identify a row? Primary Key
What language is used to access a table in a relational database? SQL (Structured Query Language)
Can you name eight Teradata objects? Tables, views, macros, triggers, stored procedures, UDFs, Join and Hash Indexes and Permanent Journals
Name four major components of the Teradata architecture? PEs, AMPs, BYNET (message passing layer), Vdisks
What are views? Filter (or subset) of columns or rows on one or more tables
What are macros? Predefined, stored set of SQL statements that act as a single transaction
Can you name all software elements that might reside in clients? CLI, ODBC, JDBC, .NET, TDP and MTDP
What three things does the PE do besides check the syntax? Parse, optimize and dispatches query plans
What two things does an AMP do? Manage and retrieve data from its virtual disk
Questions without answers:
How many sessions can a single PE support?
Will a Teradata system have more than one PE?
What validates the SQL syntax?
What connects AMPs and PEs?
What Balances sessions across PEs?
What provides a library of Session Management Routines?
What does Aggregating and Locking?
What provides Client side OS independence?
What PE software turns SQL into AMP steps?
What PE software sends the plan steps to the AMPs?
What is the foundation of the Teradata architecture?
What provides a library of Teradata Service Routines?
Will a user always have a password?
Will the sum of all user and database Perm Space equal the total space on the system?
Will the sum of all user and database Spool Space equal the total space on the system?
Questions with answers:
How many sessions can a single PE support? 120
Will a Teradata system have more than one PE? Yes
What validates the SQL syntax? Parser
What connects AMPs and PEs? Message Passing Layer
What Balances sessions across PEs? Teradata Director Program (TDP)
What provides a library of Session Management Routines? Micro Teradata Director Program (MTDP)
What does Aggregating and Locking? AMP
What provides Client side OS independence? MOSI
What PE software turns SQL into AMP steps? Optimizer
What PE software sends the plan steps to the AMPs? Dispatcher
What is the foundation of the Teradata architecture? Parallelism
What provides a library of Teradata Service Routines? Call Level Interface CLI
Will a user always have a password? Yes
Will the sum of all user and database Perm Space equal the total space on the system? Yes
Will the sum of all user and database Spool Space equal the total space on the system? No
Questions without answers:
Will tables, index subtables and stored procedures each require Perm space?
Will views or macros require Perm Space to create?
Is the SPOOL space allocated actually divided by the number of AMPs for a Per AMP Basis?
Can maximum SPOOL space be defined at the database or user level?
Is UPI and NUPI equality value accesses always a one-AMP operation?
Will both an UPI and a NUPI allow for a NULL value to be in the Primary Index column?
Can you have a duplicate row in a multi-set table that has a Unique Primary Index?
Can you implement a Primary Key with a Unique Primary Index?
The output of the row hash is called what?
A Row-ID consists of a Row Hash and what else?
How many bytes is the Row Hash and how many bytes is the Uniqueness Value?
Can two exact values containing the same data type hash differently?
Can two different values containing the same data type hash to the same value?
Does every row in a Teradata database have a Row-ID or a Row Key (if it is a PPI Table)?
Questions with answers:
Will tables, index subtables and stored procedures each require Perm space? Yes
Will views or macros require Perm Space to create? No
Is the SPOOL space allocated actually divided by the number of AMPs for a Per AMP Basis? Yes
Can maximum SPOOL space be defined at the database or user level? Yes
Is UPI and NUPI equality value accesses always a one-AMP operation? Yes
Will both an UPI and a NUPI allow for a NULL value to be in the Primary Index column? Yes
Can you have a duplicate row in a multi-set table that has a Unique Primary Index? No
Can you implement a Primary Key with a Unique Primary Index? Yes
The output of the row hash is called what? Row Hash
A Row-ID consists of a Row Hash and what else? Uniqueness value
How many bytes is the Row Hash and how many bytes is the Uniqueness Value? 4 bytes each
Can two exact values containing the same data type hash differently? No
Can two different values containing the same data type hash to the same value? Yes, it is called a collision
Does every row in a Teradata database have a Row-ID or a Row Key (if it is a PPI Table)? Yes
Questions without answers:
What architecture is it when an AMP has its own memory and manages its own disk?
The physical message passing interconnect is called what?
What does a Clique protect?
When a node fails and vprocs move to another node within the clique this is called what?
What provides for a transaction rollback in case of a failure?
Name two Teradata backup and recovery applications?
How are rows protected in a table?
What logs changed rows for a down AMP?
What provides for recovery to a point in time?
What applies to all tables and views within?
Name a Multi-platform archive utility?
Name the lowest level of protection granularity?
What protects tables from an AMP failure?
What protects a database from a physical drive failure?
Two AMPs that are grouped together for Fallback purposes are said to be in the same what?
Questions with answers:
What architecture is it when an AMP has its own memory and manages its own disk? Shared Nothing
The physical message passing interconnect is called what? BYNET
What does a Clique protect? Node failure
When a node fails and vprocs move to another node within the clique this is called what? Vproc Migration
What provides for a transaction rollback in case of a failure? Transient Journal
Name two Teradata backup and recovery applications? NetBackup and Tivoli
How are rows protected in a table? Table locks
What logs changed rows for a down AMP? Recovery Journal
What provides for recovery to a point in time? Permanent Journal
What applies to all tables and views within? Database locks
Name a Multi-platform archive utility? Archive Recovery Console (ARC)
Name the lowest level of protection granularity? Row Hash locks
What protects tables from an AMP failure? FALLBACK
What protects a database from a physical drive failure? Disk Array
Two AMPs that are grouped together for Fallback purposes are said to be in the same what? Cluster
Questions without answers:
Can you list two capabilities of Teradata Virtual Storage?
What is associated with data temperature?
Name two types of data that are usually HOT data?
Name 3 advantages of a 3NF data model?
Which data model would include the definition of a Partition Primary Index (PPI) table?
What is a benefit of implementing data types at the domain level?
The Hash Map consists of entries or buckets that contain what?
What two things does the Row-ID consist of?
What to data interfaces are used by SQL Assistant?
Can BLOBs and CLOBs be stored in NoPI tables?
In a PPI table what three components are used to identify a row?
In a PPI table what constitutes the Row Key?
What are two advantages of PPI tables?
Questions with answers:
Can you list two capabilities of Teradata Virtual Storage? Simplify adding disk space and multi-temperature
What is associated with data temperature? Frequency of access
Name two types of data that are usually HOT data? Spool and Transient Journal (now called WAL journal)
Name 3 advantages of a 3NF data model? Minimize redundancy, reduce update anomalies and access flexibility
Which data model would include the definition of a Partition Primary Index (PPI) table? Physical data model
What is a benefit of implementing data types at the domain level? Avoid data conversion
The Hash Map consists of entries or buckets that contain what? AMP numbers
What two things does the Row-ID consist of? Row Hash and Uniqueness Value
What to data interfaces are used by SQL Assistant? ODBC and Teradata .NET
Can BLOBs and CLOBs be stored in NoPI tables? Yes
In a PPI table what three components are used to identify a row? Partition # + Row Hash + Uniqueness Value
In a PPI table what constitutes the Row Key? Partition # + Row Hash
What are two advantages of PPI tables? Fast delete of rows in a partition and range queries executed without a secondary index
Questions without answers:
Can a PPI table have a Unique Primary Index if the partitioning column is not part of the Primary Index?
Is a query using the Primary Index as fast on a PPI table as a normal table?
How are normal tables sorted on each AMP?
How are PPI table sorted on each AMP?
Do columnar tables have to be NoPI tables?
Can a columnar table have multi-level partitioning?
Name two benefits of a columnar table?
Will deleting a row in a columnar table reclaim that space?
In a multi-level columnar table, can both levels have column partitioning?
What is the best way to load to a columnar table?
When two NUSIs are ANDed together Teradata can perform what retrieval technique?
When aggregation occurs on a NUSI column, the PE can access the NUSI subtable and return the result without accessing the base table. This is called what?
Which secondary indexes are hashed to different AMPs and which are AMP-Local?
Questions with answers:
Can a PPI table have a Unique Primary Index if the partitioning column is not part of the Primary Index? No
Is a query using the Primary Index as fast on a PPI table as a normal table? No
How are normal tables sorted on each AMP? Row-ID
How are PPI table sorted on each AMP? Row-Key
Do columnar tables have to be NoPI tables? Yes
Can a columnar table have multi-level partitioning? Yes
Name two benefits of a columnar table? Reduced I/O and reduced disk space usage
Will deleting a row in a columnar table reclaim that space? No
In a multi-level columnar table, can both levels have column partitioning? No
What is the best way to load to a columnar table? INSERT/ SELECT
When two NUSIs are ANDed together Teradata can perform what retrieval technique? NUSI Bit Mapping
When aggregation occurs on a NUSI column, the PE can access the NUSI subtable and return the result without accessing the base table. This is called what? Covering Index
Which secondary indexes are hashed to different AMPs and which are AMP-Local? USIs are hashed and reside in a subtable on a different AMP, NUSI subtables are always AMP-Local to their base rows.
Questions without answers:
How can you eliminate the Duplicate Row Check for a NUPI?
Does a Primary Key have to consist of only one column?
Can a Primary Key consist of more than one column?
Can you compress the Primary Index with Multi-value compression?
Can you compress a Non-Unique Secondary Index column with Multi-value compression?
Will the Parsing Engine take “hints” to come up with a plan?
What is the best thing a DBA can do to help the PE optimizer?
What is it called when the PE determines that some partitions won’t be needed for a query?
Where does a Global Temporary Table get its space from?
Where does a Volatile table get its space from?
Where does a Derived table get its space from?
A FastLoad is equivalent to what in TPT?
A MultiLoad is equivalent to what in TPT?
A FastExport is equivalent to what in TPT?
A TPump is equivalent to what in TPT?
Questions with answers:
How can you eliminate the Duplicate Row Check for a NUPI? Create the table as a Multiset table
Does a Primary Key have to consist of only one column? No
Can a Primary Key consist of more than one column? Yes
Can you compress the Primary Index with Multi-value compression? No
Can you compress a Non-Unique Secondary Index column with Multi-value compression? Yes
Will the Parsing Engine take “hints” to come up with a plan? No
What is the best thing a DBA can do to help the PE optimizer? Collect statistics
What is it called when the PE determines that some partitions won’t be needed for a query? Partition Elimination
Where does a Global Temporary Table get its space from? Temporary Space
Where does a Volatile table get its space from? Spool Space
Where does a Derived table get its space from? Spool Space
A FastLoad is equivalent to what in TPT? LOAD
A MultiLoad is equivalent to what in TPT? UPDATE
A FastExport is equivalent to what in TPT? EXPORT
A TPump is equivalent to what in TPT? STREAM
Questions without answers:
Can BTEQ import data from the host to Teradata and export from Teradata to the host?
With BTEQ is it useful to utilize multiple sessions when ALL AMPS will be used for the transaction?
Can a TPT DataConnector operator function as both a producer and a consumer?
What are 3 advantages of using TPT over the standalone utilities?
Which TPT operator is used to load data to a Teradata table?
Which TPT operator is used to read from a file and produce a stream?
Does the number of Export sessions default to the number of AMPs?
Can the UPDATE operator process tables with an USI or Referential Integrity Defined?
Can a database or user have multiple owners, but only one creator?
Are an Owner or a Parent terms in Teradata that are interchangeable?
Can another user ever have more space than DBC?
Are space limits enforced at the table level?
Questions with answers:
Can BTEQ import data from the host to Teradata and export from Teradata to the host? Yes
With BTEQ is it useful to utilize multiple sessions when ALL AMPS will be used for the transaction? No
Can a TPT DataConnector operator function as both a producer and a consumer? Yes
What are 3 advantages of using TPT over the standalone utilities? Runs multiple job steps, is a single scripting language and upgrades are available with new releases.
Which TPT operator is used to load data to a Teradata table? Consumer
Which TPT operator is used to read from a file and produce a stream? Producer
Does the number of Export sessions default to the number of AMPs? Yes
Can the UPDATE operator process tables with an USI or Referential Integrity Defined? No
Can a database or user have multiple owners, but only one creator? Yes
Are an Owner or a Parent terms in Teradata that are interchangeable? Yes
Can another user ever have more space than DBC? Yes
Are space limits enforced at the table level? No
Questions without answers:
What statement would a user use to change their password, default database and date format?
When creating a new user, which option defaults to the immediate owner’s value?
When creating a new user, which options are mandatory with the CREATE USER command?
What are two reasons to use profiles?
When a user’s profile is set to NULL, what are two things in the current session that are affected immediately?
Name three types of access rights?
What keyword option on the GRANT command grants privileges to a database or user and all of its current and future descendants?
What access rights can be granted at the column level?
What user is utilized to grant an access right to every user in a Teradata system?
How many levels of role nesting are allowed?
What two things may roles be granted to?
Name three reasons to use roles?
Questions with answers:
What statement would a user use to change their password, default database and date format? Modify User
When creating a new user, which option defaults to the immediate owner’s value? SPOOL
When creating a new user, which options are mandatory with the CREATE USER command? PERM Space, a User Name, and a Password.
What are two reasons to use profiles? Specify password security for groups of users and to change common user attributes for groups of users.
When a user’s profile is set to NULL, what are two things in the current session that are affected immediately? SPOOL and TEMP values
Name three types of access rights? Automatic, Implicit and Explicit
What keyword option on the GRANT command grants privileges to a database or user and all of its current and future descendants? ALL
What access rights can be granted at the column level? UPDATE, SELECT, REFERENCES and INSERT
What user is utilized to grant an access right to every user in a Teradata system? PUBLIC
How many levels of role nesting are allowed? 1
What two things may roles be granted to? Users and other roles
Name three reasons to use roles? Simplify access rights administration, reduces the number of rows in the DBC.AccessRights table, and performance is improved because of less data dictionary contention
Questions without answers:
With DBQL, what is the size of the default text captured for queries?
With DBQL, what table contains the default rows logged?
Without TASM workloads enabled, how what two groups will be utilized?
With TASM workloads enabled, a user query is associated with what to things?
What type of TASM filter or throttle is needed to limit the number of FastLoad jobs?
What type of TASM filter or throttle is needed to limit the number of concurrent sessions?
What type of TASM filter or throttle is needed to reject queries based on max processing time?
What type of TASM filter or throttle is needed to delay more than 20 queries for a specific account?
What type of TASM filter or throttle is needed to reject queries accessing a specific database?
What purpose is the default workload definition named “WE-Default” provide?
Which application is used to initially define workload definitions?
In what order will Teradata act upon object and workload throttles, exception criteria and object filters?
Questions with answers:
With DBQL, what is the size of the default text captured for queries? 200 characters
With DBQL, what table contains the default rows logged? DBC.DBQlogTbl
Without TASM workloads enabled, how what two groups will be utilized? The user’s session is associated with a Performance Group which is assigned to an Allocation Group
With TASM workloads enabled, a user query is associated with what to things? A Workload which is assigned to an Allocation Group
What type of TASM filter or throttle is needed to limit the number of FastLoad jobs? Load Utility Throttle
What type of TASM filter or throttle is needed to limit the number of concurrent sessions? Object Throttle
What type of TASM filter or throttle is needed to reject queries based on max processing time? Query Resource Filter
What type of TASM filter or throttle is needed to delay more than 20 queries for a specific account? Object throttle
What type of TASM filter or throttle is needed to reject queries accessing a specific database? Object Access Filter
What purpose is the default workload definition named “WE-Default” provide? This is the default workload for any queries that are not associated with a workload
Which application is used to initially define workload definitions? Workload Analyzer
In what order will Teradata act upon object and workload throttles, exception criteria and object filters? Object filters, Object throttles, Workload throttles and Exception criteria
Questions without answers:
Does the permanent journal store committed, uncommitted and aborted changes to a row in a table?
Can a database or user have multiple permanent journals?
Do you need separate Permanent Journals for before and after images?
What two areas are also part of the Current Journal?
Do tables that use a Permanent Journal need to be in the same database as the Permanent Journal?
Can you use the ARC COPY statement to copy tables, views, macros and triggers from one system to another?
Is it Rollforward or Rollback that is associated with a software failure?
Is it Rollforward or Rollback that is associated with a hardware failure?
Questions with answers:
Does the permanent journal store committed, uncommitted and aborted changes to a row in a table? Yes
Can a database or user have multiple permanent journals? No
Do you need separate Permanent Journals for before and after images? No
What two areas are also part of the Current Journal? The Saved and Active areas
Do tables that use a Permanent Journal need to be in the same database as the Permanent Journal? No
Can you use the ARC COPY statement to copy tables, views, macros and triggers from one system to another? Yes
Is it Rollforward or Rollback that is associated with a software failure? Rollback
Is it Rollforward or Rollback that is associated with a hardware failure? Rollforward