17.00 - WITH - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

Keyword to use in specifying logging options for the request objects, the AMP steps produced to perform the request, the EXPLAIN text for the request, the SQL request text, or the optimizer plan for the request as an XML document.

You cannot specify any logging option if you specify LIMIT SUMMARY.

Specifying any of the logging options can have a performance impact on your system.

Only the default set of request information is logged in a defined default row for each request for each specified user, unless you specify a logging option or WITH NONE.

For descriptions of the logging options and option modifiers, see “Tracking Query Behavior with Database Query Logging” in Teradata Vantage™ - Database Administration, B035-1093. You can set the logging options listed below.

ALL
Information is logged for the EXPLAIN, OBJECTS, SQL, and STEPINFO options. Information is not logged for the XMLPLAN, LOCK, STATSUSAGE, PARAMINFO, FEATUREINFO, UTILITYINFO, or USECOUNT options.
EXPLAIN
EXPLAIN text for the request is logged.
You cannot specify the EXPLAIN option with the SUMMARY or THRESHOLD options.
LOCK=n
Lock contentions longer than n centiseconds are logged in XML format. For more information on DBQLXMLLockTbl, including how to shred the lock plan data, see “Tracking Query Behavior with Database Query Logging” in Teradata Vantage™ - Database Administration, B035-1093.
NONE
DBQL data is not logged for the items you specify, including:
  • account:user pair or account:user list
  • application name or application name list
  • user name list
  • ALL:account name
  • ALL:account string list
  • ALL without any other specification, which specifies all accounts
If you specify WITH NONE, you cannot specify additional options.
To remove the WITH NONE setting, you must use the corresponding END QUERY LOGGING statement. See END QUERY LOGGING.
OBJECTS
Database, table, column, and index information is logged.
You can specify this option with SUMMARY or THRESHOLD.
NO COLUMNS OBJECTS
Column data is not logged in DBQLObjTbl. This option can reduce logging overhead for tables with a large number of columns.
PARAMINFO
Parameter values and metadata are logged in DBQLParamTbl.
FEATUREINFO
Log feature usage information into the FeatureUsage column of DBQLogTbl.
To display the current list of features being logged, you can use this statement:
SELECT featurename FROM DBC.QryLogFeatureListV;
The FeatureUsage column stores usage information in binary format. You can use the TD_DBQLFUL table function to convert the binary data into a JSON document. For more information, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210. To display feature use information in JSON format, you can access the FeatureInfo column of the QryLogFeatureJSON view. To display current use count information, you can access DBC.QryLogFeatureUseCountV and the binary data in the FeatureUsage column of DBC.DBQLogTbl. The use count information is based on number of rows in DBC.DBQLogTbl.
SQL
The full text of all SQL statements is logged.
You can specify this option with THRESHOLD.
STATSUSAGE
Optimizer statistics and usage recommendations are logged in an XML document for DML requests.
If you also specify XMLPLAN, the data from STATSUSAGE and XMLPLAN is logged in one XML document.
The XML schema for the log this option produces is located at:
http://schemas.teradata.com/queryplan/queryplan.xsd
DETAILED STATSUSAGE
The following statistics details for all of the database objects referenced in the plan for a request are logged:
  • StatTimeStamp
  • Version
  • OrigVersion
  • NumColumns
  • NumBValues
  • NumEHIntervals
  • NumHistoryRecords
  • NumNulls
  • NumAllNulls
  • NumAMPs
  • NumPNullDistinctVals
  • PNullHighModeFreq
  • AvgAmpRPV
  • HighModeFreq
  • NumDistinctVals
  • NumRows
  • CPUUsage
  • IOUsage
The XML schema for the log this option produces is located at:
http://schemas.teradata.com/queryplan/queryplan.xsd
The DETAILED keyword is a modifier for the STATSUSAGE option.
DETAILED does not support geospatial statistics.
STEPINFO
AMP step-level information is logged.
This option can be used with THRESHOLD.
USECOUNT
Use count information is logged for a database or user. Collects use count information on the specified databases or users, regardless of who is accessing the database objects.
If you specify USECOUNT for a:
  • user, you can specify any of the other logging options.
  • database, you cannot specify any other logging options.
You cannot enable the WITH USECOUNT option on account strings.
You can specify WITH USECOUNT and ON ALL to log use count information for all objects on the system, for example:
BEGIN QUERY LOGGING WITH USECOUNT ON ALL;
When USECOUNT is mixed with other options and you specify ON ALL, information for the other options is logged for all objects accessed by all users. Use count information is logged for all objects in all databases, regardless of who is accessing them, for example:
BEGIN QUERY LOGGING WITH USECOUNT, SQL, OBJECTS LIMIT SQLTEXT=0 ON ALL;
UTILITYINFO
Utility information is logged in DBC.DBQLUtilityTbl.
XMLPLAN
The plan is logged in XML format for DDL, DML, and DCL requests. For DDL statements, XMLPLAN logs basic information such as StatementType and the corresponding StepNames.
In addition, the XMLPLAN option logs detailed information for the following DDL statements:
  • COLLECT STATISTICS
  • CREATE INDEX
  • CREATE TABLE
  • DROP INDEX
  • DROP TABLE
XMLPLAN also logs detailed information for FastLoad and MultiLoad jobs.
The XML schema for the log this option produces is located at:
http://schemas.teradata.com/queryplan/queryplan.xsd
VERBOSE XMLPLAN
VERBOSE EXPLAIN text for a request is logged in XML format. Also includes details on SpoolAsgnList and HashFields that are not available with only EXPLAIN.
The VERBOSE keyword is a modifier for the XMLPLAN option.
The XML schema for the log this option produces is located at:
http://schemas.teradata.com/queryplan/queryplan.xsd