- logging-option
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 SQL Statements to Control Logging . You can set the following logging options.
- OBJECTS
- Database, table, column and index information
in DBQLObjTbl.
- SQL
- Log the full text of all SQL statements in DBQLSqlTbl.
- STEPINFO
- Log AMP step-level information in DBQLStepTbl.
- EXPLAIN
- Generate and log the 'Explain' for the query in DBQLExplainTbl.
- XMLPLAN
- Log the query plan in XML format in DBQLXMLTbl.
- LOCK=n
- Log any lock contention that exceeds n centiseconds in XML format in DBQLXMLLOCKTbl.
- STATSUSAGE
- Log query optimizer statistics usage and recommendations.
- PARAMINFO
- Log parameter information and data parcels into DBQLParamTbl.
- FEATUREINFO
- Log feature usage information into FeatureUsage column of DBQLogTbl.
- USECOUNT
- log object use counts.
- UTILITYINFO
- Log utility information in DBQLUtilityTbl.
- VERBOSE
- Log verbose explain and related information in XML format. It is a sub-option to XMLPLAN.
- DETAILED
- Log summary statistics details from the interval zero of the histogram for the existing stats on all objects referenced in the query plan. It is a sub-option to STATSUSAGE option.
- NO COLUMNS
- Sub-option to the OBJECTS logging rule to control the logging of column information.
- ALL
- Log object, step information, explain, and SQL.
- NONE
- Do not enable DBQL for specified applications, users, or accounts.
- mode=m
- Specifies the CPU and I/O data collection mode algorithm to
use:
m Algorithm 0 Use default for this release. 1 Classic V2R6 algorithm 1 for step adjustments. 2 AMP algorithm 2, diagnostic only. 3 (default) AMP algorithm 3, which retrieves statistics on parallel steps using AMP coordination and captures aborted step usage data. If MODE is specified, "m" (an integer) is required. If MODE is not specified, mode is controlled by DBSControl general parameter for "DBQL CPU/IO Collection". ERRTEQDBQLMODENOTALLOWED (9573) is returned on the SQL request if you are not authorized to use.
- DETAILDIAG=n
- Specifies the diagnostic flags value to control aspects of
detailed diagnostic logging.
n Algorithm 128 AMPDataDiag - Log granular AMP and STEP DBQL data to DBC.DBQLAmpDataTbl table. 256 ExtDBQLData - Log extended DBQL data to DBC.DBQLAmpDataTbl table (includes granular DBQL data as well). 1024 SumThreshOverride - Enables override of SUMMARY and THRESHOLD Options and automatically enables Detail Logging and Step Level Logging when SLA is not met for a particular request. - STEPETTHRESHOLD=s
- Specifies a threshold value of elapsed time in
units of microseconds. The default value is 0. If this value is
set to a non-zero value, DBQL diagnostics collected into
DBC.DBQLAmpDataTbl will be controlled on the AMP side. If the
step elapsed time is above the set threshold, then that step's
stats will be collected and logged. This parameter requires
DETAILDIAG = 128 or 256 to be enabled.
Maximum value: 4,294,967,295
- limit-option
- Specifies boundary conditions for query logging:
limit-option Description SQLTEXT[=n] Maximum number of SQL text characters in DBQLogTbl. Without n: 10,000 characters, n=0 no SQL. SUMMARY=n1,n2,n3 [units] Specifies the number of queries for a session that fall into each of four intervals for the DBQLSummaryTbl. THRESHOLD[=n] [units] Specifies the queries that run in n 'units' or less are counted in DBQLSummaryTbl; others are logged in DBQLogTbl. Without n: 5 'units'. Units may be:Units Description CPUTIME Summary or Threshold is based on CPU time where n,n1,n2,n3 is specified in hundredths of a second. CPUTIMENORM Summary or Threshold is based on normalized CPU time where n,n1,n2,n3 is specified in hundredths of a second. ELAPSEDTIME Summary or Threshold is based on elapsed time where n,n1,n2,n3 is specified in hundredths of a second. IOCOUNT Without units:Unit Description ELAPSEDDESC Summary or Threshold is based on elapsed time where n,n1,n2,n3 is specified as seconds. - LIMIT
- SUMMARY cannot be specified with THRESHOLD.
Options cannot be specified with SUMMARY.
If options are specified with THRESHOLD, the allowed options are SQL, STEPINFO, and OBJECTS.
- USECOUNT
- This option is exclusive to databases. No other logging or limit options may be used on databases or in conjunction with the USECOUNT option on databases.
- ON
- Account string information can be entered for ALL or for a single user name. With a list of users, no account string can be specified. With an application name or list, no user or account can be specified.