Teradata DBA Assistant

Upgrade your skills with our Video Modules and become a proficient Teradata DBA ready to excel in any organization.

Are you starting your career as a Teradata DBA? This is the perfect place for you. Take a moment to learn about this site before exploring further!

This website provides comprehensive video tutorials on Teradata for aspiring Junior DBAs. Navigate through the top menu to access each page. Additionally, find a range of queries at the end of this page to assist Teradata DBAs in their daily tasks, covering a variety of essential topics. Just click and grab your query!

What is Teradata

Teradata is a Database Management System similar to Oracle, SQL Server, MySQL, Postgres, MongoDB, DB2, and others. Its primary purpose is to store and manage data. Teradata is commonly used in Data Warehousing for analytical purposes. Data is extracted from various source systems, transformed, and loaded into a central repository like Teradata, a process known as ETL (Extract, Transform, Load). Analytical and Business Intelligence tools then connect to this repository to generate decision-supporting reports.

Roles & Responsibilities of Teradata DBA

A Teradata Database Administrator (DBA) is crucial for overseeing and upkeeping the Teradata database system. The duties of a Teradata DBA may differ based on the particular needs of the organization and the scale and intricacy of the Teradata setup. Nevertheless, typical tasks and responsibilities of a Teradata DBA involve:

Configuration & Installation of Databases

The Database Administrator (DBA) plays a crucial role in installing, configuring, and upgrading the Teradata database software in different environments like (PROD, DR and UAT) within an organization. If there is a support contract intact with Teradata (Teradata supports on this)

Designing & Modeling Databases

They collaborate with database designers and developers to create efficient database schemas and models.

Database Performance & Tuning
Database Administrator (DBA) is specialized in monitoring database performance and identifying areas for optimization and improvement. These expertise includes tuning SQL queries, optimizing database configurations, and implementing best practices for indexing and partitioning.

Backup & Recovery

As the DBA, your role involves the implementation and upkeep of security measures to safeguard data confidentiality, integrity, and availability in the Teradata environment. This encompasses tasks such as user authentication, authorization, encryption, and auditing.

Security Management

Teradata DBA is responsible for implementing and maintaining security policies, procedures, and controls to protect the confidentiality, integrity, and availability of data within the Teradata environment. This includes user authentication, authorization, encryption, and auditing.

Resource Management & Capacity Planning

The DBA oversees resource usage and capacity trends to guarantee the Teradata environment can support current and future workloads. This includes allocating system resources, overseeing storage capacity, and adjusting the infrastructure to align with evolving business needs.

Patch Management and System Maintenance

The DBA applies software patches, updates, and maintenance releases to the Teradata software and underlying operating system to address security vulnerabilities, bug fixes, and performance improvements. If Teradata Support Contract is intact then it is supported by Teradata.

Configuring and Monitoring Alerts

A proactive approach to system monitoring by the DBA involves overseeing system health, performance metrics, and alerts to detect and resolve potential issues before they impact system availability or performance. This includes setting up monitoring tools, configuring alerts, and promptly addressing incidents.

Stakeholders Training & Coaching

The Database Administrator (DBA) is responsible for maintaining detailed documentation of database configurations, procedures, and best practices. Additionally, they offer training and support to database users, developers, and other stakeholders on database-related topics and tools.

A Teradata DBA plays a crucial role in maintaining the efficiency, performance, and security of the Teradata database environment, while also supporting the organization's data management and analytics goals.

SELECT SUM(MAXPERM), SUM(CURRENTPERM), SUM(MAXPERM)-SUM(CURRENTPERM)
FROM DBC.DISKSPACEV;

What is CurrentPerm, MaxPerm, PeakPerm in Teradata

When working with disk space utilization, CurrentPerm indicates the amount of space currently in use, while MaxPerm represents the maximum space allocated. It is recommended not to allocate space to individual users. PeakPerm reflects the maximum space that was ever utilized, even if objects are deleted and space is reclaimed. Space is always shown in bytes.

SELECT
DATABASENAME,
SUM(MAXPERM), SUM(CURRENTPERM), SUM(MAXPERM)-SUM(CURRENTPERM)
FROM DBC.DISKSPACEV
GROUP BY DATABASENAME;

Calculate CurrentPerm, MaxPerm and Remaining Perm space in Teradata

Calculate CurrentPerm, MaxPerm and Remaining Perm Database wise space in Teradata

Calculate space in Teradata Table wise and Vproc wise

SELECT
DatabaseName, TableName , Vproc , CurrentPerm , PeakPerm
FROM DBC.TableSizeV
ORDER BY Vproc ;

CREATE DATABASE SPACEMOVE FROM PARENT_DB AS PERM=2e+09;
GIVE SPACEMOVE TO SPACETOBEADDED;
DROP DATABASE SPACEMOVE;

Teradata database administrators often handle space management as part of their daily support tasks. In the event of a database failure caused by insufficient space, it is essential to assess the space allocation in each database through a relevant query. Following this, space can be transferred from a database with surplus space to the one facing space constraints. Users must possess the necessary privileges to create and delete databases. Typically, these operations are carried out using administrative accounts. The PARENT_DB will serve as the database with more available space, while a temporary database named SPACEMOVE will be created. Subsequently, the database experiencing space shortage, known as SPACETOBEADDED, will receive the reallocated space. Once the operation is complete, the SPACEMOVE database will be dropped. In Teradata, when a child object is removed, all of its space is returned to the parent database or user. 2e+09 represents 2 GB.

Adding Space from one database to another database in Teradata

SELECT TOP 10 USERNAME , STARTTIME AS START_TIME,SUM(AMPCPUTIME) AS CPU_COUNT ,
SUM(TOTALIOCOUNT) AS IO_COUNT, SUBSTRING( QUERYTEXT, 0, 70) AS QUERYTEXT
FROM DBC.DBQLOGTBL
WHERE
Cast(collecttimestamp as date) BETWEEN '2013-08-01' AND '2013-08-31' GROUP BY 1,2,5
ORDER BY 3 DESC ;

Identifying the most expensive queries in Teradata DBA is crucial for optimizing CPU usage and system performance. By analyzing query performance, DBAs can separate workloads and address high-impact queries effectively. While managing these queries falls outside the scope of this platform, the provided query can help pinpoint costly queries for further investigation.

Identification of Most Expensive Queries, Queries Consuming more CPU in Teradata

SELECT TOP 50 USERNAME , STARTTIME , AMPCPUTIME , TOTALIOCOUNT ,
((FIRSTRESPTIME - STARTTIME) HOUR(4) TO SECOND) (NAMED ELAPSEDTIME),
SUBSTRING( QUERYTEXT, 0, 70) AS QUERYTEXT
FROM DBC.DBQLOGTBLWHERE USERNAME = 'REPLACEWITHUSERNAMEYOUWISHTOCHECK'
AND Cast(collecttimestamp as date) BETWEEN '2013-08-01' AND '2013-08-31' ORDER BY ELAPSEDTIME DESC ;

Query Elapsed Time in Teradata is crucial for measuring the actual response time of a query. It is calculated from the moment the first response is initiated, taking into consideration any delays caused by workload ruleset or network issues. This calculation is essential for understanding the true execution time of a query.

Identification of Most Expensive Queries, Queries Consuming more execution time in Teradata

SELECT TOP 30 USERNAME , STARTTIME , AMPCPUTIME , TOTALIOCOUNT ,
CASE WHEN (AMPCPUTIME / (HASHAMP()+1)) =0 THEN 0
ELSE MAXAMPCPUTIME/(AMPCPUTIME/ (HASHAMP()+1)) END AS CPUSKEW,
SUBSTRING( QUERYTEXT, 0, 70) AS QUERYTEXTFROM DBC.DBQLOGTBL
WHERE LOGDATE BETWEEN '2013-08-01' AND '2013-08-31' AND USERNAME = 'USERNAMEYOUARESKEPTICAL'
AND AMPCPUTIME > 1000 AND STATEMENTTYPE NOT LIKE '%COLLECT%' ORDER BY CPUSKEW DESC ;

Optimize Teradata performance by ensuring even data distribution on all AMPs through proper primary index definition. Skewed CPU queries can be identified and resolved by checking tables involved in the queries.

Identify CPU Skewed Queries

SELECT
NodeID, cast(TheTime as INTEGER)/10000as TheHour,
count(distinct CpuId) asCpusPerNode,
sum(CPUIdle+CPUIoWait+CPUUSerV+CPUUExec)/100 as CpuSecsPerHour
FROM
dbc.ResScpuView WHERE TheDate =current_date -1
GROUP BY NodeID, TheHour ORDER BY TheHour,NodeID;

As a Teradata DBA, monitoring CPU consumption is crucial for system performance. To start, it's important to determine the total available CPU on the system. Running a specific query can provide insights into the total CPU available per hour on each node. Then sum up of all nodes CPU to know the exact CPU available on the system.

Calculate Available CPU per Hour in Teradata

CREATE MULTISET TABLE EMPLOYEE,FALLBACK
( EmployeeNo INTEGER, FirstName VARCHAR(30) , LastName VARCHAR(30) ,
DOB DATE FORMAT 'YYYY-MM-DD',JoinedDate DATE FORMAT 'YYYY-MM-DD', DepartmentNo BYTEINT )
UNIQUE PRIMARY INDEX ( EmployeeNo );

CREATE MULTISET TABLE SALARY,FALLBACK
( EmployeeNo INTEGER, Gross INTEGER, Deduction INTEGER, NetPay INTEGER )
PRIMARY INDEX ( EmployeeNo )
UNIQUE INDEX (EmployeeNo);

Understanding Teradata Join Indexes plays a crucial role in improving performance and optimizing queries. A Single Table Join Index enables partitioning a large table using primary index columns different from the base table. To gain a thorough understanding of Teradata Join Indexes, practice the provided queries step by step. Begin by creating two tables and then proceed to create various indexes on them. This hands-on approach will help you grasp the process of creating Indexes effectively.

Teradata Join Indexes - Single Table Join Index (STJI)

CREATE JOIN INDEX Employee_JI
AS SELECT EmployeeNo,FirstName,LastName, BirthDate,JoinedDate,DepartmentNo
FROM
Employee PRIMARY INDEX(FirstName);

Learn how to create a Join index named Employee_JI on Employee table. When a query is submitted with a WHERE clause on EmployeeNo, the system will query the Employee table using the unique primary index. If the query is based on employee_name, the system may access the join index Employee_JI using employee_name. The rows of the join index are hashed on the employee_name column. Without the join index or employee_name defined as a secondary index, the system will resort to a time-consuming full table scan to access the rows.

CREATE JOIN INDEX Employee_Salary_JI
AS
SELECT a.EmployeeNo,a.FirstName,a.LastName, a.BirthDate,a.JoinedDate,a.DepartmentNo,b.Gross,b.Deduction,b.NetPay
FROM
Employee a INNER JOIN Salary b ON(a.EmployeeNo = b.EmployeeNo)
PRIMARY INDEX(FirstName);

A multi-table join index is a database feature that involves joining multiple tables. This type of index is beneficial for storing the results of commonly joined tables, enhancing performance. Whenever the base tables Employee or Salary are updated, then the Join index Employee_Salary_JI is also automatically updated. If you are running a query joining Employee and Salary tables, then the optimizer may choose to access the data from Employee_Salary_JI directly instead of joining the tables.
If you have not created above two tables then do create those two tables first to practice this.

Teradata Join Indexes - Multi Table Join Index (MTJI)

CREATE JOIN INDEX Employee_Salary_JI_AG
AS
SELECT a.DepartmentNo,SUM(b.NetPay) AS TotalPay
FROM
Employee a INNER JOIN Salary b ON(a.EmployeeNo = b.EmployeeNo)GROUP BY a.DepartmentNo Primary Index(DepartmentNo);

When a table is consistently aggregated on specific columns, creating an aggregate join index on the table can enhance performance. However, it's important to note that aggregate join index only supports SUM and COUNT functions. In the given example, the total salary per Department is determined by joining Employee and Salary tables.

Teradata Join Indexes - Aggregate Join Index (AJI)

SELECT SYSLIB.AbortSessions(1,'username',0,'Y','Y');

To abort all sessions for a particular user in Teradata, the user must have abort session access rights. This action is typically performed by an Admin who has all the necessary access rights. Just replace user name with the user wish to Abort Sessions for

Aborting Sessions

DATABASE TEST_DB;
CREATE VOLATILE TABLE OBJ_DEPENDENCY AS
(SELECT CAST(TA.DATABASENAME AS VARCHAR(30)) AS SOURCE_DB,
CAST(TA.TABLENAME AS VARCHAR(30)) AS SOURCE_OBJ,
TA.TABLEKIND AS SOURCE_OBJ_KIND, D.DATABASENAME AS TARGET_DB,
T.TVMNAME AS TARGET_OBJ, T.TABLEKIND AS TARGET_OBJ_KIND FROM DBC.TVM T, DBC.DBASE D , DBC.TABLES TA
WHERE D.DATABASEID = T.DATABASEID AND T.CREATETEXT LIKE '%"' || TRIM (TA.DATABASENAME) || '"."'|| TRIM (TA.TABLENAME)|| '"%' (NOT CS)
UNION
SELECT TA.DATABASENAME AS SOURCE_DB, TA.TABLENAME AS SOURCE_OBJ, TA.TABLEKIND AS SOURCE_OBJ_KIND, D.DATABASENAME AS TARGET_DB, T.TVMNAME AS TARGET_OBJ, T.TABLEKIND AS TARGET_OBJ_KINDFROM DBC.TEXTTBL X, DBC.DBASE D, DBC.TVM T, DBC.TABLES TA
WHERE X.TEXTTYPE='C' AND X.TEXTSTRING LIKE '%"' || TRIM (TA.DATABASENAME) || '"."'|| TRIM (TA.TABLENAME)|| '"%' (NOT CS) AND X.DATABASEID=D.DATABASEID AND X.TEXTID=T.TVMID MINUSSELECT TA.DATABASENAME AS SOURCE_DB,
TA.TABLENAME AS SOURCE_OBJ, TA.TABLEKIND AS SOURCE_OBJ_KIND, D.DATABASENAME AS TARGET_DB,
T.TVMNAME AS TARGET_OBJ, T.TABLEKIND AS TARGET_OBJ_KIND FROM DBC.TVM T, DBC.DBASE D, DBC.TABLES TA WHERE D.DATABASEID=T.DATABASEID AND D.DATABASENAME= TA.DATABASENAME AND T.TVMNAME= TA.TABLENAME) WITH DATAPRIMARY INDEX (SOURCE_DB,SOURCE_OBJ)ON COMMIT PRESERVE ROWS;
WITH RECURSIVE DEPENDENT( SOURCE_DB, SOURCE_OBJ, SOURCE_OBJ_KIND, DEPENDENT_DB, DEPENDENT_OBJ, DEPENDENT_OBJ_KIND, DEPENDENCY_LEVEL ) AS(SELECT SOURCE_DB, SOURCE_OBJ, SOURCE_OBJ_KIND,
TARGET_DB AS DEPENDENT_DB, TARGET_OBJ AS DEPENDENT_OBJ, TARGET_OBJ_KIND AS DEPENDENT_OBJ_KIND,
CAST(1 AS SMALLINT) AS DEPENDENCY_LEVEL FROM OBJ_DEPENDENCY UNION ALLSELECT D.SOURCE_DB, D.SOURCE_OBJ, D.SOURCE_OBJ_KIND, O.TARGET_DB AS DEPENDENT_DB, O.TARGET_OBJ AS DEPENDENT_OBJ, O.TARGET_OBJ_KIND AS DEPENDENT_OBJ_KIND, D.DEPENDENCY_LEVEL + 1 AS DEPENDENCY_LEVELFROM OBJ_DEPENDENCY O JOIN DEPENDENT D ON O.SOURCE_DB = D.DEPENDENT_DB AND O.SOURCE_OBJ = D.DEPENDENT_OBJ AND D.DEPENDENCY_LEVEL <= 100)SELECT * FROM DEPENDENTORDER BY SOURCE_DB, SOURCE_OBJ, SOURCE_OBJ_KIND, DEPENDENCY_LEVEL;

Teradata DBA faces challenges in identifying all dependent objects of views. For instance, a view may extract data from various other views and tables, and the source view may further depend on additional views or tables. This repetitive querying process can be cumbersome. Exporting the query results to Microsoft Excel and filtering the DEPENDENT_OBJ column can help identify all dependent objects.

Finding Recursive Dependent Objects in Teradata