15.10 - QryLogObjectsV - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Query

DBC

 

View Column

Data Type

Format

Source Table.Column

ProcID

DECIMAL(5,0)

NOT NULL

-(5)9 (explicit)

DBQLObjTbl.ProcID (Composite NUPI)

CollectTimeStamp

TIMESTAMP(6)

NOT NULL

YYYY-MM-DDBHH:MI:SS

DBQLObjTbl.CollectTimeStamp (Composite NUPI)

QueryID

DECIMAL(18,0)

NOT NULL

--Z(17)9

DBQLObjTbl.QueryID

ObjectDatabaseName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

X(128)

DBQLObjTbl.ObjectDatabaseName

ObjectTableName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

X(128)

DBQLObjTbl.ObjectTableName

ObjectColumnName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

X(128)

DBQLObjTbl.ObjectColumnName

ObjectID

BYTE(4)

NOT NULL

X(8)

DBQLObjTbl.ObjectID

ObjectNum

INTEGER

--,---,---,--9 (explicit)

DBQLObjTbl.ObjectNum

ObjectType

CHAR(3) LATIN

NOT CASESPECIFIC

NOT NULL

X(3)

DBQLObjTbl.ObjectType

FreqofUse

INTEGER

--,---,---,--9 (explicit)

DBQLObjTbl.FreqofUse

TypeOfUse

VARCHAR(46)

UNICODE

X(46)

DBQLObjTbl.TypeofUse

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.

    ObjectType Column Values

     

    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

    Reference only

    2

    Access

    3

    Reference, access

    6

    Access, conditional

    7

    Reference, access, conditional

    10

    Access, inner join

    14

    Access, conditional, inner join

    18

    Access, outer join

    22

    Access, conditional, outer join

    30

    Access, conditional, inner and outer join

    34

    Access, sum

    38

    Access, conditional, sum

    46

    Access, conditional, sum, inner join

    54

    Access, conditional, sum, outer join

    70

    Access, conditional, full outer join

    102

    Access, conditional, sum, full outer join

    The following SELECT statement retrieves the object information of a query:

    SELECT QueryID, ObjectDatabaseName (Named ObjDBName), ObjectTableName (Named ObjTblName), ObjectColumnName (Named ObjColName), ObjectID (Named ObjId), ObjectNum (Named ObjNum), ObjectType (Named ObjType), FreqofUse 
    FROM DBC.QryLogObjectsV where queryid = 201205134619838031;

    Result:

    QueryID            ObjDBName    ObjTblName  ObjColName ObjID    ObjNum   ObjType  FreqofUse
    ------------------ -----------  ----------  ---------- -------- -------  -------- ------
    201205134619838031 D_PERSONNEL  ?            ?         00001604   0        DB      1
    201205134619838031 D_PERSONNEL  DEPARTMENT   ?         00009005   0        Tab     1
    201205134619838031 D_PERSONNEL  DEPARTMENT   DeptNo    00009005   1,025    Col     2
    201205134619838031 D_PERSONNEL  DEPARTMENT   DeptName  00009005   1,026    Col     1
    201205134619838031 D_PERSONNEL  DEPARTMENT   EmpCount  00009005   1,027    Col     1
    201205134619838031 D_PERSONNEL  DEPARTMENT   Loc       00009005   1,028    Col     1