17.00 - Usage Notes - Teradata Database

Teradata Vantage™ - Data Dictionary

prodname
Advanced SQL Engine
Teradata Database
vrm_release
17.00
created_date
June 2020
category
Administration
Programming Reference
featnum
B035-1092-170K

With the WITH OBJECTS option, DBQL logs one row in DBQLObjTbl and the associated view, QryLogObjectsV, for each data object referenced by the query. An object can be a database, data table, column, secondary index, join index, or journal table. (If the object is a secondary index, its number is logged rather than a name.) DBQL gets the use counts from the Optimizer and not the SQL statement itself. The system logs a count of the number of times the Optimizer accessed the object. If objects are requested, object information is stored in a DBQL table that contains a row for each object used in the query.

Any DBC database tables and columns used by the system while processing a query are not reflected in the DBQL object rows for that query. This means, for example, that statements like CREATE TABLE or SELECT FROM DBC.xxx will not have objects logged through DBQL because they deal with DBC tables and columns.

If objects are requested, object information is stored in a DBQL table that contains a row for each object used in the query.

IF the object is … THEN
an index the field ID is logged instead of a name.
a column
  • the field ID is logged instead of a name
  • the object ID can be used to quality the field ID, which is not unique across the system.

Object frequency can also be logged. This is the number of times an object is used in a query.

Possible Values for ObjectType

Value Description
Agg User-defined aggregate function
AgS User-defined aggregate STAT function
Aut Security authorization
Col Column
DB Database
GLP GLOP set
HIx Hash index
Idx Index. For each index, there is a database name, table name, and column name. The ObjectId column is the identifier of the table and the ObjectNum column is the number of the index in that table. For multi-column indexes, there is one row for each column of the index that a query used. For example, if an index consists of three columns and the query uses all three, there will be three rows, each with a different column name. The column name will be null for an index for statements such as COLLECT STATISTICS, ALTER PROCEDURE, SHOW PROCEDURE, or SELECT COUNT(*).
JIx Join index. For each join index, there is a database name and join index name in the ObjectTableName field. For these rows, the ColumnName indicates a column referred to by the join index.
  • ObjectType is ‘JIx.’
  • ObjectId matches the ID of the join index.
  • ObjectNum is 0.
Jrl Journal
Mac Macro
NoT No type (unknown)
SP Stored procedure
Sta User-defined STAT function
Tab Table
TbF Table function
Tmp Temporary
TbO Table operator
TbC Contract function
Trg Trigger
UDF User-defined function
UDM User-defined method
UDT User-defined type
Viw View
Vol Volatile
XSP External stored procedure

Possible Values for TypeOfUse

Value Description
1 Found in the resolver
2 Accessed during query processing
3 Reference, access
4 Found in a conditional text
6 Access, conditional
7 Reference, access, conditional
8 Found in inner join condition
10 Access, inner join
14 Access, conditional, inner join
16 Found in outer join condition
18 Access, outer join
22 Access, conditional, outer join
30 Access, conditional, inner and outer join
32 Found in sum node
34 Access, sum
38 Access, conditional, sum
46 Access, conditional, sum, inner join
54 Access, conditional, sum, outer join
64 Found in a full outer join condition
70 Access, conditional, full outer join
102 Access, conditional, sum, full outer join