O'Reilly logo

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Teradata Cookbook

Book Description

Data management and analytics simplified with Teradata

About This Book

  • Take your understanding of Teradata to the next level and build efficient data warehousing applications for your organization
  • Covers recipes on data handling, warehousing, advanced querying and the administrative tasks in Teradata.
  • Contains practical solutions to tackle common (and not-so-common) problems you might encounter in your day to day activities

Who This Book Is For

This book is for Database administrator's and Teradata users who are looking for a practical, one-stop resource to solve all their problems while handling their Teradata solution. If you are looking to learn the basic as well as the advanced tasks involved in Teradata querying or administration, this book will be handy. Some knowledge of relational database concepts will be helpful to get the best out of this book.

What You Will Learn

  • Understand Teradata's competitive advantage over other RDBMSs.
  • Use SQL to process data stored in Teradata tables.
  • Leverage Teradata's available application utilities and parallelism to play with large datasets
  • Apply various performance tuning techniques to optimize the queries.
  • Acquire deeper knowledge and understanding of the Teradata Architecture.
  • Easy steps to load, archive, restore data and implement Teradata protection features
  • Gain confidence in running a wide variety of Data analytics and develop applications for the Teradata environment

In Detail

Teradata is an enterprise software company that develops and sells its eponymous relational database management system (RDBMS), which is considered to be a leading data warehousing solutions and provides data management solutions for analytics. This book will help you get all the practical information you need for the creation and implementation of your data warehousing solution using Teradata.

The book begins with recipes on quickly setting up a development environment so you can work with different types of data structuring and manipulation function. You will tackle all problems related to efficient querying, stored procedure searching, and navigation techniques. Additionally, you'll master various administrative tasks such as user and security management, workload management, high availability, performance tuning, and monitoring.

This book is designed to take you through the best practices of performing the real daily tasks of a Teradata DBA, and will help you tackle any problem you might encounter in the process.

Style and approach

This book is a rich collection of recipes that will come in handy when you are working with Teradata. It addresses your common and not-so-common pain points,and this is a book that you must have on the shelf.

Downloading the example code for this book You can download the example code files for all Packt books you have purchased from your account at http://www.PacktPub.com. If you purchased this book elsewhere, you can visit http://www.PacktPub.com/support and register to have the files e-mailed directly to you.

Table of Contents

  1. Preface
    1. Who this book is for
    2. What this book covers
    3. To get the most out of this book
      1. Download the example code files
      2. Download the color images
      3. Conventions used
    4. Get in touch
      1. Reviews
  2. Installation
    1. Setting up Teradata 15.10
      1. How to do it... 
    2. Setting up Teradata Studio Express
      1. Getting ready
      2. How to do it...
    3. Teradata on Azure
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Defining a connection
      1. How to do it...
    5. Connecting to the Teradata system
      1. How to do it...
      2. There's more...
        1. Using Studio tool options
    6. Setting up Teradata SQLA
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    7. Configuring SQLA
      1. Getting ready
      2. How to do it...
      3. How it works...
    8. Building a query builder
      1. How to do it...
        1. Query builder in SQLA
        2. Query builder in Express
    9. Importing data
      1. Getting started
      2. How to do it...
        1. In Express
    10. Exporting data
      1. How to do it
      2. How it works...
      3. There's more...
  3. SQLs
    1. Introduction
    2. Writing queries
      1. Getting ready
      2. How to do it...
        1. Using SQLA
        2. Using BTEQ in Windows
      3. How it works...
      4. There's more...
    3. Querying efficiently
      1. Getting ready
      2. How to do it...
      3. How it works...
    4. Explain before executing queries
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    5. Decoding explain
      1. How to do it...
      2. How it works...
      3. There's more...
    6. Resolving skewing data
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    7. Resolving skew in database
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. Solving insert performance
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    9. Improving delete performance
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    10. Improving update performance
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    11. Performing MERGE INTO
      1. Getting ready
      2. How to do it...
      3. How it works...
  4. Advanced SQL with Backup and Restore
    1. Introduction
      1. Backup and recovery
    2. Exploring ordered analytic functions
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Using CASE statements
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Working with correlated subqueries
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    5. Experimenting with JSON
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    6. Partitioning tables column wise
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    7. Archiving data dictionary 
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. Archiving databases
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    9. Archiving PPI tables
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    10. Restoring a table
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    11. Generating a unique row number
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  5. All about Indexes
    1. Introduction
    2. Creating a partitioned primary index to improve performance
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Creating a join index to improve performance
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Creating a secondary index to improve performance
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    5. Creating a hash index to improve performance
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  6. Mixing Strategies – Joining of Tables
    1. Introduction
    2. Identifying skewness in joins
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Identify the right columns for joins
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Eliminating product joins
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    5. Improving left join
      1. Getting ready
      2. How to do it...
      3. How it works...
    6. Improving Teradata joins
      1. Getting ready
      2. How to do it...
      3. How it works...
  7. Building Loading Utility – Replication and Loading
    1. Introduction
    2. Loading data from flat to Teradata
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Resolving FastLoad error 2652
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Using MultiLoad to delete
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    5. Resolving MLOAD error 2571
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    6. Resolving failure 7547
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    7. Upserting data using MLOAD
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. Loading data from a one Teradata Database to another
      1. Getting ready
      2. How to do it...
      3. How it works...
  8. Monitoring the better way
    1. Introduction
    2. Configuring the viewpoint portlet
      1. Getting ready
      2. How to do it...
      3. How it works...
    3. Identifying killer queries in viewpoint
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Setting up viewpoint alerts
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    5. Changing and managing filters
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    6. Changing and managing throttle
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    7. Defining a ruleset
      1. Getting ready
      2. How to do it...
      3. How it works...
    8. Creating a TASM exception
      1. Getting ready
      2. How to do it...
      3. How it works...
  9. Collect Statistics the Better Way
    1. Introduction
      1. Cost-based optimizer
      2. Evolution of statistics
    2. How to collect statistics
      1. Getting ready
      2. How to do it...
      3. How it works...
    3. Identifying stats for a table
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Identifying multi-column stats
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    5. How to collect expression stats
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    6. How to copy statistics
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    7. Using help and show to resolve query issues
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
        1. Recommendations
  10. Application and OPS DBA Insight
    1. Introduction
    2. Creating, copying, and dropping tables
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Working with views
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Defining workloads based on application
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    5. Securing passwords using Teradata Wallet
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    6. Shrinking your data size
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    7. Using query band
      1. Getting ready
      2. How to do it...
      3. How it works...
  11. DBA Insight
    1. Introduction
    2. Creating a Teradata database
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Creating a Teradata database user
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Creating profiles and assigning them to users
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    5. Creating a Secure Zone
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    6. Creating user accounts
      1. Getting ready
      2. How to do it...
      3. How it works...
    7. Investigating phantom and leftover spool issues
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    8. Unlocking archive HUT locks
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  12. Performance Tuning
    1. Introduction
    2. Resolving a slow or hung system
      1. Getting ready
      2. How to do it...
      3. How it works...
    3. Monitoring slow queries
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Aborting a session from the supervisor window
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    5. Resolving AWT saturation
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    6. Identifying suspect queries
      1. Getting ready
      2. How to do it...
      3. How it works...
    7. Managing DBC space
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
      5. See also
    8. Optimizing queries
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. See also
  13. Troubleshooting
    1. Introduction
    2. Restarting the TD system
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    3. Insufficient memory to parse this request
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    4. Recovering AMP down
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    5. Performing scandisk on the system
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    6. Unlocking the DBC
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
    7. Managing the FSG cache
      1. Getting ready
      2. How to do it...
      3. How it works...
      4. There's more...
  14. Other Books You May Enjoy
    1. Leave a review - let other readers know what you think