Chapter 15 – A DBA’s best friend - The Data Dictionary

“My best friend is the one who brings out the best in me!”

- Henry Ford

The Data Dictionary Resides in User DBC

The Data Dictionary/Directory is used by the Parsing Engine a lot, but you can see a lot yourself. Just enter the SQL above, and get ready to see what information is captured.

The DBC.DBCInfoV View

Use this view to find out what version of Teradata you are on. This is a V13 system.

Querying the Data Dictionary

In real life, we have Grandparents and Parents . In Teradata, we don’t have Grandparents, but instead we call everyone above us Parents.

This view shows hierarchical information. Anyone above you in the Teradata Hierarchy is your Parent! DBC created SQL00, and then it was SQL00 that created SQL01. Both DBC and SQL00 are parents of SQL01.

Using the Keyword USER

The keyword USER is used because Teradata knows your logon. Since we logged in as USER SQL00, the query will bring back all the children of SQL00.

Restricted Views have an X at the End of their Name

The Data Dictionary has restricted Views that always have an X at the end of their name. This denotes the View is restricted, so the user can only see their information.

The V is New with Teradata V12

With V12, they introduced a V at the end of some Data Dictionary Views. These are the newer views. The only difference is that the previous views always displayed in Char(30). The newer views (with a V) have a data type of VARCHAR (128), which can display information associated with UNICODE.

The V and the Restricted X are Now Often Combined

With V12, they introduced a V at the end of some Data Dictionary Views. These are the newer views. So, a newer view that is restricted to the user’s information has VX at the end. The only difference is that the previous views always displayed in Char (30). The newer views (with a V) have a data type of VARCHAR(128), which can display information associated with UNICODE.

A Recap of What We Have Learned So Far

Newer V Views introduced in V12 and can display information using UNICODE.

The DBC.DatabasesV View

This View shows Database, user, and immediate parent information plus some great information about Space among other things.

The DBC. Users View

This view is quite similar to the Databases view but includes columns specific to users.

The DBC.AMPUsage View

The query just above shows the total number of AMPs and only uses a couple of rows to do so

This view is often used to charge back based on system CPU and Disk/IO usage.

Clearing Out the DBC.AMPUsage Data

Use the Exec statement above (in red) and the DBC.AMPUsage data is reset.