Showing posts with label Teradata. Show all posts
Showing posts with label Teradata. Show all posts

Wednesday, May 7, 2014

Getting a List of Tables accessed by a User in Teradata

Some times for reporting and management purposes one may need to see a list of tables and views (Database Objects) viewed by a particular user. This can be achieved by making an inner join between dbc.dbqlObjTbl and dbc.qryLog table.
The following query gets a list of Views in database='test' accessed by a userName='shahf'.

SELECT  DISTINCT a.objectDatabaseName,a.objectTableName , b.userName ,  b.queryText, b.logDate ,  b.startTime  
FROM   dbc.dbqlobjtbl  a 
INNER JOIN dbc.qrylog  b 
ON        a.queryid = b.queryid 
AND       a.procid = b.procid 
AND       a.objectDatabaseName = 'test' 
AND       a.objectType = 'Viw'
AND  b.userName ='shahf'


Modifying the objectType appropriately in the above query can bring a list of Objects like database, data table, column, secondary index, join index, or journal table a user has accessed. If the object is a secondary index, its number is returned rather than a name.

Note: Any DBC database tables and columns used by the system while processing a query are not reflected in the DBQL object rows for that query. This means, for example, that statements like CREATE TABLE or SELECT FROM DBC.xxx will not have objects logged through DBQL because they deal with DBC tables and columns. So DBC tables accessed by a user cannot be found with the above query.

For further information on the dbc.dbqlobjtbl works refer to the link given below.
http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Database_Management/B035_1093_111A/ch14.033.22.html 

Sunday, March 30, 2014

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