The
following tables provide a great deal of information about your Teradata
system. They store the demographics of table, views, macros, column-definition,
error-codes and numerous other information.
All we have
to understand is how to use these tables to help ourselves and enable our
Organization to "Work Smarter"
Examples:
1. SELECT *
FROM DBC.TABLES WHERE TABLENAME LIKE '%Customer%'
----this is
typically used in impact analysis phase, to see how many tables belong to a
particular subject area. (Customer in the above example)
2. SELECT *
FROM DBC.INDICES
----this
table stores information about the indices, which table they are stored, etc.
3. SELECT *
FROM DBC.COLUMNS
---this
table stores the column information, not null/nullable, datatype,
unique/non-unique. Greatly helps in Physical Data Modelling activities
4. SELECT *
FROM DBC.DBQLOGTBL
---- The
DBC.DBQLogTbl stands for database query log. As the name explains, it contains
all the core performance data for the queries that run on the system. Before you start querying this table ensure that Query logging is enabled for the User.
This can be accomplished for training_1 user as follows:
---- enable query logging so that all the queries run by a particular user is logged
begin query logging with sql on training_1 ;
select * from dbc.users;
select * from dbc.dbase;
select db1.databasename,dblog1.*from dbc.dbqlogtbl as dblog1
inner join
dbc.dbase as db1
on dblog1.userid = db1.databaseid
where db1.databasename = 'training_1';
This can be accomplished for training_1 user as follows:
---- enable query logging so that all the queries run by a particular user is logged
begin query logging with sql on training_1 ;
select * from dbc.users;
select * from dbc.dbase;
select db1.databasename,dblog1.*from dbc.dbqlogtbl as dblog1
inner join
dbc.dbase as db1
on dblog1.userid = db1.databaseid
where db1.databasename = 'training_1';
5. SELECT *
FROM DBC.ERRORMSGS WHERE ERRORCODE =3523
---this
table gives an understandable description of the Teradata errors. Great for any
Developer.
6.
select * from dbc.dbqlogtbl where
querytext like '%corp_ksn%' order by CollectTimeStamp desc
---- this
query will return the logon information about the user along with the IP
address and username. Any modification to the table can be easily tracked using
this query.
7. Select
count(distinct nodeid) from dbc.resusagescpu;
--- this
query will let you find the total no. of Teradata nodes in the system
8. Select
nodeid,count(distinct Vproc) from dbc.ResCpuUsageByAmpView group by 1;
---- this
query will allow to find the no. of Vproc in the system
Please Donate using the Donate button. Your support is much appreciated !!
Like us on Facebook or Google+
