DBQL related Queries in Teradata

Enabling/Disabling Query Logging for an Account String:

Getting Account String for a User:

Account string of a user can be verified by querying "dbc.accountinfo" view or better edit profile to check account string.

select * from dbc.accountinfo where username =’username’

Enabling DBQL for an Account String:

The DBQL can be enabled for a specific user using their account string by below command:

begin query logging with explain,objects, sql on all account=(' $M$BUSI$S$D$H')

In the above command query logging will be enabled for objects, sql text and explain on all user having account ‘$M$BUSI$S$D$H' . There are few more detailed options for query logging available namely 

• step logging, 
• xml explain, 
• summery logging.

The text size of sql text can be limited by limit clause as below. Here 0 means unlimited whereas any positive integer will limits to its value.

begin query logging with explain,sql limit sqltext=0 on all account=(' $M$BUSI$S$D$H')

Disabling DBQL for an Account String:

Query logging can be disabled with end query logging command as shown below.

end query logging with explain,objects, sql on all account=(' $M$BUSI$S$D$H')

Enabling/Disabling Query Logging for an Account String:

DBQL can be enabled/disabled for all users irrespective of their account string by below commands:

Begin query logging with objects, sql limit sqltext=0 on all;

End query logging with objects, sql limit sqltext=0 on all

Verifying DBQL Rules:

DBQL rules can be verified by querying "dbc.dbqlrules" view.

select * from dbc.dbqlrules

Deleting DBQL Logs:

Finally, these 2 queries are used for deleting DBQL Logs in order to create free space in the database.

Del  dbc.dbqlogtbl 
Del  dbc.DBQLSqlTbl  


  

Space Related Queries in Teradata

Calculate DiskSpace of a Database in GBs:

SELECT DATABASENAME, 
SUM(MAXPERM)/1024/1024/1024 Max_available_Space_GBs,
SUM(CURRENTPERM)/1024/1024/1024 Consumed_Space_GBs,
(SUM(CURRENTPERM))/(SUM(MAXPERM))*100 Percent_Utilised
FROM DBC.DISKSPACE
WHERE DATABASENAME IN('DB_NAME') 
GROUP  BY 1;

Calculate DiskSpace of a Database in MBs:

SELECT DatabaseName ,SUM(CurrentPerm)/1024/1024 AS USEDSPACE_IN_MB ,
SUM(MaxPerm)/1024/1024 AS MAXSPACE_IN_MB ,
SUM(CurrentPerm)/ NULLIFZERO (SUM(MaxPerm)) *100 (FORMAT 'zz9.99%') AS Percentage_Used  ,
MAXSPACE_IN_MB- USEDSPACE_IN_MB AS REMAININGSPACE_IN_MB 
FROM DBC.DiskSpace 
WHERE DatabaseName = 'DB_NAME'  
GROUP BY DatabaseName;

Calculate TableSize per Database per Table in MBs:

Sel databasename, tablename, sum(peakperm)/1024/1024 as peakperm, sum(currentperm)/1024/1024 as currentperm 
from dbc.tablesize
where databasename ='dbc'
group by 1,2

Calculate Tablesize per Database per Table per VPROC in MBs:

Sel databasename, tablename, vproc, sum(peakperm)/1024/1024 as peakperm, sum(currentperm)/1024/1024 as currentperm 
from dbc.tablesize
group by 1,2,3

Random Number Generation in Teradata

The syntax for random number generation is as following in Teradata:

SELECT Random("min value", "max value"); 
e.g.: SELECT Random(0, 10);

The "min value", "max value" are inclusive in the range.

If you want to update a column, try the following SQL.


update DB.TABLE_NAME set age=random(18,75);


There might be cases where Random function can produce duplicate values. SO while designing tables , make sure that the column making use of RANDOM is not part of UNIQUE PRIMARY INDEX






Selecting Datatypes of columns from DBC.Columns

Selecting DataTypes of Columns from DBC.COLUMNS

Select the data types of the columns in a tables in TERADATA, here is an SQL for that:

SELECT DatabaseName, TableName, ColumnName
,
(CASE
WHEN columntype='CV' THEN 'VARCHAR'
WHEN columntype='DA' THEN 'DATE'
WHEN columntype='I' THEN 'INTEGER'
WHEN columntype='CF' THEN 'CHAR'
WHEN columntype='F' THEN 'FLOAT'
WHEN columntype='I1' THEN 'BYTEINT'
WHEN columntype='I2' THEN 'SMALLINT'
WHEN columntype='I8' THEN 'BIGINT'
WHEN columntype='BO' THEN 'BLOB'
WHEN columntype='CO' THEN 'CLOB'
WHEN columntype='TS' THEN 'TIMESTAMP'
WHEN columntype='BF' THEN 'BYTE'
WHEN columntype='SZ' THEN 'TIMESTAMP W/ZONE'
WHEN columntype='BV' THEN 'VARBYTE'
WHEN columntype='DM' THEN 'INVERVAL DAY TO MINUTE'
WHEN columntype='D' THEN 'DECIMAL'
ELSE columntype END)||'('||trim(ColumnLength)||')' 
AS DataType
FROM DBC.COLUMNS
WHERE 
DatabaseName='DATABASE_NAME'
and 
TableName = 'TABLE_NAME'
order by 1,2,3


And the result looks like:

Random Number Generation In Teradata