Options for BEGIN QUERY LOGGING | ALTER TYPE | Teradata Vantage - Logging Options for BEGIN QUERY LOGGING - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

Logging options that you set with BEGIN QUERY LOGGING are recorded in the DBQLOptions field of DBQLRuleTbl.

Logging Option Mask Value Indicator Bit Position
EXPLAIN 2 2
SQL 4 3
OBJECTS 8 4
STEPINFO 16 5
XMLPLAN 32 6
SUMMARY 64 7
THRESHOLD 128 8
STATSUSAGE 256 9
LOCK 512 10
VERBOSE 1024 11
DETAILED STATSUSAGE 2048 12
USECOUNT 4096 13
UTILITYINFO 8192 14
PARAMINFO 16384 15
Option Definition
ALL Log query object information, step information, EXPLAIN text, and all SQL requests for all logged on users. This is equivalent to specifying the EXPLAIN, OBJECTS, SQL, and STEPINFO options individually.

The ALL option does not include LOCK, PARAMINFO, STATSUSAGE, XMLPLAN, or UTILITYINFO.

This option does not invoke the SUMMARY or THRESHOLD limit options.

You cannot specify the ALL logging option if you also specify the THRESHOLD limit option.

If you specify ALL, then you should also specify a value of 0 for SQLTEXT to ensure that the text for the SQL request is not logged in both the DBC.DBQLogTbl and DBC.DBQLSqlTbl tables.

If you specify ALL, then you cannot specify other logging options.

EXPLAIN Log the unformatted EXPLAIN text for the request.

You cannot specify the EXPLAIN logging option if you also specify the THRESHOLD limit option.

This option generates and logs the unformatted EXPLAIN text for each request. It does not generate EXPLAIN text for requests preceded by the EXPLAIN request modifier.

For example, the system does not log EXPLAIN text for the following request.

EXPLAIN SELECT *
        FROM table_a ; /* EXPLAIN text logging
                          does  not  occur     */

If you perform the same query without the preceding EXPLAIN modifier, however, the system does log unformatted EXPLAIN text.

SELECT *
FROM table_a;  /* EXPLAIN text logging
                  does  occur          */

Use care when specifying this option because of the performance cost of generating the EXPLAIN text for a query.

LOCK=n Log any lock contention that exceeds n hundredths of a second in XML format in DBQLXMLLockTbl. For more information on DBQLXMLLockTbl, including how to shred the lock plan data, see Teradata Vantage™ - Database Administration, B035-1093.
OBJECTS Log information about databases, tables, columns, and indexes accessed by SQL requests for the specified user set.

You can specify the OBJECTS logging option if you also specify the THRESHOLD limit option.

This option does not log activity for macros or views, or log activity for dictionary tables and columns. For example, CREATE TABLE does not log any objects in the DBQL system because the statement only generates dictionary objects. For example, a query like the following does not log any objects because the query only accesses dictionary tables.

SELECT *
FROM DBC.TVFields;
PARAMINFO Parameter values and metadata are logged in DBQLParamTbl.
SQL Log the full text of all SQL requests performed by the specified user set in system table DBC.DBQLSqlTbl.

You can specify the SQL logging option if you also specify the THRESHOLD limit option.

This option does not log SQL requests within SQL procedures, macros, views, or triggers.

STATSUSAGE Log DML requests that require cardinality estimation in their query optimization as an XML document in system table DBC.DBQLXMLTbl.

If you specify STATSUSAGE with XMLPLAN, the database logs the collected data in a single integrated document.

Data logged with this option is independent of the THRESHOLD or SUMMARY option limit options.

STATSUSAGE does not log activity for explained requests or for the DUMP EXPLAIN and INSERT EXPLAIN statements. For information about these statements and the EXPLAIN request modifier, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

The ALL option does not include STATSUSAGE.

STEPINFO Log AMP step-level information for all SQL requests performed by the specified user set in system table DBC.DBQLStepTbl.

You can specify the STEPINFO logging option if you also specify the THRESHOLD limit option.

USECOUNT Log use count collection for a database or user.

If you specify USECOUNT for a user, you can specify any of the other logging options.

If you specify USECOUNT for a database, you cannot specify any other logging options. Otherwise, the system returns an error to the requestor.

When you submit a BEGIN QUERY LOGGING request, the use counts and timestamps for the database or user are reset.

UTILITYINFO Utility information is logged in DBC.DBQLUtilityTbl.
XMLPLAN Logs the query plan generated by the Optimizer for SQL DML requests as an XML document in system table DBC.DBQLXMLTbl.

When logging problematic queries, the recommended best practice is to specify XMLPLAN logging. The XMLPLAN option captures the query plan with additional detail to assist diagnosing performance issues.

If you specify XMLPLAN with STATSUSAGE, the database logs the collected data in a single integrated document.

Logs basic information, such as StatementType and the corresponding StepNames, for DDL statements. In addition, detailed information is logged for:
  • COLLECT STATISTICS
  • CREATE INDEX
  • CREATE TABLE
  • DROP INDEX
  • DROP TABLE

XMLPLAN also logs detailed information for FastLoad and MultiLoad jobs.

Because the XML document includes the query and step text, you usually do not need to specify the EXPLAIN and SQL options if you specify XMLPLAN. You should also specify a value of 0 for SQLTEXT to avoid redundant logging when you specify XMLPLAN.

The XMLPLAN option does not log query plans for EXPLAIN request modifiers, or for INSERT EXPLAIN and DUMP EXPLAIN requests.

The ALL option does not include XMLPLAN.

You cannot specify the XMLPLAN logging option if you also specify the THRESHOLD limit option.