Overview of Collection Options | Teradata Vantage - Overview of Collection Options - Analytics Database - Teradata Vantage

Database Administration

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-11-03
dita:mapPath
pgf1628096104492.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
ujp1472240543947
lifecycle
latest
Product Category
Teradata Vantage™

DBQL is flexible enough to log information on the variety of SQL requests that run on Vantage, from short transactions to longer-running analysis and mining queries.

Options enable you to control the volume and detail of the logged data. You can define rules, for example, that log the first 5,000 characters of any query that runs during a session invoked by a specific user under a specific account. Or you could define a rule to log queries that take more time to complete than the specified time threshold. You could even log queries that exceed a specific CPU time specified in hundredths of a second. For example, specify “THRESHOLD=500 CPUTime” for queries that take longer than five seconds of AMP CPU time to complete.

The WITH Logging Options controls what details the system logs. The LIMIT Logging Options controls how much of information the system logs. Collection options include:
  • Default logging reports for each query with at least the leading SQL characters, the time of receipt, the number of processing steps completed, the time the first step was dispatched, the times the packets were returned to the host, and the number of rows updated, inserted, and deleted.
  • Summary logging reports the count of all queries that completed processing time within specified time intervals, I/O criteria, or CPU usage criteria.
  • Threshold logging can log a combination of default and summary data:
    • Default data for each query that ran beyond the threshold limit
    • Summary counts of all queries that ran within the threshold time

      Use elapsed time, I/O counts, or CPU time as a criterion for threshold logging of details or summary

  • Detail logging, which includes:
    • Default data
    • Step level activity, including parallel steps
    • Object usage per query
    • Full SQL text
    • Explain text
    • XML query plan
  • Preventing logging for a specific user, user/account pair, user/account list, or application name.

Logging Rules for Applications

Enable or disable logging for a utility by specifying a rule for the application name. The system logs information for any user that logs on under the application name. For example, to log all queries that result from a FastLoad job, submit:

BEGIN QUERY LOGGING ON APPLNAME= 'FASTLOAD';

DBQL first searches for a rule for an application name before searching for rules for the user and account.

Creating logging rules by application name allows control over certain utilities, such as load and unload utilities, regardless of what user/account that the application runs under. For example, if you run utilities under the same users and accounts that are used for interactive queries, creating logging rules for utilities allows you to specify minimal logging for utilities and more extensive logging for interactive users.

The application names you specify with the APPLNAME option are the names the system passes in the reserved query band UtilityName.

The following table lists the UtilityName strings.

Client/DBS Protocol Utility Type Utility Name Value
FastExport Standalone FastExport FASTEXP
Teradata Parallel Transporter EXPORT operator TPTEXP
JDBC FastExport JDBCE
.NET FastExport DOTNETE
FastLoad Standalone FastLoad FASTLOAD
Teradata Parallel Transporter LOAD operator TPTLOAD
JDBC FastLoad JDBCL
.NET FastLoad DOTNETL
Crashdumps Save Program (CSP) Save Dump CSPLOAD
MultiLoad Standalone MultiLoad MULTILOAD
Teradata Parallel Transporter UPDATE operator TPTUPD
JDBC MultiLoad JDBCM
.NET MultiLoad DOTNETM