41. What are Journals in Teradata?
In Teradata, Journals are placed on the system to provide data availability in the absence of process failure. There are two types of journals available are Recovery Journals and Permanent Journals.
42. How many types of BTEQ Exports are there in Teradata?
Teradata BTEQ Exports are four types, they are
- Export DATA
- Export INDICDATA
- Export REPORT
- Export DIF
43. Explain diff b/w SET and MULTISET tables in Teradata?
- SET Table: Not allowing duplicate records.
- MULTISET Table: Allows duplicate records.
44. Explain the ways to create a table in Teradata?
Tables can be created using CREATE TABLE statement, CREATE TABLE a statement with column definition, CREATE TABLE from an existing table, CREATE TABLE statement with a SELECT statement.
45. Write a statement to find duplicate records in a table?
We can find duplicate records using the DISTINCT or GROUP BY statement.
Finding Duplicate Records Using DISTINCT statement:
SELECT DISTINCT column 1, column 2…Â
FROM tablename;
Finding Duplicate Records Using GROUP BY statement:
SELECT column 1, column 2,…Â
FROM tablenameÂ
GROUP BY column 1, column 2….;
46. Write a query to identify the number of AMPs in the system?
By using SELECT HASHAMP() + 1; query we can find the number of AMPs.
47. What’s the purpose of this below query?
SELECT HASHMAP (HASHBUCKET(HASHROW(primaryindexvalue))), COUNT(*)
FROM table-name GROUP BY 1;Â
The above query identifies the number of rows in each AMP for a particular database table.
48. What is Spool Space Error in Teradata?Â
It’s one type of error in Teradata. Spool Space Error will trigger when intermediate results of the query exceed the AMP spool space limit set for a user.
49. Explain the TENACITY command in Teradata?
TENACITY command in Teradata specifies the total waiting time to establish a new connection.
50. Explain the diff b/w NUSI and Full table scan?
In Teradata, NUSI and FTS will access all the AMPs, but the difference is,
- FTS will access all the blocks within the AMP.
- Whereas NUSI will access the blocks only if the sub-table contains the qualifying rows.
51. What is the purpose of the SHOW statement?
SHOW statements return the result of the last data definition statement performed against the named database object in the form of a CREATE database_object or ALTER database_objectstatement.
52. What are Hot Standby Nodes (HSN) in Teradata?
Hot Standby Node (HSN) is a node that is a member of a clique that is not configured (initially) to execute any Teradata process.
53. Explain about Volatile Tables in Teradata?
The primary index for a volatile table can be nonpartitioned or row-partitioned. The table can also be defined without a primary index (NoPI).
The following options are not permitted for volatile tables.
- Referential integrity constraints
- CHECK constraints
- Permanent journaling
- DEFAULT clause
- TITLE clause
- Named indexes
- Column partitioning
- Primary AMP index
54. What is LOG and NO LOG in Teradata?
Global temporary and volatile tables permit you to define whether their activity is logged to the transient journal. While the NO LOG option reduces the system overhead of logging, it is also true that table modifications are lost and cannot be recovered upon an aborted SQL request.
55. Explain Permanent Journaling?
The permanent journal is a user-specified table that can be used to capture both before images and after images of Teradata Database transactions. Journal entries in this table can be used by the Archive/Recovery utility to roll forward or rollback transactions during a recovery operation.
56. Explain the MERGEBLOCKRATIO in Teradata?
The MERGEBLOCKRATIO option provides a way to combine existing small data blocks into a single larger data block during full table modification operations for permanent tables and permanent journal tables. This option is not available for volatile and global temporary files. The file system uses the merge block ratio that you specify to reduce the number of data blocks within a table that would otherwise consist mainly of small data blocks.
57. Explain the DATA BLOCK SIZE in Teradata?
- DATABLOCKSIZE sets the maximum data block size for blocks that contain multiple rows. The data block is the physical I/O unit for the Teradata file system.
- Larger block sizes enhance full table scan operations by selecting more rows in a single I/O. Smaller block sizes are best for transaction-oriented tables to minimize overhead by retrieving only what is needed.
58. Explain the BLOCK COMPRESSION?
Use this option to set the temperature-based block compression state of a table. Teradata Virtual Storage tracks data temperatures at the level of cylinders, not tables, and the file system obtains its temperature information from Teradata Virtual Storage, so it also handles temperature-related compression at cylinder level.
59. Explain Surrogate Keys in Teradata?
Situations sometimes occur where the identification and choice of a simple primary key are difficult, if not impossible. There might be no single column that uniquely identifies the rows of a table or there might be performance considerations that argue against using a composite key. In these situations, surrogate keys are an ideal solution.
A surrogate key is an artificial simple key used to identify individual rows uniquely when there is no natural key or when the situation demands a simple key, but no natural non-composite key exists. Surrogate keys do not identify individual rows in a meaningful way: they are simply an arbitrary method to distinguish between them.
60. Difference between PRIMARY KEY Constraints Versus UNIQUE Constraints in Teradata?
- UNIQUE and PRIMARY KEY constraints can only be defined on a column set that is also constrained to be NOT NULL.
- To create a composite, or multicolumn, the primary key for a table, you must specify the PRIMARY KEY constraint at the table level, not the column level.
- Both UNIQUE and PRIMARY KEY constraints can be defined on a UDT column.
- Teradata Database also supports the related constraints UNIQUE INDEX and UNIQUE PRIMARY INDEX.
