Teradata 16 New Features

Brief overview of some of the new features added in Teradata 16 version.
➠ Two new string functions were added

• PIVOT ~ Transpose Rows to Columns
• UNPIVOT ~ Transpose Columns to Rows

  Visit PIVOT page and UNPIVOT page to check syntax and examples for above functions.

Why is so much data being written into DBQL?

 

There are thousands of queries submitted per minute and it is too much data.

But, To avoid the performance impact on the database due to these logged queries,We need to be pro-active and choosy about the data we want to log.

So we save the space, resources,performance and etc etc.

Note : DBQL can only be improvised but can’t be ignored.Its a pillar for Admins as well,helps in disasters 

But,if space and other issues are concerened, below tips can help you some:

How to speed up Teradata Fastexport

About Teradata Fastexport

Teradata Fastexport is a nice, powerful tool to export mass volume of data to file. Its default execution flow is the following:

1.   Apply locks on the affected tables

2.   Execute the SELECT statement

3.   Places the result in a SPOOL

4.   Releases locks

5.   Exports data from the SPOOL

Hash Functions to identify skewed data

On day to day activity we often get situation when the database has a lot of space left but query fails with “2644: No more room in database“. If any table has severe skewing it can cause the database to run out of space.

Here we will learn how to identify data causing skewness using hash functions.

Following query identifies the database space:

Easy way to get Syntax for a command in Teradata

A MACRO can help you getting the Syntax help if you are new to Teradata or new to any syntax.

A MACRO is a Teradata extension to ANSI SQL that contains prewritten SQL statements.
The actual text of the macro is stored in a global repository called the Data Dictionary (DD).

A macro allows us to name a set of one or more statements. When we need to execute those statements, simply execute the named macro. Macros provide a convenient shortcut for executing groups of frequently-run SQL statements.

Below is complete list of commands to manipulate macros.

ABORTING STATE : ReCoVery Manager or RCV Manager/RMAN

The ABORTING state indicates that the session is aborting its latest request.

“Do you want to get rid of the aborting session in the system from long time, to clean up such sessions quickly?”

1.       Use RCVManager to list/abort the Rollback process   OR

2.       Use Gateway Global to abort user session   OR

3.       Use Function to abort the session

4.       Hopeless ?  Then do TPA reset.(Teradata Database Bounce)

Teradata – Secure Zones

Secure Zones

This feature supports grouping of users, databases, and database objects into Secure Zones. Business Value This feature provides the following business value: Improves total cost of ownership for systems where data from multiple applications must be completely segregated. Addresses cloud/multi-tenancy security requirements for privileged user access and data segregation. Addresses regulatory compliance requirements to separate administration duties from access control. Provides companies that are multi-national the ability to prevent data from crossing boundaries (geographical, national, divisional, etc.).

Read more blogs on Teradata & Netezza over LinkedIn Articles

https://www.linkedin.com/today/author/pchahar?trk=pprof-feedhttps://www.linkedin.com/today/author/pchahar?trk=pprof-feed
↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑↑

A --> Z of Teradata Query Performance Tuning

A) Explain the EXPLAIN: Check for EXPLAIN plan to see how exactly Teradata will be executing the query. Try to understand basic keywords in Explain Plan like confidence level, join strategy used, re-distribution happening or not.

B) Collect STATS: The stats of the columns used join conditions should updated. Secondary Indexes without proper STATS can be of little or no help. Check for STATS status of the table.

Turn off FallBackSetting in Teradata

Below are the steps to turn off FallBackSettings “ALWAYS FALLBACK” in Teradata Server on-Prem or cloud (AWS EC2 or Azure)

Login to Teradata NodeLogin to Teradata Node and run sudo su –

Run command dbscontrol from command prompt

In DBSControl window

Run “mod systemfe=T” (without double qoutes) and enter Y on prompt

TERADATA IMPORTANT DIAGNOSTIC COMMANDS !!!!

TERADATA IMPORTANT DIAGNOSTIC

DIAGNOSTIC HELPSTATS ON FOR SESSION;

DIAGNOSTIC COSTPRINT ON FOR SESSION;

DIAGNOSTIC OLDREWRITES ON FOR SESSION;

DIAGNOSTIC DUMP COSTS ON FOR SESSION;

DIAGNOSTIC HELP COSTS ON FOR SESSION;

Teradata Query Grid : Connection with different systems ( Database , NoSQL , Hadoop )

Teradata 15.0 has come up with many new exciting features and enhanced capabilities  . Teradata Query Grid is one of them.

Teradata database now able to connect Hadoop with this Query Grid so it’s called as Teradata Database-to-Hadoop also referred as  Teradata-to-Hadoop connector.

Key Importance of Teradata Query Grid is to Put Data in the Data Lake FAST across foreign servers.

What is Query Grid?

Query Grid works to connect a Teradata and Hadoop system to massive scale, with no effort, and at speeds of 10TB/second.

·         It provides a SQL interface for transferring data between Teradata Database and remote Hadoop hosts.

Teradata NPARC (Named Pipe ARChive)

Brief Introduction to Teradata:

Teradata is a fully scalable relational database management system produced by Teradata Corp. It is widely used to manage large data warehousing operations. 

The Teradata database system is based on off-the-shelf symmetric multiprocessing technology combined with communication networking, connecting symmetric multiprocessing systems to form large parallel processing systems.

What happens when business data grows?

Sometimes, working in IT we need to upgrade entire configurations of our systems or servers to get in sync with business requirements. As a growing IT, data is also growing and for that we may need to get rid of old configuration systems or servers and for that we need to migrate the existing business data.