WITH Logging Options - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantage™

The WITH logging option that can be used with the BEGIN/REPLACE QUERY LOGGING statements enables logging into the appropriate tables as listed below.

The DBQL option… Logs to…
WITH ALL the following tables:
  • DBC.DBQLogTbl
  • DBC.DBQLExplainTbl
  • DBC.DBQLObjTbl
  • DBC.DBQLStepTbl
  • DBC.DBQLSQLTbl
WITH EXPLAIN
  • DBC.DBQLogTbl
  • DBC.DBQLExplainTbl
WITH FEATUREINFO DBC.DBQLogTbl
WITH LOCK= n DBC.DBQLXMLLockTbl
WITH [NO COLUMNS] OBJECTS
  • DBC.DBQLogTbl
  • DBC.DBQLObjTbl
WITH NONE nothing. This option disables logging on the specified user or account, list of users or accounts, or application.
WITH PARAMINFO DBC.DBQLParamTbl
WITH SQL
  • DBC.DBQLogTbl
  • DBC.DBQLSQLTbl
WITH [DETAILED] STATSUSAGE DBC.DBQLXMLTbl
WITH STEPINFO
  • DBC.DBQLogTbl
  • DBC.DBQLStepTbl
WITH USECOUNT DBC.ObjectUsage
WITH UTILITYINFO DBC.DBQLUtilityTbl
WITH [VERBOSE] XMLPLAN
  • DBC.DBQLogTbl
  • DBC.DBQLXMLTbl
The WITH ALL option enables logging into all of the DBQL tables with the exception of DBQLXMLTbl and DBQLXMLLOCKTbl. The WITH NONE disables logging on the specified user/account, list of users, account list, or application.

The following table describes the different WITH options available in more detail.

Parameter Logging Behavior
WITH ALL Use the WITH ALL option sparingly and only for selected users because it can consume excessive CPU resources and grow the logs (which consume DBC PERM space) very quickly.

WITH ALL logs the information generated by these WITH rules: EXPLAIN, OBJECTS, SQL, and STEPINFO. It does not log the information generated by XMLPLAN, LOCK, FEATUREINFO, STATSUSAGE, PARAMINFO, and UTILITYINFO. The WITH ALL option generates:

  • One default row per query in DBQLogTbl that includes the first 200 characters of the SQL statement, unless you define LIMIT SQLTEXT=0.
  • One row per target object per query in DBQLObjTbl.
  • One row per step per query in DBQLStepTbl.
  • One or more rows per SQL statement in the DBQLSQLTbl.
  • One or more rows per query in DBQLExplainTbl.
WITH EXPLAIN This option generates the EXPLAIN for the query and logs it into the DBQLExplainTbl. If the additional EXPLAIN text is greater than 31,000 characters, multiple rows are generated and the ExpRowNo field indicates the row number.

This option inserts a default row into DBQLogTbl.

WITH EXPLAIN generates and logs the unformatted EXPLAIN text for each request. It does not generate EXPLAIN text for requests preceded by the EXPLAIN request modifier, nor does it log rows for cached requests.

You cannot specify the WITH EXPLAIN option with the SUMMARY or THRESHOLD options.
WITH FEATUREINFO This option captures which features an SQL request has used, and logs that information into the FeatureUsage column of DBQLogTbl. The option captures only use of features visible on the parsing engine (PE). If a feature is visible on the AMPs only, this option does not capture information about that feature. This option does not track third-party application use of database features.
WITH LOCK=n Logs in XML format in DBQLXMLLOCKTbl any lock contention longer than n centiseconds. The minimum acceptable value for n is 5.

You can access the DBQL lock log through the Viewpoint lock viewer portlet or query the system table DBC.DBQLXMLLOCKTbl or the view DBC.QrylockLogXML[V].

The DBQL lock logging rule active at the time a session logs on is applied for the duration of the session, even if lock logging is disabled or enabled after the session logs on.
WITH NONE This option causes no logging to take place for the specified user, user/account pair, user/account list, application name, or list of application names.

For example, if there is a rule for default logging on ALL users, you can exclude logging for a specific user user1, by submitting the following statement:

BEGIN QUERY LOGGING WITH NONE ON user1;
WITH [NO COLUMNS]
OBJECTS This option logs database, table, column, and index information in DBQLObjTbl.

This option can be used with SUMMARY or THRESHOLD.

This option inserts:
  • A default row in DBQLogTbl
  • One row per target object per query in DBQLObjTbl

Use this option selectively. Object data is useful for analyzing queries that make heavy use of join indexes and indexed access, but it can generate many rows.

By default, the WITH OBJECTS option logs a separate row for each column referenced in a query. The NO COLUMNS suboption turns off column logging. This suboption is especially useful for tables with many columns. Use of NO COLUMNS reduces overhead and controls the size of the DBQLObjTbl table.

Any DBC database tables and columns used by the system while processing a query are not reflected in the DBQL object rows for that query. If a statement accesses a DBC table, the DBC table will not appear. This means, for example, that statements like CREATE TABLE or SELECT FROM DBC.xxx do not have objects logged through DBQL because all objects referenced by the plan generated for the statement are DBC tables and columns. However, other objects that are accessed by the statement will be logged in the DBC.DBQLObjTbl.

Also note that certain optimized requests may bypass the Dispatcher so that DBQL will not log them.

The names of macros, views and triggers will be displayed. The tables and columns mentioned in the macro or view will be those of the base table, not the field names in the macro or view. Objects in DBQLObjTbl are those that the Optimizer actually accesses, not necessarily the objects that appear in an SQL statement.

WITH PARAMINFO This option logs parameterized request variable names, types, positions, and values into DBQLParamTbl.
WITH SQL This option logs the full text of all SQL statements in DBQLSqlTbl. It inserts:
  • A default row in DBQLogTbl.
  • The entire SQL statement for each request for each user being logged. Large statements can cause multiple rows to be written to log the full query text.
This option can be used with THRESHOLD.

DBQL is limited to logging information about base tables and logging direct SQL statements. Macros, views and triggers do not result in complete logging information. For example, SQL statements within a trigger are not logged by DBQL. However, the SQL statement that triggers the action will be logged.

If you set LIMIT SQLTEXT=0 when you specify the WITH SQL option, you avoid duplicate SQL in the DBQLogTbl.
WITH [DETAILED] STATSUSAGE This option logs query optimizer statistics and usage recommendations for DML requests that undergo query optimization. Logged data is stored as an XML document in DBC.DBQLXMLTbl and is accessible from DBC.QryLogXML[V] view. WITH [DETAILED] STATSUSAGE does not log these requests: EXPLAIN, INSERT EXPLAIN, and DUMP EXPLAIN.

Enable this option to provide data to automated statistics features, in particular the AnalyzeStatsUsage open API. Teradata recommends that you access the open APIs via the Teradata Viewpoint Stats Manager portlet. For more information, see About Automated Statistics Management.

If logging option XMLPLAN is also enabled, the data from both options is logged in one document. If only STATSUSAGE is enabled and the optimized statement has no relevant statistics usage data, no document is logged to reduce overhead.

This option requires the GRANT EXECUTE privilege on DBC.dbqlaccessmacro. This option is not enabled when you specify the ALL option.

The DETAILED suboption logs in XML format summary statistics details from the existing statistics for all objects referenced in the query plan.

The DETAILED suboption logs the following statistics details:
  • StatTimeStamp
  • Version
  • OrigVersion
  • NumColumns
  • NumBValues
  • NumEHIntervals
  • NumHistoryRecords
  • NumNulls
  • NumAllNulls
  • NumAMPs
  • NumPNullDistinctVals
  • AvgAmpRPV
  • PNullHighModeFreq
  • AvgAmpRPV
  • HighModeFreq
  • NumDistinctVals
  • NumRows
  • CPUUsage
  • IOUsage

DETAILED does not support geospatial statistics.

WITH STEPINFO This option logs AMP step-level information in DBQLStepTbl. When the query completes, it logs one row for each query step, including parallel steps.

The WITH STEPINFO option inserts a default row into DBQLogTbl.

This option can be used with THRESHOLD.
WITH UTILITYINFO This option logs utility information to DBC.DBQLUtilityTbl for the following utilities:
  • FastLoadProtocol: FastLoad, Teradata Parallel Transport (TPT) Load operator, and JDBC FastLoad.
  • MLOAD Protocol: MultiLoad and TPT Update operator.
  • MLOADX Protocol: TPT Update operator.
  • FastExport Protocol: FastExport, TPT Export operator, and JDBC FastExport.
  • Data Stream Architecture.
WITH USECOUNT This option logs use count information 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.

You cannot enable the WITH USECOUNT option on account strings.
WITH [VERBOSE] XMLPLAN This option logs the query plan in XML format in DBQLXMLTbl and inserts a default row into DBQLogTbl.

For example, the following statement logs the XML plan for all DML statements performed by User1.

BEGIN QUERY LOGGING WITH XMLPLAN LIMIT SQLTEXT=0 on User1;

This option does not log query plans for EXPLAIN request modifiers, DDL requests, or for the INSERT EXPLAIN and DUMP EXPLAIN statements.

Because the XML plan includes the query and EXPLAIN text, Teradata recommends that when you use the XMLPLAN option, do not also use the WITH EXPLAIN and WITH SQL options. Set SQLTEXT to 0 (SQLTEXT=0) to avoid redundant logging if query and EXPLAIN text is not needed for DDL statements.

The XML schema for the log this option produces is maintained at http://schemas.teradata.com/queryplan/queryplan.xsd.

  • WITH XMLPLAN cannot be used with the THRESHOLD and SUMMARY limit options.
  • The VERBOSE suboption logs the VERBOSE EXPLAIN text of a query in XML format and contains details on SpoolAsgnList and HashFields that are not available with just EXPLAIN.

For more information on the SQL syntax, see “BEGIN QUERY LOGGING” or “REPLACE QUERY LOGGING” in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.