16.10 - DBQL Tables, Views, and Macros - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Along with the DBQL tables, additional DBQL components includes a security macro and a view for each table. These are all created in database DBC by the DIP utility during installation. For details, see “Database Initialization Program (DIP)” in Utilities.

The purpose of each object is listed in the following table.

Dictionary
Object Type Purpose Method Used To Create
DBQLAccessMacro Macro (empty) controls authority of users to execute the Teradata SQL BEGIN/REPLACE/FLUSH/END QUERY LOGGING statements. the DIP utility when it runs DIPVIEWSV.
DBQLExplainTbl Table contains the explain information in an unformatted string without line breaks. Tableinit and only populated if you specify the EXPLAIN option.
DBQLModeMacro Macro

(empty)

controls the authority of users to execute the MODE option of the BEGIN/REPLACE QUERY LOGGING statements the DIP utility when it runs DIPVIEWSV.
DBQLogTbl Table is the main table containing information about queries being logged. Tableinit (invoked by the Sysinit utility), during installation.
DBQLObjTbl Table stores information on the target objects of the query being logged. One row is logged for each object referenced in the query. Tableinit and only populated if you specify the OBJECT option.
DBQLRuleCountTbl Table stores the cardinality of DBQLRuleTbl (for internal use only). Tableinit (invoked by the Sysinit utility).

This table is only populated if you specify BEGIN QUERY LOGGING statements.

DBQLRules[V] View displays the current rules in DBC.DBQLRuleTbl (to a user with DBC or SystemFE privileges). the DIP utility when it runs DIPVIEWSV.
DBQLRuleTbl Table stores the rules resulting from each BEGIN QUERY LOGGING or REPLACE QUERY statements.

One row exists for each set of username, account string and application name specified in the BEGIN QUERY LOGGING and REPLACE QUERY LOGGING statements.

The END QUERY LOGGING statement removes rows from the rule table.

This table is for internal use only.

Tableinit (invoked by the Sysinit utility).

This table is only populated if you specify BEGIN QUERY LOGGING or REPLACE QUERY LOGGING statements.

DBQLSQLTbl Table stores the full SQL text of the query. One query string may require more than one row. Tableinit and only populated if you specify the SQL option.
If a Teradata dynamic workload management software exception occurs, the Teradata dynamic workload management software causes rows to be written to the DBQLSqlTbl.
DBQLStepTbl Table stores information about each processing step used to satisfy the query. One row is logged for each step Tableinit and only populated if you specify the STEPINFO option.
DBQLSummaryTbl table stores information about queries that meet the criteria for a rule specifying the SUMMARY or THRESHOLD option Tableinit (invoked by the Sysinit utility), during installation.
DBQLUtilityTbl table stores information about load/export and Data Stream Architecture utilities at the job level the WITH UTILITYINFO option of BEGIN QUERY LOGGING or REPLACE QUERY LOGGING.
DBQLXMLLOCKTbl table logs lock delays from the Lock Manager in the AMPs in XML format the WITH LOCK option of BEGIN QUERY LOGGING or REPLACE QUERY LOGGING.
DBQLXMLTbl table stores the XML plan for all DML statements. Tableinit and only populated if you specify the XMLPLAN option.
QryLockLogXML[V] view accesses the DBQLXMLLOCKTbl the DIP utility scripts DIPVIEWSV and, for JSON views, DIPJSON.
QryLogExplainDoc[V] view access the DBQLExplainTbl
QryLogExplain[V] view accesses the DBQLExplainTbl
QryLogFeatureListV view accesses the FeatureNames table function, SYSLIB.FeatureNames_TBF()
QryLogFeatureUseCountV view accesses the FeatureUsage column of DBQLogTbl and the QryLogFeatureList view
QryLogFeatureUseJSON view accesses the FeatureUsage column of DBQLogTbl
QryLogObjects[V] view accesses DBQLObjTbl
QryLogParamV view accesses the parameterized query log table, DBQLParamTbl
QryLogParamJSON view accesses the parameterized query log table, DBQLParamTbl, in JSON format
QryLogSQLDoc[V] view access the DBQLSQLTbl
QryLogSQL[V] view accesses the DBQLSQLTbl
QryLogSteps[V] view accesses the DBQLStepTbl
QryLogSummary[V] view accesses the DBQLSummaryTbl
QryLogTDWM[V] view accesses the DBQLogTbl
QryLog[V] view accesses the DBQLogTbl
QryLogUtilityV view accesses the DBQLUtilityTbl
QryLogXMLDocV view accesses the DBQLXMLTbl
QryLogXML[V] view accesses the DBQLXMLTbl

The DBQL views are predefined for ease of use. However, database administrators can create their own customized views by taking data from one or more DBQL tables and displaying the information in the format they need.