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;