Logging options that you set with BEGIN QUERY LOGGING are recorded in the DBQLOptions field of DBQLRuleTbl.
Logging Option | Mask Value | Indicator Bit Position |
---|---|---|
EXPLAIN | 2 | 2 |
SQL | 4 | 3 |
OBJECTS | 8 | 4 |
STEPINFO | 16 | 5 |
XMLPLAN | 32 | 6 |
SUMMARY | 64 | 7 |
THRESHOLD | 128 | 8 |
STATSUSAGE | 256 | 9 |
LOCK | 512 | 10 |
VERBOSE | 1024 | 11 |
DETAILED STATSUSAGE | 2048 | 12 |
USECOUNT | 4096 | 13 |
UTILITYINFO | 8192 | 14 |
PARAMINFO | 16384 | 15 |
Option | Definition |
---|---|
ALL | Log query object information, step information, EXPLAIN text, and all SQL requests for all logged on users. This is equivalent to specifying the EXPLAIN, OBJECTS, SQL, and STEPINFO options individually. The ALL option does not include LOCK, PARAMINFO, STATSUSAGE, XMLPLAN, or UTILITYINFO. This option does not invoke the SUMMARY or THRESHOLD limit options. You cannot specify the ALL logging option if you also specify the THRESHOLD limit option. If you specify ALL, then you should also specify a value of 0 for SQLTEXT to ensure that the text for the SQL request is not logged in both the DBC.DBQLogTbl and DBC.DBQLSqlTbl tables. If you specify ALL, then you cannot specify other logging options. |
EXPLAIN | Log the unformatted EXPLAIN text for the request. You cannot specify the EXPLAIN logging option if you also specify the THRESHOLD limit option. This option generates and logs the unformatted EXPLAIN text for each request. It does not generate EXPLAIN text for requests preceded by the EXPLAIN request modifier. For example, the system does not log EXPLAIN text for the following request. EXPLAIN SELECT *
FROM table_a ; /* EXPLAIN text logging
does not occur */
If you perform the same query without the preceding EXPLAIN modifier, however, the system does log unformatted EXPLAIN text. SELECT * FROM table_a; /* EXPLAIN text logging does occur */ Use care when specifying this option because of the performance cost of generating the EXPLAIN text for a query. |
LOCK=n | Log any lock contention that exceeds n hundredths of a second in XML format in DBQLXMLLockTbl. For more information on DBQLXMLLockTbl, including how to shred the lock plan data, see Teradata Vantage™ - Database Administration, B035-1093. |
OBJECTS | Log information about databases, tables, columns, and indexes accessed by SQL requests for the specified user set. You can specify the OBJECTS logging option if you also specify the THRESHOLD limit option. This option does not log activity for macros or views, or log activity for dictionary tables and columns. For example, CREATE TABLE does not log any objects in the DBQL system because the statement only generates dictionary objects. For example, a query like the following does not log any objects because the query only accesses dictionary tables. SELECT * FROM DBC.TVFields; |
PARAMINFO | Parameter values and metadata are logged in DBQLParamTbl. |
SQL | Log the full text of all SQL requests performed by the specified user set in system table DBC.DBQLSqlTbl. You can specify the SQL logging option if you also specify the THRESHOLD limit option. This option does not log SQL requests within SQL procedures, macros, views, or triggers. |
STATSUSAGE | Log DML requests that require cardinality estimation in their query optimization as an XML document in system table DBC.DBQLXMLTbl. If you specify STATSUSAGE with XMLPLAN, the database logs the collected data in a single integrated document. Data logged with this option is independent of the THRESHOLD or SUMMARY option limit options. STATSUSAGE does not log activity for explained requests or for the DUMP EXPLAIN and INSERT EXPLAIN statements. For information about these statements and the EXPLAIN request modifier, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146. The ALL option does not include STATSUSAGE. |
STEPINFO | Log AMP step-level information for all SQL requests performed by the specified user set in system table DBC.DBQLStepTbl. You can specify the STEPINFO logging option if you also specify the THRESHOLD limit option. |
USECOUNT | Log use count collection 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. Otherwise, the system returns an error to the requestor. When you submit a BEGIN QUERY LOGGING request, the use counts and timestamps for the database or user are reset. |
UTILITYINFO | Utility information is logged in DBC.DBQLUtilityTbl. |
XMLPLAN | Logs the query plan generated by the Optimizer for SQL DML requests as an XML document in system table DBC.DBQLXMLTbl. When logging problematic queries, the recommended best practice is to specify XMLPLAN logging. The XMLPLAN option captures the query plan with additional detail to assist diagnosing performance issues. If you specify XMLPLAN with STATSUSAGE, the database logs the collected data in a single integrated document. Logs basic information, such as StatementType and the corresponding StepNames, for DDL statements. In addition, detailed information is logged for:
XMLPLAN also logs detailed information for FastLoad and MultiLoad jobs. Because the XML document includes the query and step text, you usually do not need to specify the EXPLAIN and SQL options if you specify XMLPLAN. You should also specify a value of 0 for SQLTEXT to avoid redundant logging when you specify XMLPLAN. The XMLPLAN option does not log query plans for EXPLAIN request modifiers, or for INSERT EXPLAIN and DUMP EXPLAIN requests. The ALL option does not include XMLPLAN. You cannot specify the XMLPLAN logging option if you also specify the THRESHOLD limit option. |