WITH - Advanced SQL Engine - Teradata Database

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

You cannot specify logging options if you specify LIMIT SUMMARY.

Specifying any of the logging options can impact system performance.

Only the default set of query 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 these options and option modifiers, see “Tracking Query Behavior with Database Query Logging” in Teradata Vantage™ - Database Administration, B035-1093.

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

The 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 the DBQL information 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 name 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 request. 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

Full text of all SQL statements is logged.

You can specify this option with THRESHOLD.

STATSUSAGE

Query optimizer statistics and usage recommendations are logged in an XML document for optimized 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:

REPLACE 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:

REPLACE 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

The VERBOSE EXPLAIN text for a request is logged in XML format. Also includes details on SpoolAsgnList and HashFields that are not available with EXPLAIN.

The XML schema for the log this option produces is located at:

http://schemas.teradata.com/queryplan/queryplan.xsd