DBC Tables or System tables on Teradata


System tables uses to track the database objects. In essence, access is a set of relational database tables (the system tables) about a set of relational database tables (the user project). System tables should not be altered directly by any user. For example, do not attempt to modify system tables with DELETE, UPDATE, or INSERT statements, or user-defined triggers.

System tables are much like the tables you create in a database – with a few exceptions. First, they are identified internally as system objects so that you can differentiate your regular tables from system tables. Secondly, some system tables are inherently read-only. System table are documented here solely for the purpose of giving us a better understanding of how Teradata RDBMS works internally. The following table describes each of the system tables.

DBC.TVM
Describes each table, view, or macro on the Teradata RDBMS. Identify table.


DBC.TVFIELDS
Describes each column on the Teradata RDBMS.


DBC.User
Identify user.

DBC.ACCESSRIGHTS
Describes each database and userid on the Teradata RDBMS.




DBC.DBASE
Describes each database and userid on the Teradata RDBMS. Identify database.


DBC.INDEXES
Describes columns contained in indexes in the Teradata RDBMS.


DBC.ResUsage
Log resource usage data.

Example

SELECT ACCOUNTNAME, USERNAME, SUM(CPUTIME), SUM(DISKIO) FROM
DBC.AMPUSAGE
WHERE SUBSTR(ACCOUNTNAME, 1, 2) = ‘CB’
GROUP BY USERNAME, ACCOUNTNAME
ORDER BY USERNAME, ACCOUNTNAME;
 *** Query completed. 9 rows found. 4 columns returned.
 *** Total elapsed time was 2 seconds.
AccountName     UserName             Sum(CpuTime)      Sum(DiskIO)
————–  ————-  ——————  —————
CB990902        JANETJONES               1,498.64        3,444,236
CB990903        JANETJONES                 934.23        1,588,764
CB990904        JANETJONES                 883.74          924,262
CB990905        JANETJONES                 214.99          200,657
CB990902        JOHNSMITH                  440.05          396,338
CB990903        JOHNSMITH                  380.12          229,730
CB990904        JOHNSMITH                  112.17          184,922
CB990905        JOHNSMITH                   56.88           99,677
CB990902        SAMOREILLY                 340.34          410,178

Leave a Reply

Your email address will not be published. Required fields are marked *