Managing the active data warehouse more easily

Teradata Warehouse 8.1 supports new applications and features.

by Todd Walter

The real-time enterprise (RTE) concept has become embedded in the minds of IT leaders everywhere. An active data warehouse (ADW) is a key component to a successful RTE implementation. But it is a big leap from concept to full adoption of RTE. Early adopters were willing to put much sweat equity into building industry-leading implementations. As the early adopters move into full production and many others begin implementation, it is imperative that the technologies make it easier to manage and build an RTE. Teradata Warehouse 8.1 focuses on making an active data warehouse easier to manage and easier to build. It brings new capabilities to workload management, system operations, performance and ease of building or porting applications. In addition, a new class of applications is enabled with the ability to create and manage new types of data.

What is new in workload management?
Bringing front-line applications and access to the data warehouse adds new service level requirements that must be managed. Teradata has a great foundation of tools to monitor and manage the widely varying mixed workloads of the active data warehouse. Teradata Active System Management (TASM) begins a new generation workload management philosophy with three new concepts: workgroups with service levels, request level classification and exception management during execution.

What is a workgroup?
A workgroup identifies a part of the workload through rules and patterns. A workgroup may be defined with a service-level goal (SLG) and a set of workload expectations, and each workgroup is assigned a priority. A workgroup for very response-time-sensitive tactical work could specify a SLG of 99% response under two seconds, expected arrival rate of 400 per second and a workgroup priority of tactical. A data mining workgroup could specify no SLG, an expected arrival rate of one per hour and overall workgroup priority of background.

How does work get into a workgroup?
In Teradata today, workload management is based on a user connected via a session. TASM adds the ability to manage at the individual query level. Rules or patterns are defined which will guide each incoming query into a workgroup. A single user or session is free to perform a wide variety of work types. TASM will identify and properly manage each new request individually to the appropriate workgroup specifications.

What happens if the service levels are not met?
Teradata Manager Dashboard indicators will display the service levels against current performance for ease of analysis. Flexible exception criteria can be defined that will lead to automatic alerts, workgroup change or even termination for queries that demonstrate unexpected patterns of behavior. In the future this will become a closed loop where Teradata will recommend settings and manage the workloads to the specified services levels without administrator intervention.

What is new for bulk utility management?
The bulk utilities (FastLoad, MultiLoad, FastExport) are constrained by a hard concurrency limit of 15. This limit becomes resource-sensitive in Teradata Warehouse 8.1 allowing more concurrent utilities as resources allow. Up to 60 FastExport executions will be allowed. FastLoad and MultiLoad will allow up to 30 concurrent executions, depending on the phase of execution and their resource use.

What else is new for system management?
The Checktable utility for validating the health of the data in the system has been changed to do dictionary locking and object access in new ways. This will allow it to be run while a normal workload is running rather than having to be scheduled only when the system is closed to users, thereby improving system availability and making the tool easier to use. The Checktable's reporting is also improved with a summary mode, exception-only mode and reporting when terminated before completion to improve usability.

For times when service must be performed, a new "DBC only" logon state is added and the system logon state may be switched at any time without a system restart. Teradata System Emulation Tool (TSET) is enhanced to capture system control parameters into the system emulation package that can be moved to test, development or service systems.

How is performance enhanced?
Teradata Warehouse 8.1 enhances Teradata performance in several areas. Utilization of compression, query optimization, additions to Partitioned Primary Index (PPI) and improvements to Join Index all contribute.

What is new in query optimization?
Multi-value compression is enabled in spool files with Teradata Warehouse 8.1. The optimizer recognizes when a compressed column is being copied into a spool and carries the compression attributes along without decompressing the values. This saves spool space and execution cost while writing and reading the spool.

Complex aggregation queries will be improved by breaking up the aggregation operation and moving parts of it to earlier execution steps, reducing the number of rows processed and the total cost of the query. The accuracy of cost calculations overall and hash join calculations specifically are improved for more accurate query planning. Algorithms for caching frequently used plans are improved to focus on providing the greatest benefit.

What is new in Partitioned Primary Index (PPI)?
PPI is enabled for temporary tables in Teradata Warehouse 8.1. Global and Volatile temporary tables may define and utilize partitioning. Performance of populating these tables may be improved by matching partitioning with the source. Performance of operations using temporary tables benefits from partition elimination. Collection of statistics is allowed on the system-defined column known as "partition"—more detailed information about the partitions, which ones are populated and the data distribution among them gives the optimizer the opportunity to make better plans.

What are the improvements for Join Indexes?
The Index Wizard adds Join Index (JI) advice in Teradata Warehouse 8.1. Based on the workload supplied, acting as an aggregate advisor, it will identify aggregate JIs and recommend single table JIs including ones which are hashed differently than the base table to help performance. Issues in the interaction of JI with backup and restore have been resolved, removing the barrier to widespread use of JIs.

Why is application implementation and porting easier?
Teradata Warehouse 8.1 provides a new utility interface, with new platforms supported and enhancements to existing interfaces focusing on providing customers and partners the facilities needed for ease of application development.

What is the new utility interface?
A new direct application program interface (API) provides a call level interface to the parallel, high-performance Teradata load/unload utilities. It removes the need for script generation and the requirement to launch a separate task. An extract, transform and load (ETL) tool or a customer application can simply call the API, passing control parameters and data, making it much easier to implement and manage.

What is new for the existing interfaces?
Isolation level has been promoted from being a request level attribute to a session attribute. This allows an application to set the default isolation level rather than specifying a locking clause on every SQL statement. DBMS platform information such as release level and DBMS limits is provided back to the interfaces enabling them to make the information available to applications. Additional client platforms are supported including more Linux options and 64-bit native support.

What is new for securing the database?
Password construction rules are enhanced to better enforce user passwords. The lightweight directory access protocol (LDAP) single sign-on facility is enhanced to support international character sets and Teradata Query Director. A certification suite is provided for installations that integrate with other directory servers.

User Defined Function (UDF) and Stored Procedure security is enhanced with control over their ability to perform external I/O.

How are new applications on new types of data enabled?
User Defined Type (UDT) functionality is delivered in Teradata Warehouse 8.1. UDTs allow the definition of the content and methods for managing new forms of data in the database.

UDT can describe data with a complex structure, like latitude and longitude. Or it can describe non-traditional data like documents, images or fingerprints. By giving this data a type rather than storing it in large object columns, operations on the data may be defined, expressed and enforced.

A distance method can be created and associated with the "location" type. In a SQL statement, "distance" can be used as an operator on two locations to compute the distance between them, and that operator knows it can only be used on data of the "location" type. Methods can determine how to operate by evaluating the types of the incoming parameters.

More with less
Teradata Warehouse 8.1 delivers the next installment on Teradata's mission to be the decisioning engine for the RTE. Ease of management, improved performance and ease of application development let you deliver more from the data warehouse environment quickly and at a lower cost. T

Todd Walter, CTO, Teradata Development Division, oversees R&D efforts for Teradata software and systems. He also is responsible for the future vision and development of the active data warehouse. You can send your data warehousing questions to the expert at todd.walter@Teradata-ncr.com.