Chapter 4 – A DBA's best friend - The Data Dictionary
“My best friend is the one who brings out the best in me!”
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
| CHILD | PARENT |
| SQL01 SQL02 SQL03 SQL04 SQL05 SQL06 SQL07 SQL08 SQL09 SQL10 |
SQL00 SQL00 SQL00 SQL00 SQL00 SQL00 SQL00 SQL00 SQL00 SQL00 |
We logged on as USER SQL00.
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
| CHILD | PARENT |
| SQL01 SQL02 SQL03 SQL04 SQL05 SQL06 SQL07 SQL08 SQL09 SQL10 |
SQL00 SQL00 SQL00 SQL00 SQL00 SQL00 SQL00 SQL00 SQL00 SQL00 |
We logged on as USER SQL00.
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
We logged in as SQL00
Newer V Views introduced in V12 and can display information using UNICODE.
The DBC.DatabasesV View
| SELECT DatabaseName | (CHAR(10)) AS “Name” |
| ,CreatorName | (CHAR(10)) AS “Creator” |
| ,CreateTimeStamp | |
| ,PermSpace | (FORMAT 'zzz,zz9,999') |
| ,SpoolSpace | (FORMAT 'zzz,zz9,999') |
| ,TempSpace | (FORMAT 'zzz,zz9,999') |
| ,DBKind | |
| FROM DBC.DatabasesV | |
| WHERE DatabaseName like 'SQL%' | |
| ORDER BY 1; | |
This View shows Database, user, and immediate parent information plus some great information about Space among other things.
The DBC.Users View
SELECT UserName
,CreatorName
,PermSpace
,SpoolSpace
,TempSpace
,LastAlterTimeStamp
FROM DBC.Users
WHERE UserName = USER ;
UserName ,CreatorName ,PasswordLastModDate ,PasswordLastModTime ,OwnerName ,PermSpace ,SpoolSpace ,TempSpace ,ProtectionType ,JournalFlag ,StartupString ,DefaultAccount ,DefaultDataBase ,CommentString ,DefaultCollation ,PasswordChgDate ,LockedDate ,LockedTime ,LockedCount ,TimeZoneHour ,TimeZoneMinute ,DefaultDateForm ,CreateTimeStamp ,LastAlterName ,LastAlterTimeStamp ,DefaultCharType ,RoleName ,ProfileName ,AccessCount ,LastAccessTimeStamp
Columns in this view
This view is quite similar to the Databases view but includes columns specific to users.
The DBC.Tables View
SELECT TRIM(DatabaseName) || '.' || TableName AS “Fully Qualified Name” ,TableKind
FROM DBC.TablesV
WHERE Databasename IN ( 'SQL_Class', 'SQL_Views')
AND TableName like 'Employe%';
| Fully Qualified Name | TableKind |
| SQL_Class.Employee_Table SQL_Class.Employee_Macro SQL_Class.Employee_View |
T M V |
DataBaseName ,TableName ,Version , TableKind , ProtectionType , JournalFlag , CreatorName , RequestText , CommentString , ParentCount , ChildCount , NamedTblCheckCount , UnnamedTblCheckExist , PrimaryKeyIndexId , RepStatus , CreateTimeStamp , LastAlterName , LastAlterTimeStamp , RequestTxtOverflow , AccessCount , LastAccessTimeStamp , UtilVersion , QueueFlag , CommitOpt , TransLog , CheckOpt
Columns in this view
This view shows tables, views, macros, triggers, and stored procedures information.
Using DBC.Tables to find out about Fallback
SELECT Count(*)
FROM DBC.TablesV
WHERE DatabaseName = 'SQL_Class'
AND TableKind IN('T', 'O')
AND ProtectionType = 'F';
Show the tables in the SQL_Class Database that are Fallback Protected
SELECT Count(*)
FROM DBC.TablesV
WHERE DatabaseName = 'SQL_Class'
AND TableKind IN('T', 'O')
AND ProtectionType = 'N';
Show the tables in the SQL_Class Database that are NOT Fallback Protected
These views will show you the tables that are or are not Fallback protected.
The DBC.Indices View
SELECT TableName ,ColumnName (CHAR(15)) AS “Column Name”
,UniqueFlag AS “Unique” ,IndexType AS “Type”
,IndexName AS “Name” ,IndexNumber AS “IndNo”
,ColumnPosition AS “ColPos”
FROM DBC.IndicesV
WHERE TableName = 'Employee_Table'
AND DatabaseName = DATABASE
ORDER BY IndNo, ColPos;
DatabaseName , TableName , IndexNumber , IndexType , UniqueFlag , IndexName , ColumnName , ColumnPosition , CreatorName , CreateTimeStamp , LastAlterName , LastAlterTimeStamp , IndexMode , AccessCount, LastAccessTimeStamp
Columns in this view
Notice that Last_Name and First_Name is a multi-column Unique Secondary Index.
The DBC.Columns View
SELECT TableName, ColumnName, ColumnFormat
, ColumnType, ColumnLength as “Length”
FROM DBC.ColumnsV
WHERE DatabaseName = 'SQL_Class' AND TableName = 'Department_Table';
DatabaseName , TableName , ColumnName , ColumnFormat , ColumnTitle , SPParameterType , ColumnType , ColumnUDTName , ColumnLength , DefaultValue , Nullable , CommentString , DecimalTotalDigits , DecimalFractionalDigits , ColumnId , UpperCaseFlag , Compressible , CompressValue , ColumnConstraint , ConstraintCount , CreatorName , CreateTimeStamp , LastAlterName , LastAlterTimeStamp , CharType, IdColType , AccessCount , LastAccessTimeStamp , CompressValueList
Columns in this view
This view shows columns and their parameters in tables, views, and macros.
Clever Queries for the DBC.ColumnsV View
SELECT '# of Columns// with Default count:'
,COUNT (*)
FROM DBC.ColumnsV
WHERE DefaultValue IS NOT NULL;
Show the number of columns in the entire system with Default values defined
SELECT TableName
,ColumnName
,CreateTimeStamp
,LastAlterTimeStamp
FROM DBC.ColumnsV
WHERE Databasename = 'SQL_Class'
ORDER BY 1;
Show the tables and the columns in the SQL_Class database and when they were created and then last altered.
Here are some clever queries to help you with the DBC.ColumnsV View.
New V14 - The DBC.PartitioningConstraintsV View
SELECT TableName
,ColumnPartitioningLevel AS “Level”
,PartitioningLevels AS “#Levels”
,ConstraintText AS “ConText”
FROM DBC.PartitioningConstraintsV
WHERE DatabaseName = 'SQL_Class';
Show the number of tables in the SQL_Class database that are partitioned
SELECT COUNT(*)
FROM DBC.PartitioningConstraintsV
WHERE ColumnPartitioningLevel = 0;
Show the number of tables in the entire system that are partitioned
SELECT COUNT(*)
FROM DBC.PartitioningConstraintsV
WHERE ColumnPartitioningLevel <> 0;
Show the number of tables in the entire system that have column partitioning
This new view can really give you a great description of the PPI tables and columnar.
The DBC.AccountInfo View
SELECT *
FROM DBC.AccountInfoV
ORDER BY 1;
| UserName | AccountName | UserOrProfile |
| CUBETEST MrktProfile daphPROFILE1 DAPH_USER1 DBC |
DBC $MAccount $MSQL30 DBC DBC |
User Profile Profile User User |
UserName , AccountName , UserOrProfile
Columns in this view
Above are all the columns in this view.
The DBC.AMPUsage View
SELECT UserName (CHAR(12))
,AccountName (CHAR(20))
,SUM (CPUTime) (FORMAT 'z,zzz,zzz.99') as CPU_Used
,SUM (DiskIO) (FORMAT 'zzz,zzz,999') as DiskIO_Used
FROM DBC.AMPUsageV
GROUP BY 1, 2
ORDER BY 3 DESC ;
SELECT COUNT(DISTINCT (Vproc)) AS “# of AMPs// in our System”
FROM DBC.AMPUsageV WHERE UserName = USER;
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
SELECT UserName (CHAR(12))
,AccountName (CHAR(20))
,SUM (CPUTime) (FORMAT 'z,zzz,zzz.99') as CPU_Used
,SUM (DiskIO) (FORMAT 'zzz,zzz,999') as DiskIO_Used
FROM DBC.AMPUsageV
GROUP BY 1, 2
ORDER BY 3 DESC ;
Use the Exec statement above (in red) and the DBC.AMPUsage data is reset.
The DBC.AllTempTables
SELECT HostNo
,SessionNo
,UserName (CHAR(12))
,B_DatabaseName AS “DataBase”
,B_TableName AS “TableName”
FROM DBC.AllTempTablesV;
HostNo ,SessionNo ,UserName ,B_DatabaseName ,B_TableName , E_TableID
Columns in this view
This table is only for Global Temporary Tables. We created a Global Temporary Table named TeraTom. We had to materialize this with an Insert/Select statement to load the TeraTom table. Only then did it show up in the DBC.AllTempTables view.
The DBC.Triggers
SELECT DatabaseName
,TableName (CHAR(10))
,TriggerName (CHAR(12))
,EnabledFlag AS “Enabled”
,ActionTime AS Action
,Event
,Kind
FROM DBC.TriggersV;
DatabaseName , SubjecdtTableDataBaseName , TableName , TriggerName , EnabledFlag , ActionTime , Event , Kind , OrderNumber , TriggerComment , RequestText , CreatorName , CreateTimeStamp , LastAlterName , LastAlterTimeStamp , AccessCount , LastAccessTimeStamp , CreateTxtOverflow
Columns in this view
This view is for event-driven triggers attached to a single table.
The DBC.All_RI_ChildrenV
SELECT Indexid (FORMAT 'z9') AS ID
,ChildTable
,ChildKeyColumn
,ParentTable
,ParentKeyColumn
FROM DBC.ALL_RI_ChildrenV;
IndexID , IndexName , ChildDB , ChildTable , ChildKeyColumn , ParentDB , ParentTable , ParentKeyColumn , InconsistencyFlag , CreatorName , CreateTimeStamp
Columns in this view
Above are all the columns in this view.
DBC.SessionInfoV Information
SELECT COUNT(*)FROM
DBC.SessionInfoV;
Count the number of sessions currently running on the system
SELECT * FROM DBC.SessionInfoV
WHERE UserName = USER;
Show me everything about my sessions
SELECT UserName
,COUNT(*)
FROM DBC.SessionInfoV
GROUP BY 1
ORDER BY 2 ;
Show each User and the number of sessions that they have running that are currently on the system.
The SessionInfoV view shows you what you need to know about each session.
DBC.LogonOffV
SELECT COUNT(*)
FROM DBC.LogonOffV
WHERE LOGDATE >
CURRENT_DATE – 30 ;
Show the number of logons in the last 30 days
SELECT COUNT(*)
FROM DBC.LogonOffV
WHERE LOGDATE =
CURRENT_DATE ;
Show the number of logons today
SELECT TOP 5 UserName
,COUNT(*)
FROM DBC.LogonOffV
GROUP BY 1
ORDER BY 2 DESC ;
Top 5 most logins by user
SELECT COUNT(*) AS “Number of//Bad Logons”
FROM DBC.LogonoffV WHERE EVENT LIKE 'BAD%'
AND LOGDATE > CURRENT_DATE – 30 ;
Show the number of logons that failed in the last 30 days
The LogonOffV view shows you what you need to know about logon information.
AllRoleRights, AllRightsV, UserRightsV and UserGrantedRightsV
SELECT COUNT(*)
FROM DBC.AllRightsV;
lists all rights granted to users in the system.
SELECT * FROM
DBC.AllRoleRightsV;
lists all rights granted to roles in the system.
SELECT COUNT(*)
FROM DBC.UserRightsV;
lists all rights granted to the current user.
SELECT * FROM DBC.UserRightsV
WHERE Databasename = 'sql00';
Shows all the Access Rights for user SQL00
SELECT Count(*)FROM
DBC.UserGrantedRightsV;
Show the number of Access Rights that you explicitly granted
SELECT * FROM
DBC.UserGrantedRightsV;
Shows all the Access Rights that you have explicitly granted
Find out everything you wanted to know about Access Rights.
The DBC.Profiles View
SELECT ProfileName ,DefaultAccount AS “Def Acct”
,DefaultDB ,SpoolSpace (FORMAT 'zzz,zz9,999')
,TempSpace (FORMAT 'zzz,zz9,999')
FROM DBC.ProfileInfoV
ProfileNameI , ProfileName , ProfileId , DefaultAccount , DefaultDataBase, SpoolSpace , TempSpace , ExpirePassword , PasswordMinChar , PasswordMaxChar , PasswordDigits , PasswordSpecChar , PasswordRestrictWords , MaxLogonAttempts , LockedUserExpire , PasswordReuse , Version , CommentString , CreateUID , CreateTimeStamp , LastAlterUID , LastAlterTimeStamp , CostProfName
Columns in this view
This view shows information about Profiles.
RoleMembers, RoleInfo, UserRoleRights and ProfileInfoVX,
SELECT * FROM
DBC.RoleMembersVX;
Check to see your role names, the grantor and the date it was granted
SELECT RoleName, DatabaseName,
TableName, ColumnName, AccessRight
FROM DBC.UserRoleRightsV
ORDER BY 1;
lists all rights granted to the enabled roles of the user.
SELECT COUNT(*)
FROM DBC.RoleInfoVX;
Check to see all the roles that you personally have created
SELECT * FROM
DBC.ProfileInfoVX;
Show all information about your profiles
SELECT Role;
Check to see if you have a Role
SELECT Profile;
Check to see if you have a profile
The RoleMembers, UserRoleRights, RoleInfoVX and ProfileInfoVX views show you what you need to know about Roles and Profiles. Remember that Roles are for “Rights” and Profiles are for “People”.
Understanding that Space is based on a Per-AMP Basis
SELECT *
FROM DBC.Diskspace
WHERE DatabaseName = 'sql01';
Remember that all space is done on a “Per AMP” basis. Our system has two AMPs, so the space is listed per Vproc (AMP). The next page will show an even better example.
Total Space for a Single Database or User
Remember that all space is done on a “Per AMP” basis, but Tera-Tom has once again tricked the system by using aggregation. In other words, Tera-Tom climbed the Data Dictionary mountain and found a better way to summit!
Using the Data Dictionary to see the Space for Everyone
SELECT DatabaseName
,SUM(MaxPerm) AS “Perm”
,SUM(MaxSpool) AS “Spool”
,SUM(MaxTemp) AS “Temp”
,SUM(CurrentPerm) AS C_Perm
,SUM(CurrentSpool) AS C_Spool
,SUM(CurrentTemp) AS C_Temp
,SUM(PeakPerm) AS “PeakPerm”
,SUM(PeakSpool) AS “PeakSpool”
,SUM(PeakTemp) AS “PeakTemp”
from DBC.Diskspace
Group By DatabaseName
Order By 2 DESC ;
Now you will get a report of all space for everyone, and it is sorted by who has the most Perm! How is that for power? (If you have the rights to retrieve this).
Finding the Perm Percent Used
SELECT DatabaseName AS “Database”
,SUM(MaxPerm) AS “Perm”
,SUM(CurrentPerm) AS “Current”
,(SUM(CurrentPerm)/nullifzero(SUM(MaxPerm)) * 100) AS “%_Used”
FROM DBC.Diskspace
GROUP BY DatabaseName
ORDER BY 4 DESC ;
This is a great DBC Data Dictionary Query to keep track of the users or databases most likely to run out of Perm Space.
Finding the Perm Percent Used with a HAVING Clause
SELECT
DatabaseName AS “Database”
,SUM(MaxPerm) AS “Perm”
,SUM(CurrentPerm) AS “Current”
,(SUM(CurrentPerm)/nullifzero (SUM(MaxPerm)) * 100) AS “%_Used”
FROM DBC.Diskspace
GROUP BY DatabaseName
HAVING “%_Used” > 90
ORDER BY 4 DESC ;
This is a great DBC Data Dictionary Query to keep track of the users or databases most likely to run out of Perm Space. Notice the HAVING clause to show only databases or users who have a Percent Used > 90%.
Finding the Perm Percent Left with a HAVING Clause
SELECT
DatabaseName AS “Database”
,SUM(MaxPerm) AS “Perm”
,SUM(CurrentPerm) AS “Current”
,((SUM(MaxPerm) - SUM(CurrentPerm))/nullifzero (SUM(MaxPerm)) * 100) AS “%_Left”
FROM DBC.Diskspace
GROUP BY DatabaseName
HAVING “%_Left” < 10
ORDER BY 4 DESC ;
This is a great DBC Data Dictionary Query to keep track of the users or databases most likely to run out of Perm Space. Notice the HAVING clause to show only databases or users who have a Percent Left < 10%.
Creating a Macro for Perm Percent Used with a Dynamic %
CREATE MACRO SQL_Class.Macro_Name (InParam INTEGER ) AS ( SELECT
DatabaseName AS “Database”
,SUM(MaxPerm) AS “Perm”
,SUM(CurrentPerm) AS “Current”
,(SUM(CurrentPerm)/nullifzero (SUM(MaxPerm)) * 100) AS “%_Used”
FROM DBC.Diskspace
GROUP BY DatabaseName
HAVING “%_Used” > :InParam
ORDER BY 4 DESC ;);
GRANT SELECT on DBC.Diskspace to SQL_Class with grant option;
EXEC Macro_Name (85) ;
Orphaned Spool Files That Need to be deleted
SELECT Databasename, Vproc, CurrentSpool as “DeleteMe”
FROM DBC.Diskspace
WHERE Databasename NOT IN(SELECT UserName FROM DBC.SessionInfo)
AND CurrentSpool > 0
ORDER BY 1, 2
WITH SUM(CurrentSpool);
Teradata is smart in the fact that spool files are actually dropped when the query no longer need them, but spool files are always dropped after the query finishes. Occasionally an orphaned spool file still remains. Run the above query and if you get rows returning (other than the headers) then run the command below to delete them.
The UpdateSpace utility is a utility designed to fix inconsistencies in the DBC.DatabaseSpace table, which might occur occasionally. This is run from the DB Window
From supervisor window: START UPDATESPACE
If the above query returns rows, then they are mistakes and should be deleted.
Finding Table Sizes
SELECT Databasename
,TableName
,SUM(CurrentPerm) as Current_Perm
,SUM(PeakPerm) as Peak_Perm
FROM DBC.Tablesize
WHERE DatabaseName LIKE 'SQL%'
GROUP BY 1, 2
ORDER BY 1, 3 DESC, 2;
You can now keep track of the size of tables.
Finding Skew in the Tables in a Database
SELECT
Vproc
,CAST (TableName AS CHAR(20))
,CurrentPerm
,PeakPerm
FROM DBC.TableSizeV
WHERE DatabaseName = 'SQL_Class'
ORDER BY TableName, Vproc;
A Vproc is an AMP. There are only two AMPs on this system, and they are referred to as Vproc 0 and Vproc 1. Notice that the Addresses table has skew because of the Current_Perm for Vproc 0 (1536) is different than Vproc 1 (1024).
Finding Skew in a Table
SELECT
Vproc
,TableName (CHAR(15))
,CurrentPerm
FROM DBC.TableSizeV
WHERE DatabaseName = 'SQL_Class'
AND TableName = 'Addresses'
ORDER BY 1;
| Vproc | TableName | Current_Perm |
| 0 1 |
Addresses Addresses |
1536.00 1024.00 |
A Vproc is an AMP. There are only two AMPs on this system, and they are referred to as Vproc 0 and Vproc 1. Notice that the Addresses table has skew because of the Current_Perm for Vproc 0 (1536) is different than Vproc 1 (1024).
Display the Distribution of a Column per AMP
SELECT
HASHAMP (HASHBUCKET
(HASHROW (Product_ID))) AS “AMP #”
,COUNT(*)
FROM SQL_Class.Sales_Table
GROUP BY 1
ORDER BY 1;
| AMP# | Count(*) |
| 0 1 |
14 7 |
A Vproc is an AMP. There are only two AMPs on this system and they are referred to as Vproc 0 and Vproc 1. Notice that the Sales table has skew because of the Count(*) for the Primary Index of Product_ID with 14 rows for AMP 0 Vs. 7 rows for AMP 1.
Your Users and Databases
SELECT Databasename, PermSpace, SpoolSpace, TempSpace,
AccountName, CreatorName, DefaultDataBase, CreateTimeStamp, LastAlterTimeStamp, RoleName, ProfileName
FROM DBC.DBASE
WHERE DatabaseName LIKE 'SQL%'
Order by 1
Every Time a User or Database is created, a one row entry is placed in the DBC.DBASE table. Users and Databases are considered the same thing in Teradata, but a User does have a login so they can run queries. A User or a Database can contain other databases or users and both can contain objects. Both can also be given Perm, Spool, and Temp space. Above, is a great query that pulls directly from the DBC table Dbase which, if you have the access rights to query it, you will find this table to be a valuable resource.
DBC Tables used in the Collect Statistics Process
DBC.TVFields–holds statistics collected for single column or single column indexes.
DBC.Indexes–holds statistics collected for multi-column or multi-column indexes.
DBC.StatsTbl(NEW 14.0)–repository for the statistics management data in V14.
When statistics are collected on a system, those statistics are held in the User DBC. Statistics tell the Parsing Engine how many rows per value are on a column or index. All statistics collected on a single column or a single index are stored in DBC.TVFields. All statistics collected on multiple columns (combined) or a multi-column index are stored in the DBC.Indexes table. The Parsing Engine knows exactly where to find them when you query a table. These statistics are used to improve the PE's plan and it usually gives a “High Confidence” message in the Explain plan. These statistics are still valid even after a Teradata upgrade where more AMPs have been added and the data has been redistributed. Collecting Statistics is usually not performed during peak times, but rather after hours or during the ETL batch load process.
The DBC Table DBC.Next
| RowNum DatabaseID TableID ProcsRowLock EventNum LogonSequenceNo TempTableID StatsQueryID ReconfigID |
Char(1) Numeric Byte (4) Numeric Byte (4) Numeric Byte (4) Numeric Byte (4) Numeric Byte (4) Numeric Byte (4) Number Byte (4) Number INTEGER |
DBC.Next is a Data Dictionary table that consists of a single row with 9 columns. One of the counters is used to assign a globally unique numeric ID to every Database, User, Role, and Profile. This number is astronomical in that you can continually create these objects and yet never reach the maximum number.
A different counter is used to assign a globally unique numeric ID to every Table, View, Macro, Trigger, Stored Procedure, User-Defined Function, Join Index, and Hash Index. DBC.Next always contains the next value to be assigned to any of these.
This table is used internally by the Parsing Engine and the AMPs.
DBA Advice - ClearPeakDisk to Reset Peak Space
EXEC DBC.ClearPeakDisk;
The DBA will run the above Macro once a month to reset the Peak values for Perm, Spool and Temp for everyone
REPLACE MACRO DBC.ClearPeakDisk
AS ( UPDATE DataBaseSpace
SET PeakPermSpace = 0,
PeakSpoolSpace = 0,
PeakTempSpace = 0 ALL;);
This is the DBC.ClearPeakDisk Macro
When a user complains they need more Perm, Spool, or Temp space, the DBA will often check to see if they have gone over their allotted space assignments. They can check to see if their Peak Perm, Peak Spool or Peak Temp has reached their MaxPerm, MaxSpool or MaxTemp values. The DBA can reset Peak values each month to keep tabs on this. So, above is the execute statement to clear the values, and I have also shown you the actual Macro.
DBA Advice – Clean out these Tables Periodically
The DBC.EventLog is an audit trail of all archive and recovery activities you have performed.
The above tables provide the DBA with information about the system such as the SQL being submitted (DBQL), the CPU and Disk I/O usage (ResUsage), and who is accessing certain objects (AccLogTbl). If they are not cleaned up, they grow too big. Most of the time a DBA will only keep 30-60 days of this information, but it depends on the DBA. Some DBA's will back up this information, but others will not. One thing is for sure and that is they can get so big they negatively affect system performance.
The DBC.AssociationV View
SELECT TRIM (DatabaseName) as “Resides Here// Now”
,TableName (FORMAT 'X(25)') AS “Moved//Object”
,TRIM (Original_DatabaseName) || '.' || Original_TableName AS “Database//Source”
,EventNum (FORMAT 'Z(4)9') AS “Event//Number”
FROM DBC.AssociationV
WHERE DatabaseName LIKE '%SQL@_%' Escape '@'
ORDER BY 1,2 ;
DatabaseName , TableName ,EventNum ,Original_DatabaseName ,Original_TableName,Original_TableKind ,Original_Version ,Original_ProtectionType ,Original_JournalFlag,Original_CreatorName ,Original_CommentString
Columns in this view
When the COPY command is used to copy an object from one Teradata system to another, it is recorded in the DBC.AssociationV View.
This view shows objects that have been copied from one Teradata system to another.
The DBC.JournalsV View
SELECT TRIM (Tables_DB) || '.'|| TableName (CHAR(30)) AS “Database//and Table”
,TRIM(Journals_DB) || '.'|| JournalName (CHAR(30)) AS “Assigned//to Journal”
FROM DBC.JournalsV
ORDER BY 1 ;
List all tables in the system that use a journal, and list the names of each journal.
Tables_DB ,TableName ,Journals_DB ,JournalName
Columns in this view
This View associates journals with tables when the user who either owns the table or who holds rights on the objects that are being referenced.
DBC.Databases2V is for Unresolved Reference Constraints
SELECT *
FROM DBC.Databases2V
WHERE UnresolvedRICount >= 1;
The DBC.Databases2[V][X] views show all unresolved reference constraints, which are caused by creating a table with a Foreign Key before creating the table with the Primary Key. Or, it can happen on a restore if the Foreign Key is restored and the Primary Key table is not.
DatabaseName DatabaseID UnresolvedRICount
Columns in this view
This View shows all inconsistencies with Referential Integrity. You need to restore the Parent Table (where the Primary Key resides) to “resolve” the references constraint.
The DBC.All_RI_ChildrenV for Inconsistent RI
This view is used to identify tables with Referential Integrity from the child to the parent and can also be used to show if the RI constraint is consistent (good) or inconsistent (bad).
SELECT IndexID ,IndexName ,ChildTable ,ChildKeyColumn
,ParentTable ,ParentKeyColumn ,InconsistencyFlag AS “Problem”
FROM DBC.ALL_RI_ChildrenV
WHERE ChildDB = 'SQL_Class';
IndexID ,IndexName ,ChildDB ,ChildTable ,ChildKeyColumn ,ParentDB ,ParentTable ,ParentKeyColumn ,InconsistencyFlag ,CreatorName ,CreateTimeStamp
There are two options to fix Referential Integrity problems:
1) ALTER TABLE Tablename DROP INCONSISTENT REFERENCES;
Foreign Key references are dropped. Use another ALTER statement to reproduce them.
2) Use the REVALIDATE REFERENCES command in the Archive Facility (ARC).
When either the child or the parent table is restored, all constraints are marked as inconsistent. REVALIDATE REFERENCES FOR creates error tables concerning data rows that failed referential integrity constraint checks.
The DBC.ShowColChecksV View
DatabaseName ,TableName ,ColumnName ,ColCheck ,CreatorName ,CreateTimeStamp
Columns in this view
This view shows columns that have checks inside their DDL.
The DBC.ShowTblChecksV View
DatabaseName ,TableName ,ColumnName ,TblCheck ,CreatorName ,CreateTimeStamp
Columns in this view
This view shows table level check constraints that are “named” column constraints.
The DBC.PartitioningConstraintsV View
SELECT TableName AS “TableName”
,ColumnPartitioningLevel AS “CPL”
,PartitioningLevels AS “#of Levels”
,ConstraintText AS “Constr Text”
FROM DBC.PartitioningConstraintsV
WHERE DatabaseName = 'SQL_Class';
Show Partitioning Constraints for a certain database
DatabaseName ,TableName ,IndexName ,IndexNumber ,ConstraintType ,ConstraintText ,ConstraintCollation ,CollationName ,CreatorName ,CreateTimeStamp ,CharSetID ,SessionMode ,ResolvedCurrent_Date ,ResolvedCurrent_TimeStamp ,DefinedMaxPartitions (V14) ,MaxCombinedPartitions (V14) ,PartitioningLevels (V14) ,ColumnPartitioningLevel (V14)
Columns in this view
This view shows Partitioning constraints.
The DBC.AccessLogV View
SELECT Distinct UserName, AccountName ,DatabaseName,
TVMName ,StatementText (Char(16))
FROM DBC.AccessLogV
ORDER BY TVMName;
LogDate , LogTime , LogonDate , LogonTime , LogicalHostID , IFPNo , SessionNo , UserName , AccountName , OwnerName , AccessType , Frequency , EventCount , AccLogResult , Result , DatabaseName , TVMName , ColumnName , StatementType , StatementText , QueryBand , ProxyUser
Columns in this view
This view shows information about the AccessLog when Access Logging is turned on.
The DBC.AccessLogV View for Today's Queries
SELECT Distinct LogTime, UserName (CHAR (10)) AS “;User”
,AccessType AS “Acctype” ,AccLogResult AS “Granted or Denied”
,StatementText(CHAR(16)) AS “Statement Text”
FROM DBC.AccessLogV
WHERE LogDate = DATE
ORDER BY LogDate, LogTime;
LogDate , LogTime , LogonDate , LogonTime , LogicalHostID , IFPNo , SessionNo , UserName , AccountName , OwnerName , AccessType , Frequency , EventCount , AccLogResult , Result , DatabaseName , TVMName , ColumnName , StatementType , StatementText , QueryBand , ProxyUser
Columns in this view
The DBC.AccessLogV View Denials for Today
SELECT Distinct UserName, AccountName ,DatabaseName,
TVMName ,StatementText (Char(16))
FROM DBC.AccessLogV
ORDER BY TVMName
WHERE AccLogResult = 'D' ;
LogDate , LogTime , LogonDate , LogonTime , LogicalHostID , IFPNo , SessionNo , UserName , AccountName , OwnerName , AccessType , Frequency , EventCount , AccLogResult , Result , DatabaseName , TVMName , ColumnName , StatementType , StatementText , QueryBand , ProxyUser
Columns in this view
This view shows information about Access Denials when Access Logging is turned on.
DBC.DBQLRulesV
BEGIN QUERY LOGGING WITH SQL, EXPLAIN ON ALL;
Log default rows, SQL and Explain for ALL Queries.
View the Rules
SELECT UserName
,TypeOfRule
,ExplainFlag
,ObjFlag
,SqlFlag
,TextSizeLimit
FROM DBC.DBQLRulesV ;
UserName ,AccountString , ApplName (13.0) ,TypeofRule (13.0) , ExplainFlag, ObjFlag , SqlFlag , StepFlag , XMLPlanFLag (13.0) , SummaryFlag, ThresholdFlag , TextSizeLimit , SummaryVal1 , SummaryVal2 , SummaryVal3, ThreshValue , TypeofCriterion (13.0)
Columns in this view
This view shows information about the Rules set for the DBQL Query Logging.
DBC.QryLogV
Select CollectTimeStamp ,UserName ,AppID ,MaxAmpIO
FROM DBC.QryLogV ;
ProcID , CollectTimeStamp , QueryID , UserID , UserName , DefaultDatabase , AcctString , ExpandAcctString , SessionID , LogicalHostID , RequestNum , InternalRequestNum , LogonDateTime , AcctStringTime , AcctStringHour , AcctStringDate , LogonSource , AppID , ClientID , ClientAddr , QueryBand , ProfileID , StartTime , FirstStepTime , FirstRespTime , ElapsedTime , NumSteps , NumStepswPar , MaxStepsInPar , NumResultRows , TotalIOCount , AMPCPUTime , ParserCPUTime , UtilityRowCount , ErrorCode , ErrorText , WarningOnly , AbortFlag , CacheFlag , StatementType , StatementGroup , QueryText , NumOfActiveAMPs , MaxAMPCPUTime , MaxCPUAmpNumber , MinAmpCPUTime , MaxAmpIO , MaxIOAmpNumber , MinAmpIO, SpoolUsage, LSN , EstResultRows , EstProcTime , EstMaxRowCount, AMPCPUTimeNorm, ParserCPUTimeNorm, MaxAMPCPUTimeNorm , MaxCPUAmpNumberNorm , MinAmpCPUTimeNorm , ParExpreqTime , ProxyUser , ProxyRole
Columns in this view
This view shows information about default rows in the DBQL main table DBQLogTbl.
DBC.QryLogSummaryV
BEGIN QUERY LOGGING LIMIT SUMMARY = 100, 500, 2000 CPUTIME ON DBC;
SELECT UserID, CollectTimeStamp, QueryCount
, QuerySeconds ,LowHist ,HighHist
FROM DBC.QryLogSummaryV
ORDER BY CollectTimeStamp, QueryCount, HighHist;
ProcID , CollectTimeStamp , UserID , AcctString, LogicalHostID , AppID , ClientID , ClientAddr , ProfileID , SessionID , QueryCount , ValueType , QuerySeconds , AverageTime , TotalIOCount , AverageIO , AMPCPUTime , AverageAmpCPU , ParserCPUTime , AverageParserCPU , AMPCPUTimeNorm , AverageAmpCPUNorm , ParserCPUTimeNorm , AverageParserCPUNorm , LowHist , HighHist
Columns in this view
This view shows information about summary rows in the DBQLSummaryTbl.
ResUsage Macros
One Node Macros
ResCPUByAMPOneNode
ResCPUByPEOneNode
ResCPUOneNode
ResHostOneNode
ResLvdOneNode
ResMemMgmtOneNode
ResNetOneNode
ResOneNode
ResPdskOneNode
ResVdskOneNode
Multiple Node Macros
ResAwtByAMP
ResAwtByNode
ResCPUByAMP
ResCPUByPE
ResCPUByNode
ResHostByLink
ResLdvByNode
ResMemMgmtByNode
ResNetByNode
ResNodeByNode
ResPdskByNode
ResVdskByNode
ResPsByNode
Group Node Macros
ResAmpCpuByGroup
ResPeCpuByGroup
ResCPUByGroup
ResHostByGroup
ResLdvByGroup
ResMemByGroup
ResNetByGroup
ResNodeByGroup
ResPdskByGoup
ResVdskByGroup
ResPsByGroup
All Node Macros
ResAwt
ResNode
Above, are the macros provided with ResUsage.
Executing the ResUsage Macro DBC.Resnode
Not all columns are shown in this report
The DBC.Resnode macro will display data if logging is enabled on ResUsageSpma.
The DBC.IdCol Table
SELECT *
FROM DBC.IdCol
When an Identify column is created, a row is placed into the table DBC.IdCol.
TableID ,DatabaseID ,AvailValue ,StartValue ,MinValue ,MaxValue ,Increment ,Cyc
Columns in this view
This is a table in DBC. We have two tables in the system with Identity columns.