Chapter 21 – Teradata Tools and Everything Active
Teradata Analyst Pack
Advice should be viewed from behind and the Teradata Analyst Pack will give you great advice about your Teradata system. The Teradata Analyst Pack consists of four Teradata products:
1) Teradata Visual Explain
2) Index Wizard
3) Statistics Wizard
4) Teradata System Emulation Tool (TSET)
Remember, Only Index Wizard provides feedback at the Workload level.
Teradata Visual Explain Utility
Visual Explain retrieves three things that make it effective:
1) Database Object Definitions from the Data Dictionary
2) Data Demographics from the Collect Statistics
3) Cost and Cardinality estimates from the Optimizer
TSET
TSET stands for Teradata System Emulation Tool (Teradata SET). TSET is designed to help create a production like environment on a test system. Production systems are generally a great deal larger than test systems. TSET captures the source environment from the production system and literally emulates it on a test system.
Index Wizard
Index Wizard analyzes a workload of SQL and then creates a series of reports and index recommendations describing the costs and statistics associated with the recommendations. The reports are designed to help you backup your decision to apply an index. Here are the steps to using the Index Wizard in exact order:
•Define a workload
•The workload is analyzed
•The Wizard recommends Secondary Indexes
•Reports are generated
•Indexes are validated
•Indexes can be applied
You can define a workload:
•Using DBQL Statements
•Using QCD
•Entering SQL Text
•Importing a Workload
•Creating a new workload from an existing one
Teradata Index Wizard uses the Capture Query Database to store needed SQL for analysis. The Query Capture Database (QCD) is actually input by:
•Visual Explain
•Index Wizard
•Statistics Wizard
Statistics Wizard
Statistics wizard is designed to help with Collecting Statistics. The wizard is a graphical tool designed to improve query performance by recommending statistics collection. It automates the process of collecting statistics for certain workloads or can select indexes or columns for collections or recollection purposes.
The Statistics Wizard enables the DBA to:
•Specify a workload for analysis and then recommend improvements
•Select a database or group of tables, indexes or columns for analysis
•Makes recommendations for workloads based on table demographics
•Schedule statistics collection
•Import workloads from other utilities
Statistics Wizard can even recommend recollection for a table if it is skewed with uneven data across the AMPs. The Statistics Wizard analyzes SQL statements given to the system and the Wizard defines a workload directly from:
•DBQL Statements
•Statement Text
•QCD Statements
•Manually
•From another Workload
Teradata Statistics Wizard uses the Capture Query Database to store needed SQL for analysis. The Query Capture Database (QCD) is actually input to:
•Visual Explain
•Index Wizard
•Statistics Wizard
Active Access Queries
Active Availability
The Active Workload Management Concept
ASM allows the DBA to visualize the current operational environment and compare that to long-term trending analysis. This allows the DBA to set Service Level Goals (SLGs ). Then the DBA can monitor to see if the SLGs are met, or they can take the necessary steps to better ensure they are met.
Active Events
Active Events notify the DBA of something important, Teradata presents the DBA with recommendations for the actions they should take. Business events are applied against business rules as current and historical data are analyzed in order for the DBA to take operational actions to correct the problem.
The Active Load Concept
Active Load is delivered through methods that support continuous data loading. This was the original idea behind TPump. Now included are other streaming methods such as Queue tables, more frequent batch updates, and moving data from other platforms like SQL Server or Oracle directly to Teradata.
Active Enterprise Integration