td14_newftrs

Teradata Database

Release SummaryRelease 14.0

B035-1098-111AJanuary 2012

The product or products described in this book are licensed products of Teradata Corporation or its affiliates.

Teradata, Active Enterprise Intelligence, Applications Within, Aprimo, Aprimo Marketing Studio, Aster, BYNET, Claraview, DecisionCast, Gridscale, Managing the Business of Marketing, MyCommerce, Raising Intelligence, Smarter. Faster. Wins., SQL-MapReduce, Teradata Decision Experts, Teradata Labs Logo, Teradata Raising Intelligence Logo, Teradata Source Experts, WebAnalyst, and Xkoto are trademarks or registered trademarks of Teradata Corporation or its affiliates in the United States and other countries.

Adaptec and SCSISelect are trademarks or registered trademarks of Adaptec, Inc.

AMD Opteron and Opteron are trademarks of Advanced Micro Devices, Inc.

EMC, PowerPath, SRDF, and Symmetrix are registered trademarks of EMC Corporation.

GoldenGate is a trademark of Oracle.

Hewlett-Packard and HP are registered trademarks of Hewlett-Packard Company.

Intel, Pentium, and XEON are registered trademarks of Intel Corporation.

IBM, CICS, RACF, Tivoli, and z/OS are registered trademarks of International Business Machines Corporation.

Linux is a registered trademark of Linus Torvalds.

LSI is a registered trademark of LSI Corporation.

Microsoft, Active Directory, Windows, Windows NT, and Windows Server are registered trademarks of Microsoft Corporation in the United States and other countries.

NetVault is a trademark or registered trademark of Quest Software, Inc. in the United States and/or other countries.

Novell and SUSE are registered trademarks of Novell, Inc., in the United States and other countries.

Oracle, Java, and Solaris are registered trademarks of Oracle and/or its affiliates.

QLogic and SANbox are trademarks or registered trademarks of QLogic Corporation.

SAS and SAS/C are trademarks or registered trademarks of SAS Institute Inc.

SPARC is a registered trademark of SPARC International, Inc.

Symantec, NetBackup, and VERITAS are trademarks or registered trademarks of Symantec Corporation or its affiliates in the United States and other countries.

Unicode is a registered trademark of Unicode, Inc. in the United States and other countries.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Other product and company names mentioned herein may be the trademarks of their respective owners.

THE INFORMATION CONTAINED IN THIS DOCUMENT IS PROVIDED ON AN “AS-IS” BASIS, WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING THE IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR NON-INFRINGEMENT. SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED WARRANTIES, SO THE ABOVE EXCLUSION MAY NOT APPLY TO YOU. IN NO EVENT WILL TERADATA CORPORATION BE LIABLE FOR ANY INDIRECT, DIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS OR LOST SAVINGS, EVEN IF EXPRESSLY ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

The information contained in this document may contain references or cross-references to features, functions, products, or services that are not announced or available in your country. Such references do not imply that Teradata Corporation intends to announce such features, functions, products, or services in your country. Please consult your local Teradata Corporation representative for those features, functions, products, or services available in your country.

Information contained in this document may contain technical inaccuracies or typographical errors. Information may be changed or updated without notice. Teradata Corporation may also make improvements or changes in the products or services described in this information at any time without notice.

To maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document. Please email: [email protected].

Any comments or materials (collectively referred to as “Feedback”) sent to Teradata Corporation will be deemed non-confidential. Teradata Corporation will have no obligation of any kind with respect to Feedback and will be free to use, reproduce, disclose, exhibit, display, transform, create derivative works of, and distribute the Feedback and derivative works thereof without limitation on a royalty-free basis. Further, Teradata Corporation will be free to use any ideas, concepts, know-how, or techniques contained in such Feedback for any purpose whatsoever, including developing, manufacturing, or marketing products or services incorporating Feedback.

Copyright © 2000 - 2012 by Teradata Corporation. All Rights Reserved.

mailto:[email protected]

Release Summary 3

Preface

Purpose

This book provides an overview of the features, compatibilities, and requirements of Teradata Database 14.0.

Audience

This book is designed for those who want an overview of the features in Teradata Database 14.0.

Supported Software Releases and Operating Systems

This book supports Teradata® Database 14.0.

Teradata Database 14.0 is supported on:

• SUSE Linux Enterprise Server (SLES)10

• SLES 11

Note that SLES 11 will be supported after the initial release of Teradata Database 14.0.

Teradata Database client applications support other operating systems.

Changes to this Book

Release Description

Teradata Database 14.0January 2012

Clarified use of permanent journals on tables with column partitioning.

Added compression / decompression functions to the list of Embedded Services functions.

Teradata Database 14.0November 2011

Initial release

PrefaceAdditional Information

4 Release Summary

Additional Information

To maintain the quality of our products and services, we would like your comments on the accuracy, clarity, organization, and value of this document. Please email [email protected].

Teradata Database Optional Features

This book may include descriptions of the following optional Teradata Database features and products:

• Teradata Row Level Security

• Temporal Table Support

URL Description

www.info.teradata.com/ Use the Teradata Information Products Publishing Library site to:

• View or download a manual:

1 Under Online Publications, select General Search.

2 Enter your search criteria and click Search.

• Download a documentation CD-ROM:

1 Under Online Publications, select General Search.

2 In the Title or Keyword field, enter CD-ROM, and click Search.

• Order printed manuals:

Under Print & CD Publications, select How to Order.

www.teradata.com The Teradata home page provides links to numerous sources of information about Teradata. Links include:

• Executive reports, case studies of customer experiences with Teradata, and thought leadership

• Technical information, solutions, and expert advice

• Press releases, mentions and media resources

www.teradata.com/t/TEN/ Teradata Customer Education designs, develops and delivers education that builds skills and capabilities for our customers, enabling them to maximize their Teradata investment.

www.teradataatyourservice.com Use Teradata @ Your Service to access Orange Books, technical alerts, and knowledge repositories, view and join forums, and download software patches.

developer.teradata.com/ Teradata Developer Exchange provides articles on using Teradata products, technical discussion forums, and code downloads.

http://www.info.teradata.com

http://www.teradata.com

http://www.teradata.com/t/TEN/

www.teradataatyourservice.com

http://developer.teradata.com/



PrefaceTeradata Database Optional Features

Release Summary 5

• Teradata Columnar

• Teradata Virtual Storage (VS)

You may not use these features without the appropriate licenses. The fact that these features may be included in product media or downloads, or described in documentation that you receive, does not authorize you to use them without the appropriate licenses.

Contact your Teradata sales representative to purchase and enable optional features.

PrefaceTeradata Database Optional Features

6 Release Summary

Table of Contents

Release Summary 7

Table of Contents

Preface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Audience . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Supported Software Releases and Operating Systems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Changes to this Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .3

Additional Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

Teradata Database Optional Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .4

Chapter 1: Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9

Content Organization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .9

Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Active Enabled. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Enterprise Fit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Quality/Supportability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11

Ease of Use . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

Chapter 2: New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

Active Fallback, Phase II . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

Block-Level Compression Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13

COLLECT STATISTICS Enhancements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

Encryption Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

Expansion by Business Days . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17

Hash Join Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Improved Workload Management (SLES 11 Only) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19

Increased Maximum Number of Vprocs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Increased Partition Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20

Indexes on UDT Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Initial Data Temperature, Phase II . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Table of Contents

8 Release Summary

FastLoad Support for Temporal Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .23

Kerberos Authentication from Linux Clients. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24

LDAP Authentication for Multiple Directory Services . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .24

Multiple WITH/WITH RECURSIVE Clauses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25

New Embedded Services Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .25

New Fields in the Workload Management APIs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .26

New Time Unit Anchors for EXPAND ON Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .28

NUMBER Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .29

Online Reconfiguration Phase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .30

Persistent Standby Nodes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .31

Provide Table with Teradata Reserved Query Band Names . . . . . . . . . . . . . . . . . . . . . . . . . . . .31

Redesign RSS to Support an Application Data Pull Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . .32

Restricted Creation of New Kanji1 Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .33

Row-Level Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .34

Secure Password Storage and Retrieval. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .35

Selective Dump Capability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .36

Separate LogonSource String . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37

Set AuditTrailId to Authcid for LDAP Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .37

SQL ARRAY/VARRAY Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .38

Teradata Database Uses Non-Root Linux User Account . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .39

TD_ANYTYPE Parameter Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .40

TDWM New Events . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .41

Temperature-Based Block-Level Compression . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .43

Teradata Columnar. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .44

Teradata Unity . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

Unicode 6.0 Update . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46

Unicode Support in LOWER Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .46

User-Level Export Width . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .47

Workload Management API Support for Extended Object Name (EON) . . . . . . . . . . . . . . . .48

Appendix A: New Reserved Words . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

Teradata Reserved Words . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

Teradata Nonreserved Words . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

Teradata Parallel Transporter Reserved Words . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .51

ANSI SQL:2008 Nonreserved Words . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .52

Release Summary 9

CHAPTER 1 Introduction

This document provides an overview of the new features in Teradata Database Release 14.0, including benefits and considerations, and points readers to other documents for more details. You can use this material to:

• Decide about upgrading to the release.

• Plan for implementing the release.

• Introduce features to end users.

Unless otherwise specified, features run on all operating systems supported for this release. For details on hardware and software requirements, see Teradata Database 14.0 Release Definition.

Content Organization

This chapter lists major features according to their strategic category and provides links to the feature descriptions in other chapters. Strategic categories include:

• Performance

• Active Enabled

• Enterprise Fit

• Quality/Supportability

• Ease of Use

Chapter 2 lists Release 14.0 features alphabetically and describes them.

Appendix A lists reserved and nonreserved words new to this release. For a complete list of reserved and nonreserved words, see SQL Fundamentals.

Chapter 1: IntroductionPerformance

10 Release Summary

Performance

This strategic category supports:

Features in this category include:

“COLLECT STATISTICS Enhancements” on page 15.

“Hash Join Enhancements” on page 18.

“Increased Partition Limits” on page 20.

“Initial Data Temperature, Phase II” on page 22

“Teradata Columnar” on page 44.

Active Enabled



“Active Fallback, Phase II” on page 13.

“Improved Workload Management (SLES 11 Only)” on page 19.

“Online Reconfiguration Phase” on page 30.

“Teradata Unity” on page 45.

Enterprise Fit


• Database improvements in path length reductions

• Scalability

• Competitive positioning in active data warehousing

• Competitive improvements

• Integration of analytical extensions into the database

• Optimizer enhancements

• Complex query performance

• Tactical query performance

• Data currency for decisions using near real-time data

• Performance for event-driven decisions

• Tactical query enhancements

• Mixed workload management tools that result in predictable, short, repeatable response time

• Ability to define and manage business rules to detect high volume, time critical events that automatically deploy action

• Triggers and interfaces (client and data encryption) to external processes

• High availability features

• Continuous load and backup

Chapter 1: IntroductionQuality/Supportability

Release Summary 11


“Block-Level Compression Enhancements” on page 13.

“Encryption Enhancements” on page 16.

“Increased Maximum Number of Vprocs” on page 20.

“Indexes on UDT Columns” on page 22.

“Kerberos Authentication from Linux Clients” on page 24.

“LDAP Authentication for Multiple Directory Services” on page 24

“Multiple WITH/WITH RECURSIVE Clauses” on page 25.

“Persistent Standby Nodes” on page 31.

“Provide Table with Teradata Reserved Query Band Names” on page 31.

“Restricted Creation of New Kanji1 Data” on page 33.

“Row-Level Security” on page 34.

“Separate LogonSource String” on page 37

“Set AuditTrailId to Authcid for LDAP Authentication” on page 37.

“Temperature-Based Block-Level Compression” on page 43.

“Unicode 6.0 Update” on page 46.

“Unicode Support in LOWER Function” on page 46.

“User-Level Export Width” on page 47

“Workload Management API Support for Extended Object Name (EON)” on page 48.

Quality/Supportability


• Flexible, scalable, and open interface applications and standards

• Application-friendly enhancements

• Interfacing with external processes (for example, security compliance) and customer infrastructure, both software and hardware

• Tools that ease Teradata integration with external applications, processes, and system management enterprise infrastructure

• Features that extend the life of the customer investment (for example, coexistence)

• Globalization and localization

• Application integration

• Product partnerships

Chapter 1: IntroductionEase of Use

12 Release Summary


“Teradata Database Uses Non-Root Linux User Account” on page 39.

“Redesign RSS to Support an Application Data Pull Mode” on page 32.

“Selective Dump Capability” on page 36.

Ease of Use



“Expansion by Business Days” on page 17.

“New Embedded Services Functions” on page 25.

“New Fields in the Workload Management APIs” on page 26.

“NUMBER Data Type” on page 29.

“SQL ARRAY/VARRAY Data Type” on page 38.

“TD_ANYTYPE Parameter Data Type” on page 40.

• Improvements in product and service quality

• Service and product offers designed as“re-use” models for rapid development and deployment of applications

• Features that offer faster fault recovery

• Features that reduce costs through higher quality and development schedule attainment

• Deployment / rollout activities

• Support of Partner-provided Teradata-specific feature functions

• Lower cost of maintenance solutions

• Reduced need for planned downtime

• Remote support tools (debugging and remote crash dump analysis)

• Customer self-service

• Built-in process improvements

• Integrated systems management utilities that provide a single view of all system components

Release Summary 13

CHAPTER 2 New Features

This chapter provides an overview of the new features in Release 14.0

Active Fallback, Phase II

Phase II of this feature repairs the primary copy of a data block from fallback when an unrecoverable bit error occurs. Phase I allowed the data block to be read from the fallback copy.

Benefits

Systems with solid state drives can handle unrecoverable bit errors without manual intervention. Previously, only systems with RAID could handle these errors.

Considerations

A data block with an unrecoverable bit error is not repaired if:

• It occurs during startup.

• It is not in a permanent primary table.

• It results from the use of:

• Filer or Ferret.

• An AMP-level utility, such as CheckTable and Reconfiguration.

• A File System background task, such as MinicCylPack, defrag, or AutoCylPack.

• A data block merge.

• There is lock contention.

Block-Level Compression Enhancements

Block-level compression (BLC) can now be applied independently to primary and fallback data, including CLOB data.

BLC enhancements include:

• Ferret COMPRESS and UNCOMPRESS commands have been extended to:

• Compress primary and fallback data independently.

• Compress CLOB data.

Chapter 2: New FeaturesBlock-Level Compression Enhancements

14 Release Summary

• Accept an asterisk wildcard argument. This argument causes the commands to operate on all data that qualifies for compression in a specified database or all data of a particular type (primary, fallback, CLOB primary, CLOB fallback).

• Include an ESTIMATE option, which estimates the effects of the COMPRESS and UNCOMPRESS commands before you run them.

• The Ferret SHOWBLOCKS command now shows additional information about the compression status of data blocks, the estimated compression ratio, and an estimate of the proportion of blocks that are uncompressed.

• The New DBS Control compression settings specify whether primary, fallback, and CLOB data should be compressed by default.

• The BLOCKCOMPRESSION query band has been extended to support independent compression of primary, fallback, and CLOB data.

• The New Ferret SHOWCOMPRESS command displays tables that have BLC compressed data blocks and lists whether the data blocks were compressed manually or automatically using temperature-based compression.

• Teradata now collects Resource usage (ResUsage) statistics for compress and uncompress operations.

Benefits

• Finer control over the types of tables that are compressed.

• Single-command compression of all qualifying tables in a database.

• Improved performance from compressing only the fallback data subtables.

• Better capacity planning because you can estimate the results of compression and decompression prior to issuing the commands.

• Better monitoring of compression status of tables.

Considerations

Even if the asterisk wildcard is used with the COMPRESS and UNCOMPRESS commands:

• Compression of fallback data includes fallback for secondary indexes. Primary data for secondary indexes is never compressed.

• Compression remains subject to the tunables set in the Compression group of DBS Control fields.

For More Information

See:

• Database Administration

• SQL Data Definition Language

• Utilities

• Database Design

• “Temperature-Based Block-Level Compression” on page 43.

Chapter 2: New FeaturesCOLLECT STATISTICS Enhancements

Release Summary 15

COLLECT STATISTICS Enhancements

Enhancements to how statistics are collected and maintained include:

• Adding the SUMMARY option to collect table-level statistics

• Adding a number of internal enhancements to the Optimizer with respect to histogram structure and use, including:

• Maintaining statistics history.

• Enhancing extrapolation methods for stale statistics.

• Enhancing sampling options.

• Storing statistics data in their native Teradata data types without losing precision.

• Adding cost-based optimization techniques such as rollup and pre-aggregation to determine the optimal access path for collecting or recollecting statistics on a table or index.

• Adding a system sample option to the USING clause, which allows the system to determine the sampling percentage for sampled statistics.

• Storing statistics in a new system table, DBC.StatsTbl, instead of DBC.TVFields. This reduces access contention and improves performance. There are new system views you can use to query DBC.StatsTbl.

• Adding the SHOW STATISTICS statement, which reports detailed statistics in either plain text or XML formatting. SHOW STATISTICS has separate Optimizer and Query Capture Database (QCD) versions.

Benefits

• Permits you to specify whether you or the system should determine a sampling percentage for sampled statistics.

• Enables you to collect or recollect either summary statistics only or both full and summary statistics.

• Removes the restriction on collecting statistics on global temporary tables.

• Enables you to provide a name for statistics collected on a base table or global temporary table.

• Lets you explicitly specify the column ordering for multicolumn statistics.

• Improves query optimization time through a new dedicated statistics cache.

• Simplifies privileges:

• To collect or report statistics on a row-level security (RLS) table, you must have the OVERRIDE SELECT CONSTRAINT privilege.

• To collect or drop statistics on non-RLS tables, you need only have the STATISTICS privilege.

• To copy statistics from one table to a duplicate source table, you must have the SELECT privilege on the source table.

Chapter 2: New FeaturesEncryption Enhancements

16 Release Summary

Considerations

• You cannot use ALTER TABLE requests to modify or drop columns if statistics have been collected for them.

• Dropping column-level statistics does not drop SUMMARY statistics.

• The following new system views are not compatible with releases prior to Teradata Database 14.0:

• DBC.ColumnStatsV

• DBC.MultiColumnStatsV

• DBC.IndexStatsV

• You cannot name statistics collected on a volatile table.

• You can no longer specify a column or index for a HELP STATISTICS request, nor can you display detailed statistics because HELP STATISTICS now reports only table-level summary statistics.

• Statistics on an index are not dropped when you drop the index.

• When you need to collect multiple statistics, you should group them into a single COLLECT STATISTICS request. For first-time collections, group collections together that specify the same USING options.

• The new COLLECT STATISTICS enhancements are not available using the legacy syntax for the following statements:

• COLLECT STATISTICS

• DROP STATISTICS

• HELP STATISTICS


See:


• SQL Request and Transaction Processing

• Data Dictionary

• Database Design

Encryption Enhancements

In previous releases, Teradata Database defaulted to a single set of encryption standards with no options for increasing encryption strength:

• Teradata client applications provided the option to encrypt network traffic to and from the database, but all encryption used the default AES 128-bit algorithm.

• The encryption key length was set at 640-bits.

In this release:

Chapter 2: New FeaturesExpansion by Business Days

Release Summary 17

• You can configure the system to offer encryption strengths of 128, 192, and 256-bit, and assign them, according to site policy, to the user-selectable Default, Low, Medium, and High encryption levels.

• The encryption key defaults to 2,048 bits. You can also configure lesser values.

• Support for the following has been discontinued:

• The TD1, NTLM, NTLMC, and KRB5C mechanisms. These cannot operate with the new encryption options.

• The Blowfish encryption algorithm.

• Certain mode and padding options previously usable for configuring custom encryption algorithms.

Benefits

Stronger data protection and more flexibility in configuring security settings.

Considerations

You must complete certain configuration tasks to use the new encryption options.

The new encryption options are only available for the TD2 and LDAP authentication mechanisms.

Note: For compatibility with earlier releases, Teradata Database continues to support the existing methods of network traffic encryption and encryption key generation.


See Security Administration.

Expansion by Business Days

This feature provides three system-defined business calendars, TERADATA, ISO, and COMPATIBLE, and allows the user to set one of these calendars as the business calendar for the session.

In particular, the feature:

• Provides a set of business calendar user-defined functions (UDFs) that allow you to retrieve dates from any of the three system-defined business calendars.

• Enhances the EXPAND ON clause in the SELECT statement to support business calendars.

• Adds support for the following new business anchors: WEEK_BEGIN, WEEK_END, QUARTER_BEGIN, QUARTER_END, YEAR_BEGIN and YEAR_END.

• Adds macros to configure the patterns and exceptions for the business calendars.

• Enhances existing Sys_calendar.calendar views to produce calendar information with respect to the calendar set in the session.

Chapter 2: New FeaturesHash Join Enhancements

18 Release Summary

• Includes new business calendar views that you can use the views to retrieve dates relevant to your business, such as the first business day of a week.

Benefits

The three system-defined business calendars enable you to perform DateTime operations relevant to your business.

Considerations

The default business calendar for a session, called TERADATA, provides the same calendar behavior as in previous releases.

You must run the Database Initialization Program (DIP) utility and execute the DIPSYSFNC script before you can use the business calendar functions.


See:

• SQL Data Types and Literals

• SQL Functions, Operators, Expressions, and Predicates

• Data Dictionary


• SQL Data Manipulation Language

Hash Join Enhancements

These enhancements extend the application of hash joins to include:

• Classical and dynamic hash outer joins

• Inclusion hash semijoins and exclusion hash semijoins

• Dynamic, inclusion, and exclusion hash semijoins with dynamic partition elimination

• Hash joins with cross terms

Benefits

• More efficient hash joins.

• Enhanced performance of outer joins, inclusion and exclusion semijoins, joins with partition elimination, and joins with cross terms.

Considerations

• Hash join enhancements with dynamic partition elimination only apply to dynamic hash joins, inclusion hash joins, and exclusion hash joins. They do not apply to classical or direct hash joins.

Chapter 2: New FeaturesImproved Workload Management (SLES 11 Only)

Release Summary 19

• Hash semijoin enhancements only apply to ordinary inclusion and exclusion joins and are not supported for correlated hash semijoins or outer hash semijoins.

• Outer hash outer join enhancements only apply only to classical hash joins and dynamic hash joins. They are not supported for direct hash joins.


See SQL Request and Transaction Processing.

Improved Workload Management (SLES 11 Only)

For Teradata Database running on SLES 11, workload management and prioritization have been improved with a smaller, more focused set of administrative controls.

Benefits

Tier-Oriented Resource Allocation

Workload definitions can be assigned to one of three workload management tiers that determine how system resources are allocated. The three tiers are:

• Tactical

Tactical workloads receive system resources in preference to workloads in lower tiers. Because tactical workloads yield the fastest available response times, the Tactical tier is only intended for the very highest priority, highly tuned tactical workloads, typically composed of single-AMP or simple all-AMP queries.

• Service Level Goals (SLG)

Workloads in the SLG tier are assigned a user-defined percentage of the resources that are available after allocations have been made for tactical workloads.

• Timeshare

Workloads in the Timeshare tier are expected to consume the majority of resources on the platform. Within the Timeshare tier, you can assign workloads to one of four stepped access levels. This reflects a deterministic method for allocating varying amounts of resources within the tier.

Typically, the Tactical and SLG tiers are only used for workloads that consume few resources, but whose response time is critical to the business. This means that a majority of system resources are available to the more common Timeshare workloads.

Virtual Partitions

Although most sites have their priority management needs satisfied within a single set of tiers, under specific circumstances, you can subdivide the platform resources into several virtual partitions, where each virtual partition would have its own independent group of tiers, and

Chapter 2: New FeaturesIncreased Maximum Number of Vprocs

20 Release Summary

within each partition there would be any number of workloads. Resource allocation would be influenced primarily by other workloads and tiers within that virtual partition.

Having virtual partitions increases flexibility for a workload management strategy. For example, you can define separate virtual partitions for different geographic regions or for business groups that share the same Teradata Database system.

Considerations

Viewpoint is required for workload management on SLES 11.


See Teradata Database Viewpoint documentation.

Increased Maximum Number of Vprocs

The maximum number of virtual processors (vprocs) supported per Teradata Database system has been increased from 16,384 to 30,720.

Benefits

• Allows Teradata Database systems to include up to 1024 nodes.

• Increases data processing capacities for large systems.

Considerations

The new vproc limit changes the vproc numbering method in systems having more than 16,384 vprocs. The Parallel Upgrade Tool (PUT) determines the new system vproc numbering during initial system configuration.


See:

• Parallel Upgrade Tool (PUT) Reference

• Support Utilities

• Utilities

Increased Partition Limits

This feature increases limits related to partitioned primary indexes (PPIs) and their partitioning expressions. The new limits for partitioning expressions also apply to column-partitioned NoPI tables and column-partitioned NoPI join indexes.

Chapter 2: New FeaturesIncreased Partition Limits

Release Summary 21

• When the number of combined partitions 65,535, the partition number is stored in a 2-byte field of the row header.

• When the number of combined partitions > 65,535, the partition number is stored in an 8-byte field of the row header.

• The RANGE_N function can now have a test value that results in a BYTEINT, SMALLINT, INTEGER, BIGINT, DATE, TIMESTAMP(n) [WITH TIME ZONE], CHARACTER, VARCHAR, GRAPHIC, or VARGRAPHIC or data type.

• The maximum number of combined partitions for a table or join index is now 9,223,372,036,854,775,807.

• The maximum number of partitioning levels for a multilevel PPI is now 62.

• The maximum number of ranges or conditions for a specific level of a partitioning expression is now 9,223,372,036,854,775,805.

• The maximum number of ranges or conditions for a RANGE_N function with an INTEGER type that is part of a partitioning expression, including the NO RANGE, UNKNOWN, and NO RANGE OR UNKNOWN partitions, is 2,147,483,647.

• The maximum number of ranges or conditions for a RANGE_N function with a BIGINT type that is part of a partitioning expression is now 9,223,372,036,854,775,805.

• The maximum number of partitions for a CASE_N partitioning expression is now 2,147,483,647.

Benefits

• Allows for more flexibility in defining a PPI.

• Improves workload performance through added flexibility.

Considerations

• Teradata does not recommend partitioning to a granularity where populated combined partitions have less than 5 to 10 data blocks of rows. This applies to all partitioning.

• You cannot use an ALTER TABLE request to alter the primary index of a populated table or join index between a 2-byte form and an 8-byte form of partition numbering.


See:



• Database Design

• Utilities

Chapter 2: New FeaturesIndexes on UDT Columns

22 Release Summary

Indexes on UDT Columns

You can now create primary and secondary indexes on most types of user-defined type (UDT) columns.

Benefits

You can declare a primary or secondary index on a UDT column when you create:

• Indexed tables

• Join indexes

• Hash indexes

• Secondary indexes

Considerations

• You can now create primary or secondary indexes on:

• LOB UDTs

• Period families of data types (Teradata internal UDTs)

• Geospatial families of data types (Teradata internal UDTs)

• ARRAY/VARRAY (Teradata internal UDT)

• The VARIANT_TYPE data type

• Teradata does not support referential integrity constraints on UDT columns.


See:



• Database Design

Initial Data Temperature, Phase II

In Phase I, query banding enabled you to specify data temperature on data loads.

Phase II enhances query band options for temperature specification and allows data to retain its temperature during:

• Backup, Archive, and Restore (BAR)

• Table Rebuild (REBUILD)

• System reconfiguration (RECONFIG)

Chapter 2: New FeaturesFastLoad Support for Temporal Tables

Release Summary 23

Benefits

• For BAR, REBUILD, and RECONFIG, temperatures move with the data. The database administer no longer needs to reassign data temperatures after performing backup, archive and restore, table rebuild, or system reconfiguration.

• Temperatures are kept and placed on storage with the best match if you:

• Purchase and enable Teradata Virtual Storage (TVS).

• Enable Temperature-Based Block Level Compress (TBBLC).

• There is negligible increase in storage to archive the heat map.

Considerations

• Generating and searching the heat map causes a slight increase in CPU load.

• To enable this feature, you must:

• Purchase and enable Teradata Virtual Storage (TVS).

• Enable Temperature-Based Block Level Compress (TBBLC).

• Accumulating data statistics, even for systems with TVS disabled, allows for such features as TBBLC.


See Teradata Virtual Storage.

FastLoad Support for Temporal Tables

FastLoad can now inserts into temporal tables.

Benefit

Faster loading of temporal tables without using staging tables.

Considerations

• Do not use CURRENT INSERT (valid time or transaction time) if the FastLoad script includes a CHECKPOINT specification.

• Use NONTEMPORAL INSERT to ensure a one-to-one correspondence between the number of rows inserted and the number of rows that results in the target table. Restarts during loading can change the valid time and transaction time values for rows that are inserted after the restart.


See:

• Teradata FastLoad Reference


Chapter 2: New FeaturesKerberos Authentication from Linux Clients

24 Release Summary

Kerberos Authentication from Linux Clients

Kerberos authentication is available for Linux clients.

In previous releases, Kerberos authentication was only available for users logging on from Windows clients.

Benefits

Both Windows and Linux clients can now use one standard authentication method.

Considerations

Linux clients require a different configuration procedure than that previously used for Windows clients.

The Windows configuration procedure has new options.


See Security Administration.

LDAP Authentication for Multiple Directory Services

In previous releases, Teradata Database supported Lightweight Directory Access Protocol (LDAP) authentication only for a single directory service.

In this release, LDAP can authenticate users from multiple directory services.

The release enhances <Identity Map> and <Identity Search> capabilities, which improve directory user identification.

Benefits

Enterprise-Wide Access to Teradata Database

Companies with locations that maintain separate directory services can now offer enterprise-wide database access using LDAP authentication.

You can set up a separate LDAP configuration for each service, including Secure Sockets Layer/ Transport Layer Security (SSL/TLS) protection properties, identity maps, and identity searches.

Enhanced Directory User Identification Capabilities

Additional <Identity Map> and <Identity Search> capabilities allow Teradata Database to operate more efficiently with a range of non-standard directory user names and to represent

Chapter 2: New FeaturesMultiple WITH/WITH RECURSIVE Clauses

Release Summary 25

directory users in standard authcid format in the database user logs, whether or not users configure authentication for multiple directory services.

Considerations

You must add the new <LdapConfig> section to the TDGSS configuration to implement authentication of directory users for multiple directory services.

You may need to reconfigure any existing <Identity Map> and <Identity Search> elements.


See:

• Security Administration

• Security: User Authentication and Directory Integration (Teradata Orange Book)

Multiple WITH/WITH RECURSIVE Clauses

The number of WITH or WITH RECURSIVE request modifiers that can be specified with a Data Manipulation Language (DML) request increases from one to any number.

Benefits

Enhances the capability of Teradata SQL to perform recursive DML requests.

Enhances compatibility with third-party products and other databases that support this functionality.

Considerations

You no longer must specify a recursive view to perform multiple recursions through a set of data.


See SQL Data Manipulation Language

New Embedded Services Functions

This feature provides the following new Teradata Database functions:

• Numeric:

POWER, SIGN, TRUNC(Numeric), ROUND(Numeric), GREATEST, LEAST

• Date:

LAST_DAY, NEXT_DAY, MONTHS_BETWEEN, OADD_MONTHS, TRUNC(Date), ROUND(Date)

Chapter 2: New FeaturesNew Fields in the Workload Management APIs

26 Release Summary

• String:

ASCII, CHR, LENGTH, INITCAP, LPAD, RPAD, LTRIM, RTRIM, OREPLACE, INSTR, OTRANSLATE, NGRAM, EDITDISTANCE, STRTOK_SPLIT_TO_TABLE, STRTOK

• Conversion:

TO_NUMBER, TO_CHAR(DateTime), TO_CHAR(Numeric), TO_TIMESTAMP, TO_DATE, TO_TIMESTAMP_TZ, TO_YMINTERVAL, TO_DSINTERVAL, NUMTODSINTERVAL, NUMTOYMINTERVAL, TO_BYTES, FROM_BYTES

• Regular expression-based string functions:

REGEXP_SUBSTR, REGEXP_REPLACE, REGEXP_INSTR, REGEXP_SIMILAR, REGEXP_SPLIT_TO_TABLE

• LOB-based functions:

EMPTY_BLOB, EMPTY_CLOB

• Null handling functions:

NVL, NVL2

• Compression / decompression functions:

TD_LZ_COMPRESS, TD_LZ_DECOMPRESS

• Miscellaneous functions:

DECODE

This feature also adds support for DECIMAL/NUMERIC and NUMBER parameters in the CEIL and FLOOR functions.

Benefits

• Expands database functionality.

• Increases compatibility with other databases that support some or all of the above functionality.


See SQL Functions, Operators, Expressions, and Predicates.

New Fields in the Workload Management APIs

This feature enhances Workload Management Performance Monitor/Application Programming Interfaces (PM/APIs), open APIs, and DBS Control. It adds:

• Several new fields to the PM/APIs and open APIs.

• Two new open APIs:

• MonitorAMPLoad, which is equivalent to the PM/API MONITOR AWT RESOURCE request. You can use MonitorAMPLoad to get AMP Worker Task (AWT) statistics from MONITOR AWT RESOURCE response.

Chapter 2: New FeaturesNew Fields in the Workload Management APIs

Release Summary 27

• GetPSFVersion, which determines the type of Priority Scheduler used (for example, PERFGROUPS or WORKLOADS).

• A new DBS Control field, PMPC_SessionRateThreshold, which tells you whether the session cache is refreshed on demand in relation to the exception interval and session rate. For example, PERFGROUPS or WORKLOADS.

You can now:

• Refresh the PM/API MONITOR SESSION request cache at regular intervals.

• Use the System-level NetAUp and NetBUp fields to reflect the overall system BYNET status.

• Use the MONITOR AWT RESOURCE request to return AMP Worker Task (AWT) usage information.

• Use the MONITOR SESSION request to return the DBQL flush rate.

• Use the following PM/API requests to return the collection data and time:

• MONITOR PHYSICAL SUMMARY

• MONITOR SESSION

• MONITOR VIRTUAL RESOURCE

• MONITOR VIRTUAL SUMMARY

• MONITOR WD

• Use the PM/API MONITOR PHYSICAL RESOURCE and MONITOR PHYSICAL CONFIG requests and the MonitorPhysicalConfig function to return the individual node-level BYNET status.

Considerations

• The new PM/API fields are only available on monitor software version 9 or later.

• If you are using monitor software version 9 or later, the CICUse field of MonitorVirtualResource and the fields NetBUse, CICUse, NVMemAgings, NVMemAllocate, and NVMemAllocSegs, of MonitorPhysicalResource are removed.

• If you are using monitor software version 8 or earlier, the following fields are obsolete and return a value of 0:

• CICUse of the PM/API requests, MONITOR VIRTUAL RESOURCE and MonitorVirtualResource.

• NetBUse, CICUse, NVMemAgings, NVMemAllocate, and NVMemAllocSegs of the PM/API requests, MONITOR PHYSICAL RESOURCE and MonitorPhysicalResource.

• Some of the new PM/API fields supported by MONITOR SESSION and MONITOR WD are improved Workload Management fields.

• RunPEVprocNo is now a required input field in the PM/API MONITOR SQL request and a valid PE vproc number must be specified. You can no longer specify a zero or NULL value for this field.

• The NetAUp and NetBUp fields are node-level attributes as well as system-level attributes.

Chapter 2: New FeaturesNew Time Unit Anchors for EXPAND ON Clause

28 Release Summary

• If you are running SLES 11, the data returned from the PM/API MONITOR WD request or the MonitorWD function is sorted by the service-level-goal-driven Priority Scheduler workload definition ID (pWDid) and vproc type (VprType) fields.


See:

• Application Programming Reference

• Resource Usage Macros and Tables

• Utilities

New Time Unit Anchors for EXPAND ON Clause

Teradata Database provides new hour, minute, second, and millisecond time unit anchors that are used in the EXPAND ON clause of SELECT statements. These anchors enable you to query in more granular time units the business data values the system processes. You can use the anchors in Anchor Period and Anchor Point time unit expansion.

In previous releases, the day was the smallest time unit that you can specify in the EXPAND ON clause of SELECT statements.

Benefits

• More robust and flexible Anchor Period and Anchor Point time unit expansion.

• More granular queries of business data.

• More detailed picture of business data and system activity.


See:




This time unit anchor... Adjusts the anchor to the nearest...

ANCHOR_HOUR hours boundary.

ANCHOR_MINUTE minute boundary.

ANCHOR_SECOND second boundary.

ANCHOR_MILLISECOND millisecond boundary.

Chapter 2: New FeaturesNUMBER Data Type

Release Summary 29

NUMBER Data Type

NUMBER, a numeric data type, can be used in SQL data definition phrases, such as in column definitions and parameter and variable declarations.

The following syntax is available for representing NUMBER.

Calculations for the NUMBER data type are guaranteed to 38 digits of precision, but are often performed with 39 or 40 digits of precision. If precision and scale are not limited, NUMBER data retains the full precision and scale of the calculations.

NUMBER supports the following range of values:

± [1E-130 to 9.99…9E125], including 0.

Benefits

• More flexibility in defining numeric columns by providing the ability to change the precision or scale of existing NUMBER columns in tables without modifying the data rows.

• More flexibility in computation compared with the DECIMAL data type because the result is not limited by the precision or scale of the input.

• Greater efficiency in storing numeric data because NUMBER is a variable length data type that can vary from 0-18 bytes depending on the value stored.

• Greater range than the DECIMAL data type.

• Greater accuracy than the FLOAT data type because NUMBER has greater guaranteed precision. NUMBER can represent common decimals exactly.

• Increased compatibility with other databases, which include a similar NUMBER data type.

In order to... Use the following syntax...

indicate NUMBER with the default precision and scale limitations

NUMBER or NUMBER (*)

limit the scale NUMBER (*,m)

where m specifies a scale in the range from 0 to 38. This indicates the maximum number of digits allowed to the right of the decimal point.

limit the precision and scale • NUMBER (n,m)

• NUMBER (n), which is equivalent to NUMBER (n,0)

where n specifies the precision and m specifies the scale. The value of n can range from 1 to 38. The scale can range from 0 to n.

Chapter 2: New FeaturesOnline Reconfiguration Phase

30 Release Summary

Considerations

NUMBER is a floating point type and, therefore, an approximate data type on the Teradata Database.

If you run a query that includes a NUMBER (or another floating point) value on a database from other vendors and then run the same query on the Teradata Database, the result may be different because the order in which each database evaluates the expression may be different.


See:




• SQL External Routine Programming

Online Reconfiguration Phase

The Reconfiguration utility is enhanced to allow Teradata Database to remain available to users during much of the reconfiguration process.

Benefits

The redistribution phase of reconfiguration can now occur while Teradata Database is running with logons enabled. This allows Teradata Database to remain online and functional for much of the reconfiguration process.

Considerations

• To allow some of the reconfiguration process to occur online:

• Run SCANDISK and CheckTable immediately prior to online reconfiguration.

• Use the DBC.ReconfigRedistOrderTbl and the DBC.ReconfigDeleteOrderTbl to specify the order in which Reconfig should process the tables.

• Do not change the hash bucket size or delete AMPs.

• Certain phases of the online reconfiguration require that logons be disabled and no user sessions are running.

• The reconfiguration process always requires a system restart. However, the online reconfiguration phase dramatically reduces the time that the system will be offline.

Chapter 2: New FeaturesPersistent Standby Nodes

Release Summary 31


See Support Utilities.

Persistent Standby Nodes

In previous releases, hot standby nodes that became active because of a node failure returned to an inactive state as soon as the failed node was back in service.

In this release, hot standby nodes that become active stay in the configuration indefinitely and a previously failed node can become the standby, once it is repaired.

Benefits

Setting a hot standby node to persist in the configuration prevents the system from:

• Migrating AMPs from the standby node to the TPA node, once the TPA node is repaired.

• Performing the associated master index rebuild.

This avoids the temporary performance. degradation associated with AMP migration and a master index rebuild.

Considerations

• For new systems with over 100 nodes, hot standby nodes are set to persist by default.

• Hot standby nodes in smaller new systems and all existing systems return to an inactive state as soon as a failed node is back in service, but you can request that Teradata Customer Service reset standbys for persistence during an upgrade or migration to Release 14.0.

Note: Smaller systems (significantly fewer than 100 nodes) may not see any significant benefit from configuring standby nodes for persistence.

• If the system undergoes a forced reset (tpareset -f), any active persistent standby nodes automatically return to DOWN/STANDBY status, and the corresponding repaired node(s) rejoin the TPA configuration. Other system resets do not have this result.


Contact your Teradata representative.

Provide Table with Teradata Reserved Query Band Names

This feature provides a system function, QueryBandReservedNames_TBF, and a view, QueryBandReservedNames, that return the status of Teradata query band reserved names, including:

• The Teradata Database release in which the query band reserved name was introduced.

Chapter 2: New FeaturesRedesign RSS to Support an Application Data Pull Mode

32 Release Summary

• Whether the query band name is a restricted name, an application query band name, or a Teradata internal name.

• The context in which the query band name should be used.

• Whether the query band name can be used in a session or transaction query band or both.

Benefits

The UDF and view support the JDBC API, DatabaseMetaDatagetClientInfoProperties.


See Application Programming Reference.

Redesign RSS to Support an Application Data Pull Mode

The Application Data Pull mode provides each user independence from other Resource Sampling Subsystem (RSS) users in sampling rate and data reporting interactions.

This feature:

• Removes the CollectIntervals column from the resource usage tables.

• Replaces most columns that reported averaged values using CollectIntervals as a devisor with single sample data values.

• Renames several existing columns. For example:

• The Teradata Database 13.10 Spare columns being used are now converted to ResUsage table columns.

• All ResUsage columns that ended in Sum have been renamed to drop the Sum suffix. These columns report current values and not average values.

• Adds several new columns, among them the LdvOutReqDiv column in the ResUsageSldv table. It is used to calculate the LdvOutReqAvg value in the ResSldvView.

Benefits

• Improves data reporting reliability to:

• The Teradata Dynamic Workload Management (TDWM) software.

• Performance Monitor and Production Control (PMPC) APIs.

• ResUsage tables.

• Updates the ResUsage tables to include columns for recent features, such as BLC and Monitor WD.

• Removes the CollectIntervals column from the ResUsage tables.

Chapter 2: New FeaturesRestricted Creation of New Kanji1 Data

Release Summary 33

Considerations

• The SpareInt field has a 32-bit internal resolution. All other spare column fields have a 64-bit internal resolution. All spare column fields default to count data types, but can be converted to minimum, maximum, or track type data fields, if needed, when they are used.

• You can view the AMP and PE only node usage report using the ResNodeByGroup macro.


See:

• Application Programming Reference

• Resource Usage Macros and Tables

• Teradata Virtual Storage

• Utilities

Restricted Creation of New Kanji1 Data

This release provides the last opportunity to convert Kanji1 data to Unicode or another character set. Kanji1 support is being deprecated and will be completely discontinued in a future Teradata Database release.

As part of the plans for discontinuing Kanji1 support, the creation of new Kanji1 objects in this release is highly restricted. Inclusion of the phrase CHARACTER SET KANJI1 in the following SQL statements returns a syntax error:

• CREATE USER/MODIFY USER

• CREATE TABLE/ALTER TABLE

• CREATE FUNCTION/REPLACE FUNCTION

• CREATE TYPE/ALTER TYPE

• CREATE PROCEDURE/REPLACE PROCEDURE

• CREATE MACRO/REPLACE MACRO

• CREATE VIEW/REPLACE VIEW

• CAST function

Considerations

Although many Kanji1 queries and applications continue to operate with this release, sites using Kanji1 should prepare to convert to another character set as soon as possible.

Use the TRANSLATE function to convert your data from Kanji1 to Unicode or another supported character set. Use of the User-Level Export Width feature allows Unicode columns to appear similar to Kanji1 columns for existing applications.

Chapter 2: New FeaturesRow-Level Security

34 Release Summary

Note: When you upgrade to this release, the system automatically replaces DEFAULT CHARACTER SET KANJI1 with DEFAULT CHARACTER SET UNICODE in existing user definitions.


See International Character Set Support.

Row-Level Security

Teradata row-level security (RLC) allows you to restrict data access by row.

You can create security constraint objects, each defining a classification category, hierarchical or non-hierarchical, and a range of valid classification labels expressed as name=value pairs, for example:

• Hierarchical category: Security Classification Labels: Top Secret = 4, Secret = 3, Classified = 2, Unclassified = 1

• Non-Hierarchical category: CountryLabels: USA = 4, UK = 3, Germany = 2, France = 1

Each constraint object specifies a set of 1 to 4 UDFs that enforce row-level access controls. When you submit a request against an RLC object, the UDF enforces the access rule for the operation.

You can assign up to 6 hierarchical and 2 non-hierarchical constraint objects, either directly to users or to profiles.

Defining a table column named for a security constraint object allows you to assign classification labels to each row. Tables may have up to 5 security constraint columns. Views and some index types can also have security constraint columns.

Benefits

Row-Level Control of Access to Sensitive Data

In addition to the normal object-level privileges required for data access, the system checks the user clearance level for each operation on each row of data.

Mandatory Controls on Granting Access Privileges

Normally, owners of database objects automatically have the privilege to grant access to the owned objects to any other user. For objects protected by RLC, only users that are explicitly granted certain privileges can assign security constraints and other RLC access privileges to another user.

Chapter 2: New FeaturesSecure Password Storage and Retrieval

Release Summary 35

Considerations

To implement row-level security, you must:

• Set up a system of user security classifications.

• Perform the necessary configuration procedure in the database.


See:


• SQL Data Definition Language - Syntax and Examples

• SQL Data Definition Language - Details

• SQL Data Control Language


Secure Password Storage and Retrieval

In previous releases, logging on to Teradata Database required the user to submit a password. This sometimes caused problems. For example:

• Job scripts required the inclusion of a password that was exposed in plain text.

• Users with access to multiple database systems needed to remember which password went to which system. The need to have easily accessible passwords led to unsecure storage.

In this release, you can store passwords and other logon string data in coded file locations that the Teradata Wallet application manages. Users own and manage their own wallet. Wallet data can be password protected. At logon, a user can retrieve data from his wallet in response to special syntax specified in the logon string.

Benefits

Secure Data Storage

Passwords and other data are securely stored in user wallets on each client using:

• AES 256-bit encryption on UNIX

• The CryptProtectData function in the Windows Data Protection API on Windows

Note: You can securely store any data, such as credit card numbers or non-Teradata passwords, in a wallet.

Easy Retrieval

You can retrieve logon string data, such as a stored password, in two ways:

• Specify an alias in the portion of the logon string for which you want to retrieve data. For example, to retrieve a password, specify the following:

tdpid/username,$tdwallet(password_alias)

Chapter 2: New FeaturesSelective Dump Capability

36 Release Summary

Note: The alias retrieves the password from the wallet belonging to the user.

• Link a password to another piece of logon string information, such as the tdpId, and then specify $tdwallet in the password position of the logon string, for example:

tdpid/username,$tdwallet

Considerations

• In cases where the client is an application server, or another shared computer accessed by multiple users, you must have a unique identities so that users can access only their personal wallet.

• The system supports data retrieval in the logon string for all authentication methods.

• You must configure Teradata Wallet and follow the required data storage procedures before you can retrieve wallet data.


See:

• Security Administration.

• Teradata Tools and Utilities installation guide for each client operating system.

Selective Dump Capability

Teradata Database can perform selective memory dumps for fatal database errors. These dumps include only those processes that are likely to be relevant in diagnosing the problem that caused the error rather than all processes on all TPA nodes.

Benefits

Because only relevant dump information is collected, dumps are smaller and can be collected more quickly. This allows Teradata Database to restart more efficiently after experiencing a fatal database error.

Considerations

The information collected in selective dumps may not be adequate to diagnose all problems that cause fatal database errors. Teradata recommends that selective dumps be enabled only if excessive restart times or other issues related to large dump sizes have been a concern.


See:


• Utilities

Chapter 2: New FeaturesSeparate LogonSource String

Release Summary 37

Separate LogonSource String

The DBC.SessionTbl and DBC.EventLog Data Dictionary tables now contain current data about the source of client logon activity. The data is also available in the SessionInfo and LogOnOff system views.

In previous releases, columns in tables that were defined to store this data were not populated. The data was available, but was stored in a single column (LogonSource) in these tables. This release populates the columns dedicated to client logon source information.

Examples of the client logon source data now available in the DBC.SessionTbl and DBC.EventLog include client:

• Application name

• System user ID

• Job ID and name

• Operating system and host name

• Terminal ID

• Transaction ID

• Teradata Database release ID

• CLIv2 release ID

Benefits

Enhances the accessibility and usability of the client logon source data.


See Data Dictionary.

Set AuditTrailId to Authcid for LDAP Authentication

In previous releases, when a directory user logged on to Teradata Database, the automatic entries in the AuditTrailId column in DBC.SessionTbl and the Username column in the DBC.AccLogTbl and the EventLog varied by directory type. This sometimes caused difficulties in accurately identifying the logon user.

Logons from some directory types provided the user Authcid (logon user name), while logons from other directories resulted in a derived entry based on the UUID or GUID. In addition, the database entries did not distinguish between users from two subdirectories with duplicate user names.

Chapter 2: New FeaturesSQL ARRAY/VARRAY Data Type

38 Release Summary

In this release, directory user logons always result in the user Authcid being entered in the AuditTrailId column in DBC.SessionTbl and the Username column in the DBC.AccLogTbl. This occurs automatically for all logons after you install Teradata Database 14.0.

The only exception to this occurs when directory authorization is enabled and the directory user is mapped to a permanent database user. In this case, the Username column logs the mapped database user name.

The AuthUser columns in DBC.SessionTbl and the EventLog contain the directory user Authcid, prepended with the URL for the authenticating directory.

Benefits

• The log entry for the directory user name is always the Authcid, regardless of directory type.

• Prepending the directory URL uniquely identifies users from multiple directories that have the same logon user name.

Considerations

Due to the object name character limit, the database continues to use only the first 30 bytes of the user name (when expressed to the database character set), so the first 30 bytes of all user names should be unique.


See:


• Data Dictionary

SQL ARRAY/VARRAY Data Type

This release supports:

• ARRAY, a user-defined multidimensional data type with up to 5 dimensions and a user-defined maximum number of elements, all of the same specific data type.

• VARRAY, an Oracle-compatible form of the ARRAY data type. Unlike the Oracle VARRAY data type, the Teradata VARRAY type can be defined in multiple dimensions.

Teradata provides many built-in functions and operators and a constructor expression to support the ARRAY/VARRAY data types.

Benefits

• The ARRAY/VARRAY data types permit many values of the same data type to be stored sequentially or in a matrix-like format, extending the number of values of the same data type that can be stored in a row.

Chapter 2: New FeaturesTeradata Database Uses Non-Root Linux User Account

Release Summary 39

• The elements of an ARRAY/VARRAY data type can be accessed individually or by using one of the built-in functions provided to support ARRAY/VARRAY data types as a sequential subset.

• The values of an ARRAY column are stored within the row itself, not in a LOB. Although this limits the size of ARRAY data types to 64K bytes, it provides major performance benefits because you can access and modify individual elements without having to create and write a new LOB object each time.

Considerations

• You cannot specify an ARRAY column in the ORDER BY, GROUP BY, or HAVING clauses of a SELECT request.

• You cannot specify an ARRAY column in an index.

• Ordering and casting functionality are provided for ARRAY data types, although you can create your own UDFs to cast the elements.

• There is no ANSI SQL:2008 standard for a multidimensional ARRAY data type, and no other database vendor supports a multidimensional array data type.


See:






Teradata Database Uses Non-Root Linux User Account

Teradata Database no longer requires root account privileges to run on Linux. If you are a member of the tdtrusted group, you can run database processes and most system management and support tools.

Benefits

Improved security for Teradata Database systems. Teradata Database administrative user accounts need only be members of the tdtrusted group.

Considerations

• Root account privileges are still required to:

Chapter 2: New FeaturesTD_ANYTYPE Parameter Data Type

40 Release Summary

• Start Teradata Database when Parallel Database Extension (PDE) is not already running.

• Install new versions of the software.

• UDFs written to run in protected mode under previous versions of Teradata Database may need to be updated if they do not run under an account belonging to the tdatudf group.

• Any system resources that communicate with protected mode UDFs must be owned by the Teradata Database software, running as account teradata in user group tdtrusted.

• If you need to change the user group whose members may run secure UDFs, contact the Teradata Support Center. A new command-line tool, pdeconf, replaces the functions of the SecureGroupMembership setting that existed in prior releases of the Cufconfig utility. Use the pdeconf tool only under the direction of the Teradata Support Center.


See:

• Utilities


TD_ANYTYPE Parameter Data Type

This data type can be used with input or result parameters in C/C++ UDFs, user-defined methods (UDMs), and external stored procedures.

Parameters defined with the TD_ANYTYPE data type can accept any system-defined data type or UDT. At execution time, the parameter attributes and return type are determined from the actual arguments passed into the routine.

Benefits

• Allows routines to support parameters with differing precisions and character parameters with varying character sets.

• Removes precision loss of decimal parameters due to truncation when the arguments do not match the parameters in the function signature.

• Reduces the number of overloaded routines needed to support routines that have constant parameter counts but differing parameter data types.

• Allows developers to declare routines whose return data type is determined at runtime.

Considerations

• TD_ANYTYPE is not supported as a parameter type for:

• Stored procedures

• Java external stored procedures

• Window aggregate UDFs

Chapter 2: New FeaturesTDWM New Events

Release Summary 41

• Java UDFs

• SQL UDFs

• TD_ANYTYPE cannot be used as the return type in table functions.


See:





TDWM New Events

In Teradata Database 14.0, Teradata Database Workload Management (TDWM) software monitors two new system events and six events with respect to a WD (Workload Definition) as a entity at the system level.

This event... Does the following...

System CPU Collects data from the ResUsageSPMA table.

This event does not consider PE-only nodes in its data collection and calculations.

System-Wide Node Skew Collects data from ResUsageSPMA table.

CPU Utilization by WD Collects data from the ResUsageSPS table.

Data returned includes system-level values from the ResUsageSPS table.

Missed SLG by WD Addresses missed SLGs by defining.

• A response time (with service percent), or

• A throughput goal

for a specific WD and planned environment.

Missed SLG by WD evaluates system-wide WD statistics for completed queries against SLGs.

Arrival Rate by WD Defines an arrival rate for queries that are going to start running in a specific WD.

A query is counted as arrived, even it is gets delayed.

Concurrent Active Requests by WD Defines a threshold for the number of requests that are active within a specific WD.

Chapter 2: New FeaturesTDWM New Events

42 Release Summary

TDWM evaluates the preceding events using both real-time or non-real-time measures.

• The real-time measures require that events be continuously true over a qualification period, called the Event Interval (EI), if the event is to be considered true. Real-time measures can be:

• Immediate, where one sample of data, if true, triggers the event.

• Simple, where the event must remain true for every sample taken during the user-defined qualification period specified with the event.

• Non-real-time (or historical) measures, which use an Averaging Interval (AI), maintain data for each event over a period the system administrator defines.

The maximum AI is 120 minutes.

Benefits

New TDWM events provide event analysis using real-time and non-real-time measures that produce immediate, simple, and AI snapshots.

Considerations

Existing data collection and analysis methods, as well as historical methods, are supported. The following historical methods are not supported for the these static events:

• Node Down

• AMP Down

• Gateway Down

• PE Down

• User-Defined Events (both SysCon and OpEnv)

Delayed Queue Depth by WD Detection

Is triggered by either:

• The number of requests on the delay queue (called DelayQueryDepth).

• The time a request has been held on the delay queue (called DelayQueryTime).

The user can indicate if the object delay time or the WD-specific delay time, or both, should be considered.

Message Delay Time by WD Supplements the events for AWT (AMP Worker Task) Available and AMPs in Flow Control. The event measures the maximum time any work message waits for an AWT within a specific WD.

The event can be used to analyze the impact of AWT shortages on specific WDs.

This event... Does the following...

Chapter 2: New FeaturesTemperature-Based Block-Level Compression

Release Summary 43


See:

• Workload Management

• Teradata Viewpoint documentation

Temperature-Based Block-Level Compression

Teradata Database now provides temperature-based block-level compression (TBBLC), which automatically compresses cold (infrequently accessed) data and automatically decompresses data when it becomes warm (more frequently accessed).

TBBLC includes:

• A BLOCKCOMPRESSION option for CREATE TABLE, ALTER TABLE, CREATE JOIN INDEX and CREATE HASH INDEX that specifies block-level compression for individual tables.

• DBS Control tunable compression settings that enable and control automatic temperature-based compression.

• TVSTEMPERATURE query bands that allow you to assign different temperatures to data loaded in empty primary, fallback, and CLOB subtables.

Benefits

• Provides better storage utilization by compressing infrequently accessed data.

• Operates automatically in the background. The user can modify how frequently automatic compression task occurs.

• Speeds automatic compression for data loaded into empty tables.

• Provides a tunable temperature threshold at which data is compressed.

• Removes the complex task of identifying specific data to compress.

Considerations

Any data compression operation involves a performance impact to Teradata Database; however, the impact may be mitigated by tuning the TBBLC settings in DBS Control. The default settings should be optimal for most sites.

TBBLC can be disabled during critical performance windows by means of tunable settings. When TBBLC is disabled, autotemp tables are treated as manual tables.


See;



Chapter 2: New FeaturesTeradata Columnar

44 Release Summary

• Utilities

• Database Design

• “Block-Level Compression Enhancements” on page 13.

Teradata Columnar

The Teradata Columnar feature consists of integrated column partitioning, columnar storage, autocompression, as well as other supporting capabilities that you can specify for No Primary Index (NoPI) tables and single-table, nonaggregate, noncompressed NoPI join indexes. Column partitioning stores single columns or sets of columns from a NoPI table or NoPI join index in separate partitions.

You can store data in a column partition using either traditional row storage or columnar storage. Columnar storage packs the values of a column partition into a series of containers, significantly reducing the number of row headers that would otherwise be required.

Teradata Database automatically detects and applies opportunities for autocompression. You can partition a NoPI table by column, by row, or both by using multilevel partitioning.

The primary use for a column-partitioned table or join index occurs when table or row partitions are loaded with an INSERT … SELECT request and used to run analytics or data mining. When analytics or data mining are completed, the column-partitioned NoPI table is then deleted and the process begins again.

Benefits

• Permits efficient access to selected data, which reduces query I/O.

• Adds flexibility in defining a partitioned table or join index. Such flexibility provides opportunities to improve workload performance.

• Enables the optimizer to exclude unneeded column partitions, significantly enhancing query performance.

Considerations

• Teradata Columnar is optional and disabled by default. It can be enabled by the Teradata Support Center or by an authorized Teradata representative.

• Teradata Columnar is not intended for online transaction processing (OLTP)-type workloads.

• You cannot use permanent journals on tables with column partitioning.

• If you find that the defaults of single-column partitions, system-determined row or columnar storage, and autocompression are not appropriate, you can override those defaults by, for example, specifying the NO AUTO COMPRESS option for a column partition to prevent Teradata Database from autocompressing that partition.

• Partitioning by row, by column, or by a combination of the two to a granularity at which populated combined partitions have only a few rows or containers may not provide any

Chapter 2: New FeaturesTeradata Unity

Release Summary 45

performance benefit. In some cases, excessive partitioning may degrade performance. This applies equally to 2-byte and 8-byte partitioning.


See:



• Database Design

• Utilities

Teradata Unity

Teradata Unity provides a data virtualization layer that enables you to maintain fully active independent Teradata Database servers for continuous availability and horizontal scalability. The Teradata Unity server virtualizes Teradata Database servers under its control so that applications are not aware that there are multiple database servers. To the applications, the Teradata Unity server is the database.

Each database server in the Teradata Unity environment maintains its own data. Teradata Unity does not store any data on its own server, except for recovery purposes. The feature distributes the SQL requests from applications to the appropriate Teradata Database servers, as required.

Note: Teradata Unity is available after the initial release of Teradata Database 14.0.

Benefits

Teradata Unity enables you to override nondeterministic elements of Teradata Database at the level of an individual request, making it possible for Teradata Unity to manage multiple Teradata instances while ensuring consist results across those instances.

Considerations

• You can override DateTime, ValidTime temporal, random, and session-related built-in functions for individual requests.

• Because identity columns can generate nondeterministic results, ALTER TABLE now provides an option to drop the identity attribute from a column.


See:



• Teradata Unity Installation and User Guide

Chapter 2: New FeaturesUnicode 6.0 Update

46 Release Summary

Unicode 6.0 Update

The Teradata Database Unicode character set is expanded based on the Unicode 6.0 standard. In previous releases, the Teradata Database Unicode character set was based on the Unicode 4.1 standard.

Benefits

Provides 2,855 additional Basic Multilingual Plane (BMP) Unicode characters, such as the new INDIAN RUPEE SIGN.

Considerations

As in previous releases, Teradata Database accepts only BMP characters when using the Unicode character set.



Unicode Support in LOWER Function

The SQL LOWER function now accepts Unicode characters in an input argument. The function follows a Teradata plan for converting the input into lowercase, character by character. This unambiguously determines the correct lowercase for each character.

Benefits

The LOWER function can operate with both LATIN and UNICODE server character sets.

Considerations

The LOWER function returns the result in the same character set as the input argument, except when the input argument is in KANJI1. In this case, LOWER returns the result in the LATIN server character set.


See:


• International Character Set Support

Chapter 2: New FeaturesUser-Level Export Width

Release Summary 47

User-Level Export Width

Previous Teradata Database releases supported three sets of export width rules that could only be changed at the system level.

The User-Level Export Width feature enhances Teradata Database support of export width by enabling:

• Users to change export width rules.

• Database administrators to define new export width rules to support business needs.

Benefits

For Users Migrating From KANJI1 or KANJISJIS to Unicode

These users need export width rules that enable the concurrent execution of these character set translations:

In previous releases, these translations could not be done concurrently. When the active export width rule set enabled one type, transactions that required the other type could not be executed successfully. For example, when KANJI1 or KANJISJIS to Unicode translation was supported, client applications that used a Unicode character set failed.

The user-level export width rules enable the concurrent execution of both types of character set translations. This ensures that:

• The active system character does not need to be changed frequently during the migration process.

• Client applications do not fail because of conflicts in character set data models.

For Users Having 7-bit ASCII LATIN Column Data and Using UTF-8

These users need export width rules that enable the concurrent execution of these character set translations:

System Character Set Client Character Set

KANJI1 or KANJISJIS Unicode

Unicode Unicode

System Character Set Client Character Set

LATIN (7 bit ASCII, requires one byte when translated to UTF-8) UTF-8

LATIN (requires two bytes when translated to UTF-8) UTF-8

Chapter 2: New FeaturesWorkload Management API Support for Extended Object Name (EON)

48 Release Summary

In previous releases, these translations could not be done concurrently. The export width rules also allocated too many bytes for client applications using the UTF-8 character set, and the exported system data contained empty or ghost characters.

Now users can define an export width rule set that enables the concurrent execution of both types of translations (regardless of whether the system data requires one or two bytes when translated to UTF-8). Because the new export width rule set can be assigned to specific users, all users can expect that the data exported to their client applications is correct.

For All Users

The user-level export width rules provide:

• Support for more client application character sets. Ten new character set fields are available that correspond to the most common client application character sets.

• New columns to store all export width rules in a system table.

• Support for the following character sets:

• Any with a name that ends with ‘_01’

• KATAKANAEBCDIC

• STATEMACHINE SOSI0E0F (site defined)

• Those with SO SI characters



Workload Management API Support for Extended Object Name (EON)

Client applications using the Workload Management APIs can now use the UTF-8 and UTF-16 session character sets.

Teradata Dynamic Workload Management functions support both the UNICODE and LATIN server character sets.

Benefits

Greater character range support.

Considerations

• UTF-8 and UTF-16 session character sets are supported only on APIs running monitor version 9 or later.

• The open APIs that do not return multibyte characters continue to be defined as CHARACTER SET LATIN.


Release Summary 49

• Although the name fields of the Workload Management APIs are defined with a maximum variable-length of 128, the maximum length remains 30 bytes in the current release.


See Application Programming Reference.


50 Release Summary

Release Summary 51

APPENDIX A New Reserved Words

This appendix lists new Teradata reserved words, Teradata nonreserved keywords, Teradata Parallel Transporter reserved words, and ANSI SQL_2008 nonreserved words introduced in Teradata Database Release 14.0.

For a complete list of reserved words, see SQL Fundamentals.

Teradata Reserved Words

Teradata Database reserved words cannot be used as identifiers to name host variables, correlations, local variables in stored procedures, objects (such as databases, tables, columns, or stored procedures), or parameters, such as macro or stored procedure parameters, because Teradata Database already uses the word and might misinterpret it.

The words listed here are Teradata reserved words:

NUMBER TD_ANYTYPE

Teradata Nonreserved Words

Teradata Database nonreserved keywords are permitted as identifiers but are discouraged because of possible confusion that may result.

The words listed here are Teradata nonreserved words:

ARRAY AUTO AUTOTEMP BLOCKCOMPRESSION CALENDAR

EXPORTWIDTH MANUAL MAXINTERVALS MAXVALUELENGTH

NEVER ORDINALITY PARTITION#L16-PARTITION#L62 QUARTER_BEGIN

QUARTER_END VARRAY WEEK_BEGIN WEEK_END

YEAR_BEGIN YEAR_END

Teradata Parallel Transporter Reserved Words

Teradata Parallel Transporter (PT) reserved keywords cannot be used in Teradata PT job scripts as identifiers. Identifiers are used for column names, script object names, or attributes. Identifiers cannot be string literals.

The word listed here is a Teradata PT reserved word:

Appendix A: New Reserved WordsANSI SQL:2008 Nonreserved Words

52 Release Summary

MACROCHARSET

ANSI SQL:2008 Nonreserved Words

The word listed here is an ANSI SQL:2008 nonreserved word.

STATSUSAGE

td14_newftrs

Documents