Book Image

Teradata Cookbook

By : Abhinav Khandelwal, Viswanath Kasi, Rajsekhar Bhamidipati
Book Image

Teradata Cookbook

By: Abhinav Khandelwal, Viswanath Kasi, Rajsekhar Bhamidipati

Overview of this book

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.

Related Content you might be interested in

Current Title:

Small book image
Teradata Cookbook
Abhinav Khandelwal | Viswanath Kasi | Rajsekhar Bhamidipati
Feb, 2018 | 454 pages
No titles found
Table of Contents (19 chapters)
Title Page
Title Page
Dedication
Dedication
Packt Upsell
Packt Upsell
Contributors
Contributors
Preface
Preface
Free Chapter
1
Installation
Installation
Setting up Teradata 15.10
Setting up Teradata Studio Express
Teradata on Azure
Defining a connection
Connecting to the Teradata system
Setting up Teradata SQLA
Configuring SQLA
Building a query builder
Importing data
Exporting data
2
SQLs
SQLs
Introduction
Writing queries
Querying efficiently
Explain before executing queries
Decoding explain
Resolving skewing data
Resolving skew in database
Solving insert performance
Improving delete performance
Improving update performance
Performing MERGE INTO
3
Advanced SQL with Backup and Restore
Advanced SQL with Backup and Restore
Introduction
Exploring ordered analytic functions
Using CASE statements
Working with correlated subqueries
Experimenting with JSON
Partitioning tables column wise
Archiving data dictionary 
Archiving databases
Archiving PPI tables
Restoring a table
Generating a unique row number
4
All about Indexes
All about Indexes
Introduction
Creating a partitioned primary index to improve performance
Creating a join index to improve performance
Creating a secondary index to improve performance
Creating a hash index to improve performance
5
Mixing Strategies – Joining of Tables
Mixing Strategies – Joining of Tables
Introduction
Identifying skewness in joins
Identify the right columns for joins
Eliminating product joins
Improving left join
Improving Teradata joins
6
Building Loading Utility – Replication and Loading
Building Loading Utility – Replication and Loading
Introduction
Loading data from flat to Teradata
Resolving FastLoad error 2652
Using MultiLoad to delete
Resolving MLOAD error 2571
Resolving failure 7547
Upserting data using MLOAD
Loading data from a one Teradata Database to another
7
Monitoring the better way
Monitoring the better way
Introduction
Configuring the viewpoint portlet
Identifying killer queries in viewpoint
Setting up viewpoint alerts
Changing and managing filters
Changing and managing throttle
Defining a ruleset
Creating a TASM exception
8
Collect Statistics the Better Way
Collect Statistics the Better Way
Introduction
How to collect statistics
Identifying stats for a table
Identifying multi-column stats
How to collect expression stats
How to copy statistics
Using help and show to resolve query issues
9
Application and OPS DBA Insight
Application and OPS DBA Insight
Introduction
Creating, copying, and dropping tables
Working with views
Defining workloads based on application
Securing passwords using Teradata Wallet
Shrinking your data size
Using query band
10
DBA Insight
DBA Insight
Introduction
Creating a Teradata database
Creating a Teradata database user
Creating profiles and assigning them to users
Creating a Secure Zone
Creating user accounts
Investigating phantom and leftover spool issues
Unlocking archive HUT locks
11
Performance Tuning
Performance Tuning
Introduction
Resolving a slow or hung system
Monitoring slow queries
Aborting a session from the supervisor window
Resolving AWT saturation
Identifying suspect queries
Managing DBC space
Optimizing queries
12
Troubleshooting
Troubleshooting
Introduction
Restarting the TD system
Insufficient memory to parse this request
Recovering AMP down
Performing scandisk on the system
Unlocking the DBC
Managing the FSG cache
Other Books You May Enjoy
Other Books You May Enjoy
Leave a review - let other readers know what you think
Index
Index

Creating, copying, and dropping tables

Teradata Database uses tables to store data in rows. Tables consist of rows and columns in a database, and can be seen in the following figure:

In Teradata, you can create the following types of tables:

  • Permanent Table: Remains in the database until dropped manually:
    • SET
    • MULTISET 
  • Temporary Table: Session based:
    • Derive table
    • Volatile table
    • Global temporary table
    • Queue table
    • Error logging table
    • NOPI table

In this recipe, we will create, drop, and copy data from one table to another.

Getting ready

You need to connect to Teradata Database using SQLA or Studio.

How to do it...

  1. Connect to Teradata Database using SQLA or Studio.
  2. Create the following permanent table in Database using the following code; it can be seen in the screenshot of SQLA:
/*Create set table*/
CREATE volatile TABLE td_cookbook.EMP_TBL
(EMP_ID INTEGER ,
EMP_Name VARCHAR(20),
Sal INTEGER)primary index (EMP_ID)
  1. Let's insert data into the table by using the following code. Also, check the snippet from SQLA...
Previous Section
End of Section 3
Next Section