Teradata Study Guide

Permanent Journal The purpose of a permanent journal is to maintain a sequential history of all changes made to the rows of one or more tables. Permanent journals help protect user data when users commit, uncommit or abort transactions. A permanent journal can capture a snapshot of rows before a change, after a change, or both. You use permanent journaling to protect data. Unlike the automatic journal, the contents of a permanent journal remain until you drop them. When you create a new journal table, you can use several options to control the type of information to be captured.  You create permanent journals when you create a user or database. To create permanent journals within an existing user or database, use the MODIFY statement. Users activate permanent journaling by including the JOURNAL option in the CREATE or MODIFY statements for users or databases. You must allocate sufficient permanent space to a database or user that will contain permanent journals. If a database or

How teradata makes sure that there are no duplicate rows being inserted when its a SET table? Teradata redirects the new inserted row as per its Primary Index to the target AMP (Access Module Processor) on the basis of its row hash value, and if it find same row hash value in that AMP then it start comparing the whole row, and find out if duplicate. When the target table has UPI(Unique Primary Index) then the duplicate row is rejected with an error. In case of a NUPI(Non-Unique Primary Index) then it is rejected without throwing any error

JDBC: Java Database Connectivity (JDBC) provides Java developers with a standard API (application Programming Interfaces) that is used to access databases regardless of the driver and database product. To use JDBC you'll need at least JDK 1.1 a database and a JDBC driver. There are several characteristics of JDBC:ODBC : ODBC is the acronym for Open DataBase Connectivity a Microsoft Universal Data Access standard that started life as the Windows implementation of the X/Open SQL Call Level Interface specification. ODBC Driver Manager - an application binds to this generic library which is responsible for loading the requested ODBC Driver. ODBC Driver - dynamically loaded by the ODBC Driver manager for making connection to target Database. Difference b/w them is 1.ODBC is for Microsoft and JDBC is for java applications. 2.ODBC can't be directly used with Java because it uses a C interface. 3.ODBC makes use of pointers which have been removed totally from java.

Table Type Specifications of SET VS MULTISET There are two different table type philosophies so there are two different type tables. They are SET and MULTISET. It has been said, “A man with one watch knows the time, but a man with two watches is never sure”. When Teradata was originally designed it did not allow duplicate rows in a table. If any row in the same table had the same values in every column Teradata would throw one of the rows out. They believed a second row was a mistake. Why would someone need two watches and why would someone need two rows exactly the same? This is SET theory and a SET table kicks out duplicate rows. The ANSI standard believed in a different philosophy. If two rows are entered into a table that are exact duplicates then this is acceptable. If a person wants to wear two watches then they probably have a good reason. This is a MULTISET table and duplicate rows are allowed. If you do not specify SET or MULTISET, one is used as a default. Here is the issue:

Definition: Teradata SQL Assistant (TSA), as part of Teradata Tools and Utilities (TTU), is an ODBC-based client utility used to access and manipulate data on ODBC-compliant database servers. It has two editions: 1) Teradata SQL Assistant for Microsoft Windows 2) Teradata SQL Assistant/Web Edition Teradata SQL Assistant is an information discovery tool designed for Windows XP and Windows 2000. Teradata SQL Assistant retrieves data from any ODBC-compliant database server. The data can then be manipulated and stored on the desktop PC. Teradata SQL Assistant/Web Edition is a web-based query tool that allows you to compose queries, submit them to the Teradata Database, and then view the results in a web browser. Overview : Teradata SQL Assistant for Microsoft Windows, originally called "Queryman" (before V. 6.0)or "QueryMan" (V. 6.0 and up), is also known as "SQLA" among programmers. It supports import / export tasks, but not the serious ones. Wit

PE, acronym for "Parsing Engine," is the type of vproc (Virtual Processor) for session control, task dispatching and SQL parsing in the multi-tasking and possibly parallel-processing environment of the Teradata Database. DEFINITION PE, acronym for "Parsing Engine," is the type of vproc (Virtual Processor) for session control, task dispatching and SQL parsing in the multi-tasking and possibly parallel-processing environment of the Teradata Database. Vproc is a software process running in an SMP (Symmetric Multiprocessing) environment or a node. COMPONENTS The components of a PE vproc can be classified as the following:    1. Parser: It desolves SQL statements into RDBMS processing steps;    2. Query Optimizer: It decides the optimal way to access data;    3. Step Generator: It 1) produces processing steps, and 2) encapsulates them into packages;    4. Dispatcher: It 1) transmits the encapsulated steps from the parser to the pertinent AMPs, and 2) perf