Teradata Database offers a wide variety of utilities, management tools, and peripherals. Some of these reside on Teradata Database and others are part of the Teradata Tools and Utilities management suite available for installation in client environments.
With database management tools, you can backup and restore important data, save dumps, and investigate and control Teradata Database configuration, user sessions, and various aspects of its operation and performance. Management and analysis tools help keep the database running at optimum performance levels.
Topics include:
• Data archiving utilities • Data load and export utilities
• Session and configuration management tools • System resource and workload management tools • Teradata SQL Assistant
• PocketDBA for Teradata
Data Archiving Utilities
Open Teradata Backup
Open Teradata Backup (OTB) supports open architecture products that provide Backup/ Archive/Restore (BAR) functions.
OTB products include: • NetVault
The NetVault Teradata Module is a backup system that allows you to select databases and tables graphically and specify the kinds of backups (for example, distributed, online) you want to perform.
• NetBackup
NetBackup for Teradata supports parallel backups and restores coordinated across multiple hosts connected to a single Teradata Database.
In addition, NetBackup uses an Administrative Host, which contains a graphical user interface (GUI) to provide object browsing and selection, automatic script generation, and centralized job monitoring.
Data Load and Export Utilities
• Tivoli Storage Manager
Tivoli Storage Manager is a LAN-free (it uses a SAN switch to allow the BAR servers to write to the storage device) backup solution that enables Teradata to be connected to an existing TSM server environment. TSM main features include:
• Automatic data backup and restore functions • Centralized storage management operations • High scalability
Teradata Archive/Recovery Utility
Teradata Archive/Recovery utility (ARC) supports archiving and restoring Teradata Database databases, individual tables, or permanent journals, including online archiving, to any of the following media:
• Client tape • Client file
ARC also includes recovery with rollback and rollforward functions for data tables defined with a journal option. For more information about rollback and rollforward, see Chapter 11: “Concurrency Control and Transaction Recovery.”
Data Load and Export Utilities
Teradata Tools and Utilities data load utilities are designed to operate following one of two philosophies:
Teradata Parallel Transporter
Teradata Parallel Transporter (Teradata PT) is an object-oriented software system that executes multiple instances of data extraction, transformation, and load (ETL) functions in a scalable, high-speed parallel processing environment.
Teradata PT offers the following:
Utilities operate either… For example… And are typically used…
as fast as possible, with little regard for the impact on system users
• Teradata MultiLoad • Teradata FastLoad
in a decision support environment where transactions for the day are loaded during a nightly batch window when there are few interactive users.
or, in the background and limit the impact on interactive users
Teradata TPump to process a continuous feed of near-realtime updates while interactive users require rapid responses.
Data Load and Export Utilities
• An open environment with a set of APIs that enable custom integration of third-party or user-written transformation operators. You can specify the number of instances of each extract, load, update, or optional transformation operator to optimize system resources. • An architecture based on a parallel foundation known as the Teradata PT infrastructure.
The infrastructure provides the capability to parallelize any application that is integrated into its environment through a set of open APIs.
• Extract and load operators similar to Teradata’s standalone extract and load utilities (FastLoad, MultiLoad, TPump, and FastExport). The infrastructure and operators run within a single SMP (symmetric multiprocessing) load server that can be network- connected to the source server and a Teradata Database or that can be run directly on a Teradata non-TPA (trusted parallel application) node. Technically, there is nothing preventing Teradata PT from running on a TPA node, although running on a TPA node is not recommended.
Teradata Parallel Data Pump
Teradata Parallel Data Pump (TPump) uses standard SQL/DML (not block transfers) to maintain data in tables. It also contains a resource governing method whereby you can control the use of system resources by specifying how many inserts and updates occur minute-by- minute. This allows background maintenance for insert, delete, and update operations to take place at any time of day while Teradata Database is in use.
TPump provides the following capabilities:
• Has no limitations on the number of instances running concurrently.
• Uses conventional row hash locking, which provides some amount of concurrent read and write access to the target tables.
• Supports the same restart, portability, and scalability as Teradata MultiLoad. • Perform data Upserts.
Teradata MultiLoad
Teradata MultiLoad supports bulk inserts, updates, and deletes against initially unpopulated or populated tables. Both the client and server environments support Teradata MultiLoad. Teradata MultiLoad can:
• Run against multiple tables.
• Perform block transfers with multisession parallelism. • Load data from multiple input source files.
• Pack multiple SQL statements and associated data into a request. • Perform data Upserts.
Teradata FastLoad
Teradata FastLoad loads data in unpopulated tables only. Both the client and server environments support Teradata FastLoad.
Data Load and Export Utilities
Teradata FastLoad can:
• Load data into an empty table.
FastLoad loads data into one table per job. If you want to load data into more than one table, you can submit multiple FastLoad jobs.
• Perform block transfers with multisession parallelism.
Teradata FastExport Utility
To export data, Teradata Tools and Utilities provides Teradata FastExport.
Teradata FastExport exports data in parallel. The utility exports large quantities of data from Teradata Database to a client and is the functional complement of the FastLoad and
MultiLoad utilities. Teradata FastExport can:
• Export tables to client files.
• Export data to an Output Modification (OUTMOD) routine. You can write an OUTMOD routine to select, validate, and preprocess exported data.
• Perform block transfers with multisession parallelism.
OLE DB Access Module
The Teradata OLE DB Access Module is an interface between Teradata load and export utilities (Teradata FastLoad, Teradata FastExport, Teradata MultiLoad, TPump, and BTEQ) and OLE DB data sources. The access module quickly moves data between a single OLE DB provider and a Teradata Database without requiring intermediate storage.
Teradata OLE DB Access Module is a dynamic link library (DLL) that transfers data between an OLE DB provider and Teradata Database. The access module can be used to view, edit, and re-run access module job (.amj) files, or to perform a quick, one-time copy (to create a new table in a Teradata Database with data from an OLE DB data source) or a quick, one-time import from an OLE DB data source to Teradata Database.
The Teradata OLE DB Access Module offers the following options to move data: • Export data from Teradata Database to a flat file or table in an OLE DB-compliant
database (OleLoad mode).
• Load data to Teradata Database from a flat file or table in an OLE DB-compliant database (OleLoad mode).
• Export data from one Teradata Database to another Teradata Database; however, this option does not offer the same processing performance as the first two options (OleLoad mode).
Use Teradata FastLoad, Teradata FastExport, Teradata MultiLoad, TPump, or BTEQ to run an .amj file (a job that has already been saved by the Teradata OLE DB Access Module) to export to and load from an OLE DB-compliant system (command-line mode).
Session and Configuration Management Tools
Teradata Parallel Transporter Application Programming Interface
Teradata Parallel Transporter Application Programming Interface (Teradata PTAPI) is a set of application programming interfaces used to load and export data to and from Teradata Database.
Teradata PT API enables an application to access Teradata Database using proprietary Teradata load and export protocols (Teradata FastLoad, Teradata FastExport, and Teradata MultiLoad).
Unlike Teradata utilities and Teradata Parallel Transporter that are driven by scripts, Teradata PT API is a functional library that is part of your applications. This allows the applications to have more control during the load and export processes.
The advantages for using Teradata PT API are:
• Open standard reduces research and development costs to integrate with Teradata Database
• Robust API facilitates easier access to key Teradata Database functions
• Better control of the runtime environment simplifies the management processes
Basic Teradata Query
Basic Teradata Query (BTEQ) software is a general-purpose, command-based program that allows users on a workstation to communicate with one or more Teradata Database systems and to format reports for both print and screen output.
Using BTEQ you can submit SQL queries to the Teradata Database. BTEQ formats the results and returns them to the screen, a file, or to a designated printer.
A BTEQ session provides a quick and easy way to access a Teradata Database. In a BTEQ session, you can:
• Enter Teradata SQL statements to view, add, modify, and delete data. • Enter BTEQ commands.
• Enter operating system commands. • Create and use Teradata stored procedures.
Session and Configuration Management Tools
Database management tools include utilities for investigating active sessions and the state of Teradata Database configuration, such as:• Query Session • Query Configuration • Gateway Global
System Resource and Workload Management Tools and Protocols
System Resource and Workload Management
Tools and Protocols
Teradata Database supports specific tools, protocols, and tool architectures for system resource and workload management. Among these are:
• Write Ahead Logging • Ferret utility
• Priority Scheduler • Teradata MultiTool
• Teradata Active System Management
Write Ahead Logging
Teradata Database uses a Write Ahead Logging (WAL) protocol. According to this protocol, writes of permanent data are written to a log file that contains the records representing updates. The log file is forced to disk at key moments, such as at transaction commit. Modification to permanent data from different transactions, all written to the WAL log, can also be batched. This achieves a significant reduction in I/O write operations. One I/O operation can represent multiple updates to permanent data.
The WAL Log is conceptually similar to a table, but the log has a simpler structure than a table. Log data is a sequence of WAL records, different from normal row structure and not accessible via SQL.
This utility… Does the following…
Query Session • Provides information about active Teradata
Database sessions.
• Monitors the state of all or selected sessions on selected logical host IDs attached to Teradata Database.
• Provides information about the state of each session including session details for Teradata Index Wizard. For more information about Teradata Index Wizard, see “Teradata Index Wizard” on page 152.
Query Configuration Provides reports on the current Teradata
Database configuration, including: • Node
• AMP
• PE identification and status
Gateway Global Allows you to monitor and control the sessions
System Resource and Workload Management Tools and Protocols
The WAL Log includes the following:
• Redo Records for updating disk blocks and insuring file system consistency during restarts, based on operations performed in cache during normal operation.
• Transient Journal (TJ) records used for transaction rollback.
WAL protects all permanent tables and all system tables, except Transient Journal (TJ) tables, user journal tables, and restartable spool tables (global temporary tables). Furthermore, WAL allows Teradata Database to be reconstructed from the WAL Log in the event of a system failure.
WAL stages in-place writes through a disk area called the DEPOT, a collection of cylinders. Staging in-place writes through the DEPOT ensures that either the old or the new copy is available after a restart.
The Ferret and the Filer utilities allow display and modification of the WAL Log and its index. Ferret includes the following:
• The SCANDISK option includes checking the WAL Log by default. • The SCOPE option can be set to just the WAL Log.
• The SHOWBLOCKS and SHOWSPACE options display log statistics and space. Filer includes the following:
• The WAL and WREC commands display WAL Log records by ordinal record number or by embedded log sequence number respectively, similar to the TABLE and ROW commands. • The WMI, WCI, and WDB commands display the WAL Log index structures.
• Generic commands, such as BLK, DELETE, DISPLAY, IDENT, NEXT, PREV, and PATCH, deal with log data as well as with normal data.
• The SCANDISK option includes checking the WAL log by default; it can be directed to check just the WAL log.
• A WHERE clause allows restricting Selects to a subset of the WAL Log records within a range.
Ferret Utility
The Ferret utility is a tool that you can use to set various disk space utilization attributes associated with Teradata Database while maintaining the integrity of the data managed by Teradata Database file system.
After you have selected the attributes and functions, Ferret dynamically reconfigures the data on the disks to correspond with the selections. Depending on the functions, Ferret can operate at the vproc, table, subtable, disk, or cylinder level.
Priority Scheduler
Teradata Database Priority Scheduler is a workload management facility that controls access to resources among the work active on Teradata Database. Priority Scheduler allows the administrator to define different priorities for different categories of work, and comes with a number of flexible options.
System Resource and Workload Management Tools and Protocols
The Priority Scheduler is active in all Teradata Database systems. Teradata Database itself automatically moves internal jobs into different priority levels, especially when a quick boost to one activity is critical to overall throughput.
Priority Scheduler has these capabilities:
• Instituting better service for your more important work. • Controlling resource sharing among different applications.
• Preventing aggressive queries from over-consuming at the expense of other work. • Automating changes in priority by query or session CPU usage levels.
• Placing a ceiling on CPU usage for some applications.
The Priority Scheduler Administrator available in Teradata Manager provides a graphical interface for configuration, management, and monitoring. For information, see “Teradata Manager” on page 193.
Teradata MultiTool
Teradata MultiTool is a Teradata Database utility that offers a graphical user interface (GUI) on Windows and Linux that Teradata Database administrators and support personnel can use as an interface to command-line-based Teradata Database and PDE tasks.
You can start specific utilities using the options available in the GUI. The following table lists the tools accessible from Teradata MultiTool.
Teradata Active System Management
Teradata Active System Management (Teradata ASM) is a system management architecture designed to improve the overall system management capabilities of Teradata Database by:
• Conceptualizing workload management, performance tuning, and performance monitoring under one domain, thus providing a single view of system performance.
The tool … Is used to …
Control GDO Editor (CTL)
display and modify the fields of the PDE GDO (Globally Distributed Object).
Database Window (DBW)
activate the Supervisor window and subwindows.
Database Initialization Program (DIP)
execute one or more of the standard Database Initialization Program Structured Query Language (SQL) scripts packaged with the database.
Vproc Manager perform the following functions: • Obtain the status of vprocs. • Change vproc states.
• Initialize and boot a specific vproc.
• Initialize the vdisk associated with a specific vproc. • Force a database restart.
System Resource and Workload Management Tools and Protocols
• Automating tasks associated with workload and system management in a comprehensive manner.
Teradata ASM is composed of products and components that:
• Analyze and define workloads to support business needs. This helps control resource allocation. For example, long and short queries coming from the same user can be classified to run at the correct priority before they begin to execute.
• Display real-time performance for each active workload • Enable longer-term workload trend analysis.
• Automatically regulate the system according to system condition (SysCon) and operating environment (OpEnv) events.
• Take corrective action with respect to workload performance using filter and throttle rules. • Provide automated exception handling. Queries that run in an anomalous manner are
automatically detected and dynamically managed.
Some Key Teradata ASM Products and Components
Teradata ASM is composed of some the following key products and components. The list, which reflects specific new or enhanced Teradata Database 12.0 products and components, is not exhaustive.
For a complete list of products and components, as well as a discussion of how they work together, see Performance Management.
Product / Component Description
Teradata Dynamic Workload Manager (Teradata DWM)
A product that enables users to define filter, throttle, and Workload Definition (WD) rules by which to manage the system workload and utilities, as well as system and user event handling to manage various system resources. Teradata Workload Analyzer
(Teradata WA)
A product that analyzes collected DBQL data to help group workloads and define rules to manage system performance. See “Database Query Log” on page 154.
Teradata Manager A product that, in addition to various system management tools, provides a Dashboard Workload Monitor and Workload Trend Analysis.
See “Teradata Manager” on page 193.
Open APIs An interface, invoked from any application, that provides an SQL interface to PMPC through User-Defined Functions (UDFs) and external stored procedures.
See “Workload Management Application Programming Interface” on page 108.
System Resource and Workload Management Tools and Protocols
Teradata Dynamic Workload Manager
Teradata Dynamic Workload Manager (Teradata DWM) supports the creation of • Filer rules
• Throttle rules
• Rules that define classes of queries (Workload Definitions [WDs]) • Events to monitor system resources
• States to allow changes to rule values
based on business-driven allocations of operating resources.
Request-Specific Performance Management
Teradata DWM lets users define rules according to which the workload is managed. • Filter rules reject unwanted logon and query requests before they are executed.
Filters restrict access to specific database objects for some or all types of SQL requests. You can prohibit queries that are estimated to access too many rows, take too long, and/or perform some types of joins.
• Throttle rules (also called concurrency rules) enforce session and query concurrency limits on specific objects.
Throttle rules allow you restrict the number of requests simultaneously executed against a database object, such as requests made by a user, or requests made against a table. Throttle rules can also be applied to Priority Scheduler performance groups and enforce
concurrency limits on FastLoad, MultiLoad, FastExport, and ARC.
• Workload rules (also called Workload Definitions [WDs]) specify how Teradata Database should handle queries while they are executing by specifying parameters for up to 36 separate classes of workload.
For each workload class, you can specify:
• The conditions and/or database objects that determine whether a query is assigned to the class.
• The execution priority (by more or less transparently creating a Priority Scheduler configuration).
• The concurrency level.
Query Bands A set of name-value pairs that are defined by the user or middle-tier application. Query Bands allow the user to tag session or transactions with an ID through an SQL interface. For specific information on Query Bands, see SQL Reference: Data Definition Statements, Database Administration and
Performance Management.
Resource Usage Monitor Data collection subsystem that includes Teradata ASM- related data collection. See “Resource Usage Monitoring” on