Specifies logging options for the request objects, the AMP steps produced to perform the request, the EXPLAIN text for the request, the SQL request text, or the optimizer plan for the request as an XML document.
You cannot specify any logging option if you specify LIMIT SUMMARY.
Specifying any of the logging options can have a performance impact on your system.
Only the default set of request 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 the logging options and option modifiers, see Teradata Vantage™ - Database Administration, B035-1093. You can set the following logging options.
- 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
- EXPLAIN text for the request is logged.
- 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 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 string list
- ALL without any other specification, which specifies all accounts
- OBJECTS
- Database, table, column, and index information is logged.
- 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.
- SQL
- The full text of all SQL statements is logged.
- STATSUSAGE
- Optimizer statistics and usage recommendations are logged in an XML document for DML requests.
- 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
- STEPINFO
- AMP step-level information is logged.
- 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.
- 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.
- VERBOSE XMLPLAN
- VERBOSE EXPLAIN text for a request is logged in XML format. Also includes details on SpoolAsgnList and HashFields that are not available with only EXPLAIN.