Teradata Client Access

2.   Client Access

2.1         Client Connections

Users can access data in the Teradata Database through an application on both channel-attached and network-attached clients. Additionally, the node itself can act as a client. Teradata client software is installed on each client (channel-attached, network-attached, or node) and communicates with RDBMS software on the node. You may occasionally hear either type of client referred to by the legacy term of "host," though this term is not typically used in documentation or product literature.

2.2         Channel-Attached Client

Channel-attached clients are IBM-compatible mainframe systems supported by the Teradata Database. The following software components installed on the mainframe are responsible for communications between client applications and the Channel Driver on a Teradata node:

• Teradata Director Program (TDP) software to manage session traffic, installed on the channel-attached client.
• Call-Level Interface (CLI), a library of routines that are the lowest-level interface to Teradata.

Communication with the Teradata System

Communication from client applications on the mainframe goes through the mainframe channel, to the Host Channel Adapter on the node, to the Channel Driver software.

2.3         Network Attached Client

The Teradata Database supports network-attached clients connected to the node over a LAN. The following software components installed on the network-attached client are responsible for communication between client applications and the Teradata Gateway on a Teradata node:

• ODBC
• CLIv2

Communication with the Teradata System

Communication from applications on the network-attached client goes over the LAN, to the Ethernet card on the node, to the Teradata Gateway software.

2.4         Node

The node is considered a network-attached client. If you install application software on a node, it will be treated like an application on a network-attached client. In other words, communications from applications on the node go through the Teradata Gateway. An application on a node can be executed through:

• System Console that manages an SMP system.
• Remote login, such as over a network-attached client connection.

2.5         Request Processing

A request like the one above is processed a little differently, depending on whether the user is accessing Teradata through a channel-attached or network-attached client:

1. SQL request is sent from the client to the appropriate component on the node:
   • Channel-attached client: request is sent to Channel Driver (through the TDP).
   • Network-attached client: request is sent to Teradata Gateway (through CLIv2 or ODBC).
2. Request is passed to the PE(s).
3. PEs parse the request into AMP steps.
4. PE Dispatcher sends steps to the AMPs over the BYNET.
5. AMPs perform operations on data on the vdisks.
6. Response is sent back to PEs over the BYNET.
7. PE Dispatcher receives response.
8. Response is returned to the client (channel-attached or network-attached).

2.6         Teradata Client Utilities

Teradata has a robust suite of client utilities that enable users and system administrators to enjoy optimal response time and system manageability. Various client utilities are available for tasks from loading data to managing the system.

Teradata utilities leverage Teradata high performance capabilities and are fully parallel and scalable. The same utilities run on smaller entry-level systems, as well as the largest MPP implementations.

Teradata client utilities include the following, described in this section:

• Query Submitting Utilities
  • BTEQ
  • Teradata SQL Assistant
• Load and Unload Utilities
  • FastLoad
  • MultiLoad
  • TPump
  • FastExport
  • Teradata Warehouse Builder
• Administrative Utilities
  • Teradata Manager
  • Teradata Dynamic Query Manager (TDQM)
  • Teradata Analyst Pack
• Archive Utilities
  • ARC
  • NetVault
  • NetBackup
  • ASF2

2.7         Query Submitting Utilities

Teradata provides a number of tools that are front-end interfaces for submitting SQL queries. Two mentioned in this section are BTEQ and Teradata SQL Assistant.

2.7.1   BTEQ

BTEQ (Basic Teradata Query) -- often pronounced 揃EE-teek?-- is a Teradata tool used for submitting SQL queries on all platforms. BTEQ provides the following functionality:

• Standard report writing and formatting
• Basic import and export of small amounts of data to and from the Teradata Database across all platforms. For tables more than a few thousand rows, the Teradata load utilities are recommended for more efficiency.
• Ability to submit SQL requests in the following ways:
  • Interactive
  • Batch

2.7.2   Teradata SQL Assistant

Teradata SQL Assistant (formerly known as Queryman) is an information discovery/query tool that runs on Microsoft Windows. Teradata SQL Assistant enables you to access Teradata as well as other ODBC-based databases. Some of its features include:

• Ability to save data in PC-based formats, such as Microsoft Excel, Microsoft Access, and text files.
• History of submitted SQL syntax, to help you build scripts for data mining and knowledge discovery.
• Help with SQL syntax.
• Import and export of small amounts of data to and from ODBC-compliant databases. For tables more than a few thousand rows, the Teradata load utilities are recommended for more efficiency.

2.8         Data Load and Unload Utilities

In a data warehouse environment, the database tables are populated from a variety of sources, such as mainframe applications, operational data marts, or other distributed systems throughout a company. These systems are the source of data such as daily transaction files, orders, usage records, ERP (enterprise resource planning) information, and Internet statistics. Teradata has a suite of data load and unload utilities optimized for use with the Teradata Database. They run on any of the supported client platforms:

• Channel-attached client
• Network-attached client
• Node

Using Teradata Load and Unload Utilities

Teradata load and unload utilities are fully parallel. Because the utilities are scalable, they accommodate the size of the system. Performance is not limited by the capacity of the load and unload tools.

The utilities have full restart capability. This feature means that if a load or unload job should be interrupted for some reason, it can be restarted again from the last checkpoint, without having to start the job from the beginning.

The load and unload utilities are:

• FastLoad
• MultiLoad
• TPump
• FastExport
• Teradata Warehouse Builder

By default, you can run up to 15 instances of FastLoad, MultiLoad, and FastExport in any combination. There is no limit to the number of concurrent TPump jobs.

2.8.1   FastLoad

Use the FastLoad utility to load data into empty tables.

FastLoad can only work on one table at a time. FastLoad loads data into an empty table in parallel, using multiple sessions to transfer blocks of data. FastLoad achieves high performance by fully exploiting the resources of the system. After the data load is complete, the table can be made available to users.

2.8.2   MultiLoad

Use the MultiLoad utility to maintain tables by:

• Inserting rows into a populated or empty table
• Updating rows in a table
• Deleting multiple rows from a table

MultiLoad can load multiple input files concurrently and work on up to five tables at a time, using multiple sessions. MultiLoad is optimized to apply multiple rows in block-level operations. MultiLoad usually is run during a batch window, and places a lock on on the destination table(s) to prevent user queries from getting inconsistent results before the data load or update is complete.

2.8.3   TPump

Use TPump to:

• Constantly load data into a table
• Continuously load, update, or delete data in tables
• Update lower volumes of data using fewer system resources than other load utilities
• Vary the resource consumption and speed of the data loading activity over time

The TPump utility complements MultiLoad as a data loading utility. A major difference is that TPump uses row hash locks, which eliminates the need for table locks and "batch windows" typical with MultiLoad. Users can continue to run queries during TPump data loads. In addition, TPump is designed for smaller volumes of data than MultiLoad, and maintains up to 60 tables at a time.

TPump has a dynamic throttle that operators can set to specify the percentage of system resources to be used for an operation. This enables operators to set when TPump should run at full capacity during low system usage, or within limits when TPump may affect other business users of Teradata.

2.8.4   FastExport

Use the FastExport utility to export data from either of the following to a file on a client platform:

• Table
• View

FastExport is a data extract utility. It transfers large amounts of data using block transfers over multiple sessions to a host file on the network-attached or channel-attached client. Typically, FastExport is run during a batch window, and the tables being exported are locked.

2.8.5   Teradata Warehouse Builder

Teradata Warehouse Builder (TWB) is a data warehouse loading tool that enables data extraction, transformation and loading processes common to all data warehouses.

Using built-in operators, Teradata Warehouse Builder combines the functionality of the Teradata utilities (FastLoad, MultiLoad, FastExport, and TPump) in a single parallel environment. Its extensible environment supports FastLoad INMODs, FastExport OUTMODs, and Access Modules to provide access to all the data sources you use today. There is a set of open APIs (Application Progammer Interface) to add third party or custom data transformation to Teradata Warehouse Builder scripts. Using multiple, parallel tasks, a single Teradata Warehouse Builder script can load data from disparate sources into the Teradata Database in the same job.

Teradata Warehouse Builder is scalable and enables end-to-end parallelism. The previous versions of utilities (like FastLoad) allow you to load data into Teradata in parallel, but with a single input stream. Teradata Warehouse Builder allows you to run multiple instances of the extract, optional transformation, and load operators. You can have as many loads as you have sources in the same job. With multiple sources of data coming from multiple platforms integration is important in a parallel environment.

Teradata Warehouse Builder eliminates the need for persistent storage. It stores data into data buffers so you no longer need to write data into a flat file. Since you don't need flat files, there is no longer a 2GB file limit.

Teradata Warehouse Builder provides a single, SQL-like scripting language, as well as a GUI to make scripting faster and easier. You can do the extract, some transformation, and loads all in one SQL-like scripting language. Once the dynamics of the language are learned, you can perform multiple tasks with a single script. You can use script converters to convert scripts on existing systems for utilities (FastLoad, MultiLoad, FastExport, and TPump) to Teradata Warehouse Builder scripts.

A single Teradata Warehouse Builder job can load data from multiple disparate
sources into the Teradata Database, as indicated by the green arrow.

Teradata Warehouse Builder Operators

The operators are components that "plug" into the TWB infrastructure and actually perform the functions.

• The FastLoad INMOD and FastExport OUTMOD operators support the current FastLoad and FastExport INMOD/OUTMOD features.
• The Data Connector operator is an adapter for the Access Module or non-Teradata files.
• The SQL Select and Insert operators submit the Teradata SELECT and INSERT commands.
• The Load, Update, Export and Stream operators are similar to the current FastLoad, MultiLoad, FastExport and TPump utilities, but built for the TWB parallel environment.

The INMOD and OUTMOD adapters, Data Connector operator, and the SQL Select/Insert operators are included when you purchase the Infrastructure. The Load, Update, Export and Stream operators are purchased separately.

To simplify these new concepts, let's compare the Teradata Warehouse Builder Operators with the classic utilities that we just covered.

TWB Operator	Teradata Utility	Description
LOAD	FastLoad	A consumer-type operator that uses the Teradata FastLoad protocol. Supports Error limits and Checkpoint/ Restart. Both support Multi-Value Compression and PPI.
UPDATE	MultiLoad	Utilizes the Teradata MultiLoad protocol to enable job based table updates. This allows highly scalable and parallel inserts and updates to a pre-existing table.
EXPORT	FastExport	A producer operator that emulates the FastExport utility
STREAM	TPump	Uses multiple sessions to perform DML transactions in near real-time.
DataConnector	N/A	This operator emulates the Data Connector API. Reads external data files, writes data to external data files, reads an unspecified number of data files.
ODBC	N/A	Reads data from an ODBC Provider.

 

2.9         Administrative Utilities

Administrative utilities use a graphical user interface (GUI) to monitor and manage various aspects of a Teradata system.

The administrative utilities are:

• Teradata Manager
• Teradata Dynamic Query Manager (TDQM)
• Teradata Analyst Pack

2.9.1   Teradata Manager

Teradata Manager is a production and performance monitoring system that helps a DBA or system manager to monitor, control, and administer one or more Teradata systems through a GUI. Running on LAN-attached clients, Teradata Manager has a variety of tools and applications to gather, manipulate, and analyze information about each Teradata Database being administered.

For examples of Teradata Manager functions, click here: Teradata Manager Examples

2.9.2   Teradata Dynamic Query Manager (TDQM)

Teradata Dynamic Query Manager (TDQM), formerly known as Database Query Manager (DBQM), is a query workload management tool that dynamically tunes the Teradata Database. TDQM can run, suspend, reschedule, or reject a query based on current workload and set thresholds.

For example, with TDQM a request can be scheduled to run periodically or during a specified time period without an active system connection. Results can be retrieved any time after the request has been submitted by TDQM and executed.

TDQM can restrict queries based on factors such as:

• Analysis control thresholds
• Object control thresholds
• Environmental factors

2.9.3   Teradata Analyst Pack

Teradata Analyst Pack is a suite of the following products.

Teradata Visual Explain
Teradata Visual Explain makes query plan analysis easier by providing the ability to capture and graphically represent the steps of the plan and perform comparisons of two or more plans. It is intended for application developers, database administrators and database support personnel to better understand why the Teradata Optimizer chooses a particular plan for a given SQL query. All information required for query plan analysis such as database object definitions, data demographics and cost and cardinality estimates is available through the Teradata Visual Explain interface. The tool is very helpful in identifying the performance implications of data skew and bad or missing statistics. Visual Explain uses a Query Capture Database to store query plans which can then be visualized or manipulated with other Teradata Analyst Pack tools.

Teradata System Emulation Tool (Teradata SET)
Teradata SET simplifies the task of emulating a target system by providing the ability to export and import all information necessary to fake out the optimizer in a test environment. This information can be used along with Teradata's Target Level Emulation feature to generate query plans on the test system as if they were run on the target system. This feature is useful for verifying queries and reproducing optimizer related issues in a test environment.

Teradata SET allows the user to capture the following by database, query, or workload:

• System cost parameters
• Object definitions
• Random AMP samples
• Statistics
• Query execution plans
• Demographics

This tool does not export user data.

Teradata Index Wizard
Teradata Index Wizard automates the process of manual index design by recommending secondary indexes for a particular workload. Teradata Index Wizard provides a simple, easy-to-use graphical user interface (GUI) that guides the user how to go about analyzing a database workload and provides recommendations for improving performance through the use of indexes.

Teradata Statistics Wizard
Teradata Statistics Wizard is a graphical tool that has been designed to automate the collection and re-collection of statistics, resulting in better query plans and helping the DBA to efficiently manage statistics.

The Statistics Wizard enables the DBA to:

• Specify a workload to be analyzed for recommendations specific to improving the performance of the queries in a workload.
• Select an arbitrary database or selection of tables, indexes, or columns for analysis, collection, or re-collection of statistics.

As changes are made within a database, the Statistics Wizard identifies those changes and recommends which tables should have statistics collected, based on age of data and table growth, and what columns/indexes would benefit from having statistics defined and collected for a specific workload. The DBA is then given the opportunity to accept or reject the recommendations.

2.10   Archival Utilities

Teradata has utilities specifically designed for data archive and recovery purposes. There are different utilities for channel-attached clients and network-attached clients.

2.10.1 Archiving on Channel-Attached Clients

In a channel-attached (mainframe) client environment, the Archive Recovery (ARC) utility is used to back up data. It supports commands written in Job Control Language (JCL). The ARC utility archives and restores database objects, allowing recovery of data that may have been damaged or lost.

There are several scenarios where restoring objects from external media may be necessary:

• Restoring non-Fallback tables after a disk failure.
• Restoring tables that have been corrupted by batch processes that may have left the data in an uncertain state.
• Restoring tables, views, or macros that have been accidentally dropped by the user.
• Miscellaneous user errors resulting in damaged or lost database objects.

With the ARC utility you can copy a table and restore it to another Teradata Database. It is scalable and parallel, and can run on a channel-attached client (or network-attached client) or a node.

2.10.2 Archiving on Network-Attached Clients

In a network-attached client environment, the Archive Recovery (ARC) utility is used to back up data, along with either of the following tape storage subsystems:

• NetVault (from BakBone Software Inc.)
• NetBackup (from VERITAS Software Corporation)

NetVault and NetBackup have modules created for Teradata systems for use in a scalable, parallel, enterprise environment. They run on network-attached clients or a node (Microsoft Windows or UNIX MP-RAS). Data is backed up into the NetVault or NetBackup tape storage subsystems using the ARC utility.