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:
|WITH LOCK= n||DBC.DBQLXMLLockTbl|
|WITH [NO COLUMNS] OBJECTS||
|WITH NONE||nothing. This option disables logging on the specified user or account, list of users or accounts, or application.|
|WITH [DETAILED] STATSUSAGE||DBC.DBQLXMLTbl|
|WITH [VERBOSE] XMLPLAN||
The following table describes the different WITH options available in more detail.
|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
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:
|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:
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:
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:
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:
|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.
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.