Introduction to Teradata

Derived tables

- Is a type of temporary table obtained from one or more other tables as the result of a subquery. - Is specified in an SQL SELECT statement. - Avoids the need to use the CREATE and DROP TABLE statements for storing retrieved information. - Is useful when you are coding more sophisticated, complex queries.

Foreign Key

A Foreign Key (FK) identifies table relationships. They model the relationship between data values across tables. Relational databases, like Teradata Database, permit data values to associate across more than one table. Thus each FK a table may have must exist somewhere as a PK. That is, there must be referential integrity between FKs and PKs.

Primary Key

A Primary Key (PK), a term that comes from data modeling, defines a column, or columns, that uniquely identify a row in a table. Because it is used for identification, a PK cannot be null. There must be something in that column, or columns, that uniquely identify it. PK values should not be changed. Historical information, as well as relationships with others tables, may be lost if a PK is changed or re-used. A PK is a logical relational database concept. It may or may not be the best column, or columns, to choose as a PI for a table.

Teradata CLIv2 for Workstation-Attached Systems

A Teradata proprietary API and library providing an interface between applications on a TCP/IP-connected client and Teradata Database server. Teradata CLIv2 for workstation-attached systems can: Build parcels which are packaged by Micro Teradata Director Program (MTDP) and sent to Teradata Database using the Micro Operating System Interface (MOSI). Manage all interactions between the application and Teradata Database. Provide an application with a pointer to data rows returned from Teradata Database.

Teradata CLIv2 for Mainframe-Attached Systems

A collection of callable service routines providing the interface between applications and the Teradata Director Program (TDP) on an IBM mainframe client. Teradata CLIv2 for mainframe-attached systems can operate with all versions of IBM operating systems, including Customer Information Control System (CICS), Information Management System (IMS), and IBM System z Operating System. By way of TDP, Teradata CLIv2 for mainframe-attached systems sends requests to the server and provides client applications with responses from the server. Teradata CLIv2 for mainframe-attached systems provides support for: Managing multiple serially executed requests in a session Managing multiple simultaneous sessions to the same or different servers Using cooperative processing so an application can perform operations on the client and the server at the same time Generally insulating the application from the details of communicating with a server

Single Data Store

A design goal of Teradata Database was to provide a single data store for a variety of client architectures. This approach greatly reduces data duplication and inaccuracies that can creep into data that is maintained in multiple stores. This approach to data storage is known as the single version of the business, and Teradata Database implements this through heterogeneous client access. Clients can access a single copy of enterprise data and Teradata Database takes care of such things as data type translation, connections, concurrency, and workload management.

Fallback tables

A fallback table is a duplicate copy of a primary table. Each fallback row is stored on a different AMP from the one where it is stored. Benefits Drawbacks Fallback protection maintains availability of the data in the eent of a down AMP, or its associated disk storage. In the event of a data error of the primary row, the fallback row can be used to repair the primary Copy Fallback protection doubles the storage and the I/O required for INSERT, UPDATE and DELETE operations. Critical tables should all be defined in fallback mode. Fallback protection is specified in the CREATE TABLE/ ALTER TABLE statements, the default being NO FALLBACK. CREATE MULTISET TABLE EMPLOYEE, FALLBACK, DATABLOCKSIZE=32 KBYTES,FREESPACE= 10 PERCENT,CHECKSUM=NONE (EMPNO INTEGER, SALARY INTEGER);

Node

A hardware assembly containing several, tightly coupled, central processing units (CPUs) in an SMP configuration. An SMP node is connected to one or more disk arrays with the following installed on the node: Teradata Database software Client interface software Operating system Multiple processors with shared- memory Failsafe power provisions An MPP configuration is a configuration of two or more loosely coupled SMP nodes.

Permanent journal

A journaling table is available for databases/users and tables. It keeps a before and/or after image of all changing rows, and stores them permanently for rollback recovery and/or rollforward recovery(after a full system restore). It is created for a specified database or user, with the CREATE/MODIFY USER/DATABASE, and there can be only one journal per User/Database: Each table created will default to its database/user journal. We can disable journaling for a database/user, and still specify another database/user journal as the journal for a table. We can disable journaling for a table by the NO BEFORE JOURNAL/NO AFTER JOURNAL in the CREATE statement. When creating a table, we can specify for it another permanent journal table in another database/user., with the command DEFAULT JOURNAL TABLE=... Permanent Journals take up PERM space. A permanent journal will no longer work or be applicable if the system is reconfigured with more AMPs or a MODIFY USER/DATABASE command changes the object. Before Journal: Can store permanently before images of all rows in a table for rollback recovery. A before image of a row is stored in the same AMP as the one where the row has changed. After journal: Can store permanently after images of all rows in a table for rollforward recovery(after a full system restore). An after image is stored on another AMP . After journals are never stored on the same node or the same clique. Dual Before journal: Stores permanently a before image of the changing row in the same AMP and another different AMP. Dual After journal: Stores permanently an after image of the row that changed in a different AMP and the same AMP where the row is stored.

Set processing

A lot of data processing is set processing, which is what relational databases do best. Instead of processing row-by-row sequentially, you can process relational data set-by-set, without a cursor. For example, to sum all payment rows with 100 or less balances, a single SQL statement completely processes all rows that meet the condition as a set. With sufficient rows to process, this can be 10 to 30 or more times faster than row-at-a-time processing. Some good uses of SET processing include: An update with all AMPs involved Single session processing which takes advantage of parallel processing Efficient updates of large amounts of data

Primary Index

A physical mechanism used to store and access the rows of a table. Indexes on tables in a relational database function much like indexes in books, they speed up information retrieval. The most efficient access methods to get data in a table is through the PI. For this reason, choosing a PI should take the following design goal into consideration: choosing a PI that gives good distribution of data across the AMPs must be balanced against choosing a PI that reflects the most common usage pattern of the table. In general, Teradata Database uses indexes to: Distribute data rows Locate data rows Improve performance IF you create a table with... THEN... a NUPI the PI is a column, or columns, that may have duplicate values. a UPI the PI is a column, or columns, that has no duplicate values. NoPI there is no PI column and rows are not hashed based on any column values. Unique Primary Indexes (UPIs) guarantee uniform distribution of table rows Nonunique Primary Indexes (NUPIs) can cause skewed data. While not a guarantor of uniform row distribution, the degree of uniqueness of the index will determine the degree of uniformity of the distribution. Because all rows with the same PI value are distributed to the same AMP, columns with a small number of distinct values that are repeated frequently do not make good PI candidates. User tables can also be created explicitly without a primary index. These NoPI tables are used for special purposes. NoPI tables are typically used as staging tables to allow faster data loading. NoPI tables must be used for tables that will use Teradata Columnar partitioning. Note: If you do not explicitly specify a primary index when a table is created, Teradata Database uses the first column as the nonunique primary index by default.

Teradata Database Recursive Query

A recursive query is a named query expression that references itself in its definition. The self- referencing capability gives the user a simple way to search a table using iterative self-join and set operations. The recursive query feature benefits the user by reducing the complexity of the queries and allowing a certain class of queries to execute more efficiently. Recursive queries are implemented using the WITH RECURSIVE clause in the statement and the RECURSIVE clause in the CREATE VIEW statement.

Scalar functions

A scalar function works on input parameters to create a result. When it is part of an expression, the function is invoked as needed whenever expressions are evaluated for an SQL statement. When a function completes, its result is used by the expression in which the function was referenced. For example, the following request returns the current date plus 13 years. SELECT ADD_MONTHS (CURRENT_DATE, 12*13); The following request returns the date 6 months ago. SELECT ADD_MONTHS (CURRENT_DATE, -6);

Teradata Database Server

A server implements the actual relational database that processes requests received from Teradata CLIv2 for mainframe-attached systems by way of TDP. The following figure illustrates the logical structure of the client-server interface on mainframe-attached systems.

Down AMP Recovery Journal

Active during AMP failure only. Journals fallback tables only. Used to recover an AMP, discarded after that the AMP is repaired.

Aggregate Functions

Aggregate functions produce results from sets of relational data that you have grouped (optionally) using a GROUP BY or ORDER BY clause. Aggregate functions process each set and produce one result for each set. The function... Returns the... AVG arithmetic average of the values in a specified column. COUNT number of qualified rows. MAX maximum column value for the specified column. MIN minimum column value for the specified column. SUM arithmetic sum of a specified column.

AMP Cluster

An AMP cluster is a group of 2 to 8 AMPs that provide fallback capability to each other. The system becomes unavailable if two AMPs in the same cluster go down. 2 AMPs in the same cluster shouldn't be in the same node or in the same clique. However, in a single-clique system to ensure that a disk failure will not bring down both AMPs in a cluster, disks and AMPs are divided into two subpools, and clustering is done across the subpools. A subpool is a logical grouping of AMPs and disks for fault-tolerance.

Enterprise Data Model

An Enterprise Data Model is an integrated view of the data produced and consumed across an entire organization. An Enterprise Data Model represents a single integrated definition of data, unbiased of any system or application. An Enterprise Data Model is independent of "how" the data is physically sourced, stored, processed or accessed. An EDM is a data architectural framework used for integration. It enables the identification of shareable and/or redundant data across functional and organizational boundaries. Integrated data provides a "single version of the truth" for the benefit of all.

Symmetric multiprocessing (SMP)

An SMP Teradata Database has a single node that contains multiple CPUs sharing a memory pool.

Active Data Warehouse

An active data warehouse: - Provides a single up-to-date view of the enterprise on one platform. - Represents a logically consistent store of detailed data available for strategic, tactical, and event driven business decision making. - Relies on timely updates to the critical data (as close to real time as needed). - Supports short, tactical queries that return in seconds, alongside of traditional decision support.

Macros

Consists of one or more SQL statements that can be executed by performing a single request. Each time the macro is performed, one or more rows of data may be returned. You can create a macro for your own use, or grant execution authorization to others. For example, your macro might enable a user in another department to perform operations on the data in Teradata Database. When executing the macro, the user need not be aware of the database access, the tables affected, or even the results. Regardless of the number of statements in a macro, Teradata Database treats it as a single request. When you execute a macro, the system processes either all of the SQL statements, or processes none of the statements. If a macro fails, the system aborts it, backs out any updates, and returns the database to its original state. Macros are particularly useful for enforcing data integrity rules, providing data security, and improving performance.

Global temporary tables

Created in the Temp space of a user/database. Are private to the session. Are dropped automatically at the end of a session. Have a persistent table definition stored in the Data Dictionary: The saved definition may be shared by multiple users and sessions with each session getting its own instance of the table.

Teradata Data types

Data types: Teradata Database supports ... Including, for example... Teradata Database data types - Byte - Graphic - Period - Geospatial ANSI/ISO-compliant data types - Large Objects (LOBs): - Binary Large Objects (BLOBs) - Character Large Objects (CLOBs) - Character - DateTime - Interval - Numeric User-defined Types (UDTs) - Distinct - Structured Data attributes: Data Type Attribute ANSI/ISO Teradata Database Extension to ANSI/ISO NOT NULL X UPPERCASE X [NOT] CASESPECIFIC X FORMAT string_literal X TITLE string_literal X NAMED name X DEFAULT value X DEFAULT USER X DEFAULT DATE X DEFAULT TIME X DEFAULT NULL X WITH DEFAULT X CHARACTER SET X

Views

Database views are actually virtual tables that you can use as if they were physical tables to retrieve data defining columns from underlying views or tables, or from both. A view does not contain data and is not materialized until a DML statement references it. Benefits of Using Views A user view of data in the database. Security for restricting table access and updates. Well-defined, well-tested, high-performance access to data. Logical data independence.

Fallback and the Permanent Journal

Fallback protected tables for which we specified journaling will automatically receive dual image journaling. Tables without fallback can request single image or dual image journaling.

Global temporary trace tables

Global temporary trace tables: Store trace output for the length of the session. Have a persistent table definition stored in the Data Dictionary. Are useful for debugging SQL stored procedures (via a call to an external stored procedure written to the trace output) and external routines (UDFs, UDMs, and external stored procedures).

BYNET

Hardware interprocessor that loosely couples all the SMP nodes in a multinode system. The BYNET possesses high-speed logic that provides bi-directional broadcast, multicast, and point-to-point communication and merge functions. A multinode system has at least two BYNETs. This creates a fault-tolerant environment and enhances interprocessor communication. Load-balancing software optimizes transmission of messages over the BYNETs. If one BYNET should fail, the second can handle the traffic

Hot Standby Nodes

Hot standby nodes allow spare nodes to be incorporated into the production environment. Teradata Database can use spare nodes to improve availability and maintain performance levels in the event of a node failure. A hot standby node is a node that: Is a member of a clique. Does not normally participate in Teradata Database operations. Can be brought in to participate in Teradata Database operations to compensate for the loss of a node in the clique. Configuring a hot standby node can eliminate the system-wide performance degradation associated with the loss of a node. A hot standby node is added to each clique in the system. When a node fails, all AMPs and all LAN-attached PEs on the failed node migrate to the node designated as the hot standby. The hot standby node becomes a production node. When the failed node returns to service, it becomes the new hot standby node. Configuring hot standby nodes eliminates: Restarts that are required to bring a failed node back into service. Degraded service when vprocs have migrated to other nodes in a clique.

Java Database Connectivity (JDBC)

JDBC is a specification for an API that allows platform-independent Java applications to access Teradata Database using SQL and external stored procedures. The JDBC API provides a standard set of interfaces for: Opening connections to databases Executing SQL statements Processing results Teradata JDBC Driver provides access to Teradata Database using the Java language. Teradata JDBC Driver is a type 4 (pure Java) JDBC Driver. It is a set of Java classes that use TCP/IP to communicate directly with Teradata Database.

Transient journal

Logs before image of a row before a transaction Used by the system to roll back failed transactions aborted by a user or the system. Keeps the image on the same AMP as the row, discards when the transaction or the rollback is completed. Captures: Begin/End Transaction indicators Before row images for UPDATE and DELETE statements Row IDs for INSERT statements Control records for CREATE, DROP, DELETE, and ALTER statements Each AMP has its own personal and distinct transient journal, this internal table is not spread across all AMPs, so each AMP takes the before picture of any row that changes on its virtual disk.

Micro Operating System Interface (MOSI)

MOSI is the interface between MTDP and Teradata Database. MOSI is a library of service routines providing operating system independence among clients that access Teradata Database. With MOSI, only one version of MTDP is required to run on all workstation-attached platforms.

Micro Teradata Director Program (MTDP)

MTDP is the interface between Teradata CLIv2 for workstation-attached systems and MOSI. Functions of MTDP include: - Session initiation and termination - Logging, verification, recovery, and restart - Physical input to and output from the server Note: MTDP does not control session balancing; session balancing on workstation-attached systems is controlled by Teradata Database Gateway on the server.

Massively parallel processing (MPP)

Multiple SMP nodes working together comprise a larger, MPP implementation of a Teradata system. The nodes are connected using the BYNET, which allows multiple virtual processors on multiple nodes to communicate with each other.

Teradata Generic Security Service

Network security for Teradata is provided by Teradata Generic Security Service (TDGSS) software. It provides for secure communication between a workstation client and Teradata Database.

NoPI tables

NoPI tables are permanent tables that do not have primary indexes defined on them. They provide a performance advantage when used as staging tables to load data from FastLoad or TPump Array INSERT. They can have secondary indexes defined on them to avoid full-table scans during row access.

Open Database Connectivity (ODBC)

ODBC Driver for Teradata provides an interface to Teradata Databases using the industry standard ODBC API. ODBC Driver for Teradata provides Core-level SQL and Extension-level 1 (with some Extension-level 2) function call capability using the Windows Sockets (WinSock) Transmission Control Protocol/Internet Protocol (TCP/IP) communications software interface. ODBC operates independently of CLI.

OLE DB Provider for Teradata

OLE DB Provider for Teradata allows programmers to design application programs that allow access between Teradata Database and data stores that do no use SQL.

Ordered Analytical Functions

Ordered analytical functions work over a range of data for a particular set of rows in some specific order to produce a result for each row in the set. Like aggregate functions, ordered analytical functions are called for each item in a set. But unlike an aggregate function, an ordered analytical function produces a result for each detail item. Ordered analytical functions allow you to perform sophisticated data mining on the information in your databases to get the answers to questions that SQL otherwise cannot provide.

Queue tables

Permanent tables with a timestamp column. The timestamp indicates when each row was inserted into the table. Establish first-in first-out (FIFO) ordering of table contents, which is needed for customer applications requiring event processing.

Primary Index V Primary Key

Primary Key Primary Index Identifies a row uniquely. Distributes rows. Does not imply access path. Defines most common access path. Must be unique. May be unique or nonunique. May not be null. May be null. Causes a Unique Primary Index (UPI) or Unique Secondary Index (USI) to be created. N/A Constraint used to ensure referential integrity. Physical access mechanism. Required by Teradata Database only if referential integrity checks are to be performed. Defined for most production tables. Some staging tables may not have a primary index (NoPI table). - If Teradata Database performs referential integrity checks, then the column limit is 64. - If Teradata Database performs no referential integrity checks, then there is no arbitrary column limit. 64-column limit. Values should not be changed if you want to maintain data integrity and preserve historical relations among tables. Values can be changed.

Stores Procedures

Procedures that are executed on Teradata Database server space. It is a combination of procedural control statements, SQL statements, and control declarations that provide a procedural interface to Teradata Database. You can create or replace an external stored procedure through the COMPILE command in Basic Teradata Query (BTEQ), BTEQ for Microsoft Windows systems (BTEQWIN), Teradata Studio, and SQL Assistant. You must specify a source file as input for the COMPILE command. Stored procedures do not need to be compiled, but external stored procedures do. DDL Statements with Stored Procedures: Use This Statement... To... CREATE PROCEDURE direct the stored procedure compiler to create a procedure from the SQL statements in the remainder of the statement text. ALTER PROCEDURE direct the stored procedure compiler to recompile a stored procedure created in an earlier version of Teradata Database without executing SHOW PROCEDURE and REPLACE PROCEDURE statements. DROP PROCEDURE drop a stored procedure. RENAME PROCEDURE rename a procedure. REPLACE PROCEDURE direct the stored procedure compiler to replace the definition of an existing stored procedure. If the specified stored procedure does not exist, create a new procedure by that name from the SQL statements in the remainder of the source text.

Row processing

Row-by-row processing is where there are many rows to process, one row is fetched at a time and all calculations are done on it, then it is updated or inserted. Then the next row is fetched and processed. This is row-by-row processing and it makes for a slow program. A benefit of row processing is that there is less lock contention.

User-Defined Functions

SQL UDFs allow you to encapsulate regular SQL expressions in functions and then use them like standard SQL functions. External UDFs allow you to write your own functions in the C, C++, or Java programming language, install them on the database, and then use them like standard SQL functions. UDF Type Description Aggregate Aggregate functions produce summary results. They differ from scalar functions in that they take grouped sets of relational data, make a pass over each group, and return one result for the group. Some examples of standard SQL aggregate functions are AVG, SUM, MAX, and MIN. Scalar Scalar functions take input parameters and return a single value result. Examples of standard SQL scalar functions are CHARACTER_LENGTH, POSITION, and TRIM. Table A table function is invoked in the FROM clause of a SELECT statement and returns a table to the statement.

Tactical query

Short, highly tuned queries that facilitate action-taking or decision- making in a time-sensitive environment. They usually come with a clear service level expectation and consume a very small percentage of the overall system resources. Tactical queries are usually repetitively executed and take advantage of techniques such as request (query plan) caching and session-pooling.

Boardless BYNET

Single-node SMP systems use Boardless BYNET (or virtual BYNET) software to provide the same functions without the presence of BYNET hardware.

Parallel Data Extensions (PDE)

Software interface layer that lies between the operating system and database. PDE enables the database to operate in a parallel environment. PDE provides Teradata Database with the ability to: Run in a parallel environment Execute vprocs Apply a flexible priority scheduler to Teradata Database sessions Consistently manage memory, I/O, and messaging system interfaces across multiple OS platforms PDE provides a series of parallel operating system services, which include: Facilities to manage parallel execution of database operations on multiple nodes. Dynamic distribution of database tasks. Coordination of task execution within and between nodes. PDE enables MPP systems to take advantage of hardware features such as the BYNET and shared disk arrays.

User-Defined Methods

Special kind of UDF that is associated with a UDT. Teradata Database supports two types of UDMs: Instance Methods: An instance method operates on a specific instance of a distinct or structured UDT. For example, an instance method named area might calculate and return the area of an instance of a structured UDT named circle that contains attributes x, y, and radius. Instance methods can also provide transform, ordering, and cast functionality for a distinct or structured UDT. Teradata Database uses this functionality during certain operations involving the UDT. Constructor methods: A constructor method initializes an instance of a structured UDT. A structured UDT can have more than one constructor method, each one providing different initialization options.

Error logging tables

Store information about errors associated with a permanent table. Log information about insert and update errors.

Strategic query

Strategic queries represent business questions that are intended to draw strategic advantage from large stores of data. Often complex, involving aggregations and joins across multiple tables in the database. They are sometimes long-running and tend not to have a strict service level expectation. Strategic queries are often ad hoc. They may require significant database resources to execute. They are often submitted from third-party tools, and they can take advantage of session pooling

Teradata Director Program

TDP manages communications between Teradata CLIv2 for mainframe-attached systems and the Teradata Database server. TDP executes on the same mainframe as Teradata CLIv2 for mainframe-attached systems, but runs as a different job or virtual machine. Although an individual TDP is associated with one logical server, any number of TDPs may operate and be simultaneously accessed by Teradata CLIv2 for mainframe-attached systems on the same mainframe. Each TDP is referred to by an application using an identifier called the TDPid that is unique in a mainframe; for example, TDP2. Functions of TDP include: Session initiation and termination Logging, verification, recovery, and restart Physical input to and output from the server, including session balancing and queue maintenance Security

Teradata Database Window

Teradata DBW allows database administrators, system operators, and support personnel to control the operation of Teradata Database. DBW is also the primary vehicle for starting and controlling the operation of Teradata Database utilities.

Redundant Arrays of Independent Disks (RAID)

Teradata Database employs Redundant Array of Independent Disks (RAID) storage technology to provide data protection at the disk level. You use the RAID management software to group disk drives into RAID LUNS (Logical Units) to ensure that data is available in the event of a disk failure. Redundant implies that either data, functions, or components are duplicated in the architecture of the array.

EXPLAIN Request Modifier

Teradata SQL supplies a very powerful EXPLAIN request modifier that allows you to see the execution plan of a query. The EXPLAIN request modifier not only explains how a request will be processed, but provides an estimate of the number of rows involved as well as the performance impact of the request. Teradata Database supports EXPLAIN request modifiers with detailed Optimizer information including, for example, cost estimates for Insert, Update, Upsert, Merge, and Delete steps, as well as spool size estimates. How EXPLAIN Works The EXPLAIN request modifier that precedes any SQL request causes Teradata Database to display the execution plan for that request. The request itself is not submitted for execution. When you perform an EXPLAIN against any SQL request, that request is parsed and optimized. The access and join plans generated by the Optimizer are returned in the form of a text file that explains the (possibly parallel) steps used in the execution of the request. Also included is the relative cost required to complete the request given the statistics with which the Optimizer had to work. If the statistics are not reasonably accurate, the cost estimate may not be accurate. Benefits of Using EXPLAIN EXPLAIN helps you to evaluate complex queries and to develop alternative, more efficient, processing strategies. You may be able to get a better plan by collecting more statistics on more columns, or by defining additional indexes. Your knowledge of the actual demographics information may allow you to identify row count estimates that seem badly wrong, and help to pinpoint areas where additional statistics would be helpful.

Active access

Teradata is able to access analytical intelligence quickly and consistently in support of operational business processes. Active Access provides intelligence for operational and customer interactions consistently. Active Access queries, also referred to as tactical queries, support tactical decision-making at the front-line.

Active events

Teradata is able to detect a business event automatically, apply business rules against current and historical data, and initiate operational actions when appropriate. This enables enterprises to reduce the latency between the identification of an event and taking action with respect to it. Active Events entails more than event detection.When notified of something important, Teradata presents users with recommendations for appropriate action. The analysis done for users may prescribe the best course of action or give them alternatives from which to choose.

Active Enterprise Integration

Teradata is able to integrate itself into enterprise business and technical architectures, especially those that support business users, partners, and customers. This simplifies the task of coordinating enterprise applications and business processes. For example, a Teradata event, generated from a database trigger, calls a stored procedure, which inserts a row into a queue table and publishes a message via the Teradata JMS Provider. The message is delivered to a JMS queue on a WebLogic, SAP NetWeaver, or other JMS- compatible application server. SAP Customer Relationship Management receives the message, notifies the user, and takes an action.

Active load

Teradata is able to load data actively and in a non-disruptive manner and, at the same time, process other workloads. Teradata delivers Active Load through methods that support continuous data loading, these include: streaming from a queue more frequent batch updates moving changed data from another database platform to Teradata. These methods exercise such Teradata Database features as queue tables and triggers, and use FastLoad, MultiLoad, TPump, standalone utilities, and Teradata Parallel Transporter.

Active Workload Management

Teradata is able to manage mixed workloads dynamically and to optimize system resource utilization to meet business goals. Teradata Active System Management (TASM) is a portfolio of products that enables real-time system management. TASM assists the database administrator in analyzing and establishing workloads and resource allocation to meet business needs. TASM facilitates monitoring workload requests to ensure that resources are used efficiently and that dynamic workloads are prioritized automatically. TASM also provides techniques to visualize the current operational environment and to analyze long-term trends. TASM enables database administrators to set SLGs, to monitor adherence to them, and to take any necessary steps to reallocate resources to meet business objectives.

Active Availability

Teradata is able to meet business objectives for its own availability. It assists customers in identifying application-specific availability, recoverability, and performance requirements based on the impact of enterprise downtime. Teradata can also recommend strategies for achieving system availability goals.

.NET Data Provider for Teradata

The .NET Data Provider for Teradata conforms to the ADO.NET specifications. ADO.NET provides a rich set of data access services to .NET Framework applications. The Data Provider allows .NET applications to access Teradata Database from all .NET Framework languages including C#, VB, F# and PowerShell.

Parsing Engine (PE)

The PE is the vproc that communicates with the client system on one side and with the AMPs (via the BYNET) on the other side. Each PE executes the database software that manages sessions, decomposes SQL statements into steps, possibly in parallel, and returns the answer rows to the requesting client. The PE software consists of the following elements: Parser: Decomposes SQL into relational data management processing steps. Optimizer: Determines the most efficient path to access data. Generator: Generates and packages steps. Dispatcher: Receives processing steps from the parser and sends them to the appropriate AMPs via the BYNET. Monitors the completion of steps and handles errors encountered during processing. Session Control: Manages session activities, such as logon, password validation, and logoff. Recovers sessions following client or server failures. One PE can handle up to 120 sessions in parallel.

Transparency Series/Application Program Interface (TS/API)

The Transparency Series/Application Program Interface (TS/API) provides a gateway between the IBM mainframe relational database product DB2 (IBM System z Operating System version) and Teradata Database. TS/API enables an SQL statement formulated for DB2 to be translated into Teradata SQL, permitting DB2 applications to access data stored in Teradata Database.

Preprocessor2 (PP2)

The client application languages that support embedded SQL are all compiled languages. SQL is not defined for any of them. For this reason, you must precompile your embedded SQL code to translate the SQL into native code before you can compile the source using a native compiler. The precompiler tool is called Preprocessor2 (PP2), and you use it to: Read your application source code to look for the defined SQL code Interpret the intent of the code after it isolates all the SQL code in the application and translates it into Call-Level Interface (CLI) Comment out all the SQL source The output of the precompiler is native language source code with CLI calls substituted for the SQL source. After the precompiler generates the output, you can process the converted source code with the native language compiler.

Cliques

The clique is a feature of some MPP systems that physically group nodes together by multiported access to common disk array units. Inter-node disk array connections are made using FibreChannel (FC) buses. FC paths enable redundancy to ensure that loss of a processor node or disk controller does not limit data availability. The nodes do not share data. They only share access to the disk arrays. A clique is the mechanism that supports the migration of vprocs under PDE following a node failure. If a node in a clique fails, then vprocs migrate to other nodes in the clique and continue to operate while recovery occurs on their home node. PEs that manage TPA-hosted physical channel connections cannot migrate because they are dependent on the hardware that is physically attached to the node to which they are assigned. PEs for workstation-attached TCP/IP connections do migrate when a node failure occurs, as do all AMPs.

Teradata Database File System

The file system is a layer of software between Teradata Database and PDE. File system service calls allow Teradata Database to store and retrieve data efficiently and with integrity without being concerned about the specific low-level operating system interfaces.

Vdisk

The group of cylinders currently assigned to an AMP is referred to as a vdisk, although the actual physical storage may derive from several different storage devices.

Triggers

The trigger defines events that happen when some other event, called a triggering event, occurs. This database object is essentially a stored SQL statement associated with a table called a subject table. Teradata Database implementation of triggers complies with ANSI SQL specifications and provides extensions. Triggers execute when any of the following modifies a specified column or columns in the subject table: DELETE, INSERT, UPDATE. Typically, the stored SQL statements perform a DELETE, INSERT, UPDATE, or MERGE on a table different from the subject table. Types of Triggers Teradata Database supports two types of triggers. This type of trigger... Fires for each... statement statement that modifies the subject table. row row modified in the subject table. When to Fire Triggers You can specify when triggers fire. WHEN you specify... THEN the triggered action... BEFORE executes before the completion of the triggering event. As specified in the ANSI/ISO SQL standard, a BEFORE trigger cannot have data changing statements in the triggered action. AFTER executes after completion of the triggering event. Note: To support stored procedures the CALL statement is supported in the body of an AFTER trigger. Both row and statement triggers can call a stored procedure. ANSI/ISO-Specified Order When you specify multiple triggers on a subject table, both BEFORE and AFTER triggers execute in the order in which they were created as determined by the timestamp of each trigger. Triggers are sorted according to the preceding ANSI/ISO rule unless you use the Teradata Database extension, ORDER. This extension allows you to specify the order in which the triggers execute, regardless of creation time stamp. Using Triggers You can use triggers to do various things: Define a trigger on the subject table to ensure that the UPDATE, INSERT, MERGE, and DELETE statements performed to the subject table are propagated to another table. Use triggers for auditing. For example, you can define a trigger which causes the INSERT statements in a log table when an employee receives a raise higher than 10%. Use a trigger to disallow massive UPDATE, INSERT, MERGE, or DELETE during business hours. Use a trigger to set a threshold. For example, you can use triggers to set thresholds for inventory of each item by store, to create a purchase order when the inventory drops below a threshold, or to change a price if the daily volume does not meet expect Use a trigger to call SQL stored procedures and external stored procedures.

Vproc Migration

The vprocs(PEs, AMPs) can migrate from a node to another one in the same clique if their home node fails for any reason. Although the system takes care of which node the vprocs would migrate, the user can configure preferred migratory destinations. Vprocs migration permits the system to function completly in the event of a node failure. In the event of a node failure: All Amps can migrate. All Network attached PEs can migrate PEs that manage TPA-hosted physical channel attached connrections cannot migrate because they are dependent on the hardware that is physically attached to their assigned node.

Cursors

Traditional application development languages cannot deal with result tables without some kind of intermediary mechanism because SQL is a set-oriented language. The intermediary mechanism is the cursor. A cursor is a pointer that the application program uses to move through a result table. You declare a cursor for a SELECT request, and then open the named cursor. The act of opening the cursor executes the SQL request. You use the FETCH... INTO... statement to individually fetch and write the rows into host variables. The application can then use the host variables to do computations. Teradata Preprocessor2 uses cursors to mark or tag the first row accessed by an SQL query. Preprocessor2 then increments the cursor as needed. SQL stored procedures use: Cursors to fetch one result row at a time and then to execute SQL and SQL control statements as required for each Local variables or parameters from the stored procedure can be used for computations. Result set cursors to return the result of a SELECT statement executed in the stored procedure to the caller of the stored procedure or the client

Table Rebuild Utility

Use the Table Rebuild utility to recreate: a table a database an entire disk on a single AMP Under the following conditions: The table structure or data is damaged because of a software problem, head crash, power failure, or other malfunction. The affected tables are enabled for fallback protection. Table rebuild can create all of the following on an AMP-by-AMP basis: Primary or fallback portions of a table. An entire table (both primary and fallback portions). All tables in a database. All tables on an individual AMP. The Table Rebuild utility can also remove inconsistencies in stored procedure tables in a database. A Teradata Database system engineer, field engineer, or system support representative usually runs the Table Rebuild utility.

User-Defined Types

User-defined types (UDTs) allow you to extend SQL by creating your own data types and then using them like predefined data types. UDT Type Description Example Distinct A UDT that is based on a single predefined data type, such as INTEGER or VARCHAR. A distinct UDT named euro that is based on a DECIMAL(8,2) data type can store monetary data. Structured A UDT that is a collection of one or more fields called attributes, each of which is defined as a predefined data type or other UDT (which allows nesting). A structured UDT named circle can consist of x-coordinate, y-coordinate, and radius attributes. Dynamic Only specified as the data type of (up to eight) input parameters to external UDFs. The benefit of dynamic UDTs is that they significantly increase the number of input arguments that you can pass into external UDFs. We use a CREATE TYPE statement to define a distinct or structured UDT. We use the NEW VARIANT_TYPE expression to construct an instance of a dynamic UDT and define the attributes of the UDT at runtime.

Access Module Processor (AMP)

Virtual proccessor that performs database functions such as: Executing queries (Sorting, aggregating, joining data rows) Accounting Journaling Locking tables, rows and databases Output Data conversion Disk space management Each AMP manages a portion of the physical disk space. Each AMP stores a portion of rows of every database table within that space.

Volatile table

Volatile tables are used when: Only one session needs the table. Only the creator needs to access the table. You want better performance than a global temporary table. You do not need the table definition after the session ends. Note: The definition of a volatile table can survive across a system restart if it is contained in a macro.

Vprocs

Vprocs are a set of software processes that run on a node under Teradata Parallel Database Extensions (PDE) within the multitasking environment of the operating system. Vproc types: AMP GTW Node PE RSG TVS A single system can support a maximum of 16,384 vprocs. The maximum number of vprocs per node can be as high as 128, but is typically between 6 and 12. Each vproc is a separate, independent copy of the processor software, isolated from other vprocs, but sharing some of the physical resources of the node, such as memory and CPUs. Multiple vprocs can run on an SMP platform or a node. Vprocs and the tasks running under them communicate using unique-address messaging, as if they were physically isolated from one another. This message communication is done using the Boardless BYNET Driver software on single-node platforms or BYNET hardware and BYNET Driver software on multinode platforms.

User DBC

When Teradata Database is first installed on a server, one user exists on the system, that is, User DBC. User DBC owns: All other databases and users in the system created after installation. All the free space in the entire system. Note: To protect the security of system tables within Teradata Database, the database administrator typically creates User System Administrator from User DBC. The usual procedure is to assign all database disk space that system tables do not require to User System Administrator. The database administrator then uses this user as a resource from which to allocate space to other databases and users of the system.

Workstation Attachment Methods

Workstation-attached methods include: - .NET Data Provider for Teradata - Java Database Connectivity (JDBC) - OLE DB Provider for Teradata - Open Database Connectivity (ODBC) - Teradata CLIv2 for workstation-attached systems

Teradata Gateway

communications support. The Gateway software validates messages from clients that generate sessions over the network and it controls encryption.