Teradata DBA – USEFUL SQLs

Share via:

Teradata DBA – USEFUL SQLs

This article contains all the basic queries needed by a DBA to collect different kind of information from the database.

Table of Contents

1.Disk Space
Total disk space
Percent of disk space in use
Percent of disk space free
Percent of disk avail for spool
Disk space for a given database
Disk space for a given table
What is the table distribution per amp
Amount of MaxPerm, currentperm & free perm for a given database
Whose Running out of Perm Space
Who’s got lots of perm
Who’s using a lot of spool
Modify database perm
2.Access Rights
Rights Held by a User
Rights that could be considered for revocation
Role held by User
3.Logon-Logoff
Users logged in for certain interval
Users which were logged on for less than a minute
Logon Count for Certain Time Period
Failed logon attempts during the last seven days
4.PPI
PPI Tables
Single Level PPI
MultiLevel PPI
5.CPU
ResNode Macro
CPU and I/O for a Specific user
HOT AMP Identification
Skewed Query
CPU and I/O Consumed – Group Load
Active workload by UserName and associated AccountName
6.DBQL
Table Accessed
Top CPU Consuming Statements from DBQL
Query executed more than certain Time Period
Number of Queries by Users
Top Applications accessing Teradata Node

1.Disk Space

Total disk space

Percent of disk space in use

Percent of disk space free

Percent of disk avail for spool

Disk space for a given database

Disk space for a given table

What is the table distribution per AMP?

Amount of MaxPerm, currentperm & free perm for a given database

Whose Running out of Perm Space

Who’s got lots of perm

Who’s using a lot of spool

2.Access Rights

Rights Held by a User

Create this macro, execute the macro and pass the username as argument to this macro. It will retrieve all sorts of access rights held by this user, either through role or direct.

You can also use SQLs defined in the macro to obtain direct rights or rights through roles.

Rights that could be considered for revocation

The purpose of the view is to find explicitly granted rights that could be considered for revocation because the user has the same right via a role. I think it has been tested thoroughly but there is no guarantee, so be sure you archive your DBC.AccessRights table before revoking rights.

 

Role held by User

3.Logon-Logoff

Users logged in for certain interval

Please see the argument you need to give to execute the macro.

Users which were logged on for less than a minute

Logon Count for Certain Time Period

Following is a query to display how many times each user logged into your Teradata System in each time period. By default, the query returns information for the current date. For different dates, substitute date with the actual date in single quotes (i.e. ‘2001/07/31’ for July 31, 2001). You can also substitute date with expressions such as date-1 (yesterday), date-2 (two days ago), etc.

Failed logon attempts during the last seven days

4.PPI

PPI Tables

Single Level PPI

MultiLevel PPI

5.CPU

ResNode Macro

CPU and I/O for a Specific user

This SQL statement requests totals for CPU time and I/O for user DBA01. The totals are aggregates of all resources.

HOT AMP Identification

 Skewed Query

CPU and I/O Consumed – Group Load

GroupLoad – groups the active sessions by username and gives accumulation of CPU seconds and Disk IO.

 Active workload by UserName and associated AccountName

CheckLoad – shows the active workload by username and associated account name followed by a display of the current accumulation of CPU and Disk IO by Performance Group/Allocation Group.

6.DBQL

Table Accessed

Top CPU Consuming Statements from DBQL

 Query executed more then certain Time Period

Number of Queries by Users

Top Applications accessing Teradata Node

 

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTexperts
Linkedin Page : KT EXPERTS

 

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

Add Comment