The WITH logging option that can be used with the BEGIN/REPLACE QUERY LOGGING statements enables logging into the appropriate tables:
| DBQL Option | Logs to These Tables |
|---|---|
| WITH ALL |
|
| WITH EXPLAIN |
|
| WITH FEATUREINFO | DBC.DBQLogTbl |
| WITH LOCK= n | DBC.DBQLXMLLockTbl |
| WITH [NO COLUMNS] OBJECTS |
|
| WITH NONE | None. This option disables logging on the specified user or account, list of users or accounts, or application. |
| WITH PARAMINFO | DBC.DBQLParamTbl |
| WITH SQL |
|
| WITH [DETAILED] STATSUSAGE | DBC.DBQLXMLTbl |
| WITH STEPINFO |
|
| WITH USECOUNT | DBC.ObjectUsage |
| WITH UTILITYINFO | DBC.DBQLUtilityTbl |
| WITH [VERBOSE] XMLPLAN |
|
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. The option 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:
|
| WITH EXPLAIN | This option generates the EXPLAIN for the query and logs the query 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 by querying 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 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 does 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 are logged in the DBC.DBQLObjTbl.
Certain optimized requests may bypass the Dispatcher so that DBQL does not log them. The names of macros, triggers, and views are displayed. The tables and columns in the macro or view are those of the base table, not the field names in the macro or view. Objects in DBQLObjTbl are those that the Optimizer accesses, not necessarily the objects that appear in an SQL statement. Objects that represent base tables include a Target Indicator. Its value is set to Y when the table is the target of a write operation in the current request.
Write operations can occur through SQL INSERT, UPDATE, or DELETE statements, or when using the MultiLoad Utility.
|
| 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, inserting the following:
This option can be used with THRESHOLD.
DBQL is limited to logging information about base tables and logging direct SQL statements. Macros, triggers, and views do not cause complete logging information. For example, SQL statements within a trigger are not logged by DBQL. However, the SQL statement that triggers the action are 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. INSERT EXPLAIN and DUMP EXPLAIN are supported only on the Block File System on the primary cluster, not the Object File System.
Enable this option to provide data to automated statistics features such as the AnalyzeStatsUsage open API. 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, this option 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 in SchemaFiles.zip under attachments in the left pane.
|