Important DBC tables in Teradata - The data-dictionary of Teradata

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';

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+